常用的是pandas读写,但对于一些有格式的表格,pandas无论如何不能跳过某些格式行。导致合并之后出问题
import os
import pandas as pd
import numpy as np
import xlrd
dir = r"C:\Users\lyp\Documents\WeChat Files\W2956224\FileStorage\File\2020-04\res"#设置工作路径
shuchu='D:\\merge\\sheet2.xlsx'
filename_excel = []
frames = []
count=0
for root, dirs, files in os.walk(dir):
for file in files:
filename_excel.append(os.path.join(root,file))
count+=1
print(count)
print(file)
data=xlrd.open_workbook(os.path.join(root,file))
table = data.sheet_by_index(1)
onesheet=[]
for rowNum in range(3,table.nrows):
rowVale = table.row_values(rowNum)
onesheet.append(rowVale)
data=pd.DataFrame(onesheet)
frames.append(data)
#打印文件名
print(filename_excel)
#合并所有数据
result = pd.concat(frames)
#查看合并后的数据
result.head()
result.shape
result.to_excel(shuchu)#保存合并的数据到电脑D盘的merge文件夹中,并把合并后的文件命名为a12.csv