我有大量的 EXCEL 文件(即 200 个)我想将一个特定的工作表从一个工作簿复制到另一个工作簿.我进行了一些调查，但找不到使用 Openpyxl 的方法
I have a large amount of EXCEL files (i.e. 200) I would like to copy one specific worksheet from one workbook to another one. I have done some investigations and I couldn't find a way of doing it with 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:
{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:
{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
我也遇到了同样的问题.对我来说，风格、格式和布局非常重要.此外，我不想复制公式，而只想复制(公式的)值.经过大量的跟踪、错误和 stackoverflow，我想出了以下函数.它可能看起来有点吓人，但代码将工作表从一个 Excel 文件复制到另一个(可能存在的文件)，同时保留:
I had the same problem. For me style, format, and layout were very important. Moreover, I did not want to copy formulas but only the value (of the formulas). After a lot of trail, error, and stackoverflow I came up with the following functions. It may look a bit intimidating but the code copies a sheet from one Excel file to another (possibly existing file) while preserving:
当您想从许多工作簿中收集工作表并将它们绑定到一个工作簿中时，它很有用.我复制了大多数属性，但可能还有更多.在这种情况下，您可以将此脚本用作添加更多内容的起点.
It is useful when you want to gather sheets from many workbooks and bind them into one workbook. I copied most attributes but there might be a few more. In that case you can use this script as a jumping off point to add more.
###############
## Copy a sheet with style, format, layout, ect. from one Excel file to another Excel file
## Please add the ..path\+\file.. and ..sheet_name.. according to your desire.
import openpyxl
from copy import copy
def copy_sheet(source_sheet, target_sheet):
copy_cells(source_sheet, target_sheet) # copy all the cel values and styles
copy_sheet_attributes(source_sheet, target_sheet)
def copy_sheet_attributes(source_sheet, target_sheet):
target_sheet.sheet_format = copy(source_sheet.sheet_format)
target_sheet.sheet_properties = copy(source_sheet.sheet_properties)
target_sheet.merged_cells = copy(source_sheet.merged_cells)
target_sheet.page_margins = copy(source_sheet.page_margins)
target_sheet.freeze_panes = copy(source_sheet.freeze_panes)
# set row dimensions
# So you cannot copy the row_dimensions attribute. Does not work (because of meta data in the attribute I think). So we copy every row's row_dimensions. That seems to work.
for rn in range(len(source_sheet.row_dimensions)):
target_sheet.row_dimensions[rn] = copy(source_sheet.row_dimensions[rn])
if source_sheet.sheet_format.defaultColWidth is None:
print('Unable to copy default column wide')
else:
target_sheet.sheet_format.defaultColWidth = copy(source_sheet.sheet_format.defaultColWidth)
# set specific column width and hidden property
# we cannot copy the entire column_dimensions attribute so we copy selected attributes
for key, value in source_sheet.column_dimensions.items():
target_sheet.column_dimensions[key].min = copy(source_sheet.column_dimensions[key].min) # Excel actually groups multiple columns under 1 key. Use the min max attribute to also group the columns in the targetSheet
target_sheet.column_dimensions[key].max = copy(source_sheet.column_dimensions[key].max) # https://stackoverflow.com/questions/36417278/openpyxl-can-not-read-consecutive-hidden-columns discussed the issue. Note that this is also the case for the width, not onl;y the hidden property
target_sheet.column_dimensions[key].width = copy(source_sheet.column_dimensions[key].width) # set width for every column
target_sheet.column_dimensions[key].hidden = copy(source_sheet.column_dimensions[key].hidden)
def copy_cells(source_sheet, target_sheet):
for (row, col), source_cell in source_sheet._cells.items():
target_cell = target_sheet.cell(column=col, row=row)
target_cell._value = source_cell._value
target_cell.data_type = source_cell.data_type
if source_cell.has_style:
target_cell.font = copy(source_cell.font)
target_cell.border = copy(source_cell.border)
target_cell.fill = copy(source_cell.fill)
target_cell.number_format = copy(source_cell.number_format)
target_cell.protection = copy(source_cell.protection)
target_cell.alignment = copy(source_cell.alignment)
if source_cell.hyperlink:
target_cell._hyperlink = copy(source_cell.hyperlink)
if source_cell.comment:
target_cell.comment = copy(source_cell.comment)
wb_target = openpyxl.Workbook()
target_sheet = wb_target.create_sheet(..sheet_name..)
wb_source = openpyxl.load_workbook(..path\+\file_name.., data_only=True)
source_sheet = wb_source[..sheet_name..]
copy_sheet(source_sheet, target_sheet)
if 'Sheet' in wb_target.sheetnames: # remove default sheet
wb_target.remove(wb_target['Sheet'])
wb_target.save('out.xlsx')
这篇关于如何使用openpyxl将工作表从一个工作簿复制到另一个工作簿?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！