protected void btnImport_Click(object sender, EventArgs e)
{
try
{
string Err = string.Empty;
if (!FileUpload1.HasFile)
{
Err = " Please select file";
lblerror.Text = Err;
lblerror.ForeColor = Color.Red;
lblerror.Visible = true;
grdErrors.Visible = false;
grdExcel.Visible = false;
btnUpload.Visible = false;
return;
}
if (!(Path.GetExtension(FileUpload1.FileName).Equals(".xlsx")))
{
Err = " Please Select Excel file (.xlsx) only ";
lblerror.Text = Err;
lblerror.ForeColor = Color.Red;
lblerror.Visible = true;
grdErrors.Visible = false;
grdExcel.Visible = false;
btnUpload.Visible = false;
return;
}
path = Server.MapPath(@"~/writedata/") + FileUpload1.FileName;
try
{
System.IO.File.Delete(path);
}
catch (Exception ex)
{
}
try
{
FileUpload1.SaveAs(path);
}
catch (FileNotFoundException ex)
{
lblerror.Text = "Error creating file";
}
catch (Exception ex)
{
throw ex;
}
clsExcelInterface objExcel = new clsExcelInterface();
DataTable dtExcelData = objExcel.getExcelDataTable(path);
List <ProjectionDataWB> ListRows = new List <ProjectionDataWB>();
List <ProjectionDataWB> ListRowserr = new List <ProjectionDataWB>();
List <ErrorMessage> ListError = new List <ErrorMessage>();
int rowIndex = 0;
decimal ddd = 0;
bool chk = true;
bool chk1 = true;
bool chk2 = true;
bool chk3 = true;
foreach (DataRow dr in dtExcelData.Rows)
{
if (rowIndex >= Convert.ToInt32(drpRows.SelectedValue.ToString()) && dr[11].ToString() != "")
{
int selectedindex = Convert.ToInt32(drpColumn.SelectedValue.ToString());
chk = dr[selectedindex + 9].ToString() != "" ? dr[selectedindex + 9].ToString().IsNumericText() : true;
chk1 = dr[selectedindex + 10].ToString() != "" ? dr[selectedindex + 10].ToString().IsNumericText() : true;
chk2 = dr[selectedindex + 11].ToString() != "" ? dr[selectedindex + 11].ToString().IsNumericText() : true;
chk3 = dr[selectedindex + 12].ToString() != "" ? dr[selectedindex + 12].ToString().IsNumericText() : true;
//if (!dr[selectedindex + 9].ToString().IsNumericText())
//{
// ListError.Add(new ErrorMessage() { row = rowIndex.ToString(), ColumnIndex = 2, LoanNum = "Bank Financing", column = "Bank Financing", Message = "Please enter Numeric value" });
//}
//else if (!dr[selectedindex + 10].ToString().IsNumericText())
//{
// ListError.Add(new ErrorMessage() { row = rowIndex.ToString(), ColumnIndex = 2, LoanNum = "Current Disbursement", column = "Current Disbursement", Message = "Please enter Numeric value" });
//}
//else if (!dr[selectedindex + 11].ToString().IsNumericText())
//{
// ListError.Add(new ErrorMessage() { row = rowIndex.ToString(), ColumnIndex = 2, LoanNum = "Current Disbursement", column = "Current Disbursement", Message = "Please enter Numeric value" });
//}
ProjectionDataWB row1 = new ProjectionDataWB()
{
rowIndex = rowIndex.ToString(),
ProjectNo = dr[selectedindex].ToString(),
ProjectName = dr[selectedindex + 1].ToString(),
ApprovalDate = dr[selectedindex + 2].ToString(),
EffectiveDate = dr[selectedindex + 3].ToString(),
closingdate = dr[selectedindex + 4].ToString(),
Reclosingdate = dr[selectedindex + 5].ToString(),
DO = dr[selectedindex + 6].ToString(),
IP = dr[selectedindex + 7].ToString(),
//Agency = dr[selectedindex+8].ToString(),
QuarterYear = dr[selectedindex + 8].ToString(),
Amt = dr[selectedindex + 9].ToString().Contains("E") || dr[selectedindex + 9].ToString().Contains("e") ? decimal.TryParse(dr[selectedindex + 9].ToString(), out ddd).ToString() : dr[selectedindex + 9].ToString(),
TotalDis = dr[selectedindex + 10].ToString().Contains("E") || dr[selectedindex + 10].ToString().Contains("e") ? decimal.TryParse(dr[selectedindex + 10].ToString(), out ddd).ToString() : dr[selectedindex + 10].ToString(),
OriginalDis = dr[selectedindex + 11].ToString().Contains("E") || dr[selectedindex + 11].ToString().Contains("e") ? decimal.TryParse(dr[selectedindex + 11].ToString(), out ddd).ToString() : dr[selectedindex + 11].ToString(),
FrevDis = dr[selectedindex + 12].ToString().Contains("E") || dr[selectedindex + 12].ToString().Contains("e") ? decimal.TryParse(dr[selectedindex + 12].ToString(), out ddd).ToString() : dr[selectedindex + 12].ToString()
};
ListRowserr.Add(row1);
if (chk == false || chk1 == false || chk2 == false || chk3 == false)
{
grdExcel.DataSource = ListRowserr;
grdExcel.DataBind();
grdExcel.Visible = true;
btnUpload.Visible = false;
Alert.show("Please select valid range or correct this error");
lblerror.Text = "Please select valid range or correct this error";
lblerror.Visible = true;
return;
}
ProjectionDataWB row = new ProjectionDataWB()
{
rowIndex = rowIndex.ToString(),
ProjectNo = dr[selectedindex].ToString(),
ProjectName = dr[selectedindex + 1].ToString(),
ApprovalDate = dr[selectedindex + 2].ToString(),
EffectiveDate = dr[selectedindex + 3].ToString(),
closingdate = dr[selectedindex + 4].ToString(),
Reclosingdate = dr[selectedindex + 5].ToString(),
DO = dr[selectedindex + 6].ToString(),
IP = dr[selectedindex + 7].ToString(),
//Agency = dr[selectedindex+8].ToString(),
QuarterYear = dr[selectedindex + 8].ToString(),
Amt = dr[selectedindex + 9].ToString().Contains("E") || dr[selectedindex + 9].ToString().Contains("e") ? Decimal.Parse(dr[selectedindex + 9].ToString(), System.Globalization.NumberStyles.Float).ToString() : dr[selectedindex + 9].ToString(),
TotalDis = dr[selectedindex + 10].ToString().Contains("E") || dr[selectedindex + 10].ToString().Contains("e") ? Decimal.Parse(dr[selectedindex + 10].ToString(), System.Globalization.NumberStyles.Float).ToString() : dr[selectedindex + 10].ToString(),
OriginalDis = dr[selectedindex + 11].ToString().Contains("E") || dr[selectedindex + 11].ToString().Contains("e") ? Decimal.Parse(dr[selectedindex + 11].ToString(), System.Globalization.NumberStyles.Float).ToString() : dr[selectedindex + 11].ToString(),
FrevDis = dr[selectedindex + 12].ToString().Contains("E") || dr[selectedindex + 12].ToString().Contains("e") ? Decimal.Parse(dr[selectedindex + 12].ToString(), System.Globalization.NumberStyles.Float).ToString() : dr[selectedindex + 12].ToString()
};
ListRows.Add(row);
}
rowIndex++;
}
grdExcel.DataSource = ListRows;
grdExcel.DataBind();
grdExcel.Visible = true;
Session["ExcelRows"] = ListRows;
grdErrors.DataSource = ListError;
grdErrors.DataBind();
foreach (ErrorMessage er in ListError)
{
grdExcel.Rows[Convert.ToInt32(er.row)].BackColor = Color.FromArgb(255, 220, 220);
grdExcel.Rows[Convert.ToInt32(er.row)].Cells[er.ColumnIndex + 1].BackColor = Color.FromArgb(255, 140, 140);
}
if (grdErrors.Rows.Count == 0)
{
grdErrors.Visible = false;
btnUpload.Visible = true;
lblerr.Visible = false;
}
else
{
lblerr.Visible = true;
grdErrors.Visible = true;
btnUpload.Visible = false;
}
//}
}
catch (Exception ex)
{
grdExcel.Visible = false;
grdErrors.Visible = false;
btnUpload.Visible = false;
string Error = ex.ToString();
lblerror.Text = Error;
lblerror.ForeColor = Color.Red;
lblerror.Visible = true;
}
finally
{
try
{
System.IO.File.Delete(path);
}
catch (Exception ex)
{
}
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
string path = string.Empty;
try
{
string Err = string.Empty;
if (!FileUpload1.HasFile)
{
Err = " Please select file";
lblerror.Text = Err;
return;
}
if (!(Path.GetExtension(FileUpload1.FileName).Equals(".xlsx")) && !(Path.GetExtension(FileUpload1.FileName).Equals(".xls")))
{
Err = " Please Select Excel file (.xlsx) only/(.xls) only ";
lblerror.Text = Err;
return;
}
path = Server.MapPath(@"~/writedata/") + FileUpload1.FileName;
try
{
System.IO.File.Delete(path);
}
catch (Exception ex)
{
}
try
{
FileUpload1.SaveAs(path);
}
catch (FileNotFoundException ex)
{
lblerror.Text = "Error creating file";
}
catch (Exception ex)
{
throw ex;
}
clsExcelInterface objExcel = new clsExcelInterface();
DataTable dtExcelData = objExcel.getExcelDataTable(path);
List <ProjectionDataWB> ListRows = new List <ProjectionDataWB>();
List <ErrorList> errolist = new List <ErrorList>();
float a = 0;
StringBuilder error = new StringBuilder();
int rowIndex = 0;
foreach (DataRow dr in dtExcelData.Rows)
{
if (rowIndex >= Convert.ToInt32(drpRows.SelectedValue.ToString()) && dr[0].ToString().Trim() != "")
{
int selectedindex = Convert.ToInt32(drpColumn.SelectedValue.ToString());
if (float.TryParse(dr[selectedindex + 2].ToString(), out a))
{
if (float.TryParse(dr[selectedindex + 3].ToString(), out a))
{
ProjectionDataWB row = new ProjectionDataWB()
{
rowIndex = rowIndex.ToString(),
ProjectName = dr[selectedindex + 1].ToString(),
PosedLoanAmt = dr[selectedindex + 2].ToString(),
RevisedLoanAmt = dr[selectedindex + 3].ToString(),
Sector = dr[selectedindex + 4].ToString(),
DateofPosing = dr[selectedindex + 5].ToString(),
ExpectedApprovalDateofMDB = dr[selectedindex + 6].ToString(),
ProjectDescription = dr[selectedindex + 7].ToString(),
};
ListRows.Add(row);
}
else
{
ErrorList row = new ErrorList()
{
rowIndex = rowIndex.ToString(),
ProjectName = dr[selectedindex + 1].ToString(),
PosedLoanAmt = dr[selectedindex + 2].ToString(),
RevisedLoanAmt = "<span style='color:red'>" + dr[selectedindex + 3].ToString() + "</span>",
Sector = dr[selectedindex + 4].ToString(),
DateofPosing = dr[selectedindex + 5].ToString(),
ExpectedApprovalDateofMDB = dr[selectedindex + 6].ToString(),
ProjectDescription = dr[selectedindex + 7].ToString(),
};
errolist.Add(row);
}
}
else
{
ErrorList row = new ErrorList()
{
rowIndex = rowIndex.ToString(),
ProjectName = dr[selectedindex + 1].ToString(),
PosedLoanAmt = "<span style='color:red'>" + dr[selectedindex + 2].ToString() + "</span>",
RevisedLoanAmt = dr[selectedindex + 3].ToString(),
Sector = dr[selectedindex + 4].ToString(),
DateofPosing = dr[selectedindex + 5].ToString(),
ExpectedApprovalDateofMDB = dr[selectedindex + 6].ToString(),
ProjectDescription = dr[selectedindex + 7].ToString(),
};
errolist.Add(row);
}
}
rowIndex++;
}
grdExcel.DataSource = ListRows;
grdExcel.DataBind();
grdExcel.Visible = true;
if (errolist.Count > 0)
{
grdErrorlist.DataSource = errolist;
grdErrorlist.DataBind();
grdErrorlist.Visible = true;
btnSubmit.Visible = false;
grdErrorlist.Caption = HttpUtility.HtmlDecode("<span style='color:red; font-weight:bold'>Error List Please correct Amout is only numeric</span>");
}
else
{
btnSubmit.Visible = true;
grdErrorlist.Visible = false;
Session["ExcelRows"] = ListRows;
}
//}
}
catch (Exception ex)
{
grdExcel.Visible = false;
string Error = ex.ToString();
lblerror.Text = Error;
lblerror.Visible = true;
}
finally
{
try
{
System.IO.File.Delete(path);
}
catch (Exception ex)
{
}
}
}
