This project has moved and is read-only. For the latest updates, please go here.

Excel to Datatable

Dec 18, 2013 at 8:01 PM
I was wondering if there is a way to import Excel to a Datatable without going row by row (example below)? Thanks in advance!

public static DataTable getDataTableFromExcel(string path)
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
    using (var stream = File.OpenRead(path))
    {
        pck.Load(stream);
    }
    var ws = pck.Workbook.Worksheets.First();  
    DataTable tbl = new DataTable();
    bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
    {
        tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
    }
    var startRow = hasHeader ? 2 : 1;
    for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
    {
        var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
        var row = tbl.NewRow();
        foreach (var cell in wsRow)
        {
            row[cell.Start.Column - 1] = cell.Text;
        }
        tbl.Rows.Add(row);
    }
    return tbl;
}
}