|
将当前某个目录下所有Excel文件合并(纵向)到一个excel文件中程序如下:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import pandas as pd
def get_files(path):
fs = []
for root, dirs, files in os.walk(path):
for file in files:
file_name = file.split(".")[0]
file_type = file.split(".")[1]
if file_type in ["xls", "xlsx"] and not file_name[0:2] == "合并":
fs.append(os.path.join(root, file))
print(fs)
return fs
def hebing(path,sc_filename):
# 批量表所在文件夹路径,指定为当前目录下的xlsdata目录
files = get_files(path)
arr = []
for i in files:
print(i)
arr.append(pd.read_excel(i))
# 目标文件路径为当前目录
writer = pd.read_excelwriter(sc_filename)
pd.concat(arr).to_excel(writer, 'sheet1', index=False)
writer.save()
return
if __name__ == '__main__':
xlsfiledir = './xlsdata/'
sc_filename = "./xlsdata合并文件.xlsx"
hebing(xlsfiledir, sc_filename)
运行时错误提示如下:
Traceback (most recent call last):
File "/Users/jiankunlou/PycharmProjects/test/合并多个同类xls文件.py", line 36, in <module>
hebing(xlsfiledir, sc_filename)
File "/Users/jiankunlou/PycharmProjects/test/合并多个同类xls文件.py", line 25, in hebing
arr.append(pd.read_excel(i))
File "/Users/jiankunlou/PycharmProjects/test/venv/lib/python3.7/site-packages/pandas/util/_decorators.py", line 296, in wrapper
return func(*args, **kwargs)
File "/Users/jiankunlou/PycharmProjects/test/venv/lib/python3.7/site-packages/pandas/io/excel/_base.py", line 304, in read_excel
io = ExcelFile(io, engine=engine)
File "/Users/jiankunlou/PycharmProjects/test/venv/lib/python3.7/site-packages/pandas/io/excel/_base.py", line 867, in __init__
self._reader = self._engines[engine](self._io)
File "/Users/jiankunlou/PycharmProjects/test/venv/lib/python3.7/site-packages/pandas/io/excel/_xlrd.py", line 22, in __init__
super().__init__(filepath_or_buffer)
File "/Users/jiankunlou/PycharmProjects/test/venv/lib/python3.7/site-packages/pandas/io/excel/_base.py", line 353, in __init__
self.book = self.load_workbook(filepath_or_buffer)
File "/Users/jiankunlou/PycharmProjects/test/venv/lib/python3.7/site-packages/pandas/io/excel/_xlrd.py", line 37, in load_workbook
return open_workbook(filepath_or_buffer)
File "/Users/jiankunlou/PycharmProjects/test/venv/lib/python3.7/site-packages/xlrd/__init__.py", line 170, in open_workbook
raise XLRDError(FILE_FORMAT_DESCRIPTI**[file_format]+'; not supported')
xlrd.biffh.XLRDError: Excel xlsx file; not supported
Process finished with exit code 1
请问错在哪里?该怎么解决?
|
|