Use Python to merge multiple Excel

Source: Internet
Author: User

recently watching video learning, the teacher assigned a homework, about how to use Python to merge multiple Excel, the teacher wrote the code I feel more complex, the following is my own improvement after the simpler way.

There are two main ways to implement this function, one is to use Xlrd,xlsxwriter library combination, whether xlsx,xls are applicable, and the other is OPENPYXL, this library is only valid for xlsx.
This article explains the first way.
Start by preparing three Excel files to merge the sheet of three Excel:

Test_excel1.xlsx

In order to simplify the test, the other two Excel files test_excel2.xlsx, test_excel3.xlsx content is consistent with the first Excel.

Here is the Code section:

1 #-*-coding:utf-8-*-2 3 ImportXlrd,xlsxwriter4 5 #pending Merge Excel6allxls=["e:\\python3_hellobi_work\\excel\\test_excel\\test_excel1.xlsx",7         "e:\\python3_hellobi_work\\excel\\test_excel\\test_excel2.xlsx",8         "e:\\python3_hellobi_work\\excel\\test_excel\\test_excel3.xlsx"]9 Ten #Target Excel Oneend_xls="e:\\python3_hellobi_work\\excel\\test_excel\\final_excel.xlsx" A  -  - defOpen_xls (file): the     Try: -Fh=xlrd.open_workbook (file) -         returnFH -     exceptException as E: +         Print("Error opening file:"+e) -  +  A #the content of specific tags can be obtained according to the Excel name and the first few tag information at defGet_file_value (filename,sheetnum): -Rvalue=[] -Fh=open_xls (filename) -sheet=fh.sheets () [Sheetnum] -row_num=sheet.nrows -      forRowNuminchRange (0,row_num): in rvalue.append (Sheet.row_values (rownum)) -     returnrvalue to  +  - #gets the number of sheet for the first Excel and the name as the standard theFirst_file_fh=Open_xls (allxls[0]) *first_file_sheet=first_file_fh.sheets () $first_file_sheet_num=Len (first_file_sheet)Panax NotoginsengSheet_name=[] -  forSheetNameinchFirst_file_sheet: the sheet_name.append (sheetname.name) +   A  the #define a target Excel +endxls=Xlsxwriter. Workbook (End_xls) -  $All_sheet_value=[] $  - #Put everything in the list All_sheet_value -  forSheet_numinchRange (0,first_file_sheet_num): the all_sheet_value.append ([]) -      forfile_nameinchAllxls:Wuyi         Print("is reading"+file_name+"the first"+str (sheet_num+1) +"a label ...") theFile_value=Get_file_value (file_name,sheet_num) - all_sheet_value[sheet_num].append (File_value) Wu  - #print (All_sheet_value) About  $Num=-1 -Sheet_index=-1 -  - #writes the contents of a list all_sheet_value to the target Excel A  forSheetinchAll_sheet_value: +Sheet_index+=1 theend_xls_sheet=Endxls.add_worksheet (Sheet_name[sheet_index]) -Num+=1 $Num1=-1 the      forSheet1inchSheet: the          forSheet2inchSheet1: theNum1+=1 theNum2=-1 -              forSheet3inchSheet2: inNum2+=1 the                 #print (NUM,NUM1,NUM2,SHEET3) the                 #write the contents of Sheet3 in column num2 of line NUM1 About end_xls_sheet.write (NUM1,NUM2,SHEET3) the  the  theEndxls.close ()

Run the code to get the target Excel:

All right, I'm done.

Use Python to merge multiple Excel

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.