Problem with big size file.

[kingpin58]

1. Maybe you didn't test reading&writing with very big xlsx file. For example, a xlsx file contains 20 sheets and, every sheet contains 2000 rows and 200 columns; every cell has different data. Some problems will occur when your dll handles with this big size xlsx file. When Office open the big size xlsx file created by QtXlsxWriter, it will report a reading error.Here is the code to create the big size xlsx file:

   QXlsx::Document xlsx; QString sheetName="200"; QString cellString; xlsx.addSheet(sheetName); for (int i=0;i<2000;i++) for (int j=0;j<200;++j) { cellString.sprintf("%d:%d",i,j); xlsx.write(i,j,cellString); } xlsx.saveAs("Book1.xlsx");

Then open Book1.xlsx via office 2007, a problem comes. Maybe you can let it a try.

2. It needs very big memory when handle with very big size xlsx file. I do not know the xlsx file format actually; perhaps we cannot operate xlsx just like handling with file based stream.

[kingpin58]

Thanks

[dbzhang800]

Hi,

1. There is one error in your demo code: row and column number starts from 1 instead of 0. However, these code will not generate bad .xlsx file after https://github.com/dbzhang800/QtXlsxWriter/commit/bd921dddf979a6be9f7f02b74dfc95f6e61b1b07 .As xlsx.write(row, col, xxx) will do nothing when row or col is zero.
2. Yes, we still have no large xlsx file support at present. One reason is that all the sheets are loaded into the memory, hope someone can refactor the code to make that only one sheet in memory.

Another reason is that, the function of QZipReader/QZipWriter is very limited, maybe we can make use of other libraries such as KArchive.

[kingpin58]

1.Thank you.Because array is start with 0; so I think the row OR column is start with 0 also.Thant my fault. BTW, maybe we can add assert check in the debug version, then the user can know what is happened. 2.After my project completed, I will read your code, learn the coding method from you. thank you for your job.

[luckyhacky]

Tested file 1 with indexes starting from 0 -> result: Word 2007 is complaining Tested file 2 with indexes starting from 1 -> result: Word 2007 is accepting file without any issue

All tests were done with sample code by kingpin58 (see above). So i think commit bd921dddf979a6be9f7f02b74dfc95f6e61b1b07 fixes this behaviour and is not ailliated with filesize of ~2MB. For debug version I think a warning or asssert check would be a good choice for development.

[artem-erofeev]

@dbzhang800 it is not a solution, to contain in memory even one sheet. Is it possible to change XlsWriter just for this purpose? We are making export of large dataset with the help of your library.

[dbzhang800]

Hi, artem-erofeev, thanks for your feedback. Yes, it's a design problem of QtXlsx.

In long run, this library will be replaced by another library at https://github.com/dbzhang800/QtOfficeOpenXml . The opc part of the QtOfficeOpenXml works well now, though spreedsheetml part isn't finished.

[Mercury13]

Well, I’ll insert my own $5e−2. Your library is EXTREMELY slow. Tested on 1Mb XLSX packed with data (no special formatting).

[artem-erofeev]

@dbzhang800 how things going? Any progress?

[robeug11]

@Mercury13 @dbzhang800 I used this library with 1.2 Mb of data. It's run extremely slow. Can you @Mercury13 @dbzhang800 please let me know How to overcome this slowness issue? Thanks in advance.