Python library xlwings substitute in DataIKU
I am trying to do some modification in a .xlsm file inside a 'managed folder'. This .xlsm file has macros, as well as, shapes in it. I am using function load_workbook from the library openpyxl, but the documentation clearly says:
Warning: openpyxl does currently not read all possible items in an Excel file so
shapes will be lost from existing files if they are opened and saved with
the same name.
When I download the modified file, the data is updated, but I lose the shapes. Just for reference, I am jotting down my code here:
from openpyxl import load_workbook
import dataiku
import tempfile
folder = dataiku.Folder("My_files")
# Loading the data with which I want to update the .xlsm file with
with folder.get_download_stream('data_to_be_uploaded.xlsx') as stream:
excel_data = io.BytesIO(stream.read())
df = pd.read_excel(excel_data, sheet_name="Sheet1")
# The macros file
with folder.get_download_stream('my_file_with_macros.xlsm') as f:
with tempfile.NamedTemporaryFile(suffix=".xlsm", delete=False) as tmp:
tmp.write(f.read())
tmp_path = tmp.name
# Updating the data in .xlsm file
wb = load_workbook(tmp_path, keep_vba=True) # To keep macros, we need to 'keep_vba=True'
ws = wb['Data']
rows = dataframe_to_rows(df, index=False)
for r_idx, row in enumerate(rows, 1):
for c_idx, value in enumerate(row, 1):
ws.cell(row=r_idx, column=c_idx, value=value)
wb.save(tmp_path)
with open(tmp_path, "rb") as f:
folder.upload_stream("my_file_with_macros_UPDATED.xlsm", f)
The code runs well, but as mentioned before, I loose all shapes in the file.
On my windows machine I was using xlwings python library and it worked well with all shapes preserved, but there is a caveat that it should have Window/MacOS with Excel installed. DataIKU being a Unix based system, I get an error on instantiation.
import xlwings as xw app = xw.App(visible=False)
Error
AttributeError: 'NoneType' object has no attribute 'apps'
Can anyone suggest how can I work with .xlsm files on DataIKU without losing my shapes?
Thanks