Using fpspreadsheet to write huge xlsx files

[wp]

Fixed in rev 3352. Thanks!

[marcov]

When overwriting the record (and thus the stringpointer) with #0's you only reset the record (overwriting the pointer) but the original string is still in memory

To be exact: it's reference-count isn't decremented. Since only the final decrement releases memory, it is a memory leak.

Simply assigning '' to the string is enough.  (that decreases the current ref count and assigns FPC_EMPTY_STRING (or something like it) which is a constant in the FPC rtl with double zero.)

[wp]

The current trunk version of fpspreadsheet contains the speed test application posted here by rvk, thanks a lot (folder examples/fpsSpeedTest). I modified it to allow changing of parameters (number of rows, format seletion etc. - they are stored in an ini file) and added also a routine for reading speed (not fully complete).

I extended the BufStream to be usable for the spreadsheet readers as well (all xls, ods, but not xlsx which does not have a reader at all so far). A factor 3 speed improvement for reading of biff2 compared to a few revisions ago. But I am not quite satisfied with this improvement because biff2 still reads considerably slower than biff5 and biff8 - in fact, if I copy the biff2 FileStream to a memorystream I get the same speed as for the other biffs, so something must still be wrong with the BufStream...

VirtualMode is not yet implemented for reading, but will follow.

Due to these modifications the workbook's "WritingOptions" are no longer named appropriately, and I renamed this property to "Options", and the set elements have the prefix "bo" ("book options") now (instead of "wo")

[wp]

Sorry to rename identifiers again and again, but this is work in progress...

Here's another one: The event OnNeedCellData is now called OnWriteCellData. The reason is that there is now also a OnReadCellData for virtual reading mode. The background is the same as for virtual writing mode: save memory for reading large files by passing cells through to the evaluating procedure without storing them in the worksheet. There is a demo_virtualmode_read in the examples/other folder, it requires the file created by demo_virtualmode_write (the former test_virtualmode).

As expected task manager does not show a significant increase of memory usage if a file is read in virtual mode.

[taazz]

Sorry to rename identifiers again and again, but this is work in progress...

Here's another one: The event OnNeedCellData is now called OnWriteCellData. The reason is that there is now also a OnReadCellData for virtual reading mode. The background is the same as for virtual writing mode: save memory for reading large files by passing cells through to the evaluating procedure without storing them in the worksheet. There is a demo_virtualmode_read in the examples/other folder, it requires the file created by demo_virtualmode_write (the former test_virtualmode).

As expected task manager does not show a significant increase of memory usage if a file is read in virtual mode.

So the only difference is that one is called from the reading methods and the other from the writing methods? Doesn't this makes it harder for the end users to use needing to write 2 different events for the same thing ee supply the data?

Disclaimer : I Haven't used the components my self not even installed them yet,  just a thought.

[wp]

So the only difference is that one is called from the reading methods and the other from the writing methods? Doesn't this makes it harder for the end users to use needing to write 2 different events for the same thing ee supply the data?

No, it is not possible to use the same event for both actions because of different calling parameters:

Code: [Select]

  TsWorkbookWriteCellDataEvent = procedure(Sender: TObject; ARow, ACol: Cardinal;
    var AValue: variant; var AStyleCell: PCell) of object;

  TsWorkbookReadCellDataEvent = procedure(Sender: TObject; ARow, ACol: Cardinal;
    const ADataCell: PCell) of object;


[taazz]

and where is the cell data handled on the read? if it has not data to handle then why call it dataevent?

[wp]

The data are contained within the cell.

The reason why there is a separate data value variable in the writing event is that the OnWriteCellData can also handle formatting which is taken from the StyleCell. This is a pointer to an existing template cell in the worksheet. If the data element of this cell would be used to pass also the data to the writer I see the risk that users tend to invent their own formatting which has not been listed by the writer before. There is a discussion of this topic futher up in this thread.

[taazz]

well I'm not familiar with the design of the suit so I'll just say this last thing and stop bugging you. The design seems a bit of an onion one layer on top of the other needs to be redesigned I guess any way thank you for you time, If I get any free time I'll take a closer look on the suit after all I might needed for exporting and importing my self too.