GridExcelIO application not responding

FNC TMS FNC UI Pack
1

Hello

I use gridexcelio component in order to import excel XLS files into a FNC Grid, after loaded I save row by row into a SQLite DB.

Component works fine with small files, usually less than 2MB, but If I try to "import" a bigger files then my application shows "not responding" and does not dissapear.

Why you did not take care of this when execute the "import" process in order to load big data files to a FNCGrid?

Video below shows the issue

https://timbralofacil.com/descargas/gridexcel.mp4

I am using Delphi 11.3 with latest TMS components, this happens on Android

2

Did you use BeginUpdate / EndUpdate around your code which is required to speed up the process of importing data? Without a code snippet it's difficult to understand what happens

3

The only code that hangs my app is the next line:

TTMSFNCGRIDEXCELIO.XLSImport(FILENAME);

I don't see why use BeginUpdate or EndUpdate in there isn't?

4

Use

TMSFNCGrid1.BeginUpdate;
try
  TMSFNCGridExcelIO.XLSImport();
finally
  TMSFNCGrid1.EndUpdate;
end;
5

I coded your suggestion, but remains the issue

6

Can you give us insight on how many rows / columns the XLS file contains?

7

Ferreteria1.xls (2.7 MB)

Above is part of the file but remains the troubles on Android, the original file is about 30,000 rows and you can download it from the next link:

https://timbralofacil.com/descargas/ferreteria.xls

8

Pieter concerning to this point, I did not know any about the TMSFNCGridExcelIO.XLSImport process but I think a possible solution is:

  1. Get the number of rows inside the XLS file prior to start importing in the TMSFNCGrid
  2. Start importing process in chunks of 500 rows, after a chunk is reached maybe you can put an "application.processmessages"

Do you think this could work?

9

@adrian Does the excel bridge have better performance for larger XLS files or does it purely rely on the grid?

10

The idea to use the grid is just a middle tier between the excel file and the SQLite DB, but I don't need to show the grid to the enduser.

If you have another way to read row by row the excel file then point me in the right direction please.

Also I used "export" method too in order to allow my end user to share an XLS file with some results, if the "export" FNC method takes too much in create the result the application hangs.

What I am trying to tell you is that I need a way to read/write bigger XLS files with FNC no matter if I don't use any grid, finally my enduser want results. Read XLS it's for import to a SQLite DB, and WRITE XLS is to export results from the SQLite DB and share across any electronic media like: whatsapp, facebook messenger, bluetooth or just simply to save to a usb.

The application not responding it's only on Android, I tested on Windows and iOS without any problem.

11

Hello

Any news concerning this matter???

It passed 21 days from your last response

Thanks

13

Hello

What happend again with this issue???

It pass almost two months and there is no solution

14

Apologies for the late response, we are currently investigating this.

15

Hi,
From what I see in the video, you have hangs even after loading the grid, so this seems grid related, not related to the code to load the xls file.

I don't need to show the grid to the enduser.
If you have another way to read row by row the excel file then point me in the right direction please.

If you don't need the grid, you should use FlexCel 7: TMS FlexCel for VCL & FMX Powerful, extensive & flexible component suite for native Excel report & file generation & manipulation

GridExcelIO uses an old version of FlexCel (FlexCel 3) too, but FlexCel 7 is faster and it can handle xlsx files too. It is also non-visual, which in this case will speed up the processing. You can see an example of how to read a file here:
https://doc.tmssoftware.com/flexcel/vcl/samples/firemonkey-desktop/reading-files/index.html

And you can use "Virtual Mode" to use less memory when the files are huge, and also to limit the import to say the first 1000 rows:
https://doc.tmssoftware.com/flexcel/vcl/samples/delphi/api/virtual-mode/index.html

So if you want to say show the user the first 1000 rows, but then use all the rows to export, you can use virtual mode to cut the rows you load into the grid, but then load the full file when processing it.

You can also use the FlexCelPreview component to show it, I am not sure if the performance will be different from the grid, but you can try it:

https://doc.tmssoftware.com/flexcel/vcl/samples/firemonkey-mobile/flexdocs/index.html

Finally, note that a big xls/x file can have millions of cells, and while FlexCel code is optimized a lot both for speed and memory consumption, it still can take some time. Especially because xls/x files can have formulas, so in order to calculate them we need to keep the full file in memory. Xlsx in this regard is worse than xls, both because it allows larger files and also because they are zipped xlm files, so we need to unzip them and parse the xml. Still, FlexCel should be fast enough in most cases. If you are getting errors of app not responding, normally the solution is to run the FlexCel code in a thread, and keep some progress in the main thread so Android doesn't report the app as hang.

16

I forgot to mention that to load a xls/x file to the grid with FlexCel 7 you can use the FNC Excel Bridges: TMS FNC Grid Excel Bridge Universal import/Export bridge for TMS FNC Grid to Excel .XLS and .XLSX files in VCL, FMX, LCL and web apps

But you will likely run into similar issues as you did before if the files are really big. Phones are just slower than PCs, and they can struggle with too many records. If you don't need the visual part (or can limit the visual to a max number of rows) you should be able to get much more speed.