Tuesday, December 16, 2008

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;
}

1 comment:

a said...

Thank you, ive looked for that a lot. Works nice!