none
using the open xml format sdk 2.0 version... RRS feed

  • Question

  • Here's a quickie on navigating to a cell in an Excel 2007 XML document, using the Open XML Format SDK verison 2.0 released recently...

    SpreadsheetDocument ssdocCellStyleTest = SpreadsheetDocument.Open("C:\\BorderTest.xlsx", false);
    Workbook workbook = ssdocCellStyleTest.WorkbookPart.Workbook;
    WorksheetPart sheetpart;
    foreach (WorksheetPart worksheetpart in workbook.WorkbookPart.WorksheetParts)
    {
    Worksheet worksheet = worksheetpart.Worksheet;
    foreach (SheetData sheetData in worksheet.Elements())
    {
    foreach (Row row in sheetData.Elements())
    {
    MessageBox.Show("row index is " + row.RowIndex);
    foreach (Cell cell in row.Elements())
    {
    MessageBox.Show("cell index " + cell.CellReference);
    }

    }
    }
    Stylesheet styleSheet = workbook.WorkbookPart.WorkbookStylesPart.Stylesheet;
    styleSheet.Fonts.Elements();
    foreach (CellFormat cellFormat in styleSheet.CellFormats.Elements())
    {
    MessageBox.Show("Cell Style ID " + cellFormat.FormatId);
    MessageBox.Show("Cell has border " + cellFormat.ApplyBorder);
    MessageBox.Show("Cell has fill " + cellFormat.FillId);
    DocumentFormat.OpenXml.Spreadsheet.Fonts oFonts = styleSheet.Fonts;
    DocumentFormat.OpenXml.Spreadsheet.Font oFont = (DocumentFormat.OpenXml.Spreadsheet.Font)oFonts.ChildElements[int.Parse(cellFormat.FontId.ToString())];
    foreach (FontName oFontName in oFont.Elements())
    {
    foreach (OpenXmlAttribute attrib in oFontName.GetAttributes())
    {
    MessageBox.Show("Font attrib " + attrib.LocalName + " value " + attrib.Value);
    }
    }
    }

     

    So ... as you see every element of the SpreadsheetML language... is now an OpenXmlElement class, or either an OpenXmlLeafElement class... you are familiar with WorkbookPart, ThemePart etc.. now each part comes with an object like workbookPart has Workbook object, ThemePart has Theme object, StyleSheetPart has a stylesheet object.. which is a wrapper of the data in the xml stream of its corresponding part.
    ITs stilll not that robust... as you can see... given the Font id and the stylesheet.Fonts collection object retrieving the font attributes is still difficult, because even b, u, i, name elements have classes Bold, Italic, Underline, FontName etc. (NOTE: Dont confuse this with System.Drawing objects, they are part of the DocumentFormat.OpenXml.Spreadsheet namespace)

    However the theme object is a good wrapper. For example theme.themeElements()[0].ColorScheme.AccentColor.RGBColorInHex gives the rgb value of the theme color instantly.. we dont have navigate as we did in Fonts....

    So its an inconsistant new SDK... there is no uniformity.... because DocumentFormat.OpenXml.Spreadsheet.Font is different from DocumentFormat.OpenXML.WordProcessing.Font....

    Have to explore more on VML drawing extraction usig this new SDK...

    Happy excelling!

    Regards,
    Kalpana

    Tuesday, September 9, 2008 9:00 PM

All replies

  • Kalpana,

     

    Thank you for posting this, it is very helpful.

     

    However when I tried your code, I received the following error:

    Unable to cast object of type 'DocumentFormat.OpenXml.Spreadsheet.SheetDimension' to type 'DocumentFormat.OpenXml.Spreadsheet.SheetData'.

     

    I got it starting on the second loop.

     

    Worksheet worksheet = worksheetpart.Worksheet;

    foreach (SheetData sheetData in worksheet.Elements())

     

    Any ideas what I am doing wrong?

     

    Thank you.

     

    -Gumbatman

     

     

     

     

     

     

     

    Sunday, September 14, 2008 12:23 AM
  • Hi Gumbatman,

     

    I have noticed only now... 

     

    it should have been sheetData in worksheet.Elements<SheetData>().... apparently, the forum message has truncated all the content between the < and the > sign, because I didnt check that post contains code, while posting. Sorry :-)

     

     

    The corresponding class should be mentioned while making a call to the .Elements() method, to work with them. All such classes are children of the OpenXmlElement mainclass, so we have to mention what kind of OpenXmlElement object we want to while calling .Elements()

     

     

    I have made the corrections in the following piece. The following is only an approximation. There may be other methods which can achieve the same result.. this is my first trial with the SDk... am still learning.

     

    SpreadsheetDocument ssdocCellStyleTest = SpreadsheetDocument.Open("C:\\BorderTest.xlsx", false);
    Workbook workbook = ssdocCellStyleTest.WorkbookPart.Workbook;
    WorksheetPart sheetpart;
    foreach (WorksheetPart worksheetpart in workbook.WorkbookPart.WorksheetParts)
    {
    Worksheet worksheet = worksheetpart.Worksheet;
    foreach (SheetData sheetData in worksheet.Elements<SheetData>())
    {
    foreach (Row row in sheetData.Elements<Row>())
    {
    MessageBox.Show("row index is " + row.RowIndex);
    foreach (Cell cell in row.Elements<Cell>())
    {
    MessageBox.Show("cell index " + cell.CellReference);
    }

    }
    }
    Stylesheet styleSheet = workbook.WorkbookPart.WorkbookStylesPart.Stylesheet;
    foreach (CellFormat cellFormat in styleSheet.CellFormats.Elements<CellFormat>())
    {
    MessageBox.Show("Cell Style ID " + cellFormat.FormatId);
    MessageBox.Show("Cell has border " + cellFormat.ApplyBorder);
    MessageBox.Show("Cell has fill " + cellFormat.FillId);
    DocumentFormat.OpenXml.Spreadsheet.Fonts oFonts = styleSheet.Fonts;
    DocumentFormat.OpenXml.Spreadsheet.Font oFont = (DocumentFormat.OpenXml.Spreadsheet.Font)oFonts.ChildElements[int.Parse(cellFormat.FontId.ToString())];
    foreach (FontName oFontName in oFont.Elements<FontName>())
    {
    foreach (OpenXmlAttribute attrib in oFontName.GetAttributes())
    {
    MessageBox.Show("Font attrib " + attrib.LocalName + " value " + attrib.Value);
    }
    }
    }

     

     

     

    -kalpana

    Sunday, September 14, 2008 1:50 AM
  • Hi Kalpana,

     

    Thank you so much for posting the solution (to your solution ). I really appreciate it. I've been trying to get through this new SDK and having a great deal of trouble.

     

    I am so glad there are people, like you, trying it out and posting answers to common problems or just showing how to use it properly.

     

    BTW, have you been able to use the Create method? Just for kicks, I changed the first line of your code to the following and am getting error

    Code Snippet

    SpreadsheetDocument cr = SpreadsheetDocument.Create ("c:\\BorderTestNew.xlsx",SpreadsheetDocumentType.Workbook);

     

     

    The error I am getting is:

    Error 1 The type 'System.IO.Packaging.Package' is defined in an assembly that is not referenced. You must add a reference to assembly 'WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.

     

    I tried adding System.IO, but I am still getting the error. Any ideas what I am doing wrong here?

     

    Again, thank you so much for paving the way for the rest of us.

     

    -Gumbatman

    Sunday, September 14, 2008 10:31 PM
  • You are welcome !

     

    You have to add a reference to the WIndowsbase.dll

     

    You can find it in the folder C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0

     

     

    after adding this reference to your project, add "using System.IO.Packaging;" to the import list.

     

     

    Regards,

    Kalpana

    • Proposed as answer by Roshe Thursday, April 19, 2012 2:11 AM
    • Unproposed as answer by Roshe Thursday, April 19, 2012 2:11 AM
    Sunday, September 14, 2008 11:16 PM
  • Hi, Kalpana

    How to get style of cell? I need to find cells with some color attributes. I know Cell.StyleIndex, but can`t find and compare this id with existing Fill in document.

    Thank you.
    Tuesday, April 7, 2009 1:25 PM
  • the attribute "style index" of element "cell" refernces the style information of one cell, which is stroed in styles part.
    in styles part, element "fills" define the background color, foreground color applied to the cell. it looks like this:
    <fills count="4">
            <fill>
                <patternFill patternType="none"/>
            </fill>
            <fill>
                <patternFill patternType="gray125"/>
            </fill>
            <fill>
                <patternFill patternType="solid">
                    <fgColor rgb="FFFF0000"/>
                    <bgColor indexed="64"/>
                </patternFill>
            </fill>
            <fill>
                <patternFill patternType="solid">
                    <fgColor rgb="FFFFFF00"/>
                    <bgColor indexed="64"/>
                </patternFill>
            </fill>
        </fills>

    you can read the RGB value in element "fill", which is you want as i understand.

    Thursday, April 9, 2009 4:34 AM