将Excel工作表从一个工作表复制到Python中的另一个工作表 [英] Copy excel sheet from one worksheet to another in Python

问题描述

我要做的就是将工作表从excel工作簿复制到Python中的另一个excel工作簿.

All I want to do is copy a worksheet from an excel workbook to another excel workbook in Python.

我要保留所有格式(彩色单元格，表格等)

I want to maintain all formatting (coloured cells, tables etc.)

我有许多Excel文件，我想将所有文件复制到一个工作簿中.如果对任何单个工作簿进行了更改，我也希望能够更新主工作簿.

I have a number of excel files and I want to copy the first sheet from all of them into one workbook. I also want to be able to update the main workbook if changes are made to any of the individual workbooks.

这是一个代码块，它将每隔几个小时运行一次，并更新主电子表格.

It's a code block that will run every few hours and update the master spreadsheet.

我尝试过熊猫，但是它不保留格式和表格.

I've tried pandas, but it doesn't maintain formatting and tables.

我尝试了openpyxl无济于事

I've tried openpyxl to no avail

我认为下面的xlwings代码会起作用:

I thought xlwings code below would work:

import xlwings as xw

wb = xw.Book('individual_files\\file1.xlsx')
sht = wb.sheets[0]
new_wb = xw.Book('Master Spreadsheet.xlsx')
new_wb.sheets["Sheet1"] = sht

但是我只得到错误:

----> 4 new_wb.sheets["Sheet1"] = sht

AttributeError: __setitem__

上面的

"file1.xlsx"是第一个excel文件示例.

"file1.xlsx" above is an example first excel file.

"Master Spreadsheet.xlsx"是我的主电子表格，其中包含所有单个文件.

"Master Spreadsheet.xlsx" is my master spreadsheet with all individual files.

推荐答案

最后我做到了:

def copyExcelSheet(sheetName):

read_from = load_workbook(item)
#open(destination, 'wb').write(open(source, 'rb').read())
read_sheet = read_from.active
write_to = load_workbook("Master file.xlsx")
write_sheet = write_to[sheetName]

for row in read_sheet.rows:
    for cell in row:
        new_cell = write_sheet.cell(row=cell.row, column=cell.column,
                value= cell.value)
        write_sheet.column_dimensions[get_column_letter(cell.column)].width = read_sheet.column_dimensions[get_column_letter(cell.column)].width
        if cell.has_style:
            new_cell.font = copy(cell.font)
            new_cell.border = copy(cell.border)
            new_cell.fill = copy(cell.fill)
            new_cell.number_format = copy(cell.number_format)
            new_cell.protection = copy(cell.protection)
            new_cell.alignment = copy(cell.alignment)

write_sheet.merge_cells('C8:G8')
write_sheet.merge_cells('K8:P8')
write_sheet.merge_cells('R8:S8')

write_sheet.add_table(newTable("table1","C10:G76","TableStyleLight8"))
write_sheet.add_table(newTable("table2","K10:P59","TableStyleLight9"))

write_to.save('Master file.xlsx')
read_from.close

使用此方法检查工作表是否已存在:

With this to check if the sheet already exists:

#checks if sheet already exists and updates sheet if it does.
def checkExists(sheetName):
    book = load_workbook("Master file.xlsx")   # open an Excel file and return a workbook

    if sheetName in book.sheetnames:
        print ("Removing sheet",sheetName)
        del book[sheetName]
    else:
        print ("No sheet ",sheetName," found, will create sheet")

    book.create_sheet(sheetName)
    book.save('Master file.xlsx')

以此创建新表:

def newTable(tableName,ref,styleName):
    tableName = tableName + ''.join(random.choices(string.ascii_uppercase + string.digits + string.ascii_lowercase, k=15))
    tab = Table(displayName=tableName, ref=ref)
    # Add a default style with striped rows and banded columns
    tab.tableStyleInfo = TableStyleInfo(name=styleName, showFirstColumn=False,showLastColumn=False, showRowStripes=True, showColumnStripes=True)
    return tab

这篇关于将Excel工作表从一个工作表复制到Python中的另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！