Proper support for cross-sheet references, was "Errors creating pivot tables in separate sheets" about xlsx

[LTibbetts]

@qrilka Thanks for the quick reply! It is not important that I have the data and pivot table in different sheets, just a matter of stylistic preference, so I will continue with them in the same sheet. You may close the issue if you want to as I will likely not have the time to work on adding support for this, or leave it open for future users to find easily.

[qrilka]

@LTibbetts so far xlsx was supporting references to the same sheet in most of the cases, in #157 @flhorizon added some rudimentary support for cross-sheet references but I think none of usages were made aware of them.
Proper fix for this looks to require some effort (I guess we'd better make this visible on type level and not leave it to run time).
I'm not sure I will have time soon to tackle this (at work I have switched to Rust some ago) but I will be glad to help discussing/reviewing the code.

[qrilka]

I think we should leave it as something that's better be fixed, thanks

[flhorizon]

Hey

I didn't look into how PivotTable are actually meant to work, and whether cross-sheet CellRef should be involved.

But regarding that error, it seems like writer accumulator functions like generateCache should be able to access 2 cellMaps: the one of _pvtSrcSheet and the cellMap from the sheet that contains it.

The fact there's a single _pvtSrcSheet <worksheetSource sheet="" ref=""> hints IMO the _pvtSrcRef is shaped like a local, relative ref

(looks like pivot tables references are either self->self or self->1 other).

All that to say there might be a quick win without changing the expectations about the cellref format.

I'd need to dissect a file and reads the specs though to be sure 😄

---

About refining the CellRef type, I've thought a bit about them while making the tests, could be:

• an existentially quantified SomeCellRef + unifying class
  • the class has Exception-like from/to casts and parser/writer methods
• unifying as many Typeable subtypes as configurations of cell ref (local | foreign) X (single | range)
• plus a sum type CellRefRep for a hint of what a SomeCellRef can be

But I digress, that's stuff for another issue