Shulou(Shulou.com)06/03 Report--

Editor to share with you how python merges multiple Excel according to sheet, I believe most people don't know much about it, so share this article for your reference. I hope you can learn a lot after reading this article. Let's learn about it together.

Work will encounter such requirements, there are multiple Excel format, there are multiple sheet, and the name and format of each sheet is the same, we need to merge according to sheet, that is to say, the format of the merged table and the merged money is the same. The table format of A, B and C is shown in the figure.

Now you need to merge into the following figure:

My processing is to keep the first row of the first table, and not the first row of the rest of the table. So the result will be different from the above, where all the first lines are saved.

Import xlrd,xlsxwriter# to merge excelallxls= ["C:/xxx/xxx.xlsx" "C:/xxx/xxx.xlsx"] # Target excelend_xls= "C:/xxx/merge.xlsx" def open_xls (file): try: fh=xlrd.open_workbook (file) return fh except Exception as e: print ("Open file error:" + e) # you can get the specific tag content def get_file_value (filename) based on the excel name and the tag information Sheetnum): rvalue= [] fh=open_xls (filename) sheet=fh.sheets () [sheetnum] row_num=sheet.nrows for rownum in range (0Med Rownum): rvalue.append (sheet.row_values (rownum)) return rvaluedef get_file_value_1 (filename,sheetnum): rvalue= [] fh=open_xls (filename) sheet=fh.sheets () [sheetnum] row_num=sheet.nrows for rownum in range (1) Row_num): rvalue.append (sheet.row_values (rownum)) return rvalue# gets the number of sheet of the first excel and the name as a standard first_file_fh=open_xls (allxls [0]) first_file_sheet=first_file_fh.sheets () first_file_sheet_num=len (first_file_sheet) sheet_name= [] for sheetname in first_file_sheet: sheet_name.append (sheetname.name) # defines a target excelendxls=xlsxwriter. Workbook (end_xls) all_sheet_value= [] # put everything in the list all_sheet_value for sheet_num in range (0 First_file_sheet_num): all_sheet_value.append ([]) for iMagne filewriting name in enumerate (allxls): if iMagname0: print ("reading the" + str (sheet_num+1) + "tag of" + file_name+ ".) File_value=get_file_value (file_name,sheet_num) all_sheet_ value [sheet _ num] .append (file_value) else: print ("reading the" + str (sheet_num+1) + "tag of" + file_name+ ") File_value=get_file_value_1 (file_name Sheet_num) all_sheet_ value [sheet _ num] .append (file_value) # print (all_sheet_value) num=-1sheet_index=-1# writes the contents of the list all_sheet_value to the target excelfor sheet in all_sheet_value: sheet_index+=1 end_xls_sheet=endxls.add_worksheet (sheet_ name [sheet _ index]) num+=1 num1=-1 for sheet1 in sheet: for sheet2 in sheet1 : num1+=1 num2=-1 for sheet3 in sheet2: num2+=1 # print (num Num1,num2,sheet3) # write the contents of sheet3 end_xls_sheet.write (num1,num2,sheet3) endxls.close () in column Num2 of line num 1 above is all the content of the article "how python merges multiple Excel according to sheet" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.