public static bool IsValid(this EmployeeImportRow row)
{
var properties = row.GetType().GetProperties().ToList();
foreach (var property in properties.Where(x => x.Name != "PlaceNumber"))
{
var val = row.GetType().GetProperty(property.Name).GetValue(row, null);
if (val == null || string.IsNullOrEmpty(val.ToString()))
{
return(false);
}
if (property.Name == "BirthDay")
{
DateTime date;
if (!DateTime.TryParse(val.ToString(), out date))
{
return(false);
}
}
if (property.Name == "PESEL")
{
if (!val.ToString().IsValidPESEL())
{
return(false);
}
}
}
return(true);
}
public static List <EmployeeImportRow> ReadExcelEmployeeFile(string filename)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filename);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
List <EmployeeImportRow> rows = new List <EmployeeImportRow>();
for (int i = 2; i <= rowCount; i++)
{
EmployeeImportRow row = new EmployeeImportRow();
row.RowNumber = i;
row.FirstName = xlRange.Cells[i, 1].Value2?.ToString();
row.Surname = xlRange.Cells[i, 2].Value2?.ToString();
row.PESEL = xlRange.Cells[i, 3].Value2?.ToString();
row.BirthDate = xlRange.Cells[i, 4].Value2?.ToString().Replace("/", "");
try
{
double date = double.Parse(row.BirthDate);
row.BirthDate = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");
}
catch (Exception e)
{
throw new Exception(string.Format("Nieprawidłowy format daty urodzin dla: {0} {1}. Wymagany format: rrrr-mm-dd", row.FirstName, row.Surname));
}
row.City = xlRange.Cells[i, 5].Value2?.ToString();
row.StreetName = xlRange.Cells[i, 6].Value2?.ToString();
row.StreetNumber = xlRange.Cells[i, 7].Value2?.ToString();
row.PlaceNumber = xlRange.Cells[i, 8].Value2?.ToString();
row.ZIPCode = xlRange.Cells[i, 9].Value2?.ToString();
rows.Add(row);
}
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
return(rows);
}