how to improve the speed of read+write?

[l1t1]

I compared the time cost of demo5 and same read+write to file.xlsx with duckdb.
the duckdb is faster.
could you give me some suggesion to improve openxlsx's speed?

OpenXLSX

tar xf OpenXLSX-master.zip
cd OpenXLSX-master
mkdir build
cd build
cmake ..
make

time OpenXLSX/build/output/Demo5

and duckdb

wget https://github.com/duckdb/duckdb/releases/download/v1.3.2/duckdb_cli-linux-amd64.zip

duckdb
D load excel;
D .timer on

D copy (from 'Demo05.xlsx') to 'Duckdemo5.xlsx';

[aral-matrix]

The comparison of Demo5 with a load&save is in my opinion not very suitable. We have no idea what this "duckdb" is doing - for all we know it could be doing a simple file copy operation.

That said, you can compile OpenXLSX with optimization setting -O3 (if you were using the master branch, try the development-aral branch, it currently auto-inherits (undesired side-effect) the -O3 option from included dependencies). In the master branch, the Makefile.GNU has a setting for that in line 75: OPTIMIZATION_FLAGS= - if you set that to O3 the Demo5-Demo7 executables speed up by a lot (execution time ca. 1/3)

However, please keep in mind that a sequential copy operation from one document to another (even if duckdb does more than just copying the files) will always be faster than generating the file (because insertions have to happen), and Demo5 also accesses each cell once after reading the document into memory.

Beyond the -O3 optimization (at least if you are using the GNU C++ compiler) there's nothing you can do.

[l1t1]

thanks @aral-matrix for your suggestion
I build the development-aral branch, it speed up one time.

/par/OpenXLSX-development-aral/build/output# time ./Demo5
********************************************************************************
DEMO PROGRAM #05: Ranges and Iterators
********************************************************************************

Generating spreadsheet ...
Saving spreadsheet ...
Re-opening spreadsheet ...
Reading data from spreadsheet ...
Cell count: 8388608
Sum of cell values: 415180886

real    0m20.060s
user    0m15.847s
sys     0m2.680s

but I modified the Makefile.GNU as you wrote, and run cmake && make again, no change occurs

/par/OpenXLSX/build2/output# time ./Demo5
********************************************************************************
DEMO PROGRAM #05: Ranges and Iterators
********************************************************************************

Generating spreadsheet ...
Saving spreadsheet ...
Re-opening spreadsheet ...
Reading data from spreadsheet ...
Cell count: 8388608
Sum of cell values: 415348200

real    0m41.320s
user    0m38.038s
sys     0m2.780s

BTW, the copy command of duckdb, first load the Demo5.xlsx's data into a memory table, and transfer it to another xlsx file. so it includes the process of parser xml file and generate xml structure and save it to xlsx.

[aral-matrix]

but I modified the Makefile.GNU as you wrote, and run cmake && make again, no change occurs

If you use the Makefile.GNU you have to build via Scripts/make-gnu.sh

I wrote the GNU Makefile mainly for my ow use, and it is currently disabled in the development-aral branch, but in the master branch it should still work.

[l1t1]

but I modified the Makefile.GNU as you wrote, and run cmake && make again, no change occurs

If you use the Makefile.GNU you have to build via Scripts/make-gnu.sh

I wrote the GNU Makefile mainly for my ow use, and it is currently disabled in the development-aral branch, but in the master branch it should still work.

yes, it became much faster, thank you

time output/Demo5
********************************************************************************
DEMO PROGRAM #05: Ranges and Iterators
********************************************************************************

Generating spreadsheet ...
Saving spreadsheet ...
Re-opening spreadsheet ...
Reading data from spreadsheet ...
Cell count: 8388608
Sum of cell values: 415266740

real    0m16.626s
user    0m13.333s
sys     0m2.736s

but the method no longer work for development-aral branch

Scripts/make-gnu.sh
make --makefile Makefile.GNU 
Makefile.GNU:3: *** 2025-08-16 15:30 CEST: With recent changes to the project configuration, the GNU Makefile is currently non-functional.  Stop.

[l1t1]

and I found the saving step costs the biggest part, I know it includes zip the xmls, could I use a lower compress level to speed up?

Generating spreadsheet ...
耗时: 2.661 秒
Saving spreadsheet ...
耗时: 10.252 秒
Re-opening spreadsheet ...
Reading data from spreadsheet ...
Cell count: 8388608
Sum of cell values: 415236385
耗时: 2.27 秒

[aral-matrix]

but the method no longer work for development-aral branch

I put that error in there because of the introduction of CPMFindPackage in CMake to auto-download pugixml, miniz & boost::nowide dependencies - and removing them from the repository itself. I haven't caught up to it yet with the GNU Makefile, but will eventually.

