使用python在不改变原有excel的格式下，修改指定单元格格式

需求

有一个账单，需要生成一个副本，但是需要将交易员列隐藏，不能改变原有的格式
xlsx的文件容易实现，使用openpyxl实现
xls的文件使用xlrd+xlutil实现
参考了https://segmentfault.com/q/1010000008270267

class GenCopyReport(object):"""生成账单备份，删除交易员列"""def __init__(self, filename, broker_code) -> None:self.filename = filenameself.broker_code = broker_codeself.file_type = filename.split(".")[-1]def remove_col(self, content, sheet_name, col_name, skiprows):"""删除指定sheet页指定列:param content: 二进制文件:param sheet_name: sheet 页名:param col_name: 列名:param skiprows: 列名所在行:raises ValueError: 异常:return: bytes | None"""# 判断文件格式是 .xls 还是 .xlsxif self.file_type == "xls":# 处理 .xls 文件return self.remove_col_xls(content, sheet_name, col_name, skiprows)elif self.file_type == "xlsx":# 处理 .xlsx 文件return self.remove_col_xlsx(content, sheet_name, col_name, skiprows)else:raise ValueError("Unsupported file format")def copy2(self, wb):w = XLWTWriter()process(XLRDReader(wb, 'unknown.xls'), w)return w.output[0][1], w.style_listdef remove_col_xls(self, content, sheet_name, col_name, skiprows):"""支持xls后缀的处理方法:param content: 二进制:param sheet_name: sheet页名:param col_name: 列名:param skiprows: 列名所在行号:return: bytes | None"""# 打开 Excel 文件,保留格式信息workbook = open_workbook(file_contents=content, formatting_info=True, on_demand=True)# 选择指定的 sheetsheet = workbook.sheet_by_name(sheet_name)# 找到需要删除的列索引for col_idx in range(sheet.ncols):if sheet.cell_value(skiprows-1, col_idx) == col_name:trader_col = col_idxbreakelse:trader_col = None# 如果找到了需要删除的列,则删除该列if trader_col is not None:# 创建一个可编辑的 workbook 副本,并保留原文件的格式属性wb, s = self.copy2(workbook)wbs = wb.get_sheet(sheet_name)# styles = s[sheet.cell_xf_index(row_idx, trader_col)]# 删除指定列for row_idx in range(sheet.nrows):if row_idx == skiprows-1:wbs.write(row_idx, trader_col, "备注", s[sheet.cell_xf_index(row_idx, trader_col)])else:wbs.write(row_idx, trader_col, '', s[sheet.cell_xf_index(row_idx, trader_col)])# 保存修改后的 Excel 文件output_buffer = io.BytesIO()wb.save(output_buffer)return output_buffer.getvalue()else:return Nonedef remove_col_xlsx(self, content, sheet_name, col_name, skiprows):"""支持xlsx后缀的处理方法:param content: 二进制:param sheet_name: sheet页:param col_name: 列名:param skiprows: 列名所在行:return: bytes | None   如果没找到指定列,则返回None"""# 从文件内容打开 Excel 工作簿input_buffer = io.BytesIO(content)wb = load_workbook(filename=input_buffer)# 选择指定的 sheetws = wb[sheet_name]# 找到需要删除的列索引for col_idx, cell in enumerate(ws[skiprows], start=1):if cell.value == col_name:breakelse:return None# 删除指定列for row in range(1, ws.max_row + 1):cell = ws.cell(row=row, column=col_idx)if cell.coordinate in ws.merged_cells:# 如果是合并单元格,则跳过continueif row == skiprows:cell.value = "备注"else:cell.value = ""# ws.cell(row=row, column=col_idx, value="")# 将修改后的工作簿保存到二进制 buffer 中output_buffer = io.BytesIO()wb.save(output_buffer)return output_buffer.getvalue()