The way is long ...(Today, I realized that there is no way! :() RRS feed

  • General discussion

  • When I insert a new row before one exist row, I have to renumber all rows' index behind the new row, and renumber all the cells' reference within these rows. More trouble is that I have to modify all relation part's elements for these rows and cells, such as DefinedNames, MergeCells, it is terrible!
    I am about to give up building office document by using the OOXML SDK, who can point me a right way?
    • Edited by dotAge Thursday, October 23, 2008 6:25 AM
    Thursday, October 16, 2008 9:54 AM

All replies

  • I have almost completed the code for MergeCells, DefinedNames address translation when we insert new rows, but, how about Formulas ?!!! I am fully awake to the difficuties of the mission, and it is an impossible mission! For example, how can we find out the red part (which are real address references) except blue part in the formula like this:

    LOG10(G10+$G10+RANGE_NAME_G10) & "This is a string that contains excel address G10, $G$10"

    Is there any regular express could handle all cases of the formula?
    Is it possible to implement a complie system for the formula?

    I think this is Microsoft's job, and the Open XML SDK should supply some methods about these operations for us.

    Sorry for poor English.

    Thursday, October 23, 2008 6:23 AM
  • Sorry that you feel that way, the problem is you are creating cells with out adding references, and rows without setting the RowIndex member.  If you do start using indices you have to place each element (Cell and Row) in an ordinal fashion Cells (A1,B1,C1), Rows (1,2,3).

    It is a pain, but you have to remember when you construct the spreadsheet from a stream or programatically, you normally wouldn't jump around.  It would normally occur in a sequential fashion.  This is the intention of the OpenXml Document schema, I doubt that anyone would seriously try to start rewriting the entire Excel Program. 
    However, if you are really interested in pursuing this you would have to set up methods to place each element in an ordinal fashion.  The snippet below does such a task, however, you will have to learn to use it.  I .NET 3.5 to Insert a row.  I call it as follows:
            public static void OrdinalInsert(DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData, 
              DocumentFormat.OpenXml.Spreadsheet.Row insertRow)  
                OrdinalInsert<SheetData, Row>(sheetData, insertRow,  
                    r => r.RowIndex < insertRow.RowIndex,  
                    r => r.RowIndex > insertRow.RowIndex);  

    This calls the method below which is in an abstract base class.

            /// <summary> 
            /// Performs an ordered (Ordinal) insert based on the predicates that are provided to insert elements into collections  
            /// in an indexed fashion.  It is the consumer's responsibility to accurately describe the less than predicate (lessThanPredicate)  
            /// and the greater than predicate (greaterThanPredicate).  
            /// </summary> 
            /// <typeparam name="ContainerType">OpenXmlCompositeElement</typeparam> 
            /// <typeparam name="ElementType">OpenXmlElement</typeparam> 
            /// <param name="container">container that will receive the OpenXmlElement</param> 
            /// <param name="element">the OpenXmlElement to insert into the container</param> 
            /// <param name="lessThanPredicate">the less than test to perform on the container elements</param> 
            /// <param name="greaterThanPredicate">the greater than test to perform on the container elements</param> 
            protected static void OrdinalInsert<ContainerType, ElementType>(ContainerType container,  
                ElementType element,  
                Func<ElementType, bool> lessThanPredicate,  
                Func<ElementType, bool> greaterThanPredicate)  
                where ContainerType : DocumentFormat.OpenXml.OpenXmlCompositeElement, System.Collections.IEnumerable, ICloneable, IEnumerable<DocumentFormat.OpenXml.OpenXmlElement> 
                where ElementType : DocumentFormat.OpenXml.OpenXmlElement  
                IEnumerable<ElementType> lessThanElements = container.Elements<ElementType>().Where(arg => lessThanPredicate(arg));  
                IEnumerable<ElementType> greaterThanElements = container.Elements<ElementType>().Where(arg => greaterThanPredicate(arg));  
                if (lessThanElements.Count() > 0)  
                    ElementType refElement = lessThanElements.Last();  
                    container.InsertAfter<ElementType>(element, refElement);  
                else if (greaterThanElements.Count() > 0)  
                    ElementType refElement = greaterThanElements.First();  
                    container.InsertBefore<ElementType>(element, refElement);  
    Sunday, November 30, 2008 11:11 PM