public void ImportMapping(int fileID)
{
var file = UploadFileRepository.Get().Where(m => m.ID == fileID && m.IsActive == true && m.IsImported == false).FirstOrDefault();
if (file != null)
{
using (MemoryStream ms = new MemoryStream(file.UploadFileData))
{
using (ExcelPackage excel = new ExcelPackage(ms))
{
if (excel.Workbook.Worksheets.Count == 0)
{
throw new Exception("File Is Empty");
}
else
{
try
{
List <FileMappingViewModel> fileMapping = new List <FileMappingViewModel>();
var worksheet = excel.Workbook.Worksheets.FirstOrDefault();
var start = worksheet.Dimension.Start;
var end = worksheet.Dimension.End;
for (int row = start.Row + 1; row <= end.Row; row++)
{
fileMapping.Add(new FileMappingViewModel()
{
SheetName = worksheet.Cells[row, 1].Text,
VariableName = worksheet.Cells[row, 2].Text,
Cell = worksheet.Cells[row, 3].Text,
FileTypeID = file.UploadFileTypeID,
VariableTypeID = (int)VariableTypes.Nvarchar
});
}
if (fileMapping.Count == 0)
{
throw new Exception("File Is not Correct");
}
FileMappingRepository.ImportNewMappingData(fileMapping.Select(m => Transformer.AsDatabaseModel(m)).ToList(), file.UploadFileTypeID);
UploadFileRepository.CheckAsImported(file.ID);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
}
else
{
throw new Exception("File Not Found! It's already deactivated or imported!");
}
}
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);
}
