How to fill a block of cells in xlsxwriter?

user_12421023 0
Viewed 144

I had a problem with my xlsxwriter code, it works perfectly fine but I need to figure out how to select a block of cells. For example - from A1 to J10 as it depicted on screenshot. enter image description here

Does xlsxwriter have such a function? I've searched several formats, such as: worksheet.write('A1:J1', '...') But it write only on A1. So for example - how can I fill all highlighted area with one word without writing code for all of 10 rows?

xlsxwriter python
1 Answers

Here is how I write my output from a pandas dataframe to an excel template. Please note that if data is already present in the cells where you are trying to write the dataframe, it will not be overwritten and the dataframe will be written to a new sheet which is my i have included a step to clear existing data from the template. I have not tried to write output on merged cells so that might throw an error.

Setup

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
file_path='Template.xlsx'
book=load_workbook(file_path)
writer = pd.ExcelWriter(file_path, engine='openpyxl')
writer.book = book
sheet_name="Template 1"
sheet=book[sheet_name]

Set first row and first column in the excel template where output is to be pasted. If my output is to be pasted starting in cell N2, row_start will be 2 and col_start will be 14

row_start=2
col_start=14

Clear existing data in excel template

for c_idx, col in enumerate(df.columns,col_start):
    for r_idx in range(row_start,10001):
        sheet.cell(row=r_idx, column=c_idx, value="")

Write dataframe to excel template

rows=dataframe_to_rows(df,index=False)
for r_idx, row in enumerate(rows,row_start):
    for c_idx, col in enumerate(row,col_start):
        sheet.cell(row=r_idx, column=c_idx, value=col)

writer.save()
writer.close()
user_16730590
Related