win32com python 操作wps 是很方便的一个东西
之前唯一的缺点就是会关闭wps的表格。
解决思路
新开wps 进程来处理

python">import os
import subprocess
import win32com.client as win32
import timedata = []
def get_col_value(sheet):# 参数设置row_index = 2  # 第二行start_column = 6  # F 列是第 6 列start_row = 6  # 从第 6 行开始# 获取最后一列和最后一行last_column = sheet.UsedRange.Columns.Countlast_row = sheet.UsedRange.Rows.Count# 存储数据for col in range(start_column, last_column + 1):# 检查当前列第 6 行及其以下是否有数据has_data_below = any(sheet.Cells(row, col).Value is not None for row in range(start_row, last_row + 1))if has_data_below:  # 如果列第 6 行以下有数据second_row_value = sheet.Cells(row_index, col).Value  # 获取第二行的值# 获取第 6 行及其以下的非空值及对应行号column_values_with_rows = [{"row": row, "value": sheet.Cells(row, col).Value}for row in range(start_row, last_row + 1)if sheet.Cells(row, col).Value is not None]column_letter = sheet.Cells(1, col).Address.split('$')[1]  # 获取列字母data.append({"column": column_letter,"second_row_value": second_row_value,"values_from_6th_row": column_values_with_rows,})def get_all_excel_files(directory):"""获取指定目录及其子目录中的所有 Excel 文件"""excel_files = []for root, _, files in os.walk(directory):for file in files:if file.endswith(('.xls', '.xlsx')):excel_files.append(os.path.join(root, file))return excel_filesclass WPS_handle:def get_curr_path(self):# 获取当前脚本所在目录的绝对路径current_dir = os.path.abspath(os.getcwd())# 获取所有 Excel 文件all_excel_files = get_all_excel_files('数据源')file_path = all_excel_files[0]self.root_padth = f'{current_dir}\\{file_path}'def get_wps_path(self):for root, dirs, files in os.walk("C:/"):if "wps.exe" in files:self.wps_padth = os.path.join(root, "wps.exe")return Truereturn Falsedef run_wps_processes(self):# 启动多个独立的 WPS 进程num_instances = 1  # 启动 1 个 WPS 实例# 启动每个进程并打开文件process = subprocess.Popen([self.wps_padth])  # 启动一个新的 WPS 进程self.app = win32.Dispatch("ket.Application")time.sleep(3)# 使用 COM 接口连接到每个 WPS 实例def handle_wps(self):# 打开不同的文件并显示在不同的窗口中try:file_paths = self.root_padthself.app.Visible = True  # 设置为可见，以显示 WPS 窗口# 打开不同的工作簿workbook = self.app.Workbooks.Open(file_paths)worksheet = workbook.Worksheets('Sheet1')worksheet_2 = workbook.Worksheets('Sheet2')worksheet_2.Cells(1, 1).Value = "姓名"worksheet_2.Cells(1, 2).Value = "实动工时"worksheet_2.Cells(1, 3).Value = "工事代码"worksheet_2.Cells(1, 4).Value = "分段"worksheet_2.Cells(1, 5).Value = "班组"worksheet_2.Cells(1, 6).Value = "日期"worksheet_2.Cells(1, 7).Value = "船号"date = worksheet.Cells(2, 2).Valueclass_ = worksheet.Cells(2, 4).Valueget_col_value(worksheet)star_row = 2for item in data:for n in item['values_from_6th_row']:worksheet_2.Cells(star_row, 1).Value = item['second_row_value']worksheet_2.Cells(star_row, 2).Value = n['value']worksheet_2.Cells(star_row, 3).Value = worksheet.Cells(n['row'], 5).Valueworksheet_2.Cells(star_row, 4).Value = worksheet.Cells(n['row'], 2).Valueworksheet_2.Cells(star_row, 5).Value = class_worksheet_2.Cells(star_row, 6).Value = dateworksheet_2.Cells(star_row, 7).Value = worksheet.Cells(n['row'], 1).Valuestar_row+= 1# 保存更改workbook.Save()# 关闭工作簿workbook.Close(SaveChanges=True)except Exception as e:print(f"发生错误: {e}")finally:# 退出每个 WPS 实例# app.Quit()del self.app# 结束所有启动的进程# for process in processes:#     process.terminate()print("操作完成")def __init__(self):self.root_padth = ''self.wps_padth = ''self.app = ''self.get_curr_path()self.get_wps_path()if self.wps_padth:print(f"找到 WPS 路径：{self.wps_padth}")else:print("未找到 WPS 安装路径")self.run_wps_processes()self.handle_wps()if __name__ == "__main__":WPS_handle()