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

I have .xlsm file with a Macro function. I'm loading it using openpyxl and write some data to the file and finally want to save as a different .xlsm file.

我有.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 .xlsx then 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 .xlsm file using openpyxl. How can I do that?

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

3 个解决方案

#1

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

它在文档中也是mentioend:http://openpyxl.readthedocs.org/en/latest/usage.html?highlight = keep_vba#write-a-workbook-from-xltm-as-xlsm

#2

I don't know if this is still relevant for the one who 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

1)打开原始文件(例如:1.xlsm)并使用openpyxl进行魔术

2) save as 2.xlsx

2)保存为2.xlsx

3) both files are actually zipped files: extract them to temporary dirs

3)这两个文件实际上都是压缩文件:将它们提取到临时目录

4) copy files from the dir of the original file to the dir of the xlsx file: 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

4)将文件从原始文件的目录复制到xlsx文件的目录:其中一个文件是宏(vbaProject.bin),其中2个文件是必需的,因为它们描述了文件的类型等等

5) put all of the files that belong to the xlsx dir back into a zip file and rename this from zip to xlsm This file contains the original macro and has been edited with openpyxl

5)将属于xlsx目录的所有文件放回一个zip文件,并将其从zip重命名为xlsm此文件包含原始宏,并已使用openpyxl进行编辑

Optional: 6) delete the two temporary dirs and the 2.xlsx file

可选: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')

#3

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 fork instead: if you pass keep_vba=True parameter to load_workbook it should do the job.

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

Hope that helps.

希望有所帮助。

#1