Reader 📋

• Read only
  • Therefore no calculations or updates to formula calls

Interfaces 🏗️

• Will use the DotNet core functionality by default
• But, if your target deployment allows for the use of native performant binaries, then via the use of interfaces these will be pluggable
  • i.e. Using Zlib.Net for getting the data streams out of the compressed Excel file faster. (Or SharpZipLib / PowerPlayZipper)
  • A faster / slimmer implementation for xml stream reading (i.e. TurboXml)
• Allow the implementation of different source files (i.e. XLSB)

Q & A's

• Q: Why?
• A: As mentioned above, this is to allow a developer to replace with external nugets that might perform better XML speed etc.

Memory 🌐

• The reason for this project, is to handle very large XSLX files (i.e. > 500K rows with > 180 columns per sheet, with multiple sheets of this size)
• For ETL validation scenarios, i.e. make sure that the user modified data that has been transferred has interaction rules applied, before moving onto the T and L stages
• Try not to hit / store in the LOH
• No internal .Net memory of previously loaded sheets / rows.

Q & A's

• Q: It appears that this uses more memory than other implementations
• A: Currently yes, but it is being optimised for Range Extraction,
  • AND for allowing multiple rows (With cell data) to be stored in memory at the same time, (i.e. via ToList() call);
  • AND to allow multiple sheets to be read at the same time (Unlike some to of the others that use "a single" global memory to represent a row)
  • And it appears that the current benchmarks do not extract unless a ToString and a check on the result is used (Otherwise the Jit removes the unassigned dead code)
  • And, the memory used will actually be used in the ETL pipeline anyway, so it's just being truthful

Efficiency 📦

• As hinted by the above statements, this is to be targetted at memory restricted environments (i.e. ASP Net VM's)
• Use the OS's Temp File caching, so if the memory is tight then the Owner app will not have to worry about OOM exceptions, or having to use Swap Disk speeds.
• Only unzip the sheet(s) when they are asked for
• Only load the shared strings upto the current request number

Q & A's

• Q: Sometimes the Async await s add too much overhead
• A: true, that is why there are also the equivalent base interfaces that perform the same functionality without the need for the async await overheads, and still with ConcellationToken usage(s).

Etc. 🔧

CancellationTokens

• This is to allow the Large files to be Aborted
• Make "Most" of the "Net Cores'" API's Asynchronous Tasks

IDisposable

• Got to tidy up those Temp Files, and release the FileStream's

Caveats ⛔:

Not Be: Same sheet Thread safe 📊

• It will Not be same sheet Instance thread safe, because the xml reader will be locked (Forward only) to the sheet in use.
  • but you can Open the sheet more than once, and have different threads running over it,
  • And you can have Parallel threads access the Excel file
  • Just remember to set Options{ AccessExcelFileInForwardOnlyMode = false}

Not Do: Dynamic Ranges ⚠️

• i.e. Ones that contain formulas:
  • <definedName name="Prices">OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)</definedName>

Not Do: Poco 🤖

• A POCO / Type populator (Extensions can be written for that later)

Not Be a: Writer / Modifier 📚

• Totally beyond the scope of this project remit