Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VFPxWorkbookXLSX takes lot of time to open large XLSX files!

Status
Not open for further replies.

Rajesh Karunakaran

Programmer
Sep 29, 2016
542
MU
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
 
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
 
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
 
Hi mJindrova,

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:
I will have a look into the utility you shared, not heard of it....
Thanks.


Rajesh
 
Rajesh said:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top