CODEWITHSUNDEEP
pythonpandasxlsxwriter
yasin mohammed
yasin mohammed

Reputation: 471

how to format specific cells in excel using xlsx package in python

I have a pandas df which I am formatting using xlsx package currently I have the option to format an entire row or column using xlsx but not specific cells also I would like to insert few lines in between the DF.

Image attached how I want the excel file to look.

The below code gives me the file in the 1st part of the image. I need to do some more formatting like inserting new lines and making D13 and E13 in italics. enter image description here

writer = pd.ExcelWriter('Sample Report Test.xlsx' , engine='xlsxwriter')
df.to_excel(writer , index= False , sheet_name='Sample Report')

workbook = writer.book
worksheet = writer.sheets['Sample Report']

money_fmt = workbook.add_format({'num_format':'$#,##0' ,  'font_name':'Batang' })
font_fmt = workbook.add_format({'font_name':'Batang' , 'bold':True })
tot_fmt = workbook.add_format({'num_format':'$#,##0' ,  'font_name':'Batang' ,  'bold':True })

worksheet.set_column('A:B' , 25 , font_fmt)
worksheet.set_column('C:P' , 15 , money_fmt)
worksheet.set_row(4, None , tot_fmt)
worksheet.set_row(7 , None , tot_fmt)

writer.save()

Upvotes: 3

Views: 8888

Answers (3)

jmcnamara
jmcnamara

Reputation: 41644

You can insert more than one dataframe, with offsets, into an XlsxWriter file. See this example from the docs.

It isn't possible to format cells after they are written (apart from column/row formats, see this example).

If you need very fine grained formatting you would be best to just use XlsxWriter directly with the data from the dataframe.

Some people use conditional formatting in XlsxWriter to get the effect they need, like this SO answer.

Upvotes: 4

MattiH
MattiH

Reputation: 654

You can use conditional formatting. E.g. this code paints A1 cell in nice colors

header_format = workbook.add_format({'bold':     True,
                                     'font_color': '#FDCC02',
                                     'bg_color': '#003318'})
worksheet.conditional_format('A1:A1', {'type':   'no_errors',
                                       'format': header_forma})

Upvotes: 1

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210972

Try this:

money_italic_fmt = workbook.add_format({'num_format':'$#,##0',
                                        'font_name':'Batang',
                                        'italic':True})
worksheet.write(12, # <-- The cell row (zero indexed).
                3,  # <-- The cell column (zero indexed).
                13, # <-- Value to write
                money_italic_fmt  # <-- Format
)

Upvotes: 2

Related Questions