CODEWITHSUNDEEP
pythonpandasxlsxwriter
AGH_TORN
AGH_TORN

Reputation: 833

Formatting only headers with data using xlsxwriter

I'm having a little trouble getting the formatting by using worksheet.set_row() to apply only to columns with data. As it stands, when I open the workbook, the formatting gets applied to the entire header row, even past where the data stops and this looks a bit sloppy, see below:

enter image description here

As you can see, the formatting continues onto columns AL, AM, AN, AO, etc... Since there is no data in these columns (or headers for that matter) it looks a bit sloppy.

I was previously using the following to apply formatting to each sheet in my workbook:

header = workbook.add_format({'bold': True, 'bottom': 2, 'bg_color': '#F9DA04'})
worksheet.set_row(0, None, header)

I understand this is because worksheet.set_row() uses the row index. I can't find any documentation about this taking a range, could I somehow specify A1:AK1 or something like that? If it matters, each worksheet is the result of a pd.concat() of multiple dataframes using Pandas.

Upvotes: 2

Views: 10625

Answers (1)

jmcnamara
jmcnamara

Reputation: 41524

As you can see, the formatting continues onto columns AL, AM, AN, AO, etc..

That is how row formatting works in Excel. If you apply it to a row then all cells are formatted.

could I somehow specify A1:AK1 or something like that?

If you only want to format certain cells then it is best to apply the format just to those cells. For example:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

header_data = ['EID', 'Notification', 'Test #']

header_format = workbook.add_format({'bold': True,
                                     'bottom': 2,
                                     'bg_color': '#F9DA04'})

for col_num, data in enumerate(header_data):
    worksheet.write(0, col_num, data, header_format)

workbook.close()

Output:

enter image description here

If it matters, each worksheet is the result of a pd.concat() of multiple dataframes using Pandas.

For an example of how to format header from a dataframe see this example from the XlsxWriter docs:

import pandas as pd

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data1': [10, 20, 30, 20],
                   'Data2': [10, 20, 30, 20],
                   'Data3': [10, 20, 30, 20],
                   'Data4': [10, 20, 30, 20]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Turn off the default header and skip one row to allow us to insert a
# user defined header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
header_format = workbook.add_format({'bold': True,
                                     'bottom': 2,
                                     'bg_color': '#F9DA04'})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

enter image description here

Upvotes: 7

Related Questions