在Python中将excel工作表从一个工作表复制到另一个工作表

2022-01-20 00:00:00 python 复制 excel worksheet

问题描述

我要做的就是将工作表从 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.

我尝试过 pandas，但它不维护格式和表格.

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

相关文章