如何用Python循环为提取出的多部门工作簿实现列宽自动适配

我有一张包含多部门员工考勤数据的主表，通过Python实现了按部门提取数据并导出为独立工作簿的功能，同时设置了表头样式、条件格式等格式，但导出的工作簿列宽显示不佳，内容无法完整展示，需要为每个工作簿添加列宽自动适配功能。

现有代码 #

import pandas as pd
import numpy as np
import xlsxwriter
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font,colors,Fill,GradientFill
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule, Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.worksheet.dimensions import ColumnDimension
import datetime

df=pd.read_excel(r"C:\Users\Mahmoud.Bader\Desktop\FP Attendance V1.6 Apr 22.xlsx","Attendance").fillna("")
df['Date']=pd.to_datetime(df['Date']).dt.date


dep = list(df["Department"].unique())

for i in dep:
    FP_Attendance = df[df["Department"].str.contains(i)]
    FP_Attendance=FP_Attendance.style.set_properties(**{'color':'black','text-align': 'center','font-family': 'Arial Narrow','border-color':'Black','border-width':'thin','border-style':'solid','vertical-align': 'middle'})
    with pd.ExcelWriter(f"C:\\Users\\Mahmoud.Bader\\Desktop\\Python\\Workforce Reports\\FP Test\\{i}.xlsx",engine='openpyxl') as writer:
        FP_Attendance.to_excel(writer,sheet_name=str(i), index=False,freeze_panes=(1,0))
           
    wb = load_workbook(f"C:\\Users\\Mahmoud.Bader\\Desktop\\Python\\Workforce Reports\\FP Test\\{i}.xlsx")
    ws = wb[str(i)]
   
    
    col_range = ws.max_column
    for col in range(1, col_range + 1):
        cell_header = ws.cell(1, col)
        cell_header.font = Font(color='00FFFFFF',name='Arial Narrow',size=11,bold=True)
        cell_header.fill = PatternFill(start_color='00C00000', end_color='00C00000', fill_type="solid")
        cell_header.alignment =  Alignment(horizontal="center", vertical="center")
        thin = Side(border_style="thin", color="000000")
        cell_header.border =  Border(top=thin, left=thin, right=thin, bottom=thin)
        
        
        ws.conditional_formatting.add('I2:I10000',CellIsRule(operator='greaterThan', formula=[0], stopIfTrue=False, font=Font(color="00FF0000",bold=True)))
        ws.conditional_formatting.add('F2:L10000', FormulaRule(formula=['NOT(ISERROR(SEARCH("OFF Day",F2)))'], stopIfTrue=True, font=Font(color="00000000",bold=True),fill=PatternFill(start_color='00BFBFBF', end_color='00BFBFBF', fill_type='solid'))) 
        ws.conditional_formatting.add('F2:L10000', FormulaRule(formula=['NOT(ISERROR(SEARCH("No Show",F2)))'], stopIfTrue=True, font=Font(color="009C0006",bold=True),fill=PatternFill(start_color='00FFC7CE', end_color='00FFC7CE', fill_type='solid')))
        ws.conditional_formatting.add('F2:L10000', FormulaRule(formula=['NOT(ISERROR(SEARCH("Annual",F2)))'], stopIfTrue=True, font=Font(color="00006100",bold=True),fill=PatternFill(start_color='00C6EFCE', end_color='00C6EFCE', fill_type='solid')))
        ws.auto_filter.ref = "A1:O15"
        ws.sheet_properties.tabColor = "00C00000"
        ws.sheet_view.showGridLines = False

        wb.save(f"C:\\Users\\Mahmoud.Bader\\Desktop\\Python\\Workforce Reports\\FP Test\\{i}.xlsx")

补充说明 #

• 原数据显示：导出的工作簿列宽未适配，部分内容被截断
• 预期数据显示：列宽自动调整后，所有内容完整展示
• 主表：包含各部门员工考勤记录的源Excel表
• 预期最终效果：每个部门工作簿的列宽自动适配内容长度，表头和数据都完整显示

解决方案：添加自动调整列宽的代码 #

在现有代码中，处理完表头样式和条件格式后，添加一段遍历所有列、根据内容长度计算并设置列宽的代码。修改后的完整代码如下：

