如何在 Python 中将 Excel 工作表复制到另一个工作簿

How to copy over an Excel sheet to another workbook in Python

提问by KaliMa

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

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

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.

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

Couldn't find an easy way in xlrdor xlwtor xlutilsto do this. Am I missing something?

找不到一个简单的方法xlrd或xlwt或xlutils做到这一点。我错过了什么吗？

回答by Xukrao

Solution 1

解决方案1

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

使用该openpyxl包的仅限 Python 的解决方案。只会复制数据值。

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)

Solution 2

解决方案2

A solution that uses the pywin32package 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.

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

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()

Solution 3

解决方案3

A solution that uses the xlwingspackage to delegate the copying operation to an Excel application. Xlwings is in essence a smart wrapper around (most, though not all) pywin32/appscriptexcel 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.

使用xlwings包将复制操作委托给 Excel 应用程序的解决方案。Xlwings 本质上是一个围绕（大多数，但不是全部）pywin32/ appscriptexcel API 函数的智能包装器。复制工作表中的数据值、格式和其他所有内容。注意：此解决方案仅适用于安装了 MS Excel 的 Windows 或 Mac 计算机。

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()

回答by Bubble Bubble Bubble Gut

This might help if you're not opposed to using Pandas

如果您不反对使用 Pandas，这可能会有所帮助

import pandas as pd

#change xxx with the sheet name that includes the data
data = pd.read_excel(sourcefile, sheet_name="xxx")

#save it to the 'new_tab' in destfile
data.to_excel(destfile, sheet_name='new_tab')

Hope it helps

希望能帮助到你

回答by Josh Herzberg

You could also try xlwings.

你也可以试试xlwings。

import xlwings as xw
wb = xw.Book(r'C:\path\to\file.xlsx')
sht = wb.sheets['Sheet1']
new_wb = xw.Book(r'C:\new_path\to\file.xlsx')
new_wb.sheets['name'] = sht