日常工作中,希望输出的excel带一些格式,当表单数据量较大时,用python可以解决大量手动工作量:pd.to_excel时设置好了保存即可。
下边代码包含了设置日期、边框、对齐、高亮、背景色、字体色,字号、字体、筛选等。
import numpy as np
import pandas as pd
import sys, os
def demo():
data = {
"one": [20123, '2020123', 'american', '2020-5'], "two": np.linspace(1, 4, 4), "three": [1211, 0.07, 0.99, 0.1]}
df1 = pd.DataFrame(data, index=[1, 2, 3, 4])
with pd.ExcelWriter(os.path.join(os.getcwd(), 'worksheetDemoData.xlsx'), engine='xlsxwriter') as writer:
df1.to_excel(writer, sheet_name=u'测试页签', encoding='utf8', header=False, index=False, startcol=0, startrow=3, float_format='%.2f')
workbook = writer.book
# add format
fmt = workbook.</