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)