How can I set 50 column values of excel sheet dynamically using python-CodePudding

Actually, I'm trying to Write "Test" string Value from 1st column to 50th column( i.e A column to AZ column).

I can Write upto 26th Column (i.e Z column), below is my code:-

import pandas as pd

writer = pd.ExcelWriter("Sharath.xlsx", engine='xlsxwriter')
df1 = pd.DataFrame()
df1.to_excel(writer, sheet_name='Sheet1', header=True, index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

merge_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'border': 2,
            'align': 'center',
            'valign': 'vcenter'})

for i in range(0, 26):
    columnmerge = chr(ord('A')   i)   '{}:'   chr(ord('A')   i)   '{}'
    worksheet.set_column(chr(ord('A')   i)   ':'   chr(ord('A')   i), 20)
    worksheet.merge_range(columnmerge.format(2, 4), "Test1", merge_format)


workbook.close()
print("Excel file is closed")

writer.save()
print("Excel file is saved")
print("Report generation completed")

If I tried for 27th Column(i.e AA column) I'm getting below error:-

Traceback (most recent call last):
  File "D:\test2.py", line 18, in <module>
    worksheet.set_column(chr(ord('A')   i)   ':'   chr(ord('A')   i), 20)
  File "C:\Python39\lib\site-packages\xlsxwriter\worksheet.py", line 120, in column_wrapper
    _, col_1 = xl_cell_to_rowcol(cell_1)
  File "C:\Python39\lib\site-packages\xlsxwriter\utility.py", line 121, in xl_cell_to_rowcol
    col_str = match.group(2)
AttributeError: 'NoneType' object has no attribute 'group'

Can any one help me to write my string upto 50-100 columns dynamically.

Thanks in advance!

CodePudding user response：

Set the column width and merge a range using the column number:

worksheet.set_column(i, i, 20)
worksheet.merge_range(2, i, 4, i, "Test1", merge_format)

CodePudding user response：

All XlsxWriter methods take (row, col) values as well as A1 style ranges. When trying to set ranges programatically it is always best to avoid trying to construct A1 ranges and let the library do it for you based on zero-base row-column numbers.

For example here is how you could restructure your program to use arbitrary indices:

import pandas as pd

writer = pd.ExcelWriter('Sharath.xlsx', engine='xlsxwriter')
df1 = pd.DataFrame()
df1.to_excel(writer, sheet_name='Sheet1', header=True, index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet1']

merge_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'border': 2,
            'align': 'center',
            'valign': 'vcenter'})

first_col = 0
last_col = 100

worksheet.set_column(first_col, last_col, 20)

for col in range(last_col   1):
    worksheet.merge_range(1, col, 3, col, 'Test1', merge_format)

writer.save()

Output: