Writing to large excel file (.xlsm) using XLSX

New to Julia
1

Hi all,

I’m trying to pass a dataframe to a large xlsm file (80mbs). The code I’m using is as follows:

using XLSX, DataFrames
XLSX.openxlsx("xlsm_file.xlsm", mode ="rw" ) do xf
        # pass through the dataframe, df
        sheet = xf["sheet1"]
        for r in 1:size(df,1), c in 1:size(df,2)
             sheet[XLSX.CellRef(r , c )] = df[r,c]
        end
end

This takes a really long time and eventually crashes giving the following error:

AssertionError: isempty(XML_GLOBAL_ERROR_STACK)

I have rerun the code using a smaller version of the file (i.e… removing formulas, tabs etc) and the code runs ok.

I also tried updating the code to disable caching using:


XLSX.openxlsx("xlsm_file.xlsm", enable_cache = false ) do xf
        # pass through the dataframe, df
        sheet = xf["sheet1"]
        for r in 1:size(df,1), c in 1:size(df,2)
             sheet[XLSX.CellRef(r , c )] = df[r,c]
        end
end

But this gives:

AssertionError: XLSXFile instance is not writable.

Is there a way to disable caching while in “rw” mode?

Any recommendations on how to write to large excel files?
Thanks in advance.

2

I have no solution for this. It is possible that a workaround via python might be a (cumbersome) solution, as some python packages are more mature (and tested) than Julia packages.
I have written very large files with the xlsxwriter package (via PyCall).
However I am wondering if your Excel file is really that large. Does it contain that much data?
A high number of sheets and formulas usually does not lead to an 80mb file size. At times Excel has an ‘inflated’ file size due to unused cells or formatting issues. If you google ‘reduce excel file size unused cells’ you may find some hints to reduce the file size while maintaining all functionality and data.