使用openpyxl修改Excel表格表头后出现表格损坏问题求助

当Excel文件包含结构化表格（ListObject）时，直接通过单元格坐标修改表头值会破坏表格的内部XML结构，导致Excel识别文件损坏。这是因为Excel的表格有独立的元数据定义，表头属于表格对象的一部分，而非单纯的独立单元格内容。

要安全修改带表格的Excel表头，需要通过openpyxl提供的ListObject API操作，而非直接修改单元格。以下是修正后的代码：

import openpyxl as pxl
from tkinter import filedialog, messagebox
import logging
import string

class TCA:
    def __init__(self):
        self.path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx; *.xls")], initialdir='.\\')
        self.wb: pxl.Workbook = pxl.load_workbook(self.path, read_only=False)
        self.ws = None
        self.table = None  # 存储工作表中的结构化表格对象

        self.header_names = ['Project', 'TC_all', 'TC_manual', 'TC_automated', 'TC_automateable', 'TC_3rd', 'Ratio']
        self.header_order = {name: None for name in self.header_names}

    def load_header_order(self):
        # 映射表格现有列与表头的关系
        table_columns = {col.name: col for col in self.table.columns}
        for header in self.header_names:
            if header in table_columns:
                self.header_order[header] = table_columns[header]
        
        # 插入缺失的表头
        for header in self.header_names:
            if self.header_order[header] is None:
                self.insert_missing_header(header)

    def insert_missing_header(self, header):
        logging.info(f'There is missing header {header}')
        logging.info(f'Insert in progress')
        
        # 扩展表格的列范围：获取当前表格结束列，向后推一列
        current_end_col = self.table.ref.split(':')[1][0]
        new_end_col = chr(ord(current_end_col) + 1)
        new_table_ref = f"{self.table.ref.split(':')[0]}:{new_end_col}{self.table.max_row}"
        
        # 更新表格的引用范围，确保Excel识别新列属于表格
        self.table.ref = new_table_ref
        
        # 添加新列的元数据定义
        new_col = pxl.worksheet.table.TableColumn(name=header)
        self.table.columns.append(new_col)
        
        # 同步单元格显示值
        self.ws[f'{new_end_col}1'] = header
        
        logging.info(f'Header {header} was inserted into {new_end_col}1')

    def check_wb(self):
        return 'Preview' in self.wb.sheetnames

    def load_project_list(self):
        self.ws = self.wb['Preview']
        # 优先处理带表格的场景
        if self.ws.tables:
            self.table = list(self.ws.tables.values())[0]
            self.load_header_order()
        else:
            # 兼容无表格的Excel文件，沿用原逻辑
            self.empty_spaces = list(string.ascii_uppercase[:7])
            self._handle_no_table_case()

    def _handle_no_table_case(self):
        self.header_order = {name: -1 for name in self.header_names}
        for header in self.header_names:
            for letter in string.ascii_uppercase:
                if header == self.ws[f'{letter}1'].value:
                    self.header_order[header] = letter
                    self.empty_spaces.remove(letter)

        for key, value in self.header_order.items():
            if value == -1:
                self._insert_header_no_table(key)

    def _insert_header_no_table(self, header):
        logging.info(f'There is missing header {header}')
        logging.info(f'Insert in progress')
        self.ws[f'{self.empty_spaces[0]}1'] = f'{header}'
        logging.info(f'Header {header} was inserted into {self.empty_spaces[0]}1')
        self.empty_spaces.__delitem__(0)

    def exit(self):
        self.wb.save(self.path)


if __name__ == '__main__':
    tca = TCA()

    if not tca.check_wb():
        messagebox.showerror('Crucial error!', 'There is no sheet named "Preview"!\n'
                                               'Due to this exception script cannot proceed!\n'
                                               'If you have renamed the sheet please rename it back...')
        exit(-1)

    tca.load_project_list()
    tca.exit()

1. 新增表格对象管理：通过self.ws.tables获取Excel中的结构化表格，用self.table存储，确保操作基于表格元数据而非独立单元格
2. 表头修改逻辑优化：直接修改表格列的name属性，保持表格元数据与单元格内容一致，避免破坏结构
3. 缺失表头插入逻辑：先扩展表格的引用范围，再添加新列的元数据定义，最后同步单元格值，确保Excel识别新列属于表格的一部分
4. 兼容无表格场景：保留原逻辑处理不带表格的Excel文件，保证代码通用性

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