How do I access the individual data from an object when reading an Excel file using EPPlus?

Question

I'm trying to read a row from an excel file in c# and store in the object using EPPLUS.

Now when I print the content of the object, it displays all the content of the row as single string.

How do I limit my access to single cell content? i.e., individual cell content so that I can use those particular fields as per my requirement.

var existingFile = new FileInfo("D:\\Fields.xlsx"); 
using (var package = new ExcelPackage(existingFile)) 
{
    ExcelWorkbook workBook = package.Workbook;
    if (workBook != null) 
    { 
        if (workBook.Worksheets.Count > 0) 
        { 
            ExcelWorksheet currentWorksheet = workBook.Worksheets.First(); 
            var lastrow = currentWorksheet.Dimension.End.Row; 
            var lastcol = currentWorksheet.Dimension.End.Column; 
            for (int i = 1; i <= lastrow; i++) 
            {
                for (int j = 1; j <= lastcol; j++) 
                {
                    object asd = new object(); 
                    asd = currentWorksheet.Cells[i, j].Value; 
                    Console.WriteLine(asd);
                }
            }
        }
     }
}

Answer 1

Your code should work fine. If you are seeing the entire row of the excel file printed as a single string then my guess would be it is a problem with the content of the excel file itself in which case you would have to parse the string somehow. Post a screen shot or your, even better, the excel file itself online somewhere so we can take a look.

In the meantime, check out this code which creates its own excel file first and then reopens and saves using your code:

public void ConsoleWriteTest()
{
    //Create some data
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var pck = new ExcelPackage(existingFile))
    {
        var workbook = pck.Workbook;
        var worksheet = workbook.Worksheets.Add("newsheet");

        #region Data

        //The data
        worksheet.Cells["A1"].Value = "Col1";
        worksheet.Cells["A2"].Value = "sdf";
        worksheet.Cells["A3"].Value = "ghgh";
        worksheet.Cells["A4"].Value = "sdf";
        worksheet.Cells["A5"].Value = "wer";

        worksheet.Cells["B1"].Value = "Col2";
        worksheet.Cells["B2"].Value = "Group B";
        worksheet.Cells["B3"].Value = "Group A";
        worksheet.Cells["B4"].Value = "Group C";
        worksheet.Cells["B5"].Value = "Group A";

        worksheet.Cells["C1"].Value = "Col3";
        worksheet.Cells["C2"].Value = 634.5;
        worksheet.Cells["C3"].Value = 274.5;
        worksheet.Cells["C4"].Value = 453.5;
        worksheet.Cells["C5"].Value = 634.5;

        worksheet.Cells["D1"].Value = "Col4";
        worksheet.Cells["D2"].Value = 996440;
        worksheet.Cells["D3"].Value = 185780;
        worksheet.Cells["D4"].Value = 686468;
        worksheet.Cells["D5"].Value = 996440;

        #endregion

        pck.Save();
    }

    //Reopen the file
    using (var package = new ExcelPackage(existingFile))
    {
        ExcelWorkbook workBook = package.Workbook;
        if (workBook != null)
        {
            if (workBook.Worksheets.Count > 0)
            {
                ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                var lastrow = currentWorksheet.Dimension.End.Row;
                var lastcol = currentWorksheet.Dimension.End.Column;
                for (int i = 1; i <= lastrow; i++)
                {
                    for (int j = 1; j <= lastcol; j++)
                    {
                        object asd = new object();
                        asd = currentWorksheet.Cells[i, j].Value;
                        Console.WriteLine(asd);
                    }
                }
            }
        }
    }
}

---

EDIT 1: Row or column as a collection

//Get entire rows or columns as collection and then print by casting
using (var package = new ExcelPackage(existingFile))
{
    ExcelWorkbook workBook = package.Workbook;
    if (workBook != null)
    {
        if (workBook.Worksheets.Count <= 0) 
            return;

        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
        var lastrow = currentWorksheet.Dimension.End.Row;
        var lastcol = currentWorksheet.Dimension.End.Column;

        //get the row of column headers which are strings
        var asdrange = currentWorksheet.Cells[1, 1, 1, lastcol];
        Console.WriteLine("As cell objects");
        foreach (var cell in asdrange)
            Console.WriteLine(cell.Value);

        object asd = new object();
        asd = currentWorksheet.Cells[1, 1, 1, lastcol].Value;

        object[,] cellObjects = (object[,])asd;
        List<string> stringList = cellObjects.Cast<string>().ToList();

        Console.WriteLine(Environment.NewLine + "As casted to a List");
        Console.WriteLine(stringList[0]);
        Console.WriteLine(stringList[1]);
        Console.WriteLine(stringList[2]);
        Console.WriteLine(stringList[3]);

        //get the second row which is a mix of strings and double
        asdrange = currentWorksheet.Cells[2, 1, 2, lastcol];
        Console.WriteLine(Environment.NewLine + "As cell objects");
        foreach (var cell in asdrange)
            Console.WriteLine(cell.Value); 

        asd = currentWorksheet.Cells[2, 1, 2, lastcol].Value;
        cellObjects = (object[,])asd;
        List<object> objectList = cellObjects.Cast<object>().ToList();

        Console.WriteLine(Environment.NewLine + "As casted to a List");
        Console.WriteLine(objectList[0]);
        Console.WriteLine(objectList[1]);
        Console.WriteLine(objectList[2]);
        Console.WriteLine(objectList[3]);

        //Get Col3 which are doubles
        asdrange = currentWorksheet.Cells[2, 3, lastrow, 3];
        Console.WriteLine(Environment.NewLine + "As cell objects");
        foreach (var cell in asdrange)
            Console.WriteLine(cell.Value); 

        asd = currentWorksheet.Cells[2, 3, lastrow, 3].Value;
        cellObjects = (object[,])asd;
        List<double> doubleList = cellObjects.Cast<double>().ToList();

        Console.WriteLine(Environment.NewLine + "As casted to a List");
        Console.WriteLine(doubleList[0]);
        Console.WriteLine(doubleList[1]);
        Console.WriteLine(doubleList[2]);
        Console.WriteLine(doubleList[3]);
        Console.WriteLine(doubleList[4]);
        Console.WriteLine(doubleList[5]);
        Console.WriteLine(doubleList[6]);
        Console.WriteLine(doubleList[7]);
        Console.WriteLine(doubleList[8]);
    }
}