xlsx reader: optionally keep cell references, formulas, hidden rows and comments

[nachogarcia]

The xlsx reader gives each cell only its cached value, plus bold and italic. That's right for reading a workbook as a document. It loses what you need to check a workbook, though, such as one behind a table or figure in a paper:

• Formulas. =AVERAGE(L30:L44), where the author meant L30:L49, reads as just its result. That is the error at the heart of the Reinhart–Rogoff dispute.
• Cell positions. Nothing in the output says a value is B20, so nothing can point back to it.
• Hidden rows, columns and sheets. They read like visible data.
• Cell comments.

72 of the 73 real-world workbooks I've been testing with contain formulas.

The AST already has a place for this: rows and cells carry attributes. So I'd propose an extension, off by default, like the docx reader's styles, that adds:

• ref="B20" to every cell;
• formula="AVERAGE(B5:B19)" to every cell holding a formula, written as stored. Shared formulas would be expanded, so each cell gets its own references;
• comment="..." to a cell with a comment;
• a hidden class on hidden rows, on the cells of hidden columns, and on the heading of a hidden sheet.

HTML, JSON and native output keep these attributes, and Lua filters can use them. The Markdown writers drop them, so anyone wanting them in Markdown would render them with a filter, for example by appending the formula to the cell's text.

Questions:

1. Is an extension the right shape for this, and what should it be called?
2. Should hidden rows, columns and sheets be marked by default instead, as in Pptx reader: mark hidden slides #12031 for hidden slides, since that is structure rather than extra information?
3. Merged cells could become real row and column spans. Do you see a reason not to do that by default?
4. Shared formulas store their text once and the other cells refer to it, so expanding them means shifting relative references. That's the largest part of the work. Is it acceptable in the reader, or should a cell with a shared formula only get the formula of the cell that defines it?

[nachogarcia]

To make the proposal concrete, #12039 implements it as a draft. On 73 real-world workbooks, its formula text, including shared formulas, matches openpyxl's for all 27,834 formulas. Happy to change the name or any part of it according to what's decided here.