private DOGEN_BulkImportExcelTemplateMaster PLoadTemplateByID(int templateID)
{
DOGEN_BulkImportExcelTemplateMaster objDOGEN_BulkImportExcelTemplateMaster = new DOGEN_BulkImportExcelTemplateMaster();
try
{
objDOGEN_BulkImportExcelTemplateMaster = CacheUtility.GetBulkImportExcelTemplateFromCache().lstDOGEN_BulkImportExcelTemplateMaster.Where(xx => xx.GEN_BulkImportExcelTemplateMasterId == templateID).FirstOrDefault();
return(objDOGEN_BulkImportExcelTemplateMaster);
}
catch (Exception ex)
{
BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, string.Empty, ex.ToString());
throw ex;
}
}
private bool IsExcelFileValid(long excelTemplateId, string filePath, string fileName, out string errorMessage)
{
bool isExcelFileValid = true;
errorMessage = string.Empty;
bool repeatedColumnFound = false;
string[] uploadFileColumnsList = null;
string[] templateColumnsList;
DOGEN_BulkImportExcelTemplateMaster objDOGEN_BulkImportExcelTemplateMaster = new DOGEN_BulkImportExcelTemplateMaster();
List <DOGEN_BulkImportColumnsMapping> lstDOGEN_BulkImportColumnsMapping = new List <DOGEN_BulkImportColumnsMapping>();
try
{
objDOGEN_BulkImportExcelTemplateMaster = CacheUtility.GetBulkImportExcelTemplateFromCache().lstDOGEN_BulkImportExcelTemplateMaster.Where(xx => xx.GEN_BulkImportExcelTemplateMasterId == excelTemplateId && xx.IsActive == true).FirstOrDefault();
lstDOGEN_BulkImportColumnsMapping = CacheUtility.GetBulkImportExcelTemplateFromCache().lstDOGEN_BulkImportColumnsMapping.Where(xx => xx.GEN_BulkImportExcelTemplateMasterRef == excelTemplateId && xx.IsActive == true).ToList();
templateColumnsList = lstDOGEN_BulkImportColumnsMapping.OrderBy(x => x.ColumnSequence).Select(s => s.ColumnDisplayName).ToArray();
if (fileName != objDOGEN_BulkImportExcelTemplateMaster.ExcelTemplateName)
{
errorMessage = "Invalid File.";
return(false);
}
var getXlsxHeaderRowAsStringRetVal = GetCSVHeaderRowAsString(filePath, objDOGEN_BulkImportExcelTemplateMaster.SheetName, objDOGEN_BulkImportExcelTemplateMaster.StartRow, out errorMessage, out repeatedColumnFound);
//var getXlsxHeaderRowAsStringRetVal = GetXlsxHeaderRowAsString(filePath, objDOGEN_BulkImportExcelTemplateMaster.SheetName, objDOGEN_BulkImportExcelTemplateMaster.StartRow, out errorMessage, out repeatedColumnFound);
if (repeatedColumnFound)
{
errorMessage = "There are some repeated columns in the excel.";
isExcelFileValid = false;
return(isExcelFileValid);
}
if (getXlsxHeaderRowAsStringRetVal != null && getXlsxHeaderRowAsStringRetVal.Count > 0)
{
if (getXlsxHeaderRowAsStringRetVal.Count >= objDOGEN_BulkImportExcelTemplateMaster.StartColumn)
{
uploadFileColumnsList = getXlsxHeaderRowAsStringRetVal.Skip((int)objDOGEN_BulkImportExcelTemplateMaster.StartColumn - 1).ToArray();
}
bool retVal = true;
string missingColumnNames = string.Empty;
if (uploadFileColumnsList != null)
{
if (uploadFileColumnsList.Length == templateColumnsList.Length)
{
for (int i = 0; i < templateColumnsList.Length; i++)
{
if (uploadFileColumnsList[i].Trim().Length > 0 && templateColumnsList[i].Trim() != uploadFileColumnsList[i].Trim())
{
retVal = false;
missingColumnNames += ',' + templateColumnsList[i];
}
}
if (retVal == false)
{
errorMessage = "Following column/s are missing or renamed or not as per template order in the uploaded excel:" + missingColumnNames.TrimStart(',') + ".";
isExcelFileValid = false;
}
else
{
isExcelFileValid = true;//if all columns are present in uploaded file and in same order then return true
}
}
else if (uploadFileColumnsList.Length > 0)
{
for (int i = 0; i < templateColumnsList.Length; i++)
{
//column matching is not casesensitive
if (i < uploadFileColumnsList.Length)
{
if (templateColumnsList[i] != uploadFileColumnsList[i])
{
missingColumnNames += ',' + templateColumnsList[i];
}
}
else
{
missingColumnNames += ',' + templateColumnsList[i];
}
}
errorMessage = "Following column/s are missing or renamed or not as per template order in the uploaded excel:" + missingColumnNames.TrimStart(',') + ".";
isExcelFileValid = false;
}
}
}
else
{
isExcelFileValid = false;
}
if (!isExcelFileValid)
{
BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, errorMessage, errorMessage);
}
}
catch (Exception ex)
{
errorMessage += ex.Message + ex.StackTrace;
BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, errorMessage, errorMessage);
isExcelFileValid = false;
}
return(isExcelFileValid);
}
