public List <ExcelDataModel> Go()
{
var r = new List <ExcelDataModel>();
if (ExcelPath == "")
{
return(r);
}
if (WorkSheetToRead < 1)
{
return(r);
}
if (!File.Exists(ExcelPath))
{
return(r);
}
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(ExcelPath))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First(); //read the first workbook
int totalRows = ws.Dimension.End.Row;
int totalCols = ws.Dimension.End.Column;
int rowStart = 1;
if (IgnoreHeader)
{
rowStart = 2;
IgnoreHeader = false;
}
try
{
for (int i = rowStart; i <= totalRows; i++)
{
var excelModel = new ExcelDataModel();
for (int j = 1; j <= totalCols; j++)
{
var v = "";
if (ws.Cells[i, j].Value != null)
{
v = ws.Cells[i, j].Value.ToString();
}
if (j == 6)
{
if (v.Length >= 10)
{
v = v.Substring(0, 10);
}
ProcessExcelValue.Go(j, v, excelModel);
}
else
{
ProcessExcelValue.Go(j, v, excelModel);
}
switch (j) //Excel columns should be known
{
case 1:
excelModel.Id = Convert.ToInt32(v);
break;
case 2:
excelModel.Surname = v;
break;
case 3:
excelModel.FirstName = v;
break;
case 4:
excelModel.Cell = Convert.ToInt64(FormatCellNumber.ToInternational(v));
break;
case 5:
excelModel.Email = v;
break;
}
}
r.Add(excelModel);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return(r);
}
public List <ExcelDataModel> Go()
{
var r = new List <ExcelDataModel>();
if (ExcelPath == "")
{
return(r);
}
if (WorkSheetToRead < 1)
{
return(r);
}
if (!File.Exists(ExcelPath))
{
return(r);
}
var app = new Application();
var wb = app.Workbooks.Open(
ExcelPath, 0, true, 5, "", "", true,
XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
var ws = (Worksheet)wb.Worksheets.get_Item(WorkSheetToRead);
var range = ws.UsedRange;
int rowCount, cellCount;
for (rowCount = 1; rowCount <= range.Rows.Count; rowCount++)
{
if (IgnoreHeader)
{
IgnoreHeader = false;
continue;
}
var excelModel = new ExcelDataModel();
for (cellCount = 1; cellCount <= range.Columns.Count; cellCount++)
{
try
{
var v = (range.Cells[rowCount, cellCount] as Range).Value2;
if (cellCount == 6)
{
//format date from 99999 to dd/MM/yyyy
var x = DateTime.FromOADate(v).ToShortDateString();
ProcessExcelValue.Go(cellCount, x, excelModel);
}
else
{
ProcessExcelValue.Go(cellCount, v, excelModel);
}
}
catch (Exception ex)
{
//TODO - log
Console.WriteLine(ex.Message);
}
}
r.Add(excelModel);
}
wb.Close(false, null, null); //no need to save the document as we just reading it
app.Quit();
Marshal.ReleaseComObject(ws);
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(app);
return(r);
}