Blog

EPPlus Excel worksheet to .Net DataTable

Posted Friday, November 6th 2015 in Programming - Permalink

Here is a simple function that I wrote that will convert a worksheet from an EPPlus Excel object to a .Net DataTable. You can specify if the table includes columns name in the first row or not.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using OfficeOpenXml;
using System.Data;

namespace jShip.Tools
{
    public class ExcelHelpers
    {
        public static DataTable ExcelToDataTable(ExcelWorksheet pWorksheet, bool pHasHeaderRow)
        {
            DataTable lTable = new DataTable();
 
            lTable.Columns.AddRange(
                Enumerable.Range(1, 1 + pWorksheet.Dimension.End.Column - pWorksheet.Dimension.Start.Column)
                    .Select(i => new DataColumn(
                        pHasHeaderRow
                            ? pWorksheet.Cells[pWorksheet.Dimension.Start.Row, i].Value.ToString()
                            : i.ToString()
                        )).ToArray());

            for (int r = pHasHeaderRow ? pWorksheet.Dimension.Start.Row + 1 : pWorksheet.Dimension.Start.Row;
                r <= pWorksheet.Dimension.End.Row;
                r++)
            {
                DataRow lRow = lTable.NewRow();

                int x = 0;

                for (int c = pWorksheet.Dimension.Start.Column; c <= pWorksheet.Dimension.End.Column; c++)
                    lRow[x++] = pWorksheet.Cells[r, c].Value;

                lTable.Rows.Add(lRow);
            }

            return lTable;
        }
    }
}