Get all the cell values from excel using EPPlus

Question

I want to use the ExcelWorksheet object from the EPPlus library and retrieve all the cells with values as well as their positions.

At the end of the day, I want a dictionary similar to this.

Dictionary<KeyValuePair<int, int>, object> values.

I am looking for a nice and clean way to extract the cell values into this dictionary for use.

I am quite aware that you can reference the cell values directly as below, but is there a nice clean way of extracting all the data ?

worksheet.Cells[row, column];

Answer 1

There is no native function in EPPlus to convert a the cells collection to a another collection directly but it does implement IEnumerable so you can do it fairly easily with Linq on the worksheet. This works out nicely because cellsonly contains cell references that contain values so you do not even need to specify a row/column limit:

[TestMethod]
public void Cells_To_Dictionary_Test()
{
    //http://stackoverflow.com/questions/32066500/get-all-the-cell-values-from-excel-using-epplus

    //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 existingFile = new FileInfo(@"c:\temp\Grouped.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

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

    //Load a dictionary from a file
    using (var pck = new ExcelPackage(existingFile))
    {
        var worksheet = pck.Workbook.Worksheets["Sheet1"];

        //Cells only contains references to cells with actual data
        var cells = worksheet.Cells;
        var dictionary = cells
            .GroupBy(c => new {c.Start.Row, c.Start.Column})
            .ToDictionary(
                rcg => new KeyValuePair<int, int>(rcg.Key.Row, rcg.Key.Column),
                rcg => cells[rcg.Key.Row, rcg.Key.Column].Value);

        foreach (var kvp in dictionary)
            Console.WriteLine("{{ Row: {0}, Column: {1}, Value: \"{2}\" }}", kvp.Key.Key, kvp.Key.Value, kvp.Value);
    }
}

Which produces this in the output:

{ Row: 1, Column: 1, Value: "Col1" }
{ Row: 1, Column: 2, Value: "Col2" }
{ Row: 1, Column: 3, Value: "Col3" }
{ Row: 2, Column: 1, Value: "0" }
{ Row: 2, Column: 2, Value: "0" }
{ Row: 2, Column: 3, Value: "o103ujwu.hmq" }
{ Row: 3, Column: 1, Value: "1" }
{ Row: 3, Column: 2, Value: "10" }
{ Row: 3, Column: 3, Value: "awyajw2t.lwa" }
{ Row: 4, Column: 1, Value: "2" }
{ Row: 4, Column: 2, Value: "20" }
{ Row: 4, Column: 3, Value: "mjhlre2d.nh2" }
{ Row: 5, Column: 1, Value: "3" }
{ Row: 5, Column: 2, Value: "30" }
{ Row: 5, Column: 3, Value: "dypjvona.m3w" }
{ Row: 6, Column: 1, Value: "4" }
{ Row: 6, Column: 2, Value: "40" }
{ Row: 6, Column: 3, Value: "s5oushbr.sax" }
{ Row: 7, Column: 1, Value: "5" }
{ Row: 7, Column: 2, Value: "50" }
{ Row: 7, Column: 3, Value: "1vqjxxky.2gn" }
{ Row: 8, Column: 1, Value: "6" }
{ Row: 8, Column: 2, Value: "60" }
{ Row: 8, Column: 3, Value: "2dw00hi0.pjp" }
{ Row: 9, Column: 1, Value: "7" }
{ Row: 9, Column: 2, Value: "70" }
{ Row: 9, Column: 3, Value: "qgottyza.vnu" }
{ Row: 10, Column: 1, Value: "8" }
{ Row: 10, Column: 2, Value: "80" }
{ Row: 10, Column: 3, Value: "yx4tmvdp.2pq" }
{ Row: 11, Column: 1, Value: "9" }
{ Row: 11, Column: 2, Value: "90" }
{ Row: 11, Column: 3, Value: "jutk2r0v.1yx" }