Python-将Excel文件的不同工作表另存为单个Excel文件 [英] Python - save different sheets of an excel file as individual excel files
问题描述
新手:我有一个Excel文件,其中包含100多种不同的图纸.每张纸都包含几个表格和图表.
Newbie : I have an Excel file, which has more than 100 different Sheets. Each sheet contains several tables and charts.
我希望将每个工作表另存为新的Excel文件.
I wish to save every sheet as a new Excel file.
我尝试了许多python代码,但没有一个起作用.
I tried many python codes, but none of them worked.
请对此提供帮助.谢谢!
Kindly help in this. Thanks!
为了回应评论,这是我尝试过的:
Edit 1 : In reponse to comments, this is what I tried:
import pandas as pd
import xlrd
inputFile = 'D:\Excel\Complete_data.xlsx'
#getting sheet names
xls = xlrd.open_workbook(inputFile, on_demand=True)
sheet_names = xls.sheet_names()
path = "D:/Excel/All Files/"
#create a new excel file for every sheet
for name in sheet_names:
parsing = pd.ExcelFile(inputFile).parse(sheetname = name)
#writing data to the new excel file
parsing.to_excel(path+str(name)+".xlsx", index=False)
确切地说,问题出在复制表格和图表中.
To be precise, the problem is coming in copying tables and charts.
推荐答案
我刚刚解决了此问题,因此将发布我的解决方案,我不知道它会如何影响图表等.
I have just worked through this issue so will post my solution, I do not know how it will affect charts etc.
import os
import xlrd
from xlutils.copy import copy
import xlwt
path = #place path where files to split up are
targetdir = (path + "New_Files/") #where you want your new files
if not os.path.exists(targetdir): #makes your new directory
os.makedirs(targetdir)
for root,dir,files in os.walk(path, topdown=False): #all the files you want to split
xlsfiles=[f for f in files] #can add selection condition here
for f in xlsfiles:
wb = xlrd.open_workbook(os.path.join(root, f), on_demand=True)
for sheet in wb.sheets(): #cycles through each sheet in each workbook
newwb = copy(wb) #makes a temp copy of that book
newwb._Workbook__worksheets = [ worksheet for worksheet in newwb._Workbook__worksheets if worksheet.name == sheet.name ]
#brute force, but strips away all other sheets apart from the sheet being looked at
newwb.save(targetdir + f.strip(".xls") + sheet.name + ".xls")
#saves each sheet as the original file name plus the sheet name
不太优雅,但对我来说效果很好,并且功能简单.希望对某人有用.
Not particularly elegant but worked well for me and gives easy functionality. Hopefully useful for someone.
这篇关于Python-将Excel文件的不同工作表另存为单个Excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!