Tools

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows

class Tools:
    def save_to_excel(self, data_list, file_name, sheet_name):
        df = pd.DataFrame(data_list)
        wb = Workbook()
        # if os.path.exists(file_name):
        #     wb = openpyxl.load_workbook(file_name)
        # else:
        #     wb = Workbook()
        ws = wb.active
        ws.title = sheet_name

        # 将DataFrame写入工作表
        for row in dataframe_to_rows(df, index=False, header=False):
            ws.append(row)

        # 格式化标题行
        for cell in ws[1]:
            cell.font = Font(bold=True, size=12)
            cell.fill = PatternFill(start_color="00FFAA00", end_color="00FFAA00", fill_type="solid")
            cell.alignment = Alignment(horizontal="center", vertical="center")

        # 格式化数据行
        thin_border = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))

        for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
            for cell in row:
                # cell.font = Font(size=12)
                cell.border = thin_border

        # 调整列宽
        for col in ws.columns:
            max_length = 0
            column_name = col[0].column_letter  # Get the column name
            for cell in col:
                # 设置单元格的对齐方式，启用自动换行
                cell.alignment = Alignment(wrap_text=True)
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(cell.value)
                except:
                    pass
            adjusted_width = (max_length + 12)
            ws.column_dimensions[column_name].width = adjusted_width
        # 调整行高
        for row in range(2, ws.max_row):
            ws.row_dimensions[row].height = 15

        wb.save(file_name)
        # with pd.ExcelWriter(file_name, mode='a', engine='openpyxl') as writer:
        #     df.to_excel(writer, sheet_name=sheet_name, index=False, header=False)