How to read blank cell column value of excel using OpenXML in C#

Question

Here in my Execl Sheet there are some blank value column cell, so When I use This code then in get Error "Object reference not set to an instance of an object".

foreach (Row row in rows)
{
   DataRow dataRow = dataTable.NewRow();
   for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
   {
        dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
   }

   dataTable.Rows.Add(dataRow);
}

private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;

    string value = cell.CellValue.InnerXml;

    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
    }
    else
    {
        return value;
    }
}

On which line you got the error ?

Maxime Porté
– Maxime Porté

2015-07-30 15:09:18 +00:00
Commented Jul 30, 2015 at 15:09 — Maxime Porté
– Maxime Porté, Commented Jul 30, 2015 at 15:09
string value = cell.CellValue.InnerXml;

Amol Khairnar
– Amol Khairnar

2015-07-31 04:35:56 +00:00
Commented Jul 31, 2015 at 4:35 — Amol Khairnar
– Amol Khairnar, Commented Jul 31, 2015 at 4:35

Maxime Porté · Accepted Answer · 2015-07-31 07:01:11Z

The "CellValue" don't necessarily exist. In your case it is "null", so you have your error. To read your blank cell :

If you don't want format the result depending what the cell contain, try

private static string GetCellValue(Cell cell)
{
    return cell.InnerText;
}

if you want to format your cell before returning the value of it

private static string GetCellValue(SpreadsheetDocument doc, Cell cell)
{
    // if no dataType, return the value of the innerText of the cell
    if (cell.DataType == null) return cell.InnerText;

    // depending type of the cell
    switch (cell.DataType.Value)
    {
        // string => search for CellValue
        case CellValues.String:
            return cell.CellValue != null ? cell.CellValue.Text : string.Empty;

        // inlineString => search of InlineString
        case CellValues.InlineString:
            return cell.InlineString != null ? cell.InlineString.Text.Text : string.Empty;

        // sharedString => search for the SharedString
        case CellValues.SharedString:
            // is sharedPart exist ?
            if (doc.WorkbookPart.SharedStringTablePart == null) doc.WorkbookPart.SharedStringTablePart = new SharedStringTablePart();
            // is the text exist ?
            foreach (SharedStringItem item in doc.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>())
            {
                // the text exist, return it from SharedStringTable
                if (item.InnerText == cell.InnerText) return cell.InnerText;
            }
            // no text in sharedStringTable, create it and return it
            doc.WorkbookPart.SharedStringTablePart.SharedStringTable.Append(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(cell.InnerText)));
            doc.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
            return cell.InnerText;

        // default case : bool / number / date
        // return the value of the cell in plain text
        // you can parse types depending your needs
        default:
            return cell.InnerText;
    }
}

Two usefull documentations:

About cell : https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cell(v=office.14).aspx
About SharedString :
https://msdn.microsoft.com/en-us/library/office/gg278314.aspx

Collectives™ on Stack Overflow

How to read blank cell column value of excel using OpenXML in C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related