有什么办法可以从头开始在python中创建.xlsm文件? [英] is there any way to create a .xlsm file from scratch in python?

问题描述

我在Mac上使用python 3.8.1，并试图从头开始创建.xlsm文件.我看过openpyxl和xlsxwriter，它们都可以从头开始创建.xlsx文件，并且都可以编辑现有的.xlsm文件，但是我找不到关于从头开始实际创建.xlsm文件的任何信息.

I am using python 3.8.1 on a mac and am trying to create a .xlsm file from scratch. I have looked at openpyxl and xlsxwriter, both of them are able to create .xlsx files from scratch and both can edit existing .xlsm files but I can't find any thing about actually creating a .xlsm file from scratch.

我已经查看了openpyxl文档此处和xlsxwriter文档此处，但我无法找到有关如何从头开始创建.xlsm文件的任何信息.我什至找不到有关如何将.xlsx文件转换为.xlsm文件的任何信息.

I've looked over the openpyxl documentation here and the xlsxwriter documentation here but I have not been able to find anything about how to create a .xlsm file from scratch. I can't even find anything about how to convert a .xlsx file to an .xlsm file.

最接近的是，您可以使用xlsxwriter中包含的vba_extract.py从现有的.xlsm文件中提取名为vbaProject.bin的文件，然后可以将其添加到.xlsx文件中，然后可以将其另存为.xlsm文件，但是我需要从头开始创建.xlsm文件，而不是依赖在那里使用某些文件来创建它.

The closest I have come is that you can use vba_extract.py which is included in xlsxwriter to extract a file named vbaProject.bin from an existing .xlsm file which you can then add to a .xlsx file and then you can save it as a .xlsm file, but I need to create a .xlsm file from scratch, not relying on having some file there to use to create it.

openpyxl，xlsxwriter或我可以与python 3.8.1一起使用的任何其他实用程序是否有任何东西可以从头开始创建.xlsm文件，而不必依赖于已有的.xlsm或vbaProject.bin文件?与往常一样，正确的，明确解释的答案将被标记为已接受，并且会被赞成.

Is there anything out there with openpyxl, xlsxwriter or any other utility I can use with python 3.8.1 to create a .xlsm file from scratch without having to depend on having an existing .xlsm or a vbaProject.bin file? As always a correct, clearly explained answer will be marked as accepted and will be upvoted.

推荐答案

感谢Alexander Pushkarev和APhillips都为这个问题提供了帮助.走出亚历山大的职位，我能够找出一个使它起作用的方法.我对此并不感到特别自豪，但是确实有效.

Thanks to both Alexander Pushkarev and APhillips for helping out with this question. Going off of Alexander's post I was able to figure out a hack to get this to work. I'm not really proud of this, but it works.

运行Alexander的代码时出现此错误:

Running Alexander's code I get this error:

在以下情况中忽略异常: 追溯(最近一次通话):文件 "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/zipfile.py"， 第1819行，位于 del self.close()文件"/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/zipfile.py"， 1836行，处于关闭状态 self.fp.seek(self.start_dir)ValueError:对已关闭文件的I/O操作.

Exception ignored in: Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/zipfile.py", line 1819, in del self.close() File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/zipfile.py", line 1836, in close self.fp.seek(self.start_dir) ValueError: I/O operation on closed file.

我尝试了一下，发现如果我从load_workbook函数中取出keep_vba=True，代码可以运行，但是当我尝试使用Excel打开.xlsm文件时，仍然遇到上面提到的错误.

I played around with this and found out that if I took out keep_vba=True from the load_workbook function the code ran but I still got the error I noted above when trying to open the .xlsm file with Excel.

因此，查看最新的错误，我看到最后一行说

So, looking at the latest error I saw the last line says

对已关闭文件的I/O操作.

I/O operation on closed file.

我查看了openpxyl文档，并尝试在不使用keep_vba=True选项的情况下打开文件，然后再使用keep_vba=True打开它，并且该文件可以正常工作.

I looked at openpxyl documentation and tried opening the file without the keep_vba=True option before opening it with keep_vba=True and it worked.

请原谅这个丑陋的代码，但这将可以从头开始创建.xlsm文件，而无需依赖任何现有文件(已准备好复制和粘贴):

So excuse this ugly code, but this will work to create a .xlsm file from scratch without depending on any existing files (copy and paste ready):

from openpyxl import Workbook
from openpyxl import load_workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws.append([1, 2, 3])
wb.save('new_document.xlsm')
wb1 = load_workbook('new_document.xlsm')
wb2 = load_workbook('new_document.xlsm', keep_vba=True)
wb2.save('new_document.xlsm')

这篇关于有什么办法可以从头开始在python中创建.xlsm文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！