'Object reference not set to an instance of an object' while importing excel in dataset using OpenXML

Question

I'm working on windows application where I import multiple excel file one by one in dataset file which may contain multiple sheets. I'm using OpenXML for that, currently, its work finely when there is only one excel file but it gives exception while importing multiple excel I got "Object reference not set to an instance of an object." exception.

here is the method i used

internal DataSet GetDataFromExcelsxFile(string filePath)
{
    try
    {
        KeywordFiles objKeywordFilesController = new KeywordFiles();
        DataSet ds = new DataSet();
        using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, false))
        {

            WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
            IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
            foreach (var item in sheets)
            {
                DataTable dt = new DataTable();
                string relationshipId = item.Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                /*Exception getting on this loop start*/
                foreach (Cell cell in rows.ElementAtOrDefault(0))
                {
                    dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                }

                foreach (Row row in rows) //this will also include your header row...
                {
                    DataRow tempRow = dt.NewRow();

                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                    }

                    dt.Rows.Add(tempRow);
                }
                dt.Rows.RemoveAt(0);
                ds.Tables.Add(dt);

            }
        }

        return ds

    }
    catch (Exception ex)
    {
        Logger.LogInfo(DateTime.Now.ToLongTimeString() + "\n" + "Exception occured to - " + fullName + " \n: " + ex.Message);
        //DialogResult dr = RadMessageBox.Show("Exception occured to - " + fullName + " \n: " + ex.Message, "Error Alert", MessageBoxButtons.OK, RadMessageIcon.Error);

    }

}

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

/* Exception getting on this loop start

     foreach (Cell cell in rows.ElementAtOrDefault(0))
                {
                    dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                }

i tried rows.ElementAt(0) instead of rows.ElementAtOrDefault(0) but still getting same exception
*/

Answer 1

The NullReferenceException is thrown because, in the line of code you already marked, rows.ElementAtOrDefault(0) returns null. This happens if the SheetData instance does not contain any Row instances.

IEnumerable<Row> rows = sheetData.Descendants<Row>();

/*Exception getting on this loop start*/
foreach (Cell cell in rows.ElementAtOrDefault(0))
{
    // Loop body removed
}

Your code should never throw a NullReference exception. In this case, the culprit is the term rows.ElementAtOrDefault(0), which can return null and, thus, should not be used in a foreach loop.

Further, Cell cell in rows.ElementAtOrDefault(0) is not a good practice and can lead to further issues. In this case, a Row instance can contain both Cell and ExtensionList child instances, which can lead to type casting exceptions.