Xlsxwriter: format three cell ranges in same worksheet

Question

I would like to format A1:E14 as US Dollars, F1:K14 as percentages and A15:Z1000 as US Dollars. Is there a way to do this in XlsxWriter?

I know how to format full columns as Dollars/Percentages, but I don't know how to format parts of columns -- whatever I do last will overwrite Columns F:K.

Data is starting in pandas so happy to solve the problem there. The following does not seem to work:

sheet.set_column('A1:E14', None, money_format)

More Code:

with pd.ExcelWriter(write_path) as writer:
    book = writer.book
    money_fmt = book.add_format({'num_format': '$#,##0'})
    pct_fmt = book.add_format({'num_format': '0.00%'})
    # call func that creates a worksheet  named total with no format
    df.to_excel(writer, sheet_name='Total', startrow=0)
    other_df.to_excel(writer, sheet_name='Total', startrow=15)
    writer.sheets['Total'].set_column('A1:E14',20, money_fmt)
    writer.sheets['Total'].set_column('F1:K14',20, pct_fmt)
    writer.sheets['Total'].set_column('F15:Z1000', 20, money_fmt)

Answer 1

I cannot see a way to achieve per cell formatting using just xlsxwriter with Pandas, but it would be possible to apply the formatting in a separate step using openpyxl as follows:

import openpyxl

def write_format(ws, cell_range, format):
    for row in ws[cell_range]:
        for cell in row:
            cell.number_format = format

sheet_name = "Total"

with pd.ExcelWriter(write_path) as writer:
    write_worksheet(df, writer, sheet_name=sheet_name)  

wb = openpyxl.load_workbook(write_path)
ws = wb.get_sheet_by_name(sheet_name)   

money_fmt = '$#,##0_-'
pct_fmt = '0.00%'

write_format(ws, 'A1:G1', money_fmt)
write_format(ws, 'A1:E14', money_fmt)
write_format(ws, 'F1:K14', pct_fmt)
write_format(ws, 'F15:Z1000', money_fmt)    

wb.save(write_path) 

When attempted with xlsxwriter, it always overwrites the existing data from Pandas. But if Pandas is then made to re-write the data, it then overwrites any applied formatting. There does not appear to be any method to apply formatting to an existing cell without overwriting the contents. For example, the write_blank() function states:

This method is used to add formatting to a cell which doesn’t contain a string or number value.