如何在python中使用xlsx包格式化Excel中的特定单元格 [英] how to format specific cells in excel using xlsx package in python

问题描述

我有一个熊猫df，目前正在使用xlsx软件包进行格式化，我可以选择使用xlsx格式化整个行或列，但不格式化特定的单元格，我也想在DF之间插入几行.

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.

图片附上我希望Excel文件的外观.

Image attached how I want the excel file to look.

以下代码为我提供了图像第一部分中的文件.我需要做更多格式化，例如插入新行，并用斜体表示D13和E13.

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.

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()

推荐答案

您可以在XlsxWriter文件中插入多个具有偏移量的数据框.请参阅文档中的此示例.

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).

如果您需要非常精细的格式设置，最好只对数据框中的数据直接使用XlsxWriter.

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

有些人在XlsxWriter中使用条件格式来获得所需的效果，例如

Some people use conditional formatting in XlsxWriter to get the effect they need, like this SO answer. However, that isn't applicable to single cells.

这篇关于如何在python中使用xlsx包格式化Excel中的特定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！