Python explains the integration examples of multiple sheet tables

1. Description

xlwt module is non-appended write. xls module, so it is necessary to write for loop and list once, so there is no append and non-append.

And merge the Excel tables, Take every Excel table as a row, Merge immediately, In another way, if the tags in the Excel table are taken as columns, column merging can be carried out, that is, different tags composed of the same tags in different files can be merged. First, the same tags in different files can be merged, and the same tags in different files can form a list. Then, the contents of all Excel files can be obtained by merging the different tags previously composed.

2. Examples

#导入xlrd和xlwt模块
#xlrd模块是读取.xls的Excel文件的模块，xlwt模块是以非追加的方式写.xls的Excel文件的模块
import xlrd,xlwt
#导入要读的文件的路径
a=["C:/Users/Desktop/m1.xls","C:/Users/Desktop/m2.xls"]
#定义要写的文件的路径
b="C:/Users/Desktop/m3.xls"
#定义函数，判断打开文件是否会发生异常
def open(fileaddress):
    try:
#通过xlrd模块的open_workbook()方法，打开1个Excel文件，定义变量fo为文件句柄
        fo=xlrd.open_workbook(fileaddress)
        return fo
    except Exception as e:
        print("error!"+str(e))
#定义函数，得到某1个Excel文件下某1个标签的行数
def getrows(fo,num):
    table=fo.sheets()[num]
#sheets()函数为xlrd模块自带函数，能以列表的形式返回该Excel文件的标签
    n=table.nrows
#nrows为xlrd模块自带函数，通过某1个标签调用，可得到该标签的行数
    return n
"""
定义函数，可通过Excel文件路径和标签的序列编号得到
任意Excel文件任意标签的内容,且以列表的形式返回
"""
def getsheetcontents(fileaddress,num):
    avalue=[]
    fo=open(fileaddress)
    table=fo.sheets()[num]
    n=table.nrows
    for i in range(0,n):
        """
        row_values()函数为xlrd模块自带函数，通过某1个标签调用，
        可得到该标签的行数据,且这些行数据以列表形式返回，标签中
        1列为1个列表的元素
        """
        rdate=table.row_values(i)
        avalue.append(rdate)
    return avalue
svalue=[]
k=[]
#通过for循环得到所有Excel文件的标签数，且以列表的形式返回
for i in a:
    fo=open(i)
    k.append(len(fo.sheets()))
#对这些标签数进行升序排序
k.sort()
#通过for循环把这些Excel文件中不同标签的所有内容放在列表svalue中
for i in range(0,k[len(k)-1]):
#通过for循环把不同Excel文件的同1个标签放在列表bvalue中
    bvalue=[]
    for j in a:
        print("正在读取"+str(j)+"的第"+str(i)+"个标签")
        bvalue.append(getsheetcontents(j,i))
    svalue.append(bvalue)
#svalue里面就是所有Excel文件的内容
 
#通过xlwt模块的Workbook()创建1个.xls文件,定义1个变量fw为文件句柄
fw=xlwt.Workbook()
line=0
#add_sheet()函数为xlwt模块自带函数，可得到1个自定义标签
#定义变量ws为标签句柄
ws=fw.add_sheet("sheet1")
"""
通过for循环遍历svalue列表,把最终得到的
某个标签某个文件某行某列的数据写入新标签中
"""
for i in range(0,len(svalue)):
    for j in range(0,len(svalue[i])):
        for m in range(0,len(svalue[i][j])):
            for n in range(0,len(svalue[i][j][m])):
                ws.write(line,n,svalue[i][j][m][n])
#通过line可得到不断增加的行数
            line+=1
print("合并完成")
#save()函数为xlwt自带函数，将合并好的Excel文件保存到某个路径下
fw.save(b)
#xlrd模块和xlwt模块都没有close()函数，即用这两个模块打开文件不用关闭文件

Instance extension:

Example 1:

#!/usr/bin/env python3
# Read Excel Documents 
import pandas as pd
input_file = "F://python Getting started // Data 2//appname_test.xlsx"
output_file = "F://python Getting started // Data 2//output.xlsx"
data_frame = pd.read_excel(input_file,sheet_name='sum1',index_col = None)
data_frame_country = data_frame['country']
category_countory = set(data_frame_country)
writer = pd.ExcelWriter(output_file)
for country in list(category_countory):
  df = data_frame[data_frame['country'] == country]
  df.to_excel(writer, sheet_name= country ,index=False)
writer.save()

Example 2:

#!/usr/bin/env python3
# Read Excel Documents 
import pandas as pd
input_file = "F://python Getting started // Data 2//appname_test.xlsx"
data_frame = pd.read_excel(input_file,sheet_name='sum1',index_col = None)
data_frame_country = data_frame['country']
category_countory = set(data_frame_country)
for country in list(category_countory):
  df = data_frame[data_frame['country'] == country]
  df.to_csv("F:/python Getting started / Data 2/table_{}.csv".format(country), encoding="gbk", index=False)