Thursday, July 21, 2011

Reading XLSX format file from C#

The best guide for reading excel file without Excel instance exist here.

Pay attentions! In the final example in the loop ForEach missed validation of  not string values and NULL value cells, threfore change it to:


foreach (XElement cell in cells)
{
    if (cell.IsEmpty)
    {
        continue;
    }
    string cellPosition = cell.Attribute("r").Value;
    int index = IndexOfNumber(cellPosition);
    string column = cellPosition.Substring(0, index);
    int row = Convert.ToInt32(cellPosition.Substring(index, cellPosition.Length - index));
    if (cell.HasElements)
    {
        if (cell.Attribute("t") != null && cell.Attribute("t").Value == "s")
        {
            // Shared value
            int valueIndex = Convert.ToInt32(cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value);
            parsedCells.Add(new Cell(column, row, sharedStrings[valueIndex]));
        }
        else
        {
            string value = cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value;
            parsedCells.Add(new Cell(column, row, value));
        }
    }
    else
    {
        parsedCells.Add(new Cell(column, row, ""));
    }
}

No comments: