1

The below code is expected to set the top row to have a gray (#E7E6E6) background. However, where there is data it comes out with a white background and only is gray where there is no data. Anyone know is this is an unexpected outcome or a bug?

raw_data = {'regiment': ['Nighthawks', 'Nighthawks', 'Nighthawks', 'Nighthawks', 'Dragoons', 'Dragoons', 'Dragoons', 'Dragoons', 'Scouts', 'Scouts', 'Scouts', 'Scouts'],
        'company': ['1st', '1st', '2nd', '2nd', '1st', '1st', '2nd', '2nd','1st', '1st', '2nd', '2nd'],
        'name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze', 'Jacon', 'Ryaner', 'Sone', 'Sloan', 'Piger', 'Riani', 'Ali'],
        'preTestScore': [4, 24, 31, 2, 3, 4, 24, 31, 2, 3, 2, 3],
        'postTestScore': [25, 94, 57, 62, 70, 25, 94, 57, 62, 70, 62, 70]}
report = pandas.DataFrame(raw_data, columns = ['regiment', 'company', 'name', 'preTestScore', 'postTestScore'])

location = 'C:/Desktop/'

writer = pandas.ExcelWriter(location+'Report.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
REPORT.to_excel(writer,'Report',index=False)
# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Report']
# Add some cell formats.
format = workbook.add_format()
#format.set_bold()
format.set_bg_color('#E7E6E6')
worksheet.set_row(0, 18, format)
worksheet.set_column(0,1,25)
worksheet.set_column(2,2,8)
worksheet.set_column(3,3,21)
worksheet.set_column(4,5,25)
writer.save()
JJFord3
  • 1,976
  • 1
  • 25
  • 40
  • Could you edit your sample code to include some data so that it shows the issue that you are seeing. – jmcnamara Aug 25 '17 at 07:57
  • Added some dummy data. The issue is the Excel document generated by the code shows up with bold text white background cells for cells A1:E1 and then gray for the remainder of the top column. Would have thought they all would be gray. – JJFord3 Aug 25 '17 at 13:18
  • 1
    Ok. The row format is being overridden by the cell format used by Pandas for the heading. See the [workaround at this SO question](https://stackoverflow.com/questions/36694313/pandas-xlsxwriter-format-header) and my explanation there. – jmcnamara Aug 25 '17 at 14:04
  • Thanks for linking to that answer. Didn't know there was a default header format. Will keep question up to help funnel more people to correct answer. – JJFord3 Aug 25 '17 at 14:10

0 Answers0