Open XML and Office Services

Brian Jones has a new post on his blog, http://blogs.msdn.com/brian_jones/archive/2009/11/03/open-xml-and-office-services.aspx ,showing a great scenario for using Open XML and the new Office Services. A big highlight for me with these new tools is the ability to create PDFs at the end of the document generation process without including the Word client and performing Office automation. The scenario highlighted is just the tip of the iceberg of the power being brought to the enterprise.

SpreadsheetML - Retrieving SharedString values

Retrieving cell values in Excel spreadsheets is very straight forward, especially when string values are stored inline. Other times the string values are stored in the SharedStringTable collection. If the CellType is a SharedString. then the cell value references the index in the SharedStringTable. Below is a quick snippet that will return the SharedString value if that is the case, otherwise the inline string value.

protected string GetCellValue(WorkbookPart wbPart, string targetCell)
        {
            string result = null;
            WorksheetPart wsPart = wbPart.WorksheetParts.First();
            SheetData sheetData = wsPart.Worksheet.GetFirstChild();
            SharedStringTablePart sstPart = wbPart.GetPartsOfType().First();
            SharedStringTable ssTable = sstPart.SharedStringTable;

            try
            {
                Cell cell = wsPart.Worksheet.Descendants()
                        .Where(c => targetCell.Equals(c.CellReference))
                        .First();

                if (cell.DataType != null && cell.DataType == CellValues.SharedString)
                {
                    result = ssTable.ChildElements[Convert.ToInt32(cell.CellValue.Text)].InnerText;
                }
                else
                {
                    if (cell.CellValue != null)
                    {
                        result = cell.CellValue.Text;
                    }
                }
            }
            catch (Exception)
            {

            }

            return result;
        }