|
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;
}
}
}
|
|
|