使用python将多个xlsx文件合并到一个sheet中
1. 安装库
pip install xlrd==1.2.0
pip install openpyxl
2. 代码执行可能遇到的问题
- 读取excel时报错AttributeError: ‘ElementTree’ object has no attribute ‘getiterator’,可能在这里出错:xlrd.open_workbook(path)
- 原因:更新删除了getiterator方法,老版本的xlrd库调用getiterator方法时会报错。
- 解决:
- 获取xlrd的目录:在终端输入
pip show xlrd
- 进入目录下,打开xlsx.py,将其中的
getiterator()
替换为iter()
,保存即可
3. 代码
import os
import xlrd
import xlwt
import openpyxl
excel_dir = r'C:\Users\97875\Desktop\test'
des_path = r'C:\Users\97875\Desktop\test.xlsx'
input_str = input(f'xlsx文件位于:{excel_dir},输出文件为:{des_path}\n输入go继续,任意键退出。请输入:')
if input_str == "go":
excel_list = os.listdir(excel_dir)
excel_list.sort()
total = len(excel_list)
for num,name in enumerate(excel_list):
print(num+1,name)
workbook = openpyxl.Workbook()
to_sheet = workbook.active
line = 0
for table_index,excel_name in enumerate(excel_list):
path = os.path.join(excel_dir,excel_name)
from_excel = xlrd.open_workbook(path)
sheets = from_excel.sheet_names()
from_sheet = from_excel.sheet_by_name(sheets[0])
rows=from_sheet.nrows
cols=from_sheet.ncols
data=[]
start = 0 if table_index == 0 else 1
for i in range(start,rows):
data.append(from_sheet.row_values(i))
for i in range(0,len(data)):
for j in range(len(data[i])):
to_sheet.cell(line+1,j+1,data[i][j])
line += 1
print(f'复制进度:{(table_index+1)/total*100:.2f}%',end='\r')
workbook.save(des_path)
else:
print('复制已被手动终止!')