import os.path from win32com.client import Dispatch excel = Dispatch("ket.Application") # 启动wps excel 应用。Microsoft excel用Excel.Application excel.Visible = True end_file_basepath = r'C:\Users\meiya\Desktop\数据分省\地区' source_basepath = r'C:\Users\meiya\Desktop\数据分省' source_file_name = '构代码信息核对明细表.xls' source_file = os.path.join(source_basepath,source_file_name) wb = excel.Workbooks.Open(source_file) # 打开excel文档 for sheet in wb.Worksheets: rows_max = sheet.usedRange.Rows.Count # 获取已使用范围的最大行数 rowA_Range = sheet.Range(f'A5:A{rows_max}').value rowA_set = set(rowA_Range) rowA = [r[0] for r in rowA_set ] for r in rowA: sheet.UsedRange.AutoFilter(Field=1, Criteria1=r, Operator=1) # 也可以写 sheet.Range('A1').AutoFilter(Field=1, Criteria1=r, Operator=1) 筛选时,range可以写a1,也可以写更大的范围 for item in os.listdir(end_file_basepath): provience = item.split('.')[1] if r.find(provience) >= 0: tem_file = os.path.join(source_basepath,sheet.name) wb_tem = excel.Workbooks.Open(tem_file) # 打开excel文档 sheet_tem = wb_tem.Worksheets[0] row_mx = sheet_tem.UsedRange.Rows.Count+1 if sheet.name == 'AA列开头为99(法人行)': start = 4 else: start = 2 sheet.Range(f'A{start}:V{sheet.UsedRange.Rows.Count}').SpecialCells(12).Copy(sheet_tem.Range(f'A{row_mx}')) sheet_tem.name = sheet.name provience_folder = os.path.join(end_file_basepath, item) provience_file = os.path.join(provience_folder,provience+'-'+sheet.name) wb_tem.SaveAs(provience_file) wb.Save() wb.Close() excel.Quit()
筛选参考文章: Range单元格对象方法(二)AutoFilter自动筛选-腾讯云开发者社区-腾讯云 (tencent.com)
特殊单元格参考文章:Range单元格对象方法(三)Specialcells方法-腾讯云开发者社区-腾讯云 (tencent.com)
EXCEL 常见操作:常见 win32.com 操作_win32com-CSDN博客
标签:sheet,tem,excel,source,Range,win32com,file,筛选 From: https://www.cnblogs.com/zhangmeiyan/p/17903743.html