import pandas as pd
import numpy as np
import xlsxwriter
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font,colors,Fill,GradientFill
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule, Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.worksheet.dimensions import ColumnDimension
import datetime

df=pd.read_excel(r"C:\Users\Mahmoud.Bader\Desktop\FP Attendance V1.6 Apr 22.xlsx","Attendance").fillna("")
df['Date']=pd.to_datetime(df['Date']).dt.date


dep = list(df["Department"].unique())

for i in dep:
    FP_Attendance = df[df["Department"].str.contains(i)]
    FP_Attendance=FP_Attendance.style.set_properties(**{'color':'black','text-align': 'center','font-family': 'Arial Narrow','border-color':'Black','border-width':'thin','border-style':'solid','vertical-align': 'middle'})
    with pd.ExcelWriter(f"C:\\Users\\Mahmoud.Bader\\Desktop\\Python\\Workforce Reports\\FP Test\\{i}.xlsx",engine='openpyxl') as writer:
        FP_Attendance.to_excel(writer,sheet_name=str(i), index=False,freeze_panes=(1,0))
           
    wb = load_workbook(f"C:\\Users\\Mahmoud.Bader\\Desktop\\Python\\Workforce Reports\\FP Test\\{i}.xlsx")
    ws = wb[str(i)]
   
    
    col_range = ws.max_column
    for col in range(1, col_range + 1):
        cell_header = ws.cell(1, col)
        cell_header.font = Font(color='00FFFFFF',name='Arial Narrow',size=11,bold=True)
        cell_header.fill = PatternFill(start_color='00C00000', end_color='00C00000', fill_type="solid")
        cell_header.alignment =  Alignment(horizontal="center", vertical="center")
        thin = Side(border_style="thin", color="000000")
        cell_header.border =  Border(top=thin, left=thin, right=thin, bottom=thin)
        
        
        ws.conditional_formatting.add('I2:I10000',CellIsRule(operator='greaterThan', formula=[0], stopIfTrue=False, font=Font(color="00FF0000",bold=True)))
        ws.conditional_formatting.add('F2:L10000', FormulaRule(formula=['NOT(ISERROR(SEARCH("OFF Day",F2)))'], stopIfTrue=True, font=Font(color="00000000",bold=True),fill=PatternFill(start_color='00BFBFBF', end_color='00BFBFBF', fill_type='solid'))) 
        ws.conditional_formatting.add('F2:L10000', FormulaRule(formula=['NOT(ISERROR(SEARCH("No Show",F2)))'], stopIfTrue=True, font=Font(color="009C0006",bold=True),fill=PatternFill(start_color='00FFC7CE', end_color='00FFC7CE', fill_type='solid')))
        ws.conditional_formatting.add('F2:L10000', FormulaRule(formula=['NOT(ISERROR(SEARCH("Annual",F2)))'], stopIfTrue=True, font=Font(color="00006100",bold=True),fill=PatternFill(start_color='00C6EFCE', end_color='00C6EFCE', fill_type='solid')))
        ws.auto_filter.ref = "A1:O15"
        ws.sheet_properties.tabColor = "00C00000"
        ws.sheet_view.showGridLines = False

    # 新增：自动调整列宽的代码
    for column_cells in ws.columns:
        # 获取列名（如A、B...）
        column_name = openpyxl.utils.get_column_letter(column_cells[0].column)
        # 计算该列所有单元格内容的最大长度
        max_length = 0
        for cell in column_cells:
            try:
                cell_length = len(str(cell.value))
                if cell_length > max_length:
                    max_length = cell_length
            except:
                pass
        # 设置列宽，预留2个字符的边距，适配Arial Narrow字体
        adjusted_width = max_length + 2
        ws.column_dimensions[column_name].width = adjusted_width

    wb.save(f"C:\\Users\\Mahmoud.Bader\\Desktop\\Python\\Workforce Reports\\FP Test\\{i}.xlsx")

代码说明 #

1. 新增的列宽调整代码遍历工作表的每一列，计算该列所有单元格内容的最大长度
2. 为了避免内容紧贴列边缘，在最大长度基础上增加2个字符的边距
3. 针对使用的Arial Narrow字体，这个宽度设置可以保证内容完整显示
4. 把列宽调整代码放在所有格式设置之后、保存工作簿之前，确保格式生效

内容的提问来源于stack exchange，提问作者Mahmoud Badr