I'm working on rewriting some old code to be more efficient and transparent, as I've learned a great deal since I last wrote it. The code takes some dataframes and writes reports onto different tabs in an Excel document using xlsxwriter. Previously, I painstakingly wrote out instructions for every new sheet and every single dataframe, and I'd prefer this time to simplify that. I intend to create a function which creates a new sheet, and another function which will write a dataframe to that worksheet.
My test is found below:
#Creating the Report.xlsx and establishing formatting options.
wb=xl.Workbook('Specialty Programs Actual vs. Expected {}.{}.{}.xlsx'.format(reportdate.month,reportdate.day,reportdate.year))
title=wb.add_format({'font_size':16,'font_name':'Calibri','align':'center','border':1,'border_color':'white'})
subtitle=wb.add_format({'font_size':14,'font_name':'Calibri','align':'center','border':1,'border_color':'white'})
blank=wb.add_format({'bg_color':'white'})
black=wb.add_format({'bg_color':'black'})
bold=wb.add_format({'bold':True,'border_color':'white','border':1})
centerbold=wb.add_format({'bold':True,'border_color':'white','border':1,'align':'center'})
boldright=wb.add_format({'bold':True,'border_color':'white','border':1,'align':'right'})
lobtitle=wb.add_format({'bold':True,'italic':True,'font_size':14,'border_color':'white','border':1})
wrap=wb.add_format({'text_wrap':True})
headers=wb.add_format({'bold':True,'text_wrap':True,'bg_color':'#DCDCDC','align':'center'})
nsm_states=wb.add_format({'italic':True,'text_wrap':True,'align':'center'})
percent = wb.add_format({'num_format': '0.0%', 'border': 1, 'border_color': 'white'})
integer = wb.add_format({'num_format': '#,##0', 'border': 1, 'border_color': 'white'})
shadepercent = wb.add_format({'num_format': '0.0%','border':1,'border_color':'#DCDCDC','bg_color':'#DCDCDC'})
shadeinteger = wb.add_format({'num_format': '#,##0','border':1,'border_color':'#DCDCDC','bg_color':'#DCDCDC'})
shadebold=wb.add_format({'bold':True,'border_color':'#DCDCDC','bg_color':'#DCDCDC'})
gridinteger=wb.add_format({'num_format': '#,##0','border':1,'border_color':'gray'})
gridpercent = wb.add_format({'num_format': '0.0%','border':1,'border_color':'gray'})
smallinteger = wb.add_format({'num_format': '#,##0', 'border': 1, 'border_color': 'white','font_size':9})
smallpercent = wb.add_format({'num_format': '0.0%', 'border': 1, 'border_color': 'white','bottom_color':'black','font_size':9})
smalltext=wb.add_format({'font_size':9,'font_name':'Calibri','align':'center','border':1,'border_color':'white'})
def page_setup(tab,title):
global ws
ws=wb.add_worksheet(tab)
for j in range(100):
ws.set_row(j,None,blank)
ws.set_column('B:K',15)
ws.write(0,5,'Old Republic Specialty Insurance Underwriters',title)
ws.write(1,5,title,subtitle)
ws.write(2,5,'for {},{}'.format(reportdate.month_name(), reportdate.year),subtitle)
def write(df,formats,row,col,row_format,col_format):
r=row+1
for y in df.index.to_list():
c=col+1
for x in df.columns.to_list():
ws.write(r,c,df.loc[y,x],formats)
c+=1
ws.write(r,col,y,row_format)
r+=1
c=col+1
for x in df.columns.to_list():
ws.write(row,c,x,col_format)
c+=1
#Test for dataframe 'booked'
page_setup('Booked Premium','Booked Premium')
write(booked,integer,8,2,bold,headers)
wb.close()
When I run this, I get the error:
---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
<ipython-input-257-116331d62c10> in <module>
----> 1 wb.close()
~\AppData\Roaming\Python\Python39\site-packages\xlsxwriter\workbook.py in close(self)
320
321 try:
--> 322 self._store_workbook()
323 except IOError as e:
324 raise FileCreateError(e)
~\AppData\Roaming\Python\Python39\site-packages\xlsxwriter\workbook.py in _store_workbook(self)
710 packager._set_tmpdir(self.tmpdir)
711 packager._set_in_memory(self.in_memory)
--> 712 xml_files = packager._create_package()
713
714 # Free up the Packager object.
~\AppData\Roaming\Python\Python39\site-packages\xlsxwriter\packager.py in _create_package(self)
134 self._write_root_rels_file()
135 self._write_workbook_rels_file()
--> 136 self._write_worksheet_files()
137 self._write_chartsheet_files()
138 self._write_workbook_file()
~\AppData\Roaming\Python\Python39\site-packages\xlsxwriter\packager.py in _write_worksheet_files(self)
190 worksheet._set_xml_writer(self._filename('xl/worksheets/sheet'
191 + str(index) + '.xml'))
--> 192 worksheet._assemble_xml_file()
193 index += 1
194
~\AppData\Roaming\Python\Python39\site-packages\xlsxwriter\worksheet.py in _assemble_xml_file(self)
3957 # Write the worksheet data such as rows columns and cells.
3958 if not self.constant_memory:
-> 3959 self._write_sheet_data()
3960 else:
3961 self._write_optimized_sheet_data()
~\AppData\Roaming\Python\Python39\site-packages\xlsxwriter\worksheet.py in _write_sheet_data(self)
5588 else:
5589 self._xml_start_tag('sheetData')
-> 5590 self._write_rows()
5591 self._xml_end_tag('sheetData')
5592
~\AppData\Roaming\Python\Python39\site-packages\xlsxwriter\worksheet.py in _write_rows(self)
5781 if col_num in self.table[row_num]:
5782 col_ref = self.table[row_num][col_num]
-> 5783 self._write_cell(row_num, col_num, col_ref)
5784
5785 self._xml_end_tag('row')
~\AppData\Roaming\Python\Python39\site-packages\xlsxwriter\worksheet.py in _write_cell(self, row, col, cell)
5946 if cell.format:
5947 # Add the cell format index.
-> 5948 xf_index = cell.format._get_xf_index()
5949 attributes.append(('s', xf_index))
5950 elif row in self.set_rows and self.set_rows[row][1]:
AttributeError: 'str' object has no attribute '_get_xf_index'
When I run the same instructions outside of a function, it creates the sheet and writes the dataframe without issue. What about the function approach is causing this issue, and how can I fix it? Thanks so much for the help!