将 pandas 数据框写入xlsm文件(启用了宏的Excel) [英] Write pandas dataframe to xlsm file (Excel with Macros enabled)

问题描述

以.xlsx格式将pandas.DataFrame写入Excel工作簿很简单:

Writing a pandas.DataFrame into an Excel Workbook in the .xlsx format is as simple as:

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

给出:

   firstColumn  secondColumn
0            5             9
1            2             8
2            0            21
3           10             3
4            4             8

和相应的Excel文件.

and the corresponding Excel file.

还有可能将DataFrame写入.xlsm Excel文件吗?实际上，这实际上与.xlsx相同，但是可以将VBA宏存储在文件中.我需要这样做是因为我想在创建文件后插入并运行VBA宏.

Is there also a possibility to write the DataFrame into a .xlsm Excel 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.

但是，当在常规xlsx文件上尝试此操作时，我在弹出窗口中收到以下错误消息:

However, when trying this on a regular xlsx file, I get the following error message in a pop-up:

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.

然后我可以手动选择将文件另存为.xlsm，其中将包含我的宏.但是，我希望自动执行此操作，而无需执行额外的步骤.

I can then manually choose to save the file as .xlsm which will have my macro included. However, I would prefer to do this automatically without the extra step.

to_excel方法的文档表示应该可行(请参见engine参数).但是，我不知道如何启用此功能.

The documentation for the to_excel method suggests that this should be possible (see engine parameter). However, I don't understand how to enable this.

当我仅将输出文件名更改为*.xlsm时，就会创建一个.xlsx文件，该文件名为 .xlsm.当我尝试打开它时，我得到

When I simply change the output filename to *.xlsm, a .xlsx file is created which is named .xlsm. When I try to open it, I get

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.

如果我将扩展名手动更改为.xlsx，则可以再次打开它.

If I manually change the extension to .xlsx, I can open it again.

关于这部分pandas文档:

openpyxl:这包括对OpenPyxl 1.6.1(直到但不包括2.0.0)的稳定支持，以及对OpenPyxl 2.0.0和更高版本的实验性支持.

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版本是1.8.6.更新到2.1.4并不能解决问题.都没有将XlsxWriter从0.63更新到0.6.6.

My version of Openpyxl is 1.8.6. Updating to 2.1.4 did not solve the problem. Neither did updating XlsxWriter from 0.63 to 0.6.6.

按照建议使用df.to_excel('test.xlsx', engine='openpyxl')也不能解决问题.

Using df.to_excel('test.xlsx', engine='openpyxl') as suggested also did not solve the problem.

推荐答案

Pandas要求工作簿名称以.xls或.xlsx结尾.它使用扩展名选择要使用的Excel引擎.

Pandas requires that a workbook name ends in .xls or .xlsx. It uses the extension to choose which Excel engine to use.

您可以传递一个临时名称，然后用以下内容覆盖它:

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

这将创建扩展名为.xlsm的Excel文件.

This will create an Excel file with the a .xlsm extension.

但是，由于称为扩展强化"的功能，Excel无法打开该文件，因为它知道它不包含宏并且实际上不是xlsm文件. (这是您在上面报告的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 xlsm file. (That is the Excel error that you report above.)

您可以通过从实际的xlsm文件中提取VbaProject.bin宏文件并将其插入到新文件中，来解决最新版本的XlsxWriter的问题:

You can workaround this with recent versions of XlsxWriter by extracting the VbaProject.bin macro file from a real xlsm file and inserting it into the new file:

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

有关更多信息，请参阅XlsxWriter文档的使用VBA宏部分.

See the Working with VBA Macros section of the XlsxWriter docs for more information.

这篇关于将 pandas 数据框写入xlsm文件(启用了宏的Excel)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！