data_analysis/data_processor/combined_sales_exporter.py

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows

from data_processor.daily_sales_exporter import DailySalesExporter
from data_processor.daily_zones_exporter import DailyZonesSalesExporter


class CombinedSalesExporter:
    def __init__(self, daily_sales_exporter: DailySalesExporter, daily_zones_sales_exporter: DailyZonesSalesExporter):
        self.daily_sales_exporter = daily_sales_exporter
        self.daily_zones_sales_exporter = daily_zones_sales_exporter

    def export(self, output_file='combined_output.xlsx'):
        sales_df = self.daily_sales_exporter.export_to_dataframe()
        zones_df = self.daily_zones_sales_exporter.export_to_dataframe()

        wb = Workbook()
        ws = wb.active

        start_row_sales = 1
        # 写入第一个 DataFrame（DailySalesExporter）
        for r in dataframe_to_rows(sales_df, index=False, header=True):
            ws.append(r)
        end_row_sales = ws.max_row

        # 添加 3 行空行
        for _ in range(3):
            ws.append([])

        # 记录当前行号，用于格式化设置
        start_row_for_zones_header = end_row_sales + 1 + 3  # 第二个表头行号
        start_row_for_zones_data = start_row_for_zones_header + 1  # 第二个表数据起始行

        # 写入第二个 DataFrame（DailyZonesSalesExporter），包含表头
        for r in dataframe_to_rows(zones_df, index=False, header=True):
            ws.append(r)

        # 设置格式化（分别应用到两个区域）
        self._apply_formatting(ws, sales_df, start_row=start_row_sales + 1, header_row=start_row_sales)
        self._apply_formatting(ws, zones_df, start_row=start_row_for_zones_data, header_row=start_row_for_zones_header)

        # 保存文件
        wb.save(output_file)
        print(f"✅ 数据已成功导出到 {output_file}")

    def _apply_formatting(self, ws, df, start_row=2, header_row=1):
        """
        :param ws: worksheet 对象
        :param df: 数据 DataFrame
        :param start_row: 数据开始的行号（数据从该行开始应用格式）
        :param header_row: 表头所在行号（用于匹配列名）
        """
        header = {cell.value: cell.column for cell in ws[header_row]}
        numeric_columns = [
            '999及以内单数', '1000-1999单数', '2000-2899单数', '2900-3999单数',
            '4000-4999单数', '5000-7999单数', '8000以上单数', '满足奖励条件大单数',
            '最大单件数', '最大单金额', '总单数', '2000+单数'
        ]

        for col_name in numeric_columns:
            if col_name in header:
                col_letter = get_column_letter(header[col_name])
                for row in range(start_row, ws.max_row + 1):
                    cell = ws[f"{col_letter}{row}"]
                    cell.number_format = '0'

        if '2000+单数占比' in header:
            col_letter = get_column_letter(header['2000+单数占比'])
            for row in range(start_row, ws.max_row + 1):
                cell = ws[f"{col_letter}{row}"]
                cell.number_format = '0.00%'