public void ReadFile(List <FileListModel> filesList, string rootPath) { var MappingData = FileMappingRepository.GetFileMappingsData(); List <object> returnlist = new List <object>(); foreach (var path in filesList) { int fileID = ReadFilesRepository.Add(new Models.EntityModels.ExcelLoan.ReadFiles() { FileName = path.FileName, UploadFileTypeID = path.FileTypeID, ReadFileStatusID = (int)ReadFileStatus.Faild }).ID; FileInfo file = new FileInfo(path.FileName); using (ExcelPackage package = new ExcelPackage(file)) { foreach (var sheetName in MappingData.Where(m => m.FileTypeID == path.FileTypeID).Select(m => m.SheetName).Distinct()) { string currentSheet = sheetName; ExcelWorksheet worksheet = package.Workbook.Worksheets[currentSheet]; foreach (var cellCode in MappingData.Where(m => m.FileTypeID == path.FileTypeID && m.SheetName == currentSheet)) { string cellValue = null; try { cellValue = worksheet.Cells[cellCode.Cell].Value.ToString(); } catch { cellValue = null; } try { UnplanedExcelDataRepository.Add(new UnplanedExcelData() { ReadFileID = fileID, VariableName = cellCode.VariableName, ValueNvarchar = cellValue }); } catch (Exception ex) { throw new Exception("Insert UnplanedExcelData Error:" + ex.Message + "\n" + ex.InnerException); } } } } ReadFilesRepository.UpdateStatus(fileID, (int)ReadFileStatus.Success); } }
public List <object> ReadExcelFileData(List <FileListModel> filesList, string rootPath, Type objectType) { var MappingData = FileMappingRepository.GetFileMappingsData(); List <object> returnlist = new List <object>(); foreach (var path in filesList) { object mappingObject = Activator.CreateInstance(objectType); FileInfo file = new FileInfo(path.FileName); using (ExcelPackage package = new ExcelPackage(file)) { foreach (var sheetName in MappingData.Where(m => m.FileTypeID == path.FileTypeID).Select(m => m.SheetName).Distinct()) { string currentSheet = sheetName; ExcelWorksheet worksheet = package.Workbook.Worksheets[currentSheet]; foreach (var cellCode in MappingData.Where(m => m.FileTypeID == path.FileTypeID && m.SheetName == currentSheet)) { string cellValue = null; try { cellValue = worksheet.Cells[cellCode.Cell].Value.ToString(); } catch { cellValue = null; } PropertyInfo property = mappingObject.GetType().GetProperty(cellCode.VariableName); property.SetValue(mappingObject, Convert.ChangeType(cellValue, property.PropertyType), null); } } } returnlist.Add(mappingObject); } return(returnlist); }