vba 将 Pandas 数据帧写入 xlsm 文件（启用宏的 Excel）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28169731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Write pandas dataframe to xlsm file (Excel with Macros enabled)
提问by Dirk
Writing a pandas.DataFrameinto an Excel Workbook in the .xlsxformat is as simple as:
pandas.DataFrame以以下.xlsx格式将 a写入 Excel 工作簿非常简单：
import pandas as pd
df = pd.DataFrame({'firstColumn' : [5, 2, 0, 10, 4], 'secondColumn' : [9, 8, 21, 3, 8]})
print(df)
df.to_excel('test.xlsx')
which gives:
这使：
firstColumn secondColumn
0 5 9
1 2 8
2 0 21
3 10 3
4 4 8
and the corresponding Excel file.
和相应的 Excel 文件。
Is there also a possibility to write the DataFrameinto a .xlsmExcel file? This is actually more or less the same as .xlsx, but with the possibility enabled to store VBA macros within the file. I need this because I want to insert and run a VBA macro after creating the file.
有没有还写了一个可能性DataFrame成.xlsmExcel文件？这实际上或多或少与 相同.xlsx，但可以在文件中存储 VBA 宏。我需要这个，因为我想在创建文件后插入并运行一个 VBA 宏。
However, when trying this on a regular xlsxfile, I get the following error message in a pop-up:
但是，在常规xlsx文件上尝试此操作时，我在弹出窗口中收到以下错误消息：
The following features cannot be saved in macro-free workbooks: VB project.
To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.
To continue saving as macro-free workbook, click Yes.
I can then manually choose to save the file as .xlsmwhich will have my macro included. However, I would prefer to do this automatically without the extra step.
然后我可以手动选择将文件保存为.xlsm将包含我的宏。但是，我更愿意在没有额外步骤的情况下自动执行此操作。
The documentation for the to_excelmethodsuggests that this should be possible (see engineparameter). However, I don't understand how to enable this.
该方法的文档to_excel表明这应该是可能的（参见engine参数）。但是，我不明白如何启用它。
When I simply change the output filename to *.xlsm, a .xlsxfile is created which is named.xlsm. When I try to open it, I get
当我简单地改变输出文件名*.xlsm，一个.xlsx文件被创建被命名.xlsm。当我尝试打开它时，我得到
Excel cannot open the file 'myFilename.xlsm' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
If I manually change the extension to .xlsx, I can open it again.
如果我手动将扩展名更改为.xlsx，则可以再次打开它。
Regarding this part of the pandasdocumentation:
openpyxl: This includes stable support for OpenPyxl 1.6.1 up to but not including 2.0.0, and experimental support for OpenPyxl 2.0.0 and later.`
openpyxl：这包括对 OpenPyxl 1.6.1 直到但不包括 2.0.0 的稳定支持，以及对 OpenPyxl 2.0.0 及更高版本的实验性支持。`
My version of Openpyxlis 1.8.6. Updating to 2.1.4 did not solve the problem. Neither did updating XlsxWriterfrom 0.63 to 0.6.6.
我的版本Openpyxl是 1.8.6。更新到 2.1.4 没有解决问题。XlsxWriter从 0.63更新到 0.6.6也没有。
Using df.to_excel('test.xlsx', engine='openpyxl')as suggested also did not solve the problem.
df.to_excel('test.xlsx', engine='openpyxl')按照建议使用也没有解决问题。
回答by jmcnamara
Pandas requires that a workbook name ends in .xlsor .xlsx. It uses the extension to choose which Excel engine to use.
Pandas 要求工作簿名称以.xls或结尾.xlsx。它使用扩展来选择要使用的 Excel 引擎。
You could pass a temp name and then overwrite it with something like this:
你可以传递一个临时名称，然后用这样的东西覆盖它：
import pandas as pd
df = pd.DataFrame({'First' : [5, 2, 0, 10, 4],
'Second' : [9, 8, 21, 3, 8]})
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook = writer.book
workbook.filename = 'test.xlsm'
# !! Won't load in Excel !!
writer.save()
This will create an Excel file with the a .xlsmextension.
这将创建一个.xlsm扩展名为a 的 Excel 文件。
However, due to a feature called "extension hardening" Excel won't open this file since it knows that it doesn't contain a macro and isn't actually an xlsmfile. (That is the Excel error that you report above.)
但是，由于名为“扩展强化”的功能，Excel 不会打开此文件，因为它知道它不包含宏并且实际上不是一个xlsm文件。（这是您在上面报告的 Excel 错误。）
You can workaround this with recent versions of XlsxWriter by extracting the VbaProject.binmacro file from a real xlsm file and inserting it into the new file:
您可以使用最新版本的 XlsxWriter 解决此问题，方法是VbaProject.bin从真实的 xlsm 文件中提取宏文件并将其插入到新文件中：
import pandas as pd
df = pd.DataFrame({'First' : [5, 2, 0, 10, 4],
'Second' : [9, 8, 21, 3, 8]})
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook = writer.book
workbook.filename = 'test.xlsm'
workbook.add_vba_project('./vbaProject.bin')
writer.save()
See the Working with VBA Macrossection of the XlsxWriter docs for more information.
有关更多信息，请参阅XlsxWriter 文档的使用 VBA 宏部分。
