Python Learning-windows use python27 to merge multiple xlsx files

Sometimes there are many reports that need to be processed, merging all of the Excel format reports in a single directory, and manually working hard if you can use Python, it is convenient to combine multiple. xlsx simultaneously with more than one Excel table.

1. Python configuration on Windows

python2.7 is installed on Windows and requires a few third-party packages to be installed with PIP, so it needs to be configured. It's easy to modify the PIP source on Linux, but it's cumbersome on windows, so I just specify the pip source to download the package directly

#windows上pip执行路径为C: \python27\scriptspip install-u pip-i https://pypi.douban.com/simple/#升级pippip install-i OPENPYXL #处理excel表格

2. Merging multiple xlsx tables

Write a script to merge all xlsx files under the E:\excel\ directory

#!/env/python#coding=utf-8# #合并多个excel表格 # #import  OSIMPORT GLOBIMPORT OPENPYXLDEF MERGE_XLSX _files (xlsx_files):   #定义函数合并xlsx文件     wb = openpyxl.load_workbook (xlsx _files[0])   #调用openpyxl模块loda_workbook函数     ws = wb.active                                 #获取活跃的Worksheet     ws.title =   "Merged result"                     #定义工作表标题     for filename in xlsx_files[1:]:              #循环xlsx_files参数, get the first worksheet (only one)          workbook = openpyxl.load_workbook (filename)    #调用函数         sheet = workbook.active                     #获取活跃的表格          for row in sheet.iter_rows (min_row=2):  #遍历其他文件, ignoring first line content              values = [cell.value for cell in  row]  #循环获取单元格的值             ws.append ( Values)                         #将值依次添加末尾         return wb                                       # Back to Def get_all_xlsx_files (path):                          #定义获取所有xlsx文件     xlsx_files =  Glob.glob (Os.path.join (Path, ' *.xlsx '))   #采用glob方法指定路径下所有. xlsx file     sorted (xlsx_files, Key=str.lower)                        #按照关键字字符串小写排序     return xlsx_filesdef main ():                                            #定义主函数     xlsx_files = get_all_xlsx_files ( os.path.expanduser  ("e:\\excel\\"))   #定义变量xlsx_files为get_all_xlsx_files函数, specify the parameter for the specified directory      wb = merge_xlsx_files (xlsX_files)                               #定义wb为merge_xlsx_files函数, specifying parameters for traversal      wb.save (' merged_form.xlsx ')                                      #save方法将汇总表保存到merged_form. xlsxif __name__ == ' __main__ ':     main ()

Complete the merge as shown below

Python Learning-windows use python27 to merge multiple xlsx files