Combine multiple Excel tables into a single table with Python

Life often comes across multiple excel tables aggregated into a single table, such as you issue a form for all class students to fill out, and you are responsible for merging the results of everyone into one. There are a lot of such problems. In addition to manually copying the contents of all tables to a summary table, how do you automate these tasks with Python?

I do not know if there is any more convenient method of merging, first use Python to achieve this function, it is very convenient for their own use.

For example, there are 7 tables below the folder (imagine that there are 100 or more tables that need to be merged)

As a sample, the contents of each table are

Run the program, merging 7 tables into a Test.xls

Open the Test.xls and find that the data of multiple tables has been successfully merged into a table

Before the code runs, you need to install NUMPY,XLRD,XLWT three expansion packs. Words don't say much, the code is as follows

#The following variables require you to choose according to your specific situationbiaotou=['School Number','Student Name','First Volunteer','Second Volunteer','Third Volunteer','Fourth Volunteer','Fifth Volunteer','Contact Phone','Sex','Notes']      #where to search for multiple tablesfilelocation="C:\\users\\ann\documents\\python scripts\\"      #filename suffix for search under current folderfileform="xls"      #where to store the merged tablefiledestination="C:\\users\\ann\documents\\python scripts\\"      #the merged table is named filefile="Test"            #first find out how many documents in the default folder need to be consolidated    ImportGlob fromNumPyImport*Filearray=[]       forFileNameinchGlob.glob (filelocation+"*."+fileform): filearray.append (filename)#The above is to read all the Excel tables from the Pythonscripts folder and store all the names in the list Filearray    Print("There are%d documents under the default folder Oh"%Len (filearray)) GE=len (filearray) Matrix= [none]*GE#implementing read-Write Data          #The following is the reading of all the files into the three-dimensional list cell[][][] (not including the table header)    Importxlrd forIinchRange (GE): fname=Filearray[i] BK=Xlrd.open_workbook (fname)Try: Sh=bk.sheet_by_name ("Sheet1")          except:              Print("Sheet1 not found in file%s, read file data failed, or do you want to change the name of the table? "%fname) nrows=sh.nrows Matrix[i]= [0]* (nrows-1) Ncols=Sh.ncols forMinchRange (nrows-1): Matrix[i][m]= ["0"]*Ncols forJinchRange (1, nrows): forKinchRange (0,ncols): Matrix[i][j-1][k]=Sh.cell (j,k). Value#The following is the writing of the data into the new form Test.xls Oh    ImportXLWT filename=XLWT. Workbook () sheet=filename.add_sheet ("Hel")      #Here's a list of the headers.     forIinchRange (0,len (Biaotou)): Sheet.write (0,i,biaotou[i])#sum how many lines are written in the previous fileZh=1 forIinchRange (GE): forJinchRange (len (matrix[i)): forKinchRange (len (matrix[i][j)): Sheet.write (zh,k,matrix[i][j][k]) en=zh+1Print("I've merged%d files into 1 files and named%s.xls. Just open it up and see if it's right. "%(Ge,file)) Filename.save (Filedestination+file+". xls")        

My operating environment is windows7, 64 bits. The Python version is the 3.5.1,32 bit.

Combine multiple Excel tables into a single table with Python