void GetDataFromExcel(string Path, string Extension) { dtError.Columns.Add("Section"); dtError.Columns.Add("LineNo"); dtError.Columns.Add("Error"); dtError.Columns.Add("ColumnName"); lstcol = new List <string>() { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; Label lblOrganisation = (Label)wucTaxPayerStrip.FindControl("lblClientName"); DataTable dt = new DataTable(); List <int> EwayBill = new List <int>() { 23, 24, 26, 27, 28, 29, 31 }; if (Extension == ".xls") { using (StreamReader input = new StreamReader(Path)) { IWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(input.BaseStream)); dt = GetDTFromExcel2003(Path, 4, 35, false, "eWayBill", 2, workbook, EwayBill); } } else { ICSharpCode.SharpZipLib.Zip.ZipConstants.DefaultCodePage = Encoding.Default.CodePage; XSSFWorkbook wb; using (MemoryStream memstream = new MemoryStream()) { fuTallyExcelImport.PostedFile.InputStream.CopyTo(memstream); memstream.Position = 0; wb = new XSSFWorkbook(memstream); } //using (var fs = new FileStream(Path, FileMode.Open, FileAccess.Read)) //{ // fs.Position = 0; // wb = new XSSFWorkbook(fs); //} dt = GetDTFromExcel2007(Path, 4, 35, false, "eWayBill", 2, wb, EwayBill); } StringBuilder sb1 = new StringBuilder(); if (dtError.Rows.Count > 0) { sb1.AppendLine("-------------------------------------------------------------------------------------------------------------"); sb1.AppendLine("-------------------------------------------------------------------------------------------------------------"); sb1.AppendLine(""); sb1.AppendLine("***GST SAATHI REUTRN FILING PORTAL**** Client Code : " + Session["ClientCode"].ToString()); sb1.AppendLine(""); sb1.AppendLine(" SALES DATA IMPORT VALIDITY : Client Name : " + lblOrganisation.Text); sb1.AppendLine(" Date & Time : " + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss tt") + " Client GSTIN : " + Session["ClientGSTNNO"].ToString()); sb1.AppendLine(""); sb1.AppendLine("-------------------------------------------------------------------------------------------------------------"); sb1.AppendLine("-------------------------------------------------------------------------------------------------------------"); sb1.AppendLine(""); sb1.AppendLine("Section |Line No |Column|Error Desc With Code"); foreach (DataRow dr in dtError.Rows) { sb1.AppendLine(dr["Section"].ToString().PadRight(10, ' ') + "|" + dr["LineNo"].ToString().PadRight(8, ' ') + "|" + dr["ColumnName"].ToString().PadRight(6, ' ') + "|" + dr["Error"].ToString()); } sb1.AppendLine(""); sb1.AppendLine("******************************************END OF THE RECORDS***************************************************"); System.IO.File.WriteAllText(Server.MapPath("~/ErrorTextFile/" + ExcelNo + ".txt"), sb1.ToString()); lnkbtnViewError.Attributes.Add("href", "../ErrorTextFile/" + ExcelNo + ".txt"); lnkbtnViewError.Attributes.Add("download", "GSTR2_ErrorFile_" + ExcelNo); Panels.Visible = true; Panels.Attributes.Add("class", "alert alert-warning"); lblTotalRecords.Text = "0"; lblImported.Text = "0"; lnkbtnViewError.Visible = true; lblMessage.Text = "Excel File Data Contains Validity Errors Download Error Invoices Here."; CLSCommon objcmn = new CLSCommon(); objcmn.DeleteData(1); } else { PL_EwayBill objpl = new PL_EwayBill(); objpl.ds.Tables.Add(dt); objpl.ds.Tables[0].TableName = "EwayBill"; objpl.ind = 1; objpl.UserCode = int.Parse(Session["UserCode"].ToString()); objpl.ClientCode = int.Parse(Session["ClientCode"].ToString()); objpl.ClientCodeOdp = int.Parse(Session["ClientCodeOdp"].ToString()); objpl.ClientGSTNNO = Session["ClientGSTNNO"].ToString();//lbl.Text; objpl.CACode = int.Parse(Session["CaCode"].ToString()); objpl.CaCodeOdp = int.Parse(Session["CaCodeOdp"].ToString()); objpl.MonthCD = int.Parse(Session["MonthYrCode"].ToString()); objpl.ExcelNo = Convert.ToInt32(ExcelNo); //#region DataTable dtresponse = CLSCommon.CallApiPost("api/EwayBill/ImportEwayBill", objpl); if (dtresponse != null) { if (dtresponse.Rows.Count > 0) { if (dtresponse.Rows[0][0].ToString() == "1") { // DataSet ds1 = CLSCommon.CallApiGet("api/GSTR1Payload/SaveSGTR1Payload?Ind=1&ClientCode=" + Session["ClientCode"].ToString() + "&ClientCodeOdp=" + Session["ClientCodeOdp"].ToString() + "&CaCode=" + Session["CaCode"].ToString() + "&CacodeOdp=" + Session["CacodeOdp"].ToString() + "&GSTIN=" + Session["ClientGSTNNO"].ToString() + "&MonthCD=" + ddlMonthYear.SelectedValue.ToString() + "&UserCode=" + Session["UserCode"].ToString() + "&ExcelNo=" + dtresponse.Rows[0]["ExcelNo"].ToString()); // txtPayload.Text = ds1.Tables[0].Rows[0]["Payload"].ToString(); Panels.Visible = true; Panels.Attributes.Add("class", "alert alert-success"); string TotalRows = Convert.ToString(dt.Rows.Count); lblTotalRecords.Text = TotalRows; //dt.Rows.Count.ToString(); lblImported.Text = TotalRows; //dt.Rows.Count.ToString(); lblMessage.Text = "Excel Imported Successfully. \n Total Rows : " + TotalRows; // dt.Rows.Count.ToString(); lnkbtnViewError.Visible = false; } else if (dtresponse.Rows[0][0].ToString() == "2") { // Label lblOrganisations = (Label)wucTaxPayerStrip.FindControl("lblClientName"); Panels.Visible = true; Panels.Attributes.Add("class", "alert alert-warning"); lblTotalRecords.Text = "0"; lblImported.Text = "0"; lblMessage.Text = "Excel File Data Contains Validity Errors Download Error Invoices Here."; StringBuilder sb = new StringBuilder(); sb.AppendLine("-------------------------------------------------------------------------------------------------------------"); sb.AppendLine("-------------------------------------------------------------------------------------------------------------"); sb.AppendLine(""); sb.AppendLine("***GST SAATHI REUTRN FILING PORTAL**** Client Code : " + Session["ClientCode"].ToString()); sb.AppendLine(""); sb.AppendLine(" SALES DATA IMPORT VALIDITY : Client Name : " + lblOrganisation.Text); sb.AppendLine(" Date & Time : " + DateTime.Parse(dtresponse.Rows[0]["CurrentDate"].ToString()).ToString("dd/MM/yyyy HH:mm:ss tt") + " Client GSTIN : " + Session["ClientGSTNNO"].ToString()); sb.AppendLine(""); sb.AppendLine("-------------------------------------------------------------------------------------------------------------"); sb.AppendLine("-------------------------------------------------------------------------------------------------------------"); sb.AppendLine(""); sb.AppendLine("Section |Line No |Invoice No. |Invoice Date|Invoice Value|Error Desc With Code"); foreach (DataRow dr in dtresponse.Rows) { sb.AppendLine(dr["ApNameDesc"].ToString().PadRight(10, ' ') + "|" + dr["LineNo"].ToString().PadRight(8, ' ') + "|" + dr["CurrInvoiceNo"].ToString().PadRight(15, ' ') + "|" + dr["InvoiceDate"].ToString().PadRight(12, ' ') + "|" + dr["TotInvoiceValue"].ToString().PadRight(13, ' ') + "|" + dr["ErrorCode"].ToString() + " - " + dr["ErrorDesc"].ToString()); } sb.AppendLine(""); sb.AppendLine("******************************************END OF THE RECORDS***************************************************"); System.IO.File.WriteAllText(Server.MapPath("~/ErrorTextFile/" + ExcelNo + ".txt"), sb.ToString()); lnkbtnViewError.Attributes.Add("href", "../ErrorTextFile/" + ExcelNo + ".txt"); lnkbtnViewError.Attributes.Add("download", "GSTR1_ErrorFile_" + ExcelNo); //lnkbtnViewError.Attributes.Add("target", "_blanck"); lnkbtnViewError.Visible = true; } else { Panels.Visible = true; Panels.Attributes.Add("class", "alert alert-warning"); lblTotalRecords.Text = "0"; lblImported.Text = "0"; lblMessage.Text = dtresponse.Rows[0]["ErrorMessage"].ToString().Replace("{{MonthYear}}", Session["MonthYrCode"].ToString()); } } else { CLSCommon objcmn = new CLSCommon(); objcmn.DeleteData(1); lblTotalRecords.Text = "0"; lblImported.Text = "0"; Panels.Visible = true; Panels.Attributes.Add("class", "alert alert-danger"); lblMessage.Text = "The File is not in the correct format."; Page.ClientScript.RegisterStartupScript(this.GetType(), "CallMyFunction", "$(document).ready(function(){$('#inputModal').modal('show');});", true); } // // Page.ClientScript.RegisterStartupScript(this.GetType(), "CallMyFunction", "$(document).ready(function(){$('#inputModal').modal('show');});", true); } else { CLSCommon objcmn = new CLSCommon(); objcmn.DeleteData(1); lblTotalRecords.Text = "0"; lblImported.Text = "0"; Panels.Visible = true; Panels.Attributes.Add("class", "alert alert-danger"); lblMessage.Text = "The File is not in the correct format."; Page.ClientScript.RegisterStartupScript(this.GetType(), "CallMyFunction", "$(document).ready(function(){$('#inputModal').modal('show');});", true); } } }