Shared Strings making file open timeout

[TamaBaka]

I have a 4MB xlsx file that I'm trying to open. This would normally be easy to load, however it has a lot of unique strings because it's basically a catalog with 4000 rows. And there lays the problem, its storage strategy uses shared strings.

I have my timeout set to 60s and it isn't enough time.

Is there a way to modify this library and get this file to open and stream within 3 seconds without manually inlining the file's strings first?

The only thing I can think of is to build the cache table on the fly but I'm not familiar enough with the spreadsheet format to know if this will cause more trouble than its worth.

[adrilo]

Hi @TamaBaka,

Shared strings definitely takes longer to process than inline strings. Now, there are different strategies reading the shared strings file. Given the number of unique strings and your memory limit, Spout will either load all the strings in memory (if it fits), or split the shared strings file into smaller chunks that fit into memory and load them on demand (this behavior is to prevent running out of memory - if you have a million strings in your spreadsheet, loading them all in memory will make your application crash).

For instance, if you have the string "foo" in A1 and A4000, the string is actually stored only once in the sharedStrings.xml file (let's say the string has index 1).
When reading the entire spreadsheet, the cells are read in order. Starting with cell A1, Spout looks at its description in another file and sees that its content is the shared string at index 1. So it either gets it from memory if all strings were loaded in memory, or load the first chunk of the sharedStrings.xml file in memory (and cache it for future use) and get it from there. If the 2nd method was used, when reaching cell A4000, the sharedStrings.xml chunk loaded in memory may not necessarily be the first chunk (for instance if Spout had to get the shared string with index 400000). So when trying to read the content for A4000, Spout has to unload the current chunk and reload the first chunk, which takes some time.

So if you have this pattern (cells referencing shared strings in random order) across your whole spreadsheet, reading it will take a long time, no matter what...

Now about potential solutions:

• change the way the spreadsheet is created (if possible)
• increase the time or memory limit