As I can't auto-download dependencies with GNU make, I will probably (eventually) add support in the GNU Makefile only for already installed libraries and leave it to the user to install them directly. But I have a lot of other things to fix first, so don't hold your breath for it :)

and I found the saving step costs the biggest part, I know it includes zip the xmls, could I use a lower compress level to speed up?

I never looked into it, but it appears it's non trivial with libzip - miniz seems to have a way to support different compression levels, but I'll have to ask you to look into it on your own for now.

What you can do, however, is to test (with cmake / development branch) the performance of libzip vs. miniz (cmake option OPENXLSX_ENABLE_LIBZIP).

We are working on the development branch at the moment to improve the CMake scripts and eventually merge that into the master branch, but I can't do that until I finish my current code review, because there are still some defaulted constructors / destructors / assignment operators in header files that will cause compiler errors when those classes are used.

[l1t1]

the test is very hard

I set option(OPENXLSX_ENABLE_LIBZIP "Enable using libzip" ON) in OpenXLSX-development-aral/CMakeLists.txt
and run

mkdir build2
cd build2
cmake ..

it reports

-- Configuring done
CMake Error at OpenXLSX/CMakeLists.txt:178 (target_link_libraries):
  Target "OpenXLSX" links to:

    libzip::zip

  but the target was not found.  Possible reasons include:

    * There is a typo in the target name.
    * A find_package call is missing for an IMPORTED target.
    * An ALIAS target is missing.



-- Generating done

but I can run make, it stoped at

In file included from /par/OpenXLSX-development-aral/OpenXLSX/sources/XLZipArchive.cpp:48:
/par/OpenXLSX-development-aral/OpenXLSX/headers/detail/LibZip.hpp:11:10: fatal error: zip.h: No such file or directory
   11 | #include <zip.h>        // libzip

so I add export CPLUS_INCLUDE_PATH=$CPLUS_INCLUDE_PATH:/par/libzip-1.11.4/lib and it continue

[  8%] Linking CXX static library ../output/libOpenXLSX.a
[ 60%] Built target OpenXLSX
[ 62%] Building CXX object Examples/CMakeFiles/Demo1.dir/Demo1.cpp.o
[ 64%] Linking CXX executable ../output/Demo1
/usr/bin/ld: cannot find -llibzip::zip: No such file or directory

I then modified the OpenXLSX-development-aral/OpenXLSX/CMakeLists.txt, replace all libzip::zip to zip
and make sure the libzip.so in the following path

ls -l /par/libzip-1.11.4/build/lib
total 332
drwxrwxrwx 1 root root   4096 Aug 25 11:49 CMakeFiles
-rwxrwxrwx 1 root root    263 Aug 25 11:49 CTestTestfile.cmake
-rwxrwxrwx 1 root root 127726 Aug 25 11:49 Makefile
-rwxrwxrwx 1 root root   3132 Aug 25 11:49 cmake_install.cmake
lrwxrwxrwx 1 root root     11 Aug 25 11:49 libzip.so -> libzip.so.5
lrwxrwxrwx 1 root root     13 Aug 25 11:49 libzip.so.5 -> libzip.so.5.5
-rwxrwxrwx 1 root root 200368 Aug 25 11:49 libzip.so.5.5
-rwxrwxrwx 1 root root   3142 Aug 25 11:49 zip_err_str.c

I set export LIBRARY_PATH=$LIBRARY_PATH:/par/libzip-1.11.4/build/lib and make it again, it finished at

[100%] Built target Demo10

I made sure it depends on libzip by

ldd output/Demo5
        linux-vdso.so.1 (0x00007fffae1f0000)
        libzip.so.5 => /par/libzip-1.11.4/build/lib/libzip.so.5 (0x00007fa234a87000)

then

export LD_LIBRARY_PATH=$LIBRARY_PATH:/par/libzip-1.11.4/build/lib

it can run now, but it much slower than the orignal OPENXLSX_ENABLE_LIBZIP OFF config(in build).

root@6ae32a5ffcde:/par/OpenXLSX-development-aral/build2# time output/Demo5
********************************************************************************
DEMO PROGRAM #05: Ranges and Iterators
********************************************************************************

Generating spreadsheet ...
Saving spreadsheet ...
Re-opening spreadsheet ...
Reading data from spreadsheet ...
Cell count: 8388608
Sum of cell values: 415132707

real    0m48.306s
user    0m43.401s
sys     0m2.463s
root@6ae32a5ffcde:/par/OpenXLSX-development-aral/build2# cd ../build
root@6ae32a5ffcde:/par/OpenXLSX-development-aral/build# time output/Demo5
********************************************************************************
DEMO PROGRAM #05: Ranges and Iterators
********************************************************************************

