我正在尝试根据列名定义需要应用于 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 :42668instead 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, fromAtoZ
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]])
