OpenDocument - Adv Document Collab SC

RE: [office-collab] CT and Spreadsheet tables

  • 1.  RE: [office-collab] CT and Spreadsheet tables

    Posted 04-08-2011 16:27
    It is a well-established and not-to-be-ignored use case that users can delete/insert/move entire [blocks of] rows or columns of spreadsheet tables, and undo/reject those, all as single actions based on mass selections. This is done in a way that breaks very few of the formulas by effectively rewriting references. Furthermore, this capability is widely implemented and a requirement for any successful interoperability among the most successful products of this kind. I have numerous spreadsheets that have =SUM([.A1:.A70]) in cell A71 for example, and when I insert a new row after row 35, the formula magically becomes =SUM([.A1:.A71]) in cell A72. In addition, if there are counterparts in columns B, C, ... and I insert a new column B, the correct thing happens when those cells previously in columns B, C, ... now become cells of columns C, D, E, .... If I do a fill right of column A to the new column B, that produces the correct formulas in B as relocations of the A ones, etc. For the admittedly simple spreadsheets that I have, the adjustments appear to be instantaneous. There are cases where the simple translation of the referenced coordinates in a formula will be incorrect, and these need to be corrected manually. They are often signaled by error values or by patently-obvious errors in cell result-values. (There is something called the Detective feature that might be helpful in this regard and I suppose the persistent information that is part of that provision of ODF 1.2 is also impacted by these sheet manipulations.) This is not addressed in the OpenFormula part of the ODF 1.2 specification because we only talk about the formulas in the persistent document format, not what may be happening in a consumer/producer where the spreadsheet is being manipulated interactively, although there is some attention to recalculation. However, to define tracked changes, we now have to somehow reconcile with the use cases that already exist and that folks count on. There are already change-tracking provisions for spreadsheets in ODF 1.2. I don't profess to understand them. It has been enough of a preoccupation to deal with the provisions for tracked-changes of text. I do know that the OpenFormula part of the specification makes no reliance on them directly or indirectly with respect to host-dependent behaviors. I don't know what is retained in the tracked transformation material, or that it is useful in displaying a spreadsheet with changes shown. But it must be enough that the changes can be selectively rescinded and accepted correctly. I do think that we have to find a way to reach a level where we can show these use cases are somehow "naturally" supported and that one can make independently-implemented interoperable consumers that preserve what an independently-implemented interoperable producer emitted in conformant tracked-changes concerning spreadsheet table manipulations. - Dennis