[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: RE: [office-collab] CT and Spreadsheet tables
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 -----Original Message----- From: Robin LaFontaine [mailto:robin.lafontaine@deltaxml.com] Sent: Friday, April 08, 2011 05:57 To: office-collab@lists.oasis-open.org Subject: Re: [office-collab] CT and Spreadsheet tables Michael, Yes, you are correct - I should not work late on a train home! The change tracking will certainly be a lot shorter if formulae are omitted in recalculated cells, but this will put a burden both on the specification and on the application. For example, the specification would need to say exactly how a row insertion is handled in terms of the modification to all the formulae (on all the worksheets), and all applications would need to implement this. It could be a challenge to specify how that is done, and to test it. There is some similarity also with a global change where a simple editing operation results in a lot of changes throughout the document. But in this case it certainly seems sensible to record all the individual changes. There are some choices to be made here. In my own experience of data exchange formats, it is usually dangerous to provide shorthand ways of doing things, and being explicit and detailed is usually worth the cost of extra storage space. It may not look so nice, but it keeps the specification simpler and avoids ambiguity. Regards, Robin On 08/04/2011 06:54, Michael Brauer wrote: Hi, On 07.04.2011 21:01, Robin LaFontaine wrote: Frank, The issue here is the knock-on effect on calculated values. The simple solution is not to record calculated values in tracked changes... It is not only an effect on calculated values. These indeed may be omitted in the recorded cells. If you insert a row or column into a speadsheet, office applications update the formulas in the cells so that they still refer to the original ones. For instance, if you have a formula referencing B1 and insert a column as first column, then that reference becomes C1 because columns B becomes column C. In the worst case, all formulas are adapted. But maybe this could also be resolved by omitting the formulas in the recalculated cells. Best regards Michael there is an argument to say they should not be in the interchange format at all because they are redundant... but that is a separate issue! But your point is a good one and would need to be resolved. A possible solution is that the specification identifies derived values and these could be ignored for CT purposes, as indeed they are in the current solution. This does not seem to be a difficult rule to define or to implement. Regards, Robin Frank Meies wrote: Hi all, thinking about CT in spreadsheet tables, I was wondering about how this is supposed to be handled by the generic proposal and whether the current way to track changes is compatible with Excel. Although the generic proposal is able to handle any possible kind of changes, spreadsheets with many formulas and dependencies are somewhat problematic. Let's have a look at the following example: |----------------------------| |4711 | | |----------------------------| |42 |=SUM(A1:A2)| |----------------------------| Then we 1. Insert a row 2. Insert a column 3. Change 4711 to 4712 The result is |---------------------------------| |4712 | | | |---------------------------------| | | | | |---------------------------------| |42 | |=SUM(A1:A3)| |---------------------------------| The result in the current implementation is this (I omitted some style information): <table:tracked-changes> <table:insertion table:id="ct1" table:type="row" table:position="1" table:table="0"/> <table:insertion table:id="ct2" table:type="column" table:position="1" table:table="0"/> <table:cell-content-change table:id="ct3"> <table:cell-address table:column="0" table:row="0" table:table="0"/> <table:previous> <table:change-track-table-cell office:value-type="float" office:value="4711"/> </table:previous> </table:cell-content-change> </table:tracked-changes> <table:table> <table:table-column/> <table:table-column table:number-columns-repeated="2"/> <table:table-row> <table:table-cell office:value-type="float" office:value="4712"> <text:p>4712</text:p> </table:table-cell> <table:table-cell table:number-columns-repeated="2"/> </table:table-row> <table:table-row> <table:table-cell table:number-columns-repeated="3"/> </table:table-row> <table:table-row> <table:table-cell office:value-type="float" office:value="42"> <text:p>42</text:p> </table:table-cell> <table:table-cell/> <table:table-cell table:formula="of:=SUM([.A1:.A3])" office:value-type="float" office:value="4754"> <text:p>4754</text:p> </table:table-cell> </table:table-row> </table:table> Please note that there is no CT markup in the table xml code. There's only a high-level description of what has happened table:tracked-changes section. This results in a lean xml code, on the other hand you need to know some application logic in order to undo the changes, i.e. the office:value in C3 has to be adjusted if you revert the third change. Expressing this example using the generic ct proposal, the xml would look (somewhat) like this: <table:table> <table:table-column ac:change002="ct2,remove,table:number-columns-repeated,2"/> <table:table-column delta:insertion-type="insert-with-content" delta:insertion-change-idref="ct2" table:number-columns-repeated="2"/> <table:table-row> <table:table-cell office:value-type="float" ac:change003="ct3,modify,office:value,4711" office:value="4712"> <delta:removed-content delta:removal-change-idref="ct3"> <text:p>4711</text:p> </delta:removed-content> <text:p delta:insertion-type="insert-with-content" delta:insertion-change-idref="ct3" >4712</text:p> </table:table-cell> <table:table-cell delta:insertion-type="insert-with-content" delta:insertion-change-idref="ct2"/> <table:table-cell/> </table:table-row> <table:table-row delta:insertion-type="insert-with-content" delta:insertion-change-idref="ct1"> <table:table-cell ac:change002="ct2,modify,table:number-columns-repeated,2" table:number-columns-repeated="3"/> </table:table-row> <table:table-row> <table:table-cell office:value-type="float" office:value="42"> <text:p>42</text:p> </table:table-cell> <table:table-cell delta:insertion-type="insert-with-content" delta:insertion-change-idref="ct2"/> <table:table-cell table:formula="of:=SUM([.A1:.A3])" ac:change001="ct1,modify,table:formula,'of:=SUM([.A1:.A2])'" office:value-type="float" ac:change003="ct3,modify,office:value,4753" office:value="4754"> <delta:removed-content delta:removal-change-idref="ct3"> <text:p>4753</text:p> </delta:removed-content> <text:p delta:insertion-type="insert-with-content" delta:insertion-change-idref="ct3" >4754</text:p> </table:table-cell> </table:table-row> </table:table> Any further changes made to the cell contents of A1 adds one attribute to A1 *and* to all depending cells, in this case C3. So in case of a large table with lots of dependencies this results in a bloated xml file (for 256 x 256 cells each depending on a single cell this means that each value change of this cell results in ~ 2MB of CT code only for the ac:change attributes). So from this point of view the current approach seems to be more efficient. Any opinions? Regards, Frank -- Oracle <http://www.oracle.com> Frank Meies | Software Developer Phone: +49 49 23646 500 <tel:+49%2049%2023646%20500> Oracle OFFICE GBU ORACLE Deutschland B.V. & Co. KG | Nagelsweg 55 | 20097 Hamburg ORACLE Deutschland B.V. & Co. KG Hauptverwaltung: Riesstr. 25, D-80992 München Registergericht: Amtsgericht München, HRA 95603 Komplementärin: ORACLE Deutschland Verwaltung B.V. Rijnzathe 6, 3454PV De Meern, Niederlande Handelsregister der Handelskammer Midden-Niederlande, Nr. 30143697 Geschäftsführer: Jürgen Kunz, Marcel van de Molen, Alexander van der Ven Green Oracle <http://www.oracle.com/commitment> Oracle is committed to developing practices and products that help protect the environment -- -- ----------------------------------------------------------------- Robin La Fontaine, Director, DeltaXML Ltd "Change control for XML" T: +44 1684 592 144 E: robin.lafontaine@deltaxml.com http://www.deltaxml.com Registered in England 02528681 Reg. Office: Monsell House, WR8 0QN, UK -- Oracle <http://www.oracle.com> Michael Brauer | Oracle Office Development Phone: +49 40 23646 500 <tel:+49%2040%2023646%20500> Oracle Office Global Business Unit ORACLE Deutschland B.V. & Co. KG | Nagelsweg 55 | 20097 Hamburg ORACLE Deutschland B.V. & Co. KG Hauptverwaltung: Riesstr. 25, D-80992 München Registergericht: Amtsgericht München, HRA 95603 Komplementärin: ORACLE Deutschland Verwaltung B.V. Rijnzathe 6, 3454PV De Meern, Niederlande Handelsregister der Handelskammer Midden-Niederlande, Nr. 30143697 Geschäftsführer: Jürgen Kunz, Marcel van de Molen, Alexander van der Ven Green Oracle <http://www.oracle.com/commitment> Oracle is committed to developing practices and products that help protect the environment -- -- ----------------------------------------------------------------- Robin La Fontaine, Director, DeltaXML Ltd "Change control for XML" T: +44 1684 592 144 E: robin.lafontaine@deltaxml.com http://www.deltaxml.com Registered in England 02528681 Reg. Office: Monsell House, WR8 0QN, UK
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]