I have Python 3.11.9 on Windows 10.
I created a test program to write a small spreadsheet, columns E and F should be formatted as the format dlrformat but they aren’t. I have a header row and my understanding is that when using writer.sheets[sheetname].set_column('E:F', 12, dlrformat) then it formats the columns as given but ignores the first row, the header row. The header row (row 0) contains only text.
Anyone know what I am doing wrong here? The spreadsheet is getting written but no formats are applied to any column where .set_column() was used.
Here is my test program.
'''
Install these first: pip install pandas xlsxwriter
Purpose: This writes a test spreadsheet called "test.xlsx". Columns D-E should be formatted as dollars but are not.
OUTPUT COLUMNS WITH TABLE and column headers of output file:
- Sales Person Name and code
- Proforma date
- Job num
- Proforma amount (this includes amounts of zero)
- Total cost
- Proforma reason
'''
import inspect
import sys
import time
#####################################################
def savedataxlsx2():
r'''Save proforma output data as Excel XLSX file.
Add in column widths.
'''
import pandas
import xlsxwriter
procname = str(inspect.stack()[0][3]) + ":" # Returns function name.
maxcol = 6
proformadatecol = 2
DATASEP = r'\t'
cnt = 0
datadict = {}
headersxl = ['Sales rep', 'Proforma date', 'Job id', 'Proforma amt', 'Cost', 'Reason']
# Init datadict with values.
datadict = {'12345': 'Jim Sales' + DATASEP + '12/1/24' + DATASEP + '12345' + DATASEP + str(130.8) + DATASEP + str(99.88) + DATASEP + "no reason",
'12346': 'Joan Sales' + DATASEP + '12/2/24' + DATASEP + '12346' + DATASEP + str(1200.39) + DATASEP + str(500.31) + DATASEP + "no reason",
'12350': 'Jane Smith' + DATASEP + '12/3/24' + DATASEP + '12350' + DATASEP + str(788) + DATASEP + str(650.01) + DATASEP + "Reason A"
}
df = None # Dataframe
fn = "test.xlsx" # Output spreadsheet to write.
outdata = []
sheetname = 'Sheet1'
# Now append rest of data to outdata row-by-row.
for key, val in datadict.items():
if ((cnt % 200) ==0):
print(f"{procname} Checking record {cnt} for long rows...")
t = datadict[key]
tlist = t.split(DATASEP) # Convert one row to a list.
while len(tlist) < maxcol:
tlist.append('')
# print(f"{procname} Item {cnt} Appended blank to jobid {key}")
if len(tlist) > maxcol:
tlist = tlist[:maxcol]
print(f"{procname} Cut too long array down for jobid {t}")
outdata.append(tlist) # Save one row
cnt += 1
z = 0
try:
df=pandas.DataFrame(outdata, columns=headersxl) # Make a dataframe from the list of lists.
except ValueError as e:
print(f"{procname} Ignoring ValueError...")
time.sleep(0.5)
except Exception as e:
sys.exit(f"{procname} ERROR-dataframe2: {e}")
if type(df) == None:
print(f"{procname} ERROR making dataframe.")
sys.exit(1)
(numrows, numcols) = df.shape
try:
writer = pandas.ExcelWriter(fn, engine='xlsxwriter', date_format='MM/DD/YYYY')
except PermissionError as e:
print(f"{procname} ERROR: Please close the spreadsheet before running this.")
sys.exit()
except Exception as e:
sys.exit(f"{procname} ERROR on pandas.ExcelWriter: {e}")
try:
df.to_excel(writer, sheet_name=sheetname, header=headersxl, index=False, freeze_panes=(1,0) ) # Save to file.
except Exception as e:
sys.exit(f"{procname} ERROR on df.to_excel: {e}")
workbook = writer.book
worksheet = writer.sheets[sheetname]
header1 = "&L&F - &A"
footer1 = "&LPrinted &D &T&RPage &P"
worksheet.set_header(header1)
worksheet.set_footer(footer1)
myrange = f"A1:Z{numcols+1}"
worksheet.ignore_errors({'number_stored_as_text': myrange})
worksheet.set_landscape() # Set landscape
worksheet.set_margins(left=0.7, right=0.7, top=0.7, bottom=0.7)
worksheet.repeat_rows(0) # Set fixed row hdrs to repeat on every page.
# worksheet.repeat_columns(0)
# worksheet.print_row_col_headers() # Sets to True.
worksheet.print_across()
worksheet.set_print_scale(80) # Set pct size to print.
# worksheet.set_zoom(80) # View zoom factor/scale, a whole number.
# Add formats here.
# Format Docs at https://xlsxwriter.readthedocs.io/format.html
hdrformat = workbook.add_format({'bold':True, 'text_wrap':True, 'valign':'top', 'align':'left'})
dlrformat = workbook.add_format({'num_format': '$0.00', 'align':'right'}) # For dollar amounts.
numformat = workbook.add_format({'num_format': '0', 'align':'right'})
dateformat = workbook.add_format({'num_format': 'mm/dd/yy'})
red_text = workbook.add_format({'font_color':'red'}) # For (missing) cust code
worksheet.conditional_format('D1:D' + str(len(df)), {'type': 'text',
'criteria': 'containing',
'value': '(missing)',
'format': red_text})
worksheet.conditional_format('A1:A' + str(len(df)), {'type': 'text',
'criteria': 'containing',
'value': '(missing)',
'format': red_text})
writer.sheets[sheetname].set_column('A:A', 25)
writer.sheets[sheetname].set_column('B:B', 10, dateformat) # Date
writer.sheets[sheetname].set_column('C:C', 15, numformat) # Job right aligned
writer.sheets[sheetname].set_column('E:F', 12, dlrformat)
# Format rows in header only.
for col_num, value in enumerate(df.columns.values):
worksheet.write(0, col_num, value, hdrformat) # Write only row 0.
# workbook.close() # Save the file
writer.close()
# sz = getfilesize(fn) # in bytes
print(f"\n{procname} Wrote file to {fn} ")
z = 0 # DEBUG
return fn
#####################################################
#####################################################
fn = savedataxlsx2()
Thank you.