VFPxWorkbookXLSX takes lot of time to open large XLSX files!

[Rajesh Karunakaran]

Dear friends,

I have been using VFPxWorkbookXLSX for working with XLSX files. But so far, they were all considerably small files.
Now, when I try to open a file with 16800 records and 38 columns, it appears it takes more than half a minute to open it.
Is this usual?



Rajesh

 

[Chris Miller]

A better indicator would be the file size. As another one already said the big difference to XLS is not only that an XLSX file is not binary binary but a ZIP format file that contains a lot of files, some of which could even be images or other binaries and many XML files. So the time to open an XLS is determined by at least the two factors of unzipping the content and then parsing the XML.

How large is the one XLSX file that takes such an opening time? You can easily find out (with Winzip or WinRar or 7ZIP) what is the compression rate of the file and thus not only what is the original size of all included content but also what time it takes to compress or uncompress such a compression ratio.

Besides that, VFPxWorkbookXLSX, so you can easily measure what takes most time for it to open the file.

Chriss

 

[Rajesh Karunakaran]

Hi Chriss,

Yes, because of the nature of XLSX format, may be it takes time. The file is only 58kb.
Maybe, it has may inner files and uncompressing time may also be a reason.
I will do an analysis once my primary program code is finished.



Rajesh

 

[mJindrova]

Files with 16800 records and 38 columns is a small file.

Rajesh do mean this utility (

https://github.com/ggreen86/XLSX-Workbook-Class)?

This utility is very safety, has many features (for reading, writing, appending data) - cell by cell, but will be slow.

But if you want import data without delaying try use this utility:

https://praisachion.blogspot.com/2017/08/importfromxlsx-40.html

mJindrova

 

[tomk3]

There is a version 5 :

https://praisachion.blogspot.com/2022/

regards
tom

 

[Rajesh Karunakaran]

Hi mJindrova,

Rajesh do mean this utility (

https://github.com/ggreen86/XLSX-Workbook-Class)?

Yes, that's the one.

In fact, I want to read from XLSX and break into multiple files for subsequent import into one web application.
So, I am trying to read a set of rows (from source) and writing to a new XLSX.
I am writing cell by cell as I couldn't find a way to copy a range and write it in a single stretch.
Is such a feature there in this library?

But if you want import data without delaying try use this utility:

https://praisachion.blogspot.com/2017/08/importfro...

I will have a look into the utility you shared, not heard of it....
Thanks.


Rajesh

 

[Chris Miller]

I am writing cell by cell

That could easily explain what takes so long, not so much the opening/reading, but at the same time writing out to a new worksheet cell by cell is not fast.

I don't think the XLSX-Workbook-Class is offering methods to work on Excel sheets, Excel itself has all you need in that respect.
First make up an array of the cell values and then set the range to the array, that's the fastest way to set an area of cells in Excel all in oe go.



Chriss

 

[ggreen61]

Rajesh--

The largest amount of time is to parse the xml files, particularly the sheet1.xml, sheet2.xml, etc. VFP is not the fastest at xml parsing. I use a pointer process to read the xml segments to speed it up, but it is the slowest part. Additionally, the cell formatting has to be read, the strings, and other settings in multiple xml files.

I have several ideas on a possible speed improvements to the processing for when the need is to only read the xlsx file. If you could send me an example file to test with, I will see what I can do.

Greg