Use Python to merge multiple Excel

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