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