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