[FeatureRequest]: `LoadAsync` to take and respect `CancellationToken`

[Smurf-IV]

If loading a large xslx file (i.e. over 1GB with several sheets)
Then there is a need to perform cancellation (i.e. via page transition(s) etc.)
It will needs to be use wherever it can

• File load / open (As async)
• CopyAsync
• Between zip paging (Or use async version of zip)
• File Save

[rabanti-github]

Hi,
This is currently out of scope for v2.x.
Technically, this could be possible in the upcoming v3 version, since all reader and writer instances are designed as (replaceable) plugins.
However, there are still some possibilities to handle such situations now.

There are three possibilities, how an XLSX file can become so big:

1. There are a high number or some extremely large attachments (e.g. embedded images) in the workbook
2. There is a very high number of worksheets with a lot of numerical or formula values
3. There is one or many worksheet(s) with an extremely high number or variant text values, or one worksheet with an extremely high number of numeric or formula values

The first case is currently ignorable, since NanoXLSX will just skip unknown files in the workbook. This should not affect performance.

The second case will have an effect of the number of worksheet XML files. For example, 100 worksheets with an approx. size of 10MB will add up to 1GB. Other files within the workbook can be neglected. These 10MB chunks can be handled, though. It will take some time, but memory consumption should not go through the roof, since the worksheet reader should be disposed after every file (however, I would have to check this in detail).

The third case will be the one, that is the most difficult to handle. It either affects the size of the shared string file (for text values) or the size of the worksheet file (for numeric / formula values). Such XML files are quite hard to handle, and therefore possibly not even readable under normal circumstances.

But here is the thing: If you aren’t in a very volatile memory setup, you can predict which XLSX files to read, and which not. If you know about the data structure – and you know that the third scenario applies – you can deny reading the file from the beginning. No need trying to read it. The mentioned scenario will always lead to a single very large xml file that more or less reflects the size of the XLSX file. The only thing is to find out, what a reasonable size to process is. This could be done by testing (start with 100MB XLSX, up to a 2GB XLSX with the mentioned sample data).

If you have a mixed scenario – assuming, we are talking about external data from customers or suppliers – you still could do some mitigation. From experience, it is often known which customers/suppliers are delivering what kind of data. And therefore, some rules could be applied. Like:

Customer	Content	Decision
Customer A	Delivers workbooks with a high number of worksheets with a moderate number of values per sheet	PASS
Customer B	Delivers workbooks with one extremely large numeric worksheet and a file size of > 1GB	DENY
Customer C	Packs just a bunch of large images in his files	PASS

[Smurf-IV]

Option 4: (My Real world case)
I have an excel input data file with 14 sheets

• 2 sheets with over 400K rows,
• with a mixture of strings / bools / dates / and mostly doubles
• These have over 150 columns (Some versions have 180)
• In the future these will become 4 sheets
• The other sheets contain
  • Offset tables (More than 1 per sheet)
  • No headers
  • Formulas / Ranges / etc
    Currently this is being read via EPPlus, but the memory toil is enormous.
    I have investigated Sylvan (Very fast and memory efficient) but it does not do Ranges etc.

[rabanti-github]

I have added the request to the v3 backlog --> https://github.com/rabanti-github/NanoXLSX/wiki/Backlog
Loading and Saving could be realized between the particular loaders. For the copy operation, async was not yet considered. But let's see.

However, I still would highly recommend trying to deal with such data preemptive (just out of job experience during EDI projects). Here are some thoughts:

• Keep track of the number of defined cells or the app memory usage. If the defined cells use >50% of available memory, copy will lead to >100%, and therefore should not be done in the first place
• If copy is time- but not memory-critical: Determine the average time to copy a worksheet with n cells, and use this, multiplied by your current cell amount as a threshold whether to copy or not
• If loading is critical: Consider adding a pre-processor that just opens the XLSX file as zip, and read the file sizes (e.g. of worksheets XML files) but not the actual streams. Decide based on this whether to read or not
• If saving is critical: Same as with copy. The average time to save a workbook with n cells, multiplied with the current number of cells could used as threshold whether to save or not
• If you want to have some control during load / save, consider using a wrapper thread
• But most importantly (and probably the most difficult): If you are not under control of the file size, try to talk to your customer / contractor / business owner / product owner / supplier, whether smaller files are possible. E.g. better division of the data into 10 files with each 100MB than 1 x 1GB

[rabanti-github]

One small addition:
The thoughts in the last comment were dealing with automatic processing.
But if you intend to offer a cancellation that a user deliberately can trigger (via UI), then it is of course a little bit different.
In this case, maybe the mentioned wrapper thread could be an intermediate solution

[Smurf-IV]

All good intentions for a "New" workflow.
But this is a legacy thing :-(
And they are only getting "Bigger" (as in the number of rows). Because optimisation has been done in the processing (The actual compute) of all this data, therefore "they" (The customer) are pushing more in.
I also have a problem of getting this data back to them (In excel format) but that another thing, and a smaller data set!)