If you have large excel document then it would be better for you to use the Open XML SDK and for the smaller size files, I think the EPPLUS is better suited.
For EPPLUS
you can make use of the following code:
public DataTable GetDataTableFromExcel(string path)
{
var tbl = new DataTable();
using (var pck = new OfficeOpenXml.ExcelPackage())
{
//reading the excel file using the stream
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
//Reading the data from the 1st sheet, you can add the code to read other sheets
var ws = pck.Workbook.Worksheets.First();
//now adding the columns to the table and assuming the first row of the sheet is contaning columns if not change the we.Cells property
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(firstRowCell.Text);
}
//adding data to datatable
for (int rowNum = 1; rowNum < ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.Rows.Add();
foreach (var cell in wsRow)
{
cell.Calculate();
row[cell.Start.Column - 1] = cell.Value;
}
}
return tbl;
}
For OPEN XML SDK
you can make use of the following code:
public DataTable GetDataTableFromExcel(string path)
{
var dataTable = new DataTable();
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
{
//to read data from the 1st sheet
Worksheet worksheet = SpreedsheetHelper.GetWorksheetPart(doc.WorkbookPart, "myFirstSheetname").Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
var cells = SpreedsheetHelper.GetRowCells(rows.ElementAt(0));
//creating the columns
foreach (Cell cell in cells)
{
var colname = SpreedsheetHelper.TryGetCellValue(doc, cell);
colname = colname == null ? "" : colname;
dataTable.Columns.Add(colname, SpreedsheetHelper.GetCellDatatype(cell));
}
//adding data to datatable
foreach (Row row in rows)
{
DataRow dataRow = dataTable.NewRow();
var rowcells = SpreedsheetHelper.GetRowCells(row);
var cellindex = 0;
foreach (Cell cell in rowcells)
{
var value = SpreedsheetHelper.TryGetCellValue(doc, cell);
value = value == null ? "" : value;
dataRow[cellindex] = value;
cellindex++;
}
dataTable.Rows.Add(dataRow);
}
}
//to handle the blank row added at the top of datatable
dataTable.Rows.RemoveAt(0);
return dataTable;
}
Hope this would help.
