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