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); }