Python 如何使用宏保存 XLSM 文件，使用 openpyxl

How to save XLSM file with Macro, using openpyxl

提问by AnujAroshA

I have .xlsmfile with a Macrofunction. I'm loading it using openpyxland write some data to the file and finally want to save as a different .xlsmfile.

我有一个带有宏功能的.xlsm文件。我正在使用openpyxl加载它并将一些数据写入文件，最后想保存为不同的.xlsm文件。

To save the file as XLSM file I have used below code in my Python script.

要将文件保存为 XLSM 文件，我在 Python 脚本中使用了以下代码。

wb.save('testsave.xlsm');

But I cannot open that file if I saved as above. But if I saved it as .xlsxthen I can open the file without the Macro function that original file had.

但是如果我按上述方式保存，我将无法打开该文件。但是如果我将它保存为.xlsx那么我可以在没有原始文件的宏功能的情况下打开文件。

I want to open a Excel sheet that has Macro function, edit the file and save it as new .xlsmfile using openpyxl. How can I do that?

我想打开一个具有宏功能的 Excel 工作表，编辑文件并使用openpyxl将其保存为新的.xlsm文件。我怎样才能做到这一点？

回答by alecxe

That's right, openpyxl cannot read and write VBA code.

没错，openpyxl无法读写VBA代码。

According to this thread:

根据这个线程：

I think you should not give the xlsM extension, because the file will contain no VBA code. openpyxl is used to build xlsX files only.

我认为您不应该提供 xlsM 扩展名，因为该文件将不包含 VBA 代码。openpyxl 仅用于构建 xlsX 文件。

Give a try to this forkinstead: if you pass keep_vba=Trueparameter to load_workbookit should do the job.

试试这个 fork：如果你将keep_vba=True参数传递给load_workbook它应该可以完成这项工作。

Hope that helps.

希望有帮助。

回答by eule

For me this worked, together with version openpyxl==2.3.0-b2

对我来说，这与版本一起工作 openpyxl==2.3.0-b2

wb = load_workbook(filename='original.xlsm', read_only=False, keep_vba=True)
..
wb.save('outfile.xlsm')

It is also mentioend in the documentation here: http://openpyxl.readthedocs.org/en/latest/usage.html?highlight=keep_vba#write-a-workbook-from-xltm-as-xlsm

它也在此处的文档中提及：http://openpyxl.readthedocs.org/en/latest/usage.html?highlight=keep_vba#write-a-workbook-from-xltm-as-xlsm

回答by Joost

I don't know if this is still relevant for the person that asked the question, but I am dealing with the same problem and found a possible solution.

我不知道这对提出问题的人是否仍然相关，但我正在处理同样的问题并找到了可能的解决方案。

1. open the original file (say: 1.xlsm) and do magic with openpyxl;
2. save as 2.xlsx;
3. both files are actually zipped files: extract them to a temporary directory;
4. copy files from the directory of the original file to the directory of the xlsxfiles: one of the files is the macro (vbaProject.bin) and 2 of the files are necessary because they describe the type of the file among other things;
5. put all of the files that belong to the xlsxdirectory back into a zip file and rename this from zipto xlsm. This file contains the original macro and has been edited with openpyxl;
6. (Optional) delete the two temporary directories and the 2.xlsxfile.

1. 打开原始文件（比如1.xlsm：）并用 openpyxl 做魔术；
2. 另存为2.xlsx；
3. 这两个文件实际上都是压缩文件：将它们解压缩到一个临时目录；
4. 将文件从原始文件的目录复制到文件的目录xlsx：其中一个文件是宏 ( vbaProject.bin)，其中 2 个文件是必需的，因为它们描述了文件的类型等；
5. 把所有属于该文件的xlsx目录回成一个zip文件，并从命名此zip来xlsm。此文件包含原始宏并已使用openpyxl;进行编辑。
6. （可选）删除两个临时目录和2.xlsx文件。

Example code:

示例代码：

import openpyxl
import zipfile
from shutil import copyfile
from shutil import rmtree
import os

PAD = os.getcwd()

wb = openpyxl.load_workbook('1.xlsm')

#####
# do magic with openpyxl here and save
ws = wb.worksheets[0]
ws.cell(row=2, column=3).value = 'Edited'   # example
#####

wb.save('2.xlsx')


with zipfile.ZipFile('1.xlsm', 'r') as z:
    z.extractall('./xlsm/')

with zipfile.ZipFile('2.xlsx', 'r') as z:
    z.extractall('./xlsx/')

copyfile('./xlsm/[Content_Types].xml','./xlsx/[Content_Types].xml')
copyfile('./xlsm/xl/_rels/workbook.xml.rels','./xlsx/xl/_rels/workbook.xml.rels')
copyfile('./xlsm/xl/vbaProject.bin','./xlsx/xl/vbaProject.bin')

z = zipfile.ZipFile('2.zip', 'w')

os.chdir('./xlsx')

for root, dirs, files in os.walk('./'):
        for file in files:
            z.write(os.path.join(root, file))
z.close()

#clean
os.chdir(PAD)
rmtree('./xlsm/')
rmtree('./xlsx/')
os.remove('./2.xlsx')
os.rename('2.zip', '2.xlsm')

回答by John Prawyn

I had the same issue when editing xlsm files using openpyxl. I tried most of the solutions/workarounds available in stackoverflow and in other forums. But none of them worked. Then I found xlwings, this python library handles xlsm document, it preserve all the macros.

我在使用 openpyxl 编辑 xlsm 文件时遇到了同样的问题。我尝试了 stackoverflow 和其他论坛中提供的大多数解决方案/变通方法。但他们都没有工作。然后我找到了xlwings，这个 python 库处理 xlsm 文档，它保留了所有的宏。

import xlwings as xw
wb = xw.Book('macro_xl.xlsm')
sheet = wb.sheets['Sheet1']
sheet.range('A1').value = 'From Script'
wb.save('result_file_name.xlsm')

回答by Michel Kluger

if your initial excel was created with python you might need to add the workbook.xml as well and parse the sheet xlms:

如果您的初始 excel 是使用 python 创建的，您可能还需要添加 workbook.xml 并解析工作表 xlms：

for sheet in range(1,4):
    with open('sheetX.xml', 'r') as myfile: 'r') as myfile:
        my_str=myfile.read()

substr = "<dimension ref="
inserttxt = "<sheetPr codeName=\"Sheet"+str(sheet)+"\"/>"

idx = my_str.index(substr)
my_str = my_str[:idx] + inserttxt + my_str[idx:]

with open('sheetX.xml', "w") as text_file:
    text_file.write(my_str)