Life often encounters multiple excel tables summarized into one table, for example, you issued a form for all students in the class to fill out, and you are responsible for merging everyone's results into one. There are many such problems. In addition to manually copying the contents of all the forms one by one to the summary table, then how to use Python to automatically achieve these jobs it~!
I don't know if there's any other easier way to merge, it would be easy to implement this in Python first and use it yourself.
For example, under the folder there are 7 tables as follows (imagine 100 or more tables that need to be merged)
As a sample, the contents of each table were
Running the program merges the seven tables into the
Open it and find that you have successfully merged data from multiple tables into a single table
Before running the code, you need to install the Numpy,xlrd,xlwt extension packages. Without further ado, the code is as follows
# The following variables need to be selected according to your specific situation
biaotou=['School Number','Student Name','First volunteer','Second volunteer','Third volunteer','Fourth volunteer','The Fifth Volunteer','Contact number','Gender','Remarks']
#Where to search multiple forms
filelocation="C:\\Users\\ann\Documents\\Python Scripts\\"
# Suffixes of filenames searched in the current folder
fileform="xls"
# Locations to store merged tables to
filedestination="C:\\Users\\ann\Documents\\Python Scripts\\"
#The merged table is named file
file="test"
# First find out how many documents in the default folder need to be consolidated
import glob
from numpy import *
filearray=[]
for filename in (filelocation+"*."+fileform):
(filename)
# Above is reading all the excel sheets from the pythonscripts folder and storing all the names into the list filearray
print("There are %d documents in the default folder."%len(filearray))
ge=len(filearray)
matrix = [None]*ge
# Realize read and write data
# Below is reading data from all the files into the 3D list cell[][][] (without the table headers)
import xlrd
for i in range(ge):
fname=filearray[i]
bk=xlrd.open_workbook(fname)
try:
sh=bk.sheet_by_name("Sheet1")
except:
print ("In the file %s did not find sheet1, read the file data failed, or you change the name of the table?" %fname)
nrows=
matrix[i] = [0]*(nrows-1)
ncols=
for m in range(nrows-1):
matrix[i][m] = ["0"]*ncols
for j in range(1,nrows):
for k in range(0,ncols):
matrix[i][j-1][k]=(j,k).value
# Below is writing data to a new table Oh!
import xlwt
filename=()
sheet=filename.add_sheet("hel")
# Here's the header to put on the table
for i in range(0,len(biaotou)):
(0,i,biaotou[i])
# Sum the number of lines written in the previous file.
zh=1
for i in range(ge):
for j in range(len(matrix[i])):
for k in range(len(matrix[i][j])):
(zh,k,matrix[i][j][k])
zh=zh+1
print("I have merged %d files into 1 file and named it %. Open it up and see if it's correct."%(ge,file))
(filedestination+file+".xls")
I am running windows 7 , 64 bit. python version is 3.5.1, 32 bit.
This is the whole content of this article.