public static DataTable GenerateDataTableFromExcel(string excelfileName)
{
var dt = new DataTable();
using (var spreadSheetDocument = SpreadsheetDocument.Open(excelfileName, false))
{
var workbookPart = spreadSheetDocument.WorkbookPart;
var sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
var relationshipId = sheets.First().Id.Value;
var worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
var workSheet = worksheetPart.Worksheet;
var sheetData = workSheet.GetFirstChild <SheetData>();
var rows = sheetData.Descendants <Row>();
if (rows == null)
{
return(dt);
}
var enumerable = rows.ToList();
// dt.Columns.Add("Blank");
Global.ClearColumnCellReferenceMaps();
foreach (var openXmlElement in enumerable.ElementAt(0))
{
var cell = (Cell)openXmlElement;
var columnName = GetCellValue(spreadSheetDocument, cell);
dt.Columns.Add(columnName);
var columnIndex = Regex.Replace(cell.CellReference.Value, @"[\d-]", string.Empty);
Global.ColumnNameCellReferenceMap.Add(columnName, columnIndex);
}
Global.ClearRowIndexes();
foreach (var row in enumerable) //this will also include your header row...
{
var tempRow = dt.NewRow();
int i;
if (row.ToString() == String.Empty)
{
}
for (i = 0; i < row.Descendants <Cell>().Count(); i++)
{
//tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
var cell = row.Descendants <Cell>().ElementAt(i);
var actualCellIndex = CellReferenceToIndex(cell);
tempRow[actualCellIndex] = GetCellValue(spreadSheetDocument, cell);
}
try
{
if (string.IsNullOrEmpty(tempRow[0].ToString()))
{
//last row of excel
}
if (row.RowIndex > 1 && !string.IsNullOrEmpty(tempRow[0].ToString()))
{
Global.MetaIdRowIndexMap.Add(int.Parse(tempRow[0].ToString()), (int)row.RowIndex.Value);
}
dt.Rows.Add(tempRow);
}
catch (Exception exception)
{
RConsole.WriteLineRed(exception.StackTrace + exception.InnerException?.StackTrace);
}
}
dt.Rows.RemoveAt(0); //...so i'm taking it out here.
}
return(dt);
}