reading Excel with ClosedXML

Question

What would be the most efficient way to read an entire Excel file using ClosedXML and returning List<List<object>> ?

This somehow doesn't give me data. I get empty lists.

        var wb = new XLWorkbook(finalFilePath);
        var ws = wb.Worksheets.First();
        var range = ws.RangeUsed();
        var colCount = range.ColumnCount();
        var rowCount = range.RowCount();

        var i = 1;
        var j = 1;
        List<List<object>> data = new List<List<object>>();
        while (i < rowCount + 1)
        {
            List<object> row = new List<object>();
            while (j < colCount + 1)
            {
                row.Add(ws.Cell(i, j).Value);
                j++;
            }
            data.Add(row);
            i++;
        }

Figure out why the lists are empty first. For example, are the column and row counts 0 or are the returned cell values empty? — Francois Botha, Nov 29 '16 at 04:52
@FrancoisBotha no the used range of the document that I was looking at was A1:B19 or something like that. I am not sure but perhaps the fact that first few rows in that range were empty cells stopped my code short in its tracks? — konrad, Nov 29 '16 at 14:08

score 4 · Accepted Answer · answered Nov 29 '16 at 15:19

This gets the job done:

Dictionary<Tuple<int, int>, object> data = new Dictionary<Tuple<int, int>, object>();
using (XLWorkbook wb = new XLWorkbook(filePath))
{
    var ws = wb.Worksheets.First();
    var range = ws.RangeUsed();

    for (int i = 1; i < range.RowCount() + 1; i++)
    {
        for (int j = 1; j < range.ColumnCount() + 1; j++)
        {
            data.Add(new Tuple<int, int>(i,j), ws.Cell(i,j).Value);
        }
    }
}

Mark it as the answer. – Francois Botha Dec 11 '16 at 07:58 — Francois Botha, Dec 11 '16 at 07:58

reading Excel with ClosedXML

1 Answers1