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

Answer 2

Current is the current range when enumerating.

there is nothing wrong with this throwing an exception in debugging inspection when it is not being used within an enumerating scope.

code sample:

var range = ws.Cells[1,1,1,100];
foreach (var cell in range)
{
    var a =  range.Current.Value;  // a is same as b
    var b = cell.Value;         
}

Answer 3

Like @Thorians said, current is really meant to use when you enumerating the cells. If you want to use it in purest form and actually be able to call current then you would need something like this:

using (var pck = new ExcelPackage(existingFile))
{
    var worksheet = pck.Workbook.Worksheets.First();

    //this is important to hold onto the range reference
    var cells = worksheet.Cells;

    //this is important to start the cellEnum object (the Enumerator)
    cells.Reset();

    //Can now loop the enumerator
    while (cells.MoveNext())
    {
        //Current can now be used thanks to MoveNext
        Console.WriteLine("Cell [{0}, {1}] = {2}"
            , cells.Current.Start.Row
            , cells.Current.Start.Column
            , cells.Current.Value);
    }
}

Note that you have to create a kind of local collection cells for this to work properly. Otherwise Current will be null if you tried `worksheet.cells.current'

But it would be simpler to use a ForEach and have the CLR do the work for you.

---

UPDATE: Based on comments. Your code should work fine as is, could it be your excel file:

[TestMethod]
public void Current_Cell_Test()
{
    //http://stackoverflow.com/questions/32516676/trying-to-read-excel-file-with-epplus-and-getting-system-nullexception-error

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof (int)), new DataColumn("Col2", typeof (int)),new DataColumn("Col3", typeof (object)) });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow(); row[0] = i; row[1] = i * 10; row[2] = Path.GetRandomFileName(); datatable.Rows.Add(row);
    }

    //Create a test file
    var fi = new FileInfo(@"c:\temp\test1.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var pck = new ExcelPackage(fi))
    {
        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromDataTable(datatable, true);
        pck.Save();
    }

    var dt = new DataTable();

    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);

        }
    }

    Console.Write("{{dt Rows: {0} Columns: {1}}}", dt.Rows.Count, dt.Columns.Count);
}

Give this in the output:

{Rows: 11, Columns: 3}

Answer 4

I am also getting same issue while reading excel file and none of the solution provided worked for me. Here is working code:

public void readXLS(string FilePath)
{
    FileInfo existingFile = new FileInfo(FilePath);
    using (ExcelPackage package = new ExcelPackage(existingFile))
    {
        //get the first worksheet in the workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int colCount = worksheet.Dimension.End.Column;  //get Column Count
        int rowCount = worksheet.Dimension.End.Row;     //get row count
        for (int row = 1; row <= rowCount; row++)
        {
            for (int col = 1; col <= colCount; col++)
            {
                Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value.ToString().Trim());
            }
        }
    }
}