Trying to read excel file with epplus and getting System.NullException error?

Question

Edit

Based on the replies below, the error I am experiencing may or may not be causing my inability to read my excel file. That is, I am not getting data from the line worksheet.Cells[row,col].Value in my for loop given below.

Problem

I am trying to return a DataTable with information from an excel file. Specifically, it is an xlsx file from 2013 excel I believe. Please see the code below:

private DataTable ImportToDataTable(string Path)
        {
            DataTable dt = new DataTable();
            FileInfo fi = new FileInfo(Path);

            if(!fi.Exists)
            {
                throw new Exception("File " + Path + " Does not exist.");
            }

            using (ExcelPackage xlPackage = new ExcelPackage(fi))
            {
                //Get the worksheet in the workbook 
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.First();

                //Obtain the worksheet size 
                ExcelCellAddress startCell = worksheet.Dimension.Start;
                ExcelCellAddress endCell = worksheet.Dimension.End;

                //Create the data column 
                for(int col = startCell.Column; col <= endCell.Column; col++)
                {
                    dt.Columns.Add(col.ToString());
                }


                for(int row = startCell.Row; row <= endCell.Row; row++)
                {
                    DataRow dr = dt.NewRow(); //Create a row
                    int i = 0; 
                    for(int col = startCell.Column; col <= endCell.Column; col++)
                    {
                        dr[i++] = worksheet.Cells[row, col].Value.ToString();
                    }
                    dt.Rows.Add(dr);

                }
            }

            return dt;


        }

Error

This is where things get weird. I can see the proper value in startCell and endCell. However, when I look at worksheet I take a peek under Cells and I see something I don't understand:

worksheet.Cells.Current' threw an exception of type 'System.NullReferenceException

Attempts

• Reformatting my excel with general fields.
• Making sure no field in my excel was empty
• RTFM'ed epplus documentation. Nothing suggestive of this error.
• Looked at EPPlus errors on stackoverflow. My problem is unique.

Honestly, I am having trouble figuring out what this error is really saying? Is something wrong with my format? Is something wrong with epplus? I have read on here people had no problems with 2013 xlsx with eeplus and I am only trying to parse the excel file by row. If someone could help me shed light on what this error means and how to rectify it. I would be most grateful. I've spent quite a long time trying to figure this out.

Answer 1

When we give:

dr[i++] = worksheet.Cells[row, col].Value.ToString();

it search for value at that column, if the column is empty, it gives Null reference error.

Try instead:

dr[i++] = worksheet.Cells[row, col].Text;

Hope this will help