none
Formatting text and columns in Open XML Excel RRS feed

  • Question

  • I'm brand new to Open XML for Excel and am finding that I spend most of my time spinning my wheels with it (Has anyone else had this experience?)

    I'm trying to convert some code that used to use interop Excel objects and will now use Open XML. I am running into some very serious roadblocks.

    Most notably:
    I can create a new Excel document with one sheet named "Sheet 1".I can populate it with data from a DataSet . What I need to do is:
    1. Format the columns (width, data type, font)
    2. Utilizing NumberingFormats in StyleSheets

    Format the columns. I haven't found any examples of formatting existing columns, only adding new ones. If I add the new columns and save the document, it gets into a corrupted state)

    Utilizing NumberingFormats.  I tried accessing the pre-defined formats (ID 0 - 49) without success.  I found an example where someone added custome ones starting at ID = 200.  I tried that and still it won't work.  The CellFormat is being utilized because the text alignment is being recognized but not the NumberFormat.

    If anybody has a good (and I stress GOOD) tutorial on Open XML or can share their experiences with me, that would be greatly appreciated.

    Code snippets:

    1. Date NumberingFormat: private NumberingFormat dateFmt     = new NumberingFormat () { NumberFormatId = 200, FormatCode = "mmm dd, yyyy" };

    2. Adding to StyleSheet:  stylesPart.Stylesheet.NumberingFormats.Append ( dateFmt );

    3. Date formatting (index 9):
    new CellFormat { Alignment = new Alignment () { Horizontal = HorizontalAlignmentValues.Right,  Vertical = VerticalAlignmentValues.Center },
    FontId = 0, FillId = 0, BorderId = 0, NumberFormatId = 200, ApplyAlignment = true, ApplyNumberFormat = true },

    4. numFmts via Productivity Tool:

    namespace GeneratedCode
    {
        public class GeneratedClass
        {
            // Creates an NumberingFormats instance and adds its children.
            public NumberingFormats GenerateNumberingFormats()
            {
                NumberingFormats numberingFormats1 = new NumberingFormats(){ Count = (UInt32Value)3U };
                NumberingFormat numberingFormat1 = new NumberingFormat(){ NumberFormatId = (UInt32Value)200U, FormatCode = "mmm dd, yyyy" };
                NumberingFormat numberingFormat2 = new NumberingFormat(){ NumberFormatId = (UInt32Value)201U, FormatCode = "$##,##0.00" };
                NumberingFormat numberingFormat3 = new NumberingFormat(){ NumberFormatId = (UInt32Value)202U, FormatCode = "##,##0" };

                numberingFormats1.Append(numberingFormat1);
                numberingFormats1.Append(numberingFormat2);
                numberingFormats1.Append(numberingFormat3);
                return numberingFormats1;
            }
        }
    }

    5. Generated code for the Date Format:

    namespace GeneratedCode
    {
        public class GeneratedClass
        {
            // Creates an NumberingFormat instance and adds its children.
            public NumberingFormat GenerateNumberingFormat()
            {
                NumberingFormat numberingFormat1 = new NumberingFormat(){ NumberFormatId = (UInt32Value)200U, FormatCode = "mmm dd, yyyy" };
                return numberingFormat1;
            }
        }
    }

    6. Getting the particular cell:

            private Cell CellInfo ( string headerColumn, int index, UInt32Value styleIndex )
                {
                Cell c          = new Cell () { StyleIndex = styleIndex };

                // Attempts at making the DataType property based on the styleIndex.

                //if ( styleIndex < 9 )
                //    c.DataType     = CellValues.InlineString;
                //else
                //    {
                //    if ( styleIndex == 9 )
                //        c.DataType = CellValues.Date;
                //    else
                //        c.DataType = CellValues.Number;
                //    }

                c.DataType      = CellValues.InlineString;
                c.CellReference = headerColumn + index;

                return c;
                }

    Jerry Scannell

    Thursday, April 7, 2016 12:32 PM

Answers

  • >>>If anybody has a good (and I stress GOOD) tutorial on Open XML or can share their experiences with me, that would be greatly appreciated.

    According to your description, I suggest that you could start with Open XML SDK 2.5 for Office and refer to Eric White's Open XML Screen-Cast Series 

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:18 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:23 PM
    Friday, April 8, 2016 9:15 AM