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();
                Enumerable.Range(1, 1 + pWorksheet.Dimension.End.Column - pWorksheet.Dimension.Start.Column)
                    .Select(i => new DataColumn(
                            ? pWorksheet.Cells[pWorksheet.Dimension.Start.Row, i].Value.ToString()
                            : i.ToString()

            for (int r = pHasHeaderRow ? pWorksheet.Dimension.Start.Row + 1 : pWorksheet.Dimension.Start.Row;
                r <= pWorksheet.Dimension.End.Row;
                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;


            return lTable;

List Posts Newest Posts Page 1Next Page