How to write a 1D array into an existing Excel file starting from a specific cell?-CodePudding

I have an existing excel file (with multiple sheets) with some data in it. I want to read the data in Python, do some calculations, and then output the result (1D array) into a certain column in a specific sheet starting from certain row.

For example, how can I output array = [1,2,3,4,5] to column E starting from the 10th row in Sheet3? Something look like this

CodePudding user response：

import xlsxwriter
import numpy as np

workbook  = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet('Sheet3')

worksheet.write_column('E10', np.array([1, 2, 3, 4, 5]))

workbook.close()

To edit an existing file, use the following code.

import openpyxl
import numpy as np
wb = openpyxl.load_workbook("test.xlsx")   
sheet = wb["Sheet3"]
arr = np.array([1, 2, 3, 4, 5])
r = 10
for i in range(len(arr)):
    sheet.cell(row = r   i, column = 5).value = arr[i]
wb.save("test.xlsx")

CodePudding user response：

Try something like that:

pip install pandas

import pandas as pd

data = pd.read_excel("data.xsl", sheet_name=1)
new_column_data = []
for row in data[column]:
     # calculation, then
     new_column_data.append(result)

data["new col"] = new_column_data

data.to_excel(path)

we importing pandas, then we load data. after that, we iterating through existing column and do some calculations you need. after all, we append result to new column and saving to new file.