'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
*/

..You could tell us where you encounter the exception. I'll not start guessing.. — kara
– kara, Commented Jul 19, 2019 at 6:43

Thomas Barnekow · Accepted Answer · 2019-11-29 22:48:21Z

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.

answered Nov 29, 2019 at 22:48

Thomas Barnekow

2,3991 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Barnekow Over a year ago

@T.Giri, could you please mark this as the answer as well?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related