Python处理Excel文件:
#只有一个Excel文件且该excel中多个sheet:
import pandas as pd
import os as los
import matplotlib.pyplot as mt
sheetnames = ['sheet1','Sheet2']
for sheet_name in sheetnames:
df = None
peoples=pd.read_excel('测试.xlsx',sheet_name=sheet_name)
print(peoples)
#Excel合并:读取多个excel的内容(汇总多个excel---每个excel只有一个工作薄有内容:以便查看XXX):
# 导入模块
import os
import pandas as pd
def get_files(path):
fs = []
for root, dirs,files in os.walk(path):
for file in files:
fs.append(os.path.join(root,file))
return fs
def merge():
# 需要处理的文件放在:批量表所在文件夹路径(D:/Python/Test目录下)
files = get_files('./Test/')
arr = []
for i in files:
arr.append(pd.read_excel(i))
# 目标文件的路径(D:/Python目录下)
writer = pd.ExcelWriter('./hz.xlsx')
pd.concat(arr).to_excel(writer,'sheet1',index=False)
writer.save()
if __name__ == '__main__':
merge()
#Excel合并:读取多个excel的内容(汇总多个excel---每个个excel有多个工作薄有内容:以便查看XXX):
import os
import pandas as pd
import openpyxl
# 将excel中的sheet名称放入列表
sheet_names = ['Sheet1','Sheet2','Sheet3']
xlsx_names = [x for x in os.listdir() if x.endswith('.xlsx')]
#需要处理的多个文件放在D:/Python目录下与.py文件同一目录
#将合并后的结果放在以下目录中(与.py文件在同一目录下的下一级目录)
writer = pd.ExcelWriter('./Hb/hz.xlsx',engine='openpyxl')
num = 1
for sheet_name in sheet_names:
df = None
for xlsx_name in xlsx_names:
_df = pd.read_excel(xlsx_name, sheet_name=sheet_name)
if df is None:
df = _df
else:
df = pd.concat([df, _df], ignore_index=True)
# 下面的保存文件处填写writer,结果会不断地新增sheet,避免循环时被覆盖
df.to_excel(excel_writer=writer, sheet_name=sheet_name, encoding="utf-8", index=False)
print(sheet_name + " 保存成功!共%d个,第%d个。" % (len(sheet_names),num))
num += 1
writer.save()
writer.close()