Trying to read an excel file with epplus and get a System.NullException error?

Question

Edit

Based on the answers below, the error I am experiencing may or may not lead to my inability to read my excel file. That is, I am not getting data from a string worksheet.Cells[row,col].Valuein my for loop below.

Problem

I am trying to return a DataTable with information from an excel file. In particular, this is an xlsx file from 2013, I believe. See 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

Everything is strange here. I see the correct meaning in startCelland endCell. However, when I look worksheet, I look under Cells, and I see something that I do not understand:

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

Attempts

• Reformatting my excel using shared fields.
• Make sure there is no field in my excel.
• RTFM'ed epplus documentation. Nothing suggests this error.
• EPPlus stackoverflow. .

, , ? - ? - epplus? , 2013 xlsx eeplus, excel . - , , . . , .

Answer 1

@Thorians, . current, - :

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

, local collection cells. current null, `workheet.cells.current '

ForEach, CLR .

---

: . , , excel:

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

:

{Rows: 11, Columns: 3}

Answer 2

.

, , .

:

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

When we give:

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

it searches for a value in this column; if the column is empty, it gives a Null reference error.

Try instead:

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

Hope this helps