Python Learning-windows use python27 to merge multiple xlsx files

Source: Internet
Author: User
Tags glob


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.