如何在 Python 中将 Excel 工作表复制到另一个工作簿 [英] How to copy over an Excel sheet to another workbook in Python

问题描述

我有一个带有源文件路径的字符串和另一个带有 destfile 路径的字符串，它们都指向 Excel 工作簿.

I have a string with a sourcefile path and another string with a destfile path, both pointing to Excel workbooks.

我想获取源文件的第一张表并将其作为新选项卡复制到 destfile(与 destfile 中的哪个位置无关)，然后保存.

I want to take the first sheet of the sourcefile and copy it as a new tab to the destfile (doesn't matter where in the destfile), then save it.

在 xlrd 或 xlwt 或 xlutils 中找不到简单的方法来做到这一点.我错过了什么吗?

Couldn't find an easy way in xlrd or xlwt or xlutils to do this. Am I missing something?

推荐答案

解决方案 1

使用 openpyxl 包的纯 Python 解决方案.只会复制数据值.

Solution 1

A Python-only solution using the openpyxl package. Only data values will be copied.

import openpyxl as xl

path1 = 'C:\\Users\\Xukrao\\Desktop\\workbook1.xlsx'
path2 = 'C:\\Users\\Xukrao\\Desktop\\workbook2.xlsx'

wb1 = xl.load_workbook(filename=path1)
ws1 = wb1.worksheets[0]

wb2 = xl.load_workbook(filename=path2)
ws2 = wb2.create_sheet(ws1.title)

for row in ws1:
    for cell in row:
        ws2[cell.coordinate].value = cell.value

wb2.save(path2)

解决方案 2

使用 pywin32 包将复制操作委托给的解决方案一个 Excel 应用程序.复制工作表中的数据值、格式和其他所有内容.注意:此解决方案仅适用于安装了 MS Excel 的 Windows 计算机.

Solution 2

A solution that uses the pywin32 package to delegate the copying operation to an Excel application. Data values, formatting and everything else in the sheet is copied. Note: this solution will work only on a Windows machine that has MS Excel installed.

from win32com.client import Dispatch

path1 = 'C:\\Users\\Xukrao\\Desktop\\workbook1.xlsx'
path2 = 'C:\\Users\\Xukrao\\Desktop\\workbook2.xlsx'

xl = Dispatch("Excel.Application")
xl.Visible = True  # You can remove this line if you don't want the Excel application to be visible

wb1 = xl.Workbooks.Open(Filename=path1)
wb2 = xl.Workbooks.Open(Filename=path2)

ws1 = wb1.Worksheets(1)
ws1.Copy(Before=wb2.Worksheets(1))

wb2.Close(SaveChanges=True)
xl.Quit()

解决方案 3

使用 xlwings 包将复制操作委托给 Excel 的解决方案应用.Xlwings 本质上是一个智能包装器(大多数，尽管不是全部)pywin32/appscript excel API 函数.复制工作表中的数据值、格式和其他所有内容.注意:此解决方案仅适用于安装了 MS Excel 的 Windows 或 Mac 计算机.

Solution 3

A solution that uses the xlwings package to delegate the copying operation to an Excel application. Xlwings is in essence a smart wrapper around (most, though not all) pywin32/appscript excel API functions. Data values, formatting and everything else in the sheet is copied. Note: this solution will work only on a Windows or Mac machine that has MS Excel installed.

import xlwings as xw

path1 = 'C:\\Users\\Xukrao\\Desktop\\workbook1.xlsx'
path2 = 'C:\\Users\\Xukrao\\Desktop\\workbook2.xlsx'

wb1 = xw.Book(path1)
wb2 = xw.Book(path2)

ws1 = wb1.sheets(1)
ws1.api.Copy(Before=wb2.sheets(1).api)
wb2.save()
wb2.app.quit()

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