Fastest Way to use AdvGridFilter?

FLX TMS FlexCel for VCL & FMX
1

Hi Adrian,


I have an Excel spreadsheet with about 40k row and 30 columns. Not massive, but a reasonable size. I'd like to display the spreadsheet so the user can select the "Sheet" to import and header row. This means I don't really need any more than the first 100 row. I was hoping this would enable me to display the spreadsheet faster - so far I haven't been able to do so. Here's what I did:
  1. Created another XLS object (called dXLS) and clone the original XLS object
  2. Loop though the sheets in dXLS and delete all rows below 100
  3. Load dXLS into the AdvGridFilter.
Unfortunately this seem to be about the same speed as simply displaying the original XLS file (about 15 seconds).

Any ideas of how to speed it up?

Thanks,

Steve 
2

Hi,


There is a property in AdvGridImport that you can use: 
AdvGridImport.ImportOptions.ResizeGrid := false;

After setting the property, set the number of rows in the grid to say 100 and try it. It should be faster.

Some other things to try:
1)
The FlexCelPreviewer component in FlexCel might be faster, since you don't have to import the file into a grid, it reads the values directly form the XlsFile object. 

Try opening your file with the "Custom preview" demo (Demo\Delphi\Modules\25.Printing and Exporting\20.CustomPreview) and see if it is any faster.
While not shown in the Custom preview demo, you can also limit the number of rows and columns to display, by adding a print_area or by setting the PrintRangeRight/PrintRangeBottom properties in the FlexCelImageImport component attached to the FlexCelPreviewer.

2)If the preview doesn't work because you need a grid, you might want to try FlexCelGrid from FlexCel3 with XlsxAdapter so you can read xlsx and xls files. It should be faster than the filters again, because there is no "import" going on: the grid displays directly the cells in the XlsFile object.

If you want to try if this would be an option without needing to install FlexCel3, you can get an exe demo here:
http://tmssoftware.net/public/flexcel/XlsViewer.zip

3)The nuclear option: If times are crucial, the options above will make it faster but the XlsFile still needs to read and parse the whole file (which in xlsx can be not that fast). So if you just want a grid showing the numbers without much stuff like showing images and cell formats, you can use the "Virtual mode" to only load 100 rows and not the whole file.

You can try it with the demo:
Demo\Delphi\Modules\10.API\22.Virtual Mode

It has a checkbox to load only the first 50 rows, which of course is arbitrary, you can modify the demo to load 100. This option should be the fastest of all, because you stop reading the sheet in the file in the moment you have the data you want.  The rest of the file is not read/parsed. It will use much less memory too. But it is also the most "bare bones" solution since it will just display the cells, no merged cells for example or images, or lots of other stuff. Depending on your case, it might be a compromise you can take because it doesn't matter to select the sheet.
3

Thanks Adrian - your first suggestion works really well!


Steve