Empact/roo — pull request #136 by thefooj (2014-06-05 20:22:30+00:00)
Title: Faster processing of large xlsx files + my previous pull request

We process really large XLSX files for our company. As has been noted by others, roo performance is slow for large xlsx files.

@rlburkes has a pull request (https://github.com/Empact/roo/pull/69) for "Support for streamed parsing of very large excelx sheets" which is going through approval/merging (discussion active as of April 2014). I really like the approach that @rlburkes took.

I needed a way to process very large xlsx files without streaming (for now), though. In my particular application, memory consumption is not so much a problem as is processing time. Also, I have pre-existing code that was working for the .xls files and now needs to be adapted for .xlsx files (the data vendor changed their format, because they needed > 65536 rows per tab!). I didn't want to change all of my pre-existing code to use @rlburkes streaming code (yet).

I would be very interested in combining my work with @rlburkes work, so that transparent faster-loading and streaming can both work. Happy to help with that monster of a merge :)

This pull request takes a very similar approach to @rlburkes but addresses the non-streaming case by doing a few things:

* *Lazy-loading of sheet data in all cases* -- We don't process the sheetX.xml files until the sheet is actually requested. No more saving Nokogiri XML documents on initializing, for the sheet-specific data (cells, hyperlinks, rels, comments)
* *Use Nokogiri::XML::Reader to process the sheet-specific data* -- Xpath is slow for large documents, and our documents are well-isolated in terms of XML namespaces. I borrow from @rlburkes and use Nokogiri::XML::Reader to quickly process the data. Data is stored, as before, in `@cell[sheet], @comment[sheet], @hyperlink[sheet], @rel[sheet]`

As a result, you can call on individual cells etc. just as before. The lazy-loading is handled transparently to the user.

All tests are passing (except for an encoding issue on `test_bug_pfand_from_windows_phone_xlsx` that was pre-existing). My earlier pull request includes fixes to previously failing tests. Those are incorporated here as well.

With the optimizations listed below, I got significant performance gains. For a 8MB+ file with 2 tabs that had 70k and 110k rows respectively, I went from 15 minutes to load down to ~2 min to load. That's good enough for my application... and any further gains would involve using the streaming approach.

### A few other things to note:

* *Cell/comment/hyperlink is loaded together when any one is requested* --
* *Cell/hyperlink loading is coordinated* -- The data in sheetX.xml.rels (relationships for hyperlinks) is coordinated with cells (sheetX.xml) and hyperlinks (sheetX.xml). Cells and hyperlinks are processed together. I handle either scenario -- when hyperlinks appear first or cells appear first.
* This pull request includes test fixes and other changes I made in my previous pull request (https://github.com/Empact/roo/pull/135)
* I also changed the shared-text processing to use the same approach.

### Annoying side-effects:

* My approach lazy-loads the sheet-specific files. I love the way Roo uses `make_tmpdir do {...}` to clean up after itself. In order to maintain that "clean up temp files after yourself" approach, I replicate the `download_uri` and `unzip` blocks for each sheet/tab load. So if you have a Google spreadsheet with 100 tabs, it'll get downloaded 100 times. The only way around this is to change the overall behavior to unzip the files, keep them around and track the with the Excelx instance. Because Ruby doesn't have destructors, you can't auto-clean those files :(. I'm happy to discuss this more, and come up with a better workaround.
* Relatedly, we have to unzip and extract files for each sheet when they're requested. However, the extraction only happens for the files that are needed.

### Possible gotchas:

Because I'm using Nokogiri::XML::Reader instead of a Nokogiri XML document with explicit xpath, it's possible that we have namespace or hierarchy issues. So, for example, the old code did this to get cells:

```ruby
@sheet_doc[sheets.index(sheet)].xpath("/xmlns:worksheet/xmlns:sheetData/xmlns:row/xmlns:c"
```

This is requiring a worksheet/sheetData/row/c tree structure. My approach just looks for "c" nodes, regardless of ancestor paths. This would obviously have issues if Microsoft started doing something very strange with their XML structures (using "c" in different contexts). Given the fact that it's for a very specific document format (Excel), I would think that's not particularly an issue.

Huge thanks to @rlburkes and @Empact for the great work. Let me now how I can be of help.