set excel format

CrossPython發表於2024-07-08
ExcelORM
import openpyxl
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter


class SetExcelFormat:
    def __init__(self, filepath, sheetname):
        self.filepath = filepath
        self.workbook = openpyxl.load_workbook(filepath)
        self.worksheet = self.workbook[sheetname]
        max_col = self.worksheet.max_column
        self.cols = [get_column_letter(col) for col in range(1, max_col + 1)]

    def save(self):
        self.workbook.save(self.filepath)

    def set_title_center(self, **kwargs):
        cols = kwargs.get('cols')
        if cols is None:
            cols = self.cols
        center_alignment = Alignment(horizontal='center')
        for col in cols:
            column_range = self.worksheet[col][1:]
            for cell in column_range:
                cell.alignment = center_alignment

    def set_num_format(self, **kwargs):
        cols = kwargs.get('cols')
        if cols is None:
            cols = self.cols
        for col in cols:
            column_range = self.worksheet[col][1:]
            for cell in column_range:
                cell.number_format = '#,##0'

    def set_col_auto_width(self, **kwargs):
        dims = {}
        cols = kwargs.get('cols')
        if cols is None:
            cols = self.cols
        for row in self.worksheet.rows:
            for cell in row:
                if cell.value:
                    """
                    首先獲取每個單元格中的長度；如果有換行則按單行的長度計算，先分割再計算；
                    長度計算中：len('中文')>>>2, len('中文'.encode('utf-8'))>>>6，透過運算，將中文的位元組數定義為2；
                    字典儲存每列的寬度：將cell每列中 列名作為鍵名，cell長度計算的最大長度作為鍵值。
                    """
                    len_cell = max(
                        [(len(line.encode('utf-8')) - len(line)) / 2 + len(line) for line in
                         str(cell.value).split('\n')])
                    # dims[chr(64+cell.column)] = max((dims.get(chr(64+cell.column), 0), len(str(cell.value))))
                    dims[cell.column_letter] = max(dims.get(cell.column_letter, 0), len_cell)
        for col, value in dims.items():
            if col in cols:
                """最後透過遍歷儲存每列的寬度的字典，來設定相關列的寬度"""
                self.worksheet.column_dimensions[col].width = value + 2 if value + 2 <= 50 else 50


if __name__ == '__main__':
    xlsformat = SetExcelFormat(filepath='採購訂單ATP和PO價格對比_2024-07-08.xlsx', sheetname='Sheet1')
    xlsformat.set_col_auto_width()
    xlsformat.set_num_format(cols=['N', 'O', 'P'])
    xlsformat.save()

　　

相關文章