1. """ 
    2. 现在的需求变了。文件列表中的文件名，也就是项目名，统一放到第一列，
    3. 而且放到总表里的相同项目的行，第一列的项目名要合并单元格
    5. 需求又变了，因为合并单元格会干扰筛选，所以只能每一列都加名字
    6. """
    8. import os,glob
    9. import win32com.client 
    10. import win32api
    11. import re,time
    13. import io
    14. import sys
    15. sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf-8')
    16. #这三行为了让终端显示中文
    19. def getFilenameWithoutSuffix(path):
    20.     filename = os.path.basename(path)
    21.     filename = filename.split('.')[1] #文件名是22.xxxx.xlsx
    22.     return filename
    24. def matchStr(input_str,ref_str):
    25.     if type(input_str) == str:
    26.         match = re.search(ref_str,input_str)
    27.         if match:
    28.             return True
    29.         else:
    30.             return False
    31.     else:
    32.         return False
    34. def checkNewTable(sheet,rowNum,colNum):
    35.     for row in range(1,rowNum+1):
    36.         for col in range(1,colNum+1):
    37.             if matchStr(sheet.Cells(row,col).Value,'设备招标情况'):
    38.                 return [True,row,col]
    39.             elif matchStr(sheet.Cells(row,col).Value,'采购状态'):
    40.                 return [False,row,col]
    41.     print('\033[1;35m 查询不到表头! \033[0m')
    42.     return [0,0,0]
    44. def strProcess(input_str):
    45.     input_str = input_str.replace('$','')#去除$号
    46.     b=re.sub('[A-Za-z]','',input_str)#去除字母
    47.     return b
    49. #从"2:4"这样的字符串中获取差值
    50. def getDifNumber(input_str):
    51.     num_list = input_str.split(':')
    52.     dif_num = int(num_list[1]) - int(num_list[0])
    53.     return dif_num
    55. def pasteRows(sheet,row,paste):
    56.     paste_range = 'B' + str(row)
    57.     sheet.Range(paste_range).PasteSpecial(Paste=paste)#要带列宽的话需要多执行一遍Paste=8
    59. def getCellAddress(sheet,row,col):
    60.     temp_address = sheet.Cells(row,col).Address
    61.     cell_address = temp_address.replace('$','')
    62.     return cell_address
    64. def clearCacheInFilelist(filelist): #去除glob查到的~前缀的缓存文件
    65.     for i in range(len(filelist)-1,-1,-1):
    66.         match = re.search('~',filelist[i])
    67.         if match:
    68.             print(filelist[i])
    69.             filelist.remove(filelist[i])
    70.     return filelist
    72. def excludeOtherValues(input_str):
    73.     if type(input_str) == str:
    74.         match = re.search('已签|已定标|现场零星采购',input_str)
    75.         if match:
    76.             return True
    77.         else:
    78.             return False
    79.     else:
    80.         return False
    82. filelist = glob.glob(r'文件夹\*.xlsx')
    83. file_dir = clearCacheInFilelist(filelist)
    85. pos_col = 3       #以经办人这一列来确定是否为有效行
    86. sht2_row = 4      #开始复制的行
    88. color = int('E8E8E8',16)
    89. list1 = []
    91. first_num = 0
    92. trigger = False
    94. try:
    95.     xlApp = win32com.client.Dispatch('Excel.Application')
    97.     xlApp.Visible = False
    98.     xlBook2 = xlApp.Workbooks.Open(r'E:\测试\测试.xlsx')
    99.     sht2 = xlBook2.Sheets(1)
    100.     colNum2 = sht2.UsedRange.Columns.Count
    101.     for i in range(len(file_dir)):
    102.         xlBook1 = xlApp.Workbooks.Open(Filename=file_dir[i],ReadOnly=True)
    104.         filenameWithoutSuffix = getFilenameWithoutSuffix(file_dir[i])
    105.         print('\033[1;32;43m ',filenameWithoutSuffix,' \033[0m!')
    106.         sht1 = xlBook1.Sheets(1)            #默认选择第一张表
    107.         rowNum = sht1.UsedRange.Rows.Count  #返回文件1的行数
    108.         colNum = sht1.UsedRange.Columns.Count #返回文件1的列数
    110.         cell_state = checkNewTable(sht1,rowNum,colNum)
    111.         header_row = cell_state[1] #如果表头是合并单元格,也没必要加一
    112.         match_col = cell_state[2] #匹配列的列数
    113.         if cell_state[0]==True:
    114.             for row in range(header_row+1,rowNum+1):
    115.                 pos_cell = sht1.Cells(row,pos_col)
    116.                 cell = sht1.Cells(row,match_col)
    117.                 print('第',row,'行:',end=' ')
    118.                 sys.stdout.flush()  #刷新缓存区
    119.                 if type(pos_cell.Value) == str:
    120.                     if pos_cell.MergeCells :
    121.                         temp_address = sht1.Cells(row,pos_col).MergeArea.Address#获取合并单元格的地址
    122.                         rows_range = strProcess(temp_address)#清洗得到的字符串
    123.                         print('合并单元格行数--',rows_range)
    124.                         sys.stdout.flush()
    126.                         temp_range = rows_range.split(':')
    127.                         copy_list = ['A',str(temp_range[0]),':','AJ',str(temp_range[1])]
    128.                         copy_area = ''.join(copy_list)
    130.                         dif_num = getDifNumber(rows_range)
    132.                         if excludeOtherValues(cell.Value) == False :
    133.                             if first_num == 0:
    134.                                 first_num = sht2_row
    135.                             trigger = True
    137.                             print(cell.Value)
    138.                             sht1.Range(copy_area).Copy()   #复制这几行
    139.                             pasteRows(sht2,sht2_row,14)     #粘贴到总表
    141.                             cell_address = getCellAddress(sht2,sht2_row,1)
    142.                             #这两行在表末尾增加了一列，给单元格赋值，内容是表名称
    143.                             #last_cell = getCellAddress(sht2,sht2_row + dif_num,colNum+1)
    144.                             last_cell = getCellAddress(sht2,sht2_row + dif_num,1)
    145.                             #获取首末单元格地址
    147.                             merge_range = cell_address + ':' + last_cell
    148.                             sht2.Range(merge_range).Value = filenameWithoutSuffix
    150.                             print('复制到表2第',sht2_row,'行',end='***')
    151.                             sht2_row += dif_num + 1
    152.                             print('下次行数：',sht2_row)
    153.                             sys.stdout.flush()
    154.                     else:
    155.                         print('\033[1;35m 非空标准单元格! \033[0m',pos_cell.Value)
    156.                         if matchStr(cell.Value,'合同已签')==False :
    157.                             trigger = True
    158.                             sht1.Rows(row).Copy()#复制这几行
    159.                             pasteRows(sht2,sht2_row,14)
    160.                             sht2_row += 1
    161.                 sys.stdout.flush()  #刷新缓存区
    162.             if trigger == True:
    163.                 last_num = sht2_row - 1
    164.                 copy_rows = str(first_num) + ':' + str(last_num)
    165.                 first_num = last_num + 1
    166.                 list1.append(copy_rows)
    167.                 trigger = False
    169.         sht2.Cells.FormatConditions.Delete()#清除条件格式，不然格子的颜色会乱掉
    170.         for i in range(len(list1)):
    171.             if i % 2 == 0:
    172.                 sht2.Range(list1[i]).Interior.Color = color
    173.         xlApp.CutCopyMode = False
    174.         xlBook1.Close(False)
    175.         xlBook2.Save()
    176.     sht2.PageSetup.PrintTitleRows = "$2:$3"#设置打印顶端标题(即每一页都要打印23行)
    177.     # sht2.PageSetup.PrintTitleColumns = "$A:$B"#设置重复打印列
    178.     # sht2.PageSetup.PrintTitleColumns = ""#取消设置
    179.     # sht2.PageSetup.PrintTitleColumns = False #取消设置
    180. finally:
    181.     # if xlApp.ClipboardFormats[0]:
    182.     #     xlApp.CutCopyMode = False#清空剪贴板
    183.     xlApp.quit()
    184.     print(list1)