public bool ImportXls <TExcelType>(string filename, List <TExcelType> excelUseTypeDataList) where TExcelType : ExcelData
{
var name = Path.GetFileNameWithoutExtension(filename);
var ext = Path.GetExtension(filename);
var workBook = new WorkBook();
try
{
if (ext == ".xls")
{
workBook.read(filename);
}
else if (ext == ".xlsx")
{
workBook.readXLSX(filename);
}
else
{
//"Wrong file type", "User tried to upload a " + ext + " file.";
return(false);
}
}
catch (Exception err)
{
//"Reading Excel file", "error reading excel file: " + err.Message;
return(false);
}
var myObject = CreateGeneric(typeof(TExcelType)) as ExcelData;
ExcelGenericAttribute xlAttr = ExcelGenericAttribute.GetAttribute(myObject.GetType());
if (xlAttr != null && xlAttr.SheetNumberToRead > -1)
{
var sheets = xlAttr.SheetNumberToRead > 0 ? xlAttr.SheetNumberToRead : workBook.NumSheets;
for (var s = 0; s < sheets; s++)
{
excelUseTypeDataList.AddRange(ReadExcelSheetData <TExcelType>(workBook, s, ref _readErrors));
}
}
else // read 1st sheet only
{
excelUseTypeDataList.AddRange(ReadExcelSheetData <TExcelType>(workBook, 0, ref _readErrors));
}
return(true);
}
private IEnumerable <TExcelType> ReadExcelSheetData <TExcelType>(WorkBook workBook, int sheetNum, ref JsonObjectList errorList) where TExcelType : ExcelData
{
if (workBook == null)
{
return(new List <TExcelType>());
}
if (sheetNum < 0)
{
return(new List <TExcelType>());
}
var tempDataList = new List <TExcelType>();
workBook.Sheet = sheetNum;
var lastRow = workBook.LastRow;
var lastCol = workBook.LastCol;
var myObject = CreateGeneric(typeof(TExcelType)) as ExcelData;
// get properties of the ExcelMemberData only once
PropertyInfo[] pps = myObject.GetType().GetProperties();
ExcelGenericAttribute xlAttr = ExcelGenericAttribute.GetAttribute(myObject.GetType());
for (var i = 1; i <= lastRow; i++)
{
var mbrLine = CreateGeneric(typeof(TExcelType)) as ExcelData;
mbrLine.ExcelLine = i + 1;
if (xlAttr != null && xlAttr.AllowBlank && IsLineEmpty(workBook, i, pps.Length))
{
mbrLine.BlankLineFound = true;
continue;
}
foreach (PropertyInfo p in pps)
{
ExcelColumnAttribute xlColAttr = ExcelColumnAttribute.GetAttribute(p);
if (xlColAttr == null || xlColAttr.OutputOnly)
{
continue;
}
var xlValue = workBook.getText(i, xlColAttr.Column);
try
{
ExcelColumnAttribute.Validate(p, xlValue, mbrLine);
mbrLine.CustomPropertyValidation(p, xlValue, mbrLine);
}catch (Exception er)
{
var importStats = BaseImportWorker.GetImportStatus("GroupProperty", BaseUserContext.Instance.UserName);
importStats.Status = new { Msg = "Errors while validating" };
importStats.ImportProgres = 100;
mbrLine.IsValid = false;
mbrLine.ErrorMessage = p.Name + ":" + er.Message;
}
if (mbrLine.CurrentPropertyIsBlank && xlColAttr.IgnoreLineIfBlank)
{
mbrLine.IsValid = false;
mbrLine.WasIgnored = true;
continue;
}
try
{
if (mbrLine.IsValid && !mbrLine.CurrentPropertyIsBlank)
{
p.SetValue(mbrLine, mbrLine.ValidValue, null);
}
}
catch (Exception er)
{
if (mbrLine.IsValid) // to keep the first error
{
mbrLine.IsValid = false;
mbrLine.ErrorMessage = er.Message;
}
}
if (!mbrLine.IsValid)
{
break;
}
}
if (!mbrLine.IsValid && !mbrLine.WasIgnored)
{
var jso = new JsonObject();
jso.AddProperty("line", i + 1);
jso.AddProperty("sheet", sheetNum);
jso.AddProperty("error", mbrLine.ErrorMessage);
errorList.Add(jso);
}
tempDataList.Add((TExcelType)mbrLine);
}
return(tempDataList);
}