Generating spreadsheet ...
Saving spreadsheet ...
Re-opening spreadsheet ...
Reading data from spreadsheet ...
Cell count: 8388608
Sum of cell values: 415096577

real    0m16.781s
user    0m14.867s
sys     0m2.513s

[aral-matrix]

I am sorry you had to jump through so many hoops to make libzip work - I ran into a similar problem with library namespaces depending on whether I use a library from my operating system repository or from a direct install :/

But the main purpose of the test was not to make things faster, it was to see - with your comparison of "generate" vs "save" times, whether the generation time stays similar, but the "save" time changes (even if slower). That would indicate that there is potential in using a lower compression for sake of speed.

Please note that the (development) cmake build does not directly enable O3 optimization, rather that it "inherits" the optimization setting as an unintended side effect from one of the dependencies. From your message I suppose maybe the O3 optimization is inherited from miniz - and that's why the program slows down.

I think your best best to compare different zip algorithms is to use the master branch, and compare two builds
Scripts/make-gnu.sh OPENXLSX_ENABLE_LIBZIP=ON
vs.
Scripts/make-gnu.sh OPENXLSX_ENABLE_LIBZIP=OFF
Then compare the generation time of the spreadsheet vs. the save time.

[l1t1]

I suppose the different between minizip and libzip is very small, as their zip algorithms are the same. other algorithm such as zstd is better, but it can not be used here. it's a pity that little can be done about the save file operation.

ZIP-based packages shall not use compression algorithms except DEFLATE, as described in the ZIP Appnote.
from **ISO/IEC 29500-2:2021 **

[aral-matrix]

other algorithm such as zstd is better, but it can not be used here.

That depends. We have made the zip implementation quite encapsulated and it could be changed here:
https://github.com/troldal/OpenXLSX/blob/development-aral/OpenXLSX/headers/XLZipArchive.hpp#L61

If someone were to write a working interface to e.g. zstd with the layout of
https://github.com/troldal/OpenXLSX/blob/development-aral/OpenXLSX/headers/detail/LibZip.hpp
(which is just ~430 lines to implement a wrapper for LibZip).

I am sure @troldal would agree with me that we could add a third option for a zip library to the build environment, especially if that one is faster.
It's just not high on our priority list (or rather: not on there at all ;) - so if you would like to write & test a wrapper for zstd, we'll be happy to merge that here.

[l1t1]

you are right, accroding to the OOXML ISO standard, zstd algorithm xlsx cannot be read by any office client, so it's useless by now. maybe we can use other method, such as removing r="CR" attribute for dense data to reduce the xml size, it also results faster saving and compress.

[aral-matrix]

well - currently, OpenXLSX supports even whitespace preservation in the document, for prettier formatting of the underlying XML - I am not sure you would gain much speed that way.

At some point, the reality is really just that documents of a certain size require a certain amount of time to load/save, and as you get closer to that time, optimizations are a lot of effort for little gain.

The OOXML format was never designed to write large amounts of data at high speeds - XML is too cumbersome for that purpose.

[aral-matrix]

Do you see a way forward here still, short of trying to optimize the whole library? Because I honestly do not know how to improve speeds beyond the -O1 (or -O2, -O3) setting - by the way, I have re-enabled the GNU Makefile in the latest commit to the development-aral branch - but it requires that pugixml (>= 1.15) and libzip (>= 1.7) are installed on the system. Please note that the Makefile does not check the version, but (I think) we are using functionality of pugixml 1.15 (I can't remember which functions right now), so if a lower version is installed on the system, this would probably lead to errors.

Accordingly, the Makefile.GNU forces some options as they are the only ones currently supported:

2025-08-26 Temporary override of options (only supported configuration is with libpugixml and libzip preinstalled on the build system)
___________It remains TBD whether it will be possible (or is even desired) to pull in dependencies from the GNU Makefile
OPENXLSX_ENABLE_LIBPUGIXML         ON
OPENXLSX_ENABLE_LIBZIP             ON
OPENXLSX_ENABLE_LIBBOOST_NOWIDE    ON
OPENXLSX_SHARED_LIBRARY            OFF

So - if building on a Windows system, Boost nowide must also be installed on the system (version shouldn't matter much, but in our CMakeLists.txt we require >= 1.73.0)

I also finished the code review for defaulting class member functions, so the development-aral branch is almost ready to be merged into the master branch - it should be safe to use now.

If you have no other ideas for a speed improvement, I would eventually close this issue with the "wontfix" tag.