Skip to main content
 首页 » 编程设计

python之Xlsxwriter之根据列标签动态更改格式

2024年12月31日6zengkefu

我正在尝试根据列名定义需要应用于 Excel 电子表格每一列的格式。

例如,如果列名是“count”,则需要使用“number_format”。如果列名称是“sale_date”,则需要使用“date_format”。

number_format = workbook.add_format({'num_format': '0', 'font_size': 12}) 
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy hh:mm:ss', 'font_size': 12}) 

在各自的列中使用以上两种格式如下图:

worksheet1.write('A1', 'count', number_format) 
worksheet1.write('B1', 'sale_date', date_format) 

我可以根据列名而不是按列标签定义格式来动态设置吗?谢谢

更新:

在 excel 电子表格中显示标题列的循环

for data in title: 
    worksheet.write(row, col, data, number_format) 
    col += 1 

请您参考如下方法:

Comment: date_format = workbook.add_format({'num_format': 'dd/mm/yy'}), shows the date column as unix number rather than a proper date.
Sample value shown is : 42668 instead of displaying "24-10-16".

这是 Windows Excel 定义的默认行为。
阅读Excel for Windows stores dates by default as the number of days
文档:XlsxWriter Working with Dates and Time


Comment: ...that I could use the appropriate format based on column name (namely count, sale_date)

您可以使用 worksheet.set_column() 为整个列设置样式。
文档:XlsxWriter worksheet.set_column()

前提条件:列名称/样式的顺序必须与您的表格同步。
例如。 count == 'A', sale_date == 'B' 等等......

from collections import OrderedDict 
 
_styles = OrderedDict([('count',number_format), ('sale_date', date_format), ('total', number_format), ('text', string_format)]) 
 
for col, key in enumerate(_styles): 
    A1_notation = '{c}:{c}'.format(c=chr(col + 65)) 
    worksheet.set_column(A1_notation, None, _styles[key]) 
    print("worksheet.set_column('{}', None, {})".format(A1_notation, _styles[key])) 

Output:

worksheet.set_column('A:A', None, number_format) 
worksheet.set_column('B:B', None, date_format) 
worksheet.set_column('C:C', None, number_format) 
worksheet.set_column('D:D', None, string_format) 

对于后续的写入,您不需要分配样式,例如使用

worksheet.write('A1', 123)   

将默认为 A:A number_format


Question: Could I make this dynamic based on the column name

您没有使用“列名”,它叫做Cell A1 Notation。
设置映射dict,例如:

style_map = {'A': number_format, 'B':date_format} 

Usage:
Note: This will only work with single letter, from A to Z

def write(A1_notation, value): 
    worksheet1.write(A1_notation, value, style_map[A1_notation[0]]) 

对于行列表示法(0, 0):

style_map = {'0': number_format, '1':date_format} 

Usage:

def write(row, col, value): 
    worksheet1.write(row, col, value, style_map[col]) 
 
from xlsxwriter.utility import xl_rowcol_to_cell 
 
def write(A1_notation, value): 
    worksheet1.write(A1_notation, value, style_map[xl_cell_to_rowcol(A1_notation)[1]])