Python Forum
openpyxl and saving xlsm files
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
openpyxl and saving xlsm files
#1
Hi All,

My first post :)

I am working on an project at work where I have an xlsm file that I am updating cell values from multiple other xlsx files.
This all works as expected except but when I save as a .xlsm file and open the file in Excel I am missing buttons that are on sheet1.

I load the workbook only one time and I set keep_vba=True in load_workbook

wb = load_workbook( filename = input_file, keep_vba=True )

I save the wb multiple times as I am updating in different functions in my code but I simple call wb.save at the end of each function where I changed some values.

wb.save(output_file_name)

Is the expected? That I will not get a valid xlsm file when saving off from openpyxl?

I have done some reading on this and realize that .xlsx and .xlsm are actually zip files so I tried some code from StackOverflow

I tried this code below but I get errors when opening the file in Excel. I also just tried pasting the two sheets that I modified into the unzipped original xlsm worksheet folders and still had problems loading. It loads but doesn't recalculate the formulas unless you go hit enter in a cell with the formula so that is not an option. I am trying to not re-write the updates in win32com basically

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()
Reply
#2
You may want to read through the issues list here: https://bitbucket.org/openpyxl/openpyxl/issues
release notes here: https://openpyxl.readthedocs.io/en/latest/changes.html
You didn't mention when you installed the package, but I see the last update was just 3 days ago.
Reply
#3
Thanks Larz.

This morning I did update to the latest openpyxl for Anaconda 3.5 using the conda update and it updated to openpyxl: 2.3.2-py35_0 but that caused a new error with load_workbook so I uninstalled and re-installed Anaconda 3.5. Currently I am working around the problem using win32com. I basically get a win32com workbook object and a worksheet object and the use win32com to poke in the value. This allowed me to keep the openpyxl code where I find the cells of interest where I am changing the values and win32com is saving the file correctly so I have a workaround.

thanks for fixing my code tags Buran!!!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Trouble with saving openpyxl filename fjbianchi93 4 3,085 Apr-08-2022, 12:39 PM
Last Post: fjbianchi93
  Problem in saving .xlsm (excel) file using pandas dataframe in python shantanu97 2 4,164 Aug-29-2021, 12:39 PM
Last Post: snippsat
  extract zip from .msg files and saving according to date stamp of email natjo 3 2,884 Aug-13-2020, 09:35 PM
Last Post: bowlofred
  convert old excel files(xls) to xlsm zarize 1 3,335 Jul-14-2020, 02:12 PM
Last Post: DeaD_EyE
  Saving Files Kristenl2784 3 1,965 Jul-13-2020, 04:36 PM
Last Post: bowlofred
  How can I speed up my openpyxl program reading Excel .xlsx files? deac33 0 3,362 May-04-2020, 08:02 PM
Last Post: deac33
  Downloading And Saving Zip Files To A Particular Path Folder eddywinch82 2 2,515 Jan-06-2020, 07:56 PM
Last Post: eddywinch82
  Openpyxl - while saving excel file getting error shubhamjainj 1 4,518 Apr-09-2019, 12:05 PM
Last Post: Larz60+
  Python 3.6.5 - Saving files Mradr 1 2,846 Jul-22-2018, 12:21 AM
Last Post: Larz60+
  reading .xls files and saving as .xlsx jon0852 1 6,855 Oct-17-2017, 08:32 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020