三大Python操作Excel文件扩展库的使用指南

pip install openpyxl pandas

pip install free spire.xls

from openpyxl import workbook
from openpyxl.styles import font, patternfill
from openpyxl.formatting.rule import colorscalerule

# 创建带格式的工作簿
wb = workbook()
ws = wb.active
ws.title = "销售分析"

# 添加标题行（带样式）
header_font = font(bold=true, color="ffffff")
header_fill = patternfill(start_color="4f81bd", end_color="4f81bd", fill_type="solid")
ws.append(["产品", "季度", "销售额", "增长率"])
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# 添加示例数据
sales_data = [
    ["手机", "q1", 1500, 0.15],
    ["手机", "q2", 2100, 0.40],
    ["笔记本", "q1", 800, -0.05],
    ["笔记本", "q2", 1200, 0.50]
]
for row in sales_data:
    ws.append(row)

# 添加条件格式
color_scale = colorscalerule(start_type='min', start_color='ff0000',
                            end_type='max', end_color='00ff00')
ws.conditional_formatting.add("d2:d5", color_scale)

# 保存专业报表
wb.save("sales_analysis.xlsx")

import pandas as pd
import numpy as np

# 创建示例数据集
data = {
    '产品': ['手机', '笔记本', '平板', '耳机'] * 5,
    '月份': np.repeat(['1月', '2月', '3月', '4月', '5月'], 4),
    '销售额': np.random.randint(1000, 5000, 20),
    '成本': np.random.randint(500, 3000, 20)
}
df = pd.dataframe(data)

# 添加计算列（类似excel公式）
df['利润率'] = (df['销售额'] - df['成本']) / df['销售额']

# 创建数据透视表
pivot = pd.pivot_table(df, 
                       values='销售额',
                       index='产品',
                       columns='月份',
                       aggfunc='sum',
                       margins=true,
                       margins_name='总计')

# 多表输出到excel
with pd.excelwriter('sales_report.xlsx') as writer:
    df.to_excel(writer, sheet_name='原始数据', index=false)
    pivot.to_excel(writer, sheet_name='数据透视表')
    
with pd.excelwriter('sales_report.xlsx', engine='xlsxwriter') as writer:
    # 写入数据
    df.to_excel(writer, sheet_name='原始数据', index=false)
    pivot.to_excel(writer, sheet_name='数据透视表')
    
    # 获取工作簿和工作表对象
    workbook = writer.book
    worksheet = writer.sheets['数据透视表']
    
    # 创建柱状图 (需安装 xlsxwriter)
    chart = workbook.add_chart({'type': 'column'})
    
    # 动态获取数据范围 (b2:f6 为示例范围)
    chart.add_series({
        'name': '销售额',
        'categories': '=数据透视表!$b$2:$f$2',  # 月份数据
        'values': '=数据透视表!$b$3:$f$6',     # 产品销售额
        'gap': 150  # 柱间距
    })
    
    # 设置图表样式
    chart.set_title({'name': '产品月度销售额分布'})
    chart.set_x_axis({'name': '月份'})
    chart.set_y_axis({'name': '销售额 (万元)'})
    
    # 嵌入图表到指定位置
    worksheet.insert_chart('h2', chart)

import os
from spire.xls import workbook, fileformat, datetime


def process_financial_report(template_path: str, output_excel_path: str,
                             generate_pdf: bool) -> none:
    """
    使用 free spire.xls for python 处理财务报告模板
    填充数据并导出为excel和pdf格式
    
    参数:
        template_path: 财务报告模板的完整路径
        output_excel_path: 生成的excel报告的保存路径
        generate_pdf: 是否额外生成pdf报告
        
    流程:
        1. 加载excel模板
        2. 填充报告标题和时间戳
        3. 填充季度财务数据
        4. 保存excel报告
        5. 可选生成pdf报告
    """
    workbook = workbook()
    try:
        # 加载模板
        workbook.loadfromfile(template_path)
        # 使用第一个工作表
        worksheet = workbook.worksheets[0]

        # 设置报告标题
        worksheet.range["b2"].text = "2023年度财务报告"

        # 设置当前日期
        worksheet.range["c5"].datetimevalue = datetime.get_now()

        # 准备数据
        quarterly_data = [["q1", 1500000, 1200000, 300000],
                          ["q2", 1650000, 1250000, 400000],
                          ["q3", 1820000, 1350000, 470000],
                          ["q4", 2100000, 1450000, 650000]]

        # 数据列对应的列标识
        data_columns = ["a", "b", "c", "d"]

        # 数据起始行（第7行开始）
        start_row = 7

        for row_index, quarter_row in enumerate(quarterly_data):
            # 计算当前数据行的行号
            excel_row = start_row + row_index

            for col_index, cell_value in enumerate(quarter_row):
                # 获取当前单元格地址
                cell_address = f"{data_columns[col_index]}{excel_row}"

                # 根据数据类型设置单元格值
                if isinstance(cell_value, str):
                    worksheet.range[cell_address].text = cell_value
                else:
                    worksheet.range[cell_address].numbervalue = cell_value

        # 保存excel报告
        workbook.savetofile(output_excel_path, fileformat.version2016)

        # 可选生成pdf报告
        if generate_pdf:
            # 替换扩展名
            root, _ = os.path.splitext(output_excel_path)
            pdf_output_path = root + ".pdf"

            workbook.savetofile(pdf_output_path, fileformat.pdf)
            print(f"pdf报告已生成: {pdf_output_path}")

    except exception as e:
        print(f"报告生成失败: {str(e)}")
        # 实际项目中应记录详细错误日志
        # 可考虑重新抛出异常或返回错误状态码

    finally:
        # 确保释放工作簿资源
        workbook.dispose()
        print("工作簿资源已释放")


# 使用示例
if __name__ == "__main__":
    # 路径配置（实际使用中建议从配置文件读取）
    template_path = "财务模版.xlsx"
    output_path = "2023财务报告.xlsx"

    process_financial_report(template_path=template_path,
                             output_excel_path=output_path,
                             generate_pdf=true)

# csv中转
pd.read_excel("large_dataset.xlsx").to_csv("temp.csv", index=false)

# 分块处理文件
chunk_size = 10000
csv_chunks = pd.read_csv("temp.csv", chunksize=chunk_size)

with pd.excelwriter("processed_data.xlsx", engine='openpyxl') as writer:
    for i, chunk in tqdm(enumerate(csv_chunks), desc="processing"):
        processed = transform_data(chunk)
        # 安全写入策略
        if i >= 200:  # 预留55个sheet给其他数据
            # 合并到主表
            start_row = 0 if i == 0 else writer.sheets['main'].max_row
            processed.to_excel(writer, sheet_name='main', startrow=start_row, index=false, header=(i==0))
        else:
            processed.to_excel(writer, sheet_name=f"part_{i+1}", index=false)