将工作表从一个工作簿复制到另一个工作表使用Openpyxl
问题描述:
我有大量的EXCEL文件(即200)我想将一个特定的工作表从一个工作表复制到另一个工作表。我做了一些调查,我无法找到Openpyxl将工作表从一个工作簿复制到另一个工作表使用Openpyxl
这做这件事的方法是代码我迄今
def copy_sheet_to_different_EXCEL(path_EXCEL_read,Sheet_name_to_copy,path_EXCEL_Save,Sheet_new_name):
''' Function used to copy one EXCEL sheet into another file.
def path_EXCEL_read,Sheet_name_to_copy,path_EXCEL_Save,Sheet_new_name
Input data:
1.) path_EXCEL_read: the location of the EXCEL file along with the name where the information is going to be saved
2.) Sheet_name_to_copy= The name of the EXCEL sheet to copy
3.) path_EXCEL_Save: The path of the EXCEL file where the sheet is going to be copied
3.) Sheet_new_name: The name of the new EXCEL sheet
Output data:
1.) Status= If 0, everything went OK. If 1, one error occurred.
Version History:
1.0 (2017-02-20): Initial version.
'''
status=0
if(path_EXCEL_read.endswith('.xls')==1):
print('ERROR - EXCEL xls file format is not supported by openpyxl. Please, convert the file to an XLSX format')
status=1
return status
try:
wb = openpyxl.load_workbook(path_EXCEL_read,read_only=True)
except:
print('ERROR - EXCEL file does not exist in the following location:\n {0}'.format(path_EXCEL_read))
status=1
return status
Sheet_names=wb.get_sheet_names() # We copare against the sheet name we would like to cpy
if ((Sheet_name_to_copy in Sheet_names)==0):
print('ERROR - EXCEL sheet does not exist'.format(Sheet_name_to_copy))
status=1
return status
# We checking if the destination file exists
if (os.path.exists(path_EXCEL_Save)==1):
#If true, file exist so we open it
if(path_EXCEL_Save.endswith('.xls')==1):
print('ERROR - Destination EXCEL xls file format is not supported by openpyxl. Please, convert the file to an XLSX format')
status=1
return status
try:
wdestiny = openpyxl.load_workbook(path_EXCEL_Save)
except:
print('ERROR - Destination EXCEL file does not exist in the following location:\n {0}'.format(path_EXCEL_read))
status=1
return status
#we check if the destination sheet exists. If so, we will delete it
destination_list_sheets = wdestiny.get_sheet_names()
if((Sheet_new_name in destination_list_sheets) ==True):
print('WARNING - Sheet "{0}" exists in: {1}. It will be deleted!'.format(Sheet_new_name,path_EXCEL_Save))
wdestiny.remove_sheet(Sheet_new_name)
else:
wdestiny=openpyxl.Workbook()
# We copy the Excel sheet
try:
sheet_to_copy = wb.get_sheet_by_name(Sheet_name_to_copy)
target = wdestiny.copy_worksheet(sheet_to_copy)
target.title=Sheet_new_name
except:
print('ERROR - Could not copy the EXCEL sheet. Check the file')
status=1
return status
try:
wdestiny.save(path_EXCEL_Save)
except:
print('ERROR - Could not save the EXCEL sheet. Check the file permissions')
status=1
return status
#Program finishes
return status
发展有什么建议?
干杯
答
不能使用copy_worksheet()
工作簿之间进行复制,因为它依赖于全局常量可能簿之间会有所不同。唯一可行的安全可靠的方法是逐行和逐个单元地进行。
答
我也有类似的要求整理从多个工作簿的数据到一个工作簿。由于openpyxl中没有内置的方法。
我创建了下面的脚本来为我完成这项工作。
注意:在我的用例中,所有工作簿都包含相同格式的数据。
from openpyxl import load_workbook
import os
# The below method is used to read data from an active worksheet and store it in memory.
def reader(file):
global path
abs_file = os.path.join(path, file)
wb_sheet = load_workbook(abs_file).active
rows = []
# min_row is set to 2, to ignore the first row which contains the headers
for row in wb_sheet.iter_rows(min_row=2):
row_data = []
for cell in row:
row_data.append(cell.value)
# custom column data I am adding, not needed for typical use cases
row_data.append(file[17:-6])
# Creating a list of lists, where each list contain a typical row's data
rows.append(row_data)
return rows
if __name__ == '__main__':
# Folder in which my source excel sheets are present
path = r'C:\Users\tom\Desktop\Qt'
# To get the list of excel files
files = os.listdir(path)
for file in files:
rows = reader(file)
# below mentioned file name should be already created
book = load_workbook('new.xlsx')
sheet = book.active
for row in rows:
sheet.append(row)
book.save('new.xlsx')
答
我刚刚发现此问题。如here所述,一个很好的解决方法可能是修改内存中的原始wb
并用另一个名称保存。例如:
import openpyxl
# your starting wb with 2 Sheets: Sheet1 and Sheet2
wb = openpyxl.load_workbook('old.xlsx')
sheets = wb.sheetnames # ['Sheet1', 'Sheet2']
for s in sheets:
if s != 'Sheet2':
sheet_name = wb.get_sheet_by_name(s)
wb.remove_sheet(sheet_name)
# your final wb with just Sheet1
wb.save('new.xlsx')