xlsxwriter写入Excel

问题描述

Pandas 输出Excel的方式有许多种，最常见方式如下

data.to_excel('filename.xlsx')

但是输出的xlsx文件看起来略丑，还需要进行二次调整，效率低下

解决方案

通过xlsxwriter编写函数进行输出，特性如下：

• xlsxwriter 是所有Excel处理的package里写入速度最快的
• 不需要依赖其他软件（xlswings依赖Excel程序）

代码实现

import numpy as np
import pandas as pd

# 输出函数
def file_write(data, out_file):
    '''
    input(data, out_file):数据(dataFrame),写入文件路径
    output(file.xlsx)
    '''
    import xlsxwriter
    
    wb = xlsxwriter.Workbook(out_file)
    ws = wb.add_worksheet('Sheet1')
    
    data = data.replace(np.NaN, '') # Nan(float) 无法写入，替换成 ''(str)
    # 使用 xlsxwriter.utility.xl_col_to_name() 将最后一列转换为Excel列字母
    name = 'A:' + xlsxwriter.utility.xl_col_to_name(data.shape[1]-1) # A:N(最后一列)

    # 标题格式设置
    header_format = wb.add_format({
        'bold': True,# 加粗
        'valign': 'vcenter', # 垂直居中
        'text_wrap': True, # 自动换行
        'font_name': '微软雅黑', # 字体名称
        'font_size': 9, # 字体大小
        'border': 1 # 单元格边框
        }) 
    
    # 表格区域格式设置
    text_format = wb.add_format({
        'valign':'vcenter', # 垂直居中
        'font_name': '微软雅黑', # 字体名称
        'font_size': 8, # 字体大小
        'border': 1 # 单元格边框
    })
    # 寫入標題
    for col_num, value in enumerate(data.columns.values):
        ws.write(0, col_num, value, header_format)
        
    # 寫入数据
    (row, col) = data.shape
    for i in range(row):
        for j in range(col):
            ws.write(i+1, j, data.iloc[i, j], text_format) # 第1行留空（标题）

    #添加飾選器
    (max_row, max_col)= data.shape
    ws.autofilter(0, 0, max_row, max_col - 1)
    # 設置行高為 28.5 
    ws.set_row(0, 28.5) # 從第 0 行開始·行高為 28.5
    ws.set_column(name ,12) # 設置A:N列宽为 12.0
    
    wb.close()
    
# 写入数据到xlsx文件
data = pd.DataFrame({'小老鼠':[0, 1, 2], '小松鼠':[3, 4, 5], '北巷的猫猫的猫猫的猫猫':[8, 9, 10]})
outfile = r'C:\Users\BXDM\Desktop\out.xlsx' # 文件输出路径
file_write(data, outfile)

效果展示

• 实现表格标题加粗
• 改变原有标题和表格区域的单元格格式
• 有数据的区域添加表格边框
• 添加筛选
• 表格标题自动换行
• 统一表格列宽

参考资料

Convert spreadsheet number to column letter
Creating Excel files with Python and XlsxWriter(官网)
Thick border in xlsxwriter