Importing excel file into array in c#

Question

I'm having trouble figuring out how to import an excel file into an multidimensional array in c#. I'm not looking to do anything fancy, I just want simple excel first worksheet into an array in c#, and that's about it.

Thanks for all your help!

Answer 1

You would be best to bring it into a datatable, in this code I am using closedXML which is available via nuget

public static DataTable ImportSheet(string fileName)
    {
        var datatable = new DataTable();
        var workbook = new XLWorkbook(fileName);
        var xlWorksheet = workbook.Worksheet(1);
        var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed());

        var col = range.ColumnCount();
        var row = range.RowCount();

        //if a datatable already exists, clear the existing table 
        datatable.Clear();
        for (var i = 1; i <= col; i++)
        {
            var column = xlWorksheet.Cell(1, i);
            datatable.Columns.Add(column.Value.ToString());
        }

        var firstHeadRow = 0;
        foreach (var item in range.Rows())
        {
            if (firstHeadRow != 0)
            {
                var array = new object[col];
                for (var y = 1; y <= col; y++)
                {
                    array[y - 1] = item.Cell(y).Value;
                }

                datatable.Rows.Add(array);
            }
            firstHeadRow++;
        }
        return datatable;
    }

This is working code, so all you'll need to do it copy and paste it, and call it using this code too

DataTable _dt = new DataTable();
_dt = ImportSheet(fileName);