protected void btnExpAdd_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
lblCdnrErrorMsg.Text = "";
if (ViewState["ExpTable"] == null)
{
dt.Columns.Add("Export Type");
dt.Columns.Add("Invoice Number");
dt.Columns.Add("Invoice date");
dt.Columns.Add("Invoice Value");
dt.Columns.Add("Port Code");
dt.Columns.Add("Shipping Bill Number");
dt.Columns.Add("Shipping Bill Date");
dt.Columns.Add("Rate");
dt.Columns.Add("Taxable Value");
}
else
{
dt = (DataTable)ViewState["ExpTable"];
}
DataRow dr = dt.NewRow();
dr["Export Type"] = ddlExpExportType.SelectedItem.Text.Trim();
dr["Invoice Number"] = txtExpInvoiceNo.Text;
dr["Invoice date"] = CLSCommon.DateFormat(txtExpInvoiceDate.Text);
dr["Invoice Value"] = txtExpInvoiceValue.Text.Trim() == "" ? "0" : txtExpInvoiceValue.Text.Trim();
dr["Port Code"] = txtExpPortCode.Text.Trim();
dr["Shipping Bill Number"] = txtExpShippingBillNo.Text;
dr["Shipping Bill Date"] = CLSCommon.DateFormat(txtExpShippingBillDate.Text.ToString());
dr["Rate"] = ddltxtExpRate.SelectedItem.Text.ToString();
dr["Taxable Value"] = txtExpTaxableValue.Text;
dt.Rows.Add(dr);
ViewState["ExpTable"] = dt;
GridExp.DataSource = dt;
GridExp.DataBind();
ClearExp();
}
protected void btnCdnurAdd_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
lblCdnrErrorMsg.Text = "";
if (ViewState["CDNURTable"] == null)
{
dt.Columns.Add("UR Type");
dt.Columns.Add("Note/Refund Voucher Number");
dt.Columns.Add("Note/Refund Voucher date");
dt.Columns.Add("Document Type");
dt.Columns.Add("Invoice/Advance Receipt Number");
dt.Columns.Add("Invoice/Advance Receipt date");
dt.Columns.Add("Reason For Issuing document");
dt.Columns.Add("PlaceOfSupplyCode");
dt.Columns.Add("Place Of Supply");
dt.Columns.Add("Note/Refund Voucher Value");
dt.Columns.Add("Rate");
dt.Columns.Add("Taxable Value");
dt.Columns.Add("Cess Amount");
dt.Columns.Add("Pre GST");
dt.Columns.Add("ItemIGSTValue");
dt.Columns.Add("ItemCGSTValue");
dt.Columns.Add("ItemSGSTValue");
}
else
{
dt = (DataTable)ViewState["CDNURTable"];
}
DataRow dr = dt.NewRow();
dr["UR Type"] = ddlCdnurUrType.SelectedItem.Text.Trim();
dr["Note/Refund Voucher Number"] = txtCdnurVoucherNo.Text;
dr["Note/Refund Voucher date"] = CLSCommon.DateFormat(txtCdnurVoucherDate.Text);
dr["Document Type"] = ddlCdnurDocType.Text;
dr["Invoice/Advance Receipt Number"] = txtCdnurInvoiceNo.Text;
dr["Invoice/Advance Receipt date"] = CLSCommon.DateFormat(txtCdnurInvoiceDate.Text);
dr["Reason For Issuing document"] = ddlCdnurReasonForIssueDoc.SelectedItem.Text.ToString();
dr["PlaceOfSupplyCode"] = ddlCdnurPlaceOfSupply.Text.ToString();
dr["Place Of Supply"] = ddlCdnurPlaceOfSupply.SelectedItem.Text;
dr["Note/Refund Voucher Value"] = txtCdnurVoucherValue.Text;
dr["Rate"] = ddltxtCdnurRate.SelectedItem.Text.ToString();
dr["Taxable Value"] = txtCdnurTaxableValue.Text;
dr["Cess Amount"] = txtCdnurCessAmount.Text.Trim() == "" ? "0" : txtCdnurCessAmount.Text.Trim();
if (ddlCdnurPreGst.SelectedItem.Text.ToString() == "Yes")
{
dr["Pre GST"] = "Y";
}
else
{
dr["Pre GST"] = "N";
}
string GSTIN = Session["ClientGSTNNO"].ToString();
int stateCode = Convert.ToInt32(ddlCdnurPlaceOfSupply.SelectedValue);
int gst = Convert.ToInt32(GSTIN.Substring(0, 2));
if (gst != stateCode)
{
dr["ItemIGSTValue"] = ((Convert.ToDecimal(ddltxtCdnurRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtCdnurTaxableValue.Text)) / 100).ToString();
dr["ItemCGSTValue"] = "0";
dr["ItemSGSTValue"] = "0";
}
else
{
dr["ItemIGSTValue"] = "0";
dr["ItemCGSTValue"] = (((Convert.ToDecimal(ddltxtCdnurRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtCdnurTaxableValue.Text)) / 100) / 2).ToString();
dr["ItemSGSTValue"] = (((Convert.ToDecimal(ddltxtCdnurRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtCdnurTaxableValue.Text)) / 100) / 2).ToString();;
}
dt.Rows.Add(dr);
ViewState["CDNURTable"] = dt;
GridCdnur.DataSource = dt;
GridCdnur.DataBind();
ClearCdnur();
}
protected void btnB2clAdd_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
lblB2clErrorMsg.Text = "";
if (ViewState["B2ClTable"] == null)
{
dt.Columns.Add("Invoice Number");
dt.Columns.Add("Invoice date");
dt.Columns.Add("Invoice Value");
dt.Columns.Add("PlaceOfSupplyCode");
dt.Columns.Add("Place Of Supply");
dt.Columns.Add("Rate");
dt.Columns.Add("Taxable Value");
dt.Columns.Add("Cess Amount");
dt.Columns.Add("E-Commerce GSTIN");
dt.Columns.Add("ItemIGSTValue");
dt.Columns.Add("ItemCGSTValue");
dt.Columns.Add("ItemSGSTValue");
}
else
{
dt = (DataTable)ViewState["B2ClTable"];
}
DataRow dr = dt.NewRow();
dr["Invoice Number"] = txtB2clInvoiceNo.Text;
dr["Invoice date"] = CLSCommon.DateFormat(txtB2clInvoiceDate.Text);
dr["Invoice Value"] = txtB2clInvoiceValue.Text;
dr["PlaceOfSupplyCode"] = ddlB2clPlaceOfSupply.Text;
dr["Place Of Supply"] = ddlB2clPlaceOfSupply.SelectedItem.Text;
dr["Rate"] = ddltxtB2clRate.SelectedItem.Text.ToString();
dr["Taxable Value"] = txtB2clTaxableValue.Text.Trim();
dr["Cess Amount"] = txtB2clCessAmount.Text.Trim() == "" ? "0" : txtB2clCessAmount.Text.Trim();
dr["E-Commerce GSTIN"] = txtB2clEcommerceGstin.Text.ToString();
string GSTIN = Session["ClientGSTNNO"].ToString();
int stateCode = Convert.ToInt32(ddlB2clPlaceOfSupply.SelectedValue);
int gst = Convert.ToInt32(GSTIN.Substring(0, 2));
if (gst != stateCode)
{
dr["ItemIGSTValue"] = ((Convert.ToDecimal(ddltxtB2clRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtB2clTaxableValue.Text)) / 100).ToString();
dr["ItemCGSTValue"] = "0";
dr["ItemSGSTValue"] = "0";
}
else
{
dr["ItemIGSTValue"] = "0";
dr["ItemCGSTValue"] = (((Convert.ToDecimal(ddltxtB2clRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtB2clTaxableValue.Text)) / 100) / 2).ToString();
dr["ItemSGSTValue"] = (((Convert.ToDecimal(ddltxtB2clRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtB2clTaxableValue.Text)) / 100) / 2).ToString();;
}
dt.Rows.Add(dr);
ViewState["B2ClTable"] = dt;
GridB2cl.DataSource = dt;
GridB2cl.DataBind();
ClearB2cl();
}
protected void btnAdd_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
lblsmsSave.Text = "";
lblErrorMsg.Text = "";
if (ViewState["B2BTable"] == null)
{
dt.Columns.Add("GSTIN/UIN of Recipient");
dt.Columns.Add("Invoice Number");
dt.Columns.Add("Invoice date");
dt.Columns.Add("Invoice Value");
dt.Columns.Add("PlaceOfSupplyCode");
dt.Columns.Add("Place Of Supply");
dt.Columns.Add("Reverse Charge");
dt.Columns.Add("Invoice Type");
dt.Columns.Add("E-Commerce GSTIN");
dt.Columns.Add("Rate");
dt.Columns.Add("Taxable Value");
dt.Columns.Add("Cess Amount");
dt.Columns.Add("ItemIGSTValue");
dt.Columns.Add("ItemCGSTValue");
dt.Columns.Add("ItemSGSTValue");
}
else
{
dt = (DataTable)ViewState["B2BTable"];
}
//if (txtGstin.Text != "")
//{
// foreach (DataRow row in dt.Rows)
// {
// if (row["GSTNNO"].ToString() == txtGstin.Text)
// {
// lblErrorMsg.Text = "GSTIN Already Added.";
// txtGstin.Focus();
// return;
// }
// }
//}
// string GSTIN = Session["ClientGSTNNO"].ToString();
DataRow dr = dt.NewRow();
dr["GSTIN/UIN of Recipient"] = txtGstin.Text;
dr["Invoice Number"] = txtInvoiceNo.Text;
dr["Invoice date"] = CLSCommon.DateFormat(txtInvoiceDate.Text);
dr["Invoice Value"] = txtInvoiceValue.Text;
dr["PlaceOfSupplyCode"] = ddlPlaceSupply.Text;
dr["Place Of Supply"] = ddlPlaceSupply.SelectedItem.Text;
if (ddlReverseCharge.SelectedItem.Text == "Yes")
{
dr["Reverse Charge"] = "Y";
}
else
{
dr["Reverse Charge"] = "N";
}
dr["Invoice Type"] = ddlB2bInvoiceType.SelectedItem.Text;
dr["E-Commerce GSTIN"] = txtEcommerceGstin.Text.ToString();
dr["Rate"] = ddltxtTaxRate.SelectedItem.Text.ToString();
dr["Taxable Value"] = txtTaxableValue.Text;
dr["Cess Amount"] = txtCessAmount.Text.Trim() == "" ? "0" : txtCessAmount.Text.Trim();
string GSTIN = Session["ClientGSTNNO"].ToString();
int stateCode = Convert.ToInt32(ddlPlaceSupply.SelectedValue);
int gst = Convert.ToInt32(GSTIN.Substring(0, 2));
if (gst != stateCode)
{
dr["ItemIGSTValue"] = ((Convert.ToDecimal(ddltxtTaxRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtTaxableValue.Text)) / 100).ToString();
dr["ItemCGSTValue"] = "0";
dr["ItemSGSTValue"] = "0";
}
else
{
dr["ItemIGSTValue"] = "0";
dr["ItemCGSTValue"] = (((Convert.ToDecimal(ddltxtTaxRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtTaxableValue.Text)) / 100) / 2).ToString();
dr["ItemSGSTValue"] = (((Convert.ToDecimal(ddltxtTaxRate.SelectedItem.Text.ToString()) * Convert.ToDecimal(txtTaxableValue.Text)) / 100) / 2).ToString();;
}
dt.Rows.Add(dr);
ViewState["B2BTable"] = dt;
grdB2B.DataSource = dt;
grdB2B.DataBind();
ClearB2B();
}
protected void Unnamed_Click(object sender, EventArgs e)
{
if (ViewState["GSTINTable"] == null)
{
lbl.Text = "Add Atleast One GSTIN";
txtGSTINNO.Focus();
return;
}
else
{
DataTable dtGSTN = (DataTable)ViewState["GSTINTable"];
if (dtGSTN.Rows.Count == 0)
{
lbl.Text = "Add Atleast One GSTIN";
txtGSTINNO.Focus();
return;
}
else
{
objpl = new PL_ClientProfile();
objpl.Ind = 1;
objpl.StateCode = int.Parse(ddlState.SelectedValue.ToString());
objpl.DistrictCode = 0;
objpl.CityCode = int.Parse(ddlCity.SelectedIndex < 0 ? "0" : ddlCity.SelectedValue.ToString());
objpl.ClientType = 0;
objpl.FirmName = txtOrganisationName.Text;
objpl.ClientNameE = txtName.Text;
objpl.ClientNameH = txtName.Text;
objpl.ClientDesg = txtDesignation.Text;
objpl.ClientMobileNo1 = txtMobileNo.Text;
objpl.ClientMobileNo2 = txtAlternateMobile.Text;
objpl.EmailId = txtEmailId.Text;
objpl.ClientPANNo = txtPanNo.Text.ToUpper();
objpl.AadhaarNo = txtAadhaarNo.Text;
objpl.AlternateContactName = txtAlternatePerson.Text;
objpl.AlternateMobileNo = txtAlternateMobile.Text;
objpl.AlternateEmailid = txtEmailId.Text;
objpl.DependentInd = 1;
objpl.CACode = int.Parse(Session["CACode"].ToString());
objpl.CACodeOdp = 0;
objpl.ActiveInd = 1;
objpl.LoginUserCode = 0;
objpl.RefnoOtherSys = 0;
objpl.FullAddress = txtFullAddress.Text;
objpl.PinCode = txtPinCode.Text;
objpl.RegNo = 0;
objpl.AggrTurnOver = txtAggrTurnOver.Text.Trim() == "" ? "0" : txtAggrTurnOver.Text.Trim();
objpl.AggrTurnOverAprJun = txtAggrTurnOverAprJun.Text.Trim() == "" ? "0" : txtAggrTurnOverAprJun.Text.Trim();
objpl.CompAppInd = coyes.Checked ? 1 : 2;
objpl.CompAppDate = coyes.Checked ? CLSCommon.DateFormat(txtCoDate.Text) : "";
objpl.NonResiTaxInd = rpyes.Checked ? 1 : 2;
objpl.NonResFromDate = rpyes.Checked ? CLSCommon.DateFormat(txtResDateFrom.Text) : "";
objpl.NonResToDate = rpyes.Checked ? CLSCommon.DateFormat(txtResDateTo.Text) : "";
DataTable dt = CLSCommon.CallApiPost("api/ClientProfile/SaveClientData/", objpl);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["res"].ToString() == "1")
{
Session["ErrorMsg"] = dt.Rows[0]["ErrorMsg"].ToString();
int ClientCode = int.Parse(dt.Rows[0]["ClientCode"].ToString());
int ClientCodeOdp = int.Parse(dt.Rows[0]["ClientCodeOdp"].ToString());
foreach (DataRow dr in dtGSTN.Rows)
{
PL_InsertClientGSTIN objplInsertGSTIN = new PL_InsertClientGSTIN();
objplInsertGSTIN.Ind = 1;
objplInsertGSTIN.ClientCode = ClientCode;
objplInsertGSTIN.ClientCodeOdp = ClientCodeOdp;
objplInsertGSTIN.OdpGSTNo = 0;
objplInsertGSTIN.GSTNNO = dr["GSTNNO"].ToString();
objplInsertGSTIN.PANNO = txtPanNo.Text;
objplInsertGSTIN.TradeName = dr["TradeName"].ToString();
objplInsertGSTIN.DateOfReg = dr["DateOfReg"].ToString() == "" ? "" : CLSCommon.DateFormat(dr["DateOfReg"].ToString());
objplInsertGSTIN.ContactAddress = dr["ContactAddress"].ToString();
objplInsertGSTIN.StateCode = dr["StateCode"].ToString();
objplInsertGSTIN.ContactStateCode = int.Parse(dr["StateCode"].ToString());
objplInsertGSTIN.ContactCityCode = int.Parse(dr["CityCode"].ToString());
objplInsertGSTIN.AuthorisedSignetory = dr["AuthorisedSignetory"].ToString();
objplInsertGSTIN.ClientUserID = dr["GSTUserName"].ToString();
objplInsertGSTIN.Designation = dr["Designation"].ToString();
objplInsertGSTIN.ContactMobileNo = dr["ContactMobileNo"].ToString();
objplInsertGSTIN.ContactEmailid = dr["ContactEmailid"].ToString();
objplInsertGSTIN.AadhaarNo = dr["AadhaarNo"].ToString();
objplInsertGSTIN.RegisteredAsISD = int.Parse(dr["ISD"].ToString());
objplInsertGSTIN.RegisteredAsTDS = int.Parse(dr["TDS"].ToString());
objplInsertGSTIN.WhetherEcommerceOperator = int.Parse(dr["TCS"].ToString());
objplInsertGSTIN.Priority = 1;
objplInsertGSTIN.ContactPersonName = txtName.Text; // dr["TradeName"].ToString();
objplInsertGSTIN.ActiveInd = 1;
objplInsertGSTIN.Allotmentind = 0;
objplInsertGSTIN.AllotmentCACode = int.Parse(Session["CaCode"].ToString());
objplInsertGSTIN.AllotmentCACodeOdp = int.Parse(Session["CaCodeOdp"].ToString());
objplInsertGSTIN.CompositeInd = coyes.Checked ? 1 : 2;
objplInsertGSTIN.NRIInd = rpyes.Checked ? 1 : 2;
DataTable dtRes = CLSCommon.CallApiPost("api/ClientProfile/SaveClientGSTIN/", objplInsertGSTIN);
}
clear();
Response.Redirect("frmSubmitted.aspx");
}
else
{
lblErrorMsg.Text = dt.Rows[0]["Message"].ToString();
}
}
}
}
}
}
DataTable GetDTFromExcel2003(string extractFile, int datecol1, int datecol2, bool ExtraCol, string SheetName, int Rowindex, IWorkbook workbook, List <int> lstint)
{
DataTable dt = new DataTable();
IFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(workbook);
DataFormatter dataFormatter = new HSSFDataFormatter(new CultureInfo("en-US"));
ISheet sheet = workbook.GetSheet(SheetName);
if (sheet != null)
{
while (sheet.GetRow(Rowindex) != null)
{
dt.Rows.Add();
string RowsValue = "";
for (int j = 0; j < sheet.GetRow(Rowindex).Cells.Count; j++)
{
var cell = sheet.GetRow(Rowindex).GetCell(j);
if (cell != null)
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric || cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
if (Rowindex == 3)
{
dt.Columns.Add(sheet.GetRow(Rowindex).GetCell(j).NumericCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", ""), typeof(string));
RowsValue += sheet.GetRow(Rowindex).GetCell(j).NumericCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", "");
}
else
{
if (datecol1 == j || datecol2 == j)
{
dt.Rows[Rowindex - 4][j] = DateTime.FromOADate(sheet.GetRow(Rowindex).GetCell(j).NumericCellValue).ToString("yyyy/MM/dd");
RowsValue += sheet.GetRow(Rowindex).GetCell(j).NumericCellValue;
}
else
{
dt.Rows[Rowindex - 4][j] = sheet.GetRow(Rowindex).GetCell(j).NumericCellValue.ToString().Replace(",", "").Trim();
RowsValue += sheet.GetRow(Rowindex).GetCell(j).NumericCellValue;
if (ExtraCol)
{
dt.Rows[Rowindex - 4]["ItemIGSTValue"] = 0;
dt.Rows[Rowindex - 4]["ItemcGSTValue"] = 0;
dt.Rows[Rowindex - 4]["ItemSGSTValue"] = 0;
}
}
}
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
if (Rowindex == 3)
{
dt.Columns.Add(sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", ""), typeof(string));
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", "");
}
else
{
DateTime date;
if (datecol1 == j || datecol2 == j)
{
if (sheet.GetRow(Rowindex).GetCell(j).StringCellValue == "")
{
dt.Rows[Rowindex - 4][j] = "";
}
else
{
if (sheet.GetRow(Rowindex).GetCell(j).StringCellValue.Length == 10)
{
if (!DateTime.TryParse(CLSCommon.DateFormat(sheet.GetRow(Rowindex).GetCell(j).StringCellValue), new System.Globalization.CultureInfo("en-GB"), System.Globalization.DateTimeStyles.None, out date))
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sheet.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (Rowindex + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Date Format Must Be \"DD/MM/YYYY\"";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
else
{
dt.Rows[Rowindex - 4][j] = DateTime.Parse(CLSCommon.DateFormat(sheet.GetRow(Rowindex).GetCell(j).StringCellValue)).ToString("yyyy/MM/dd");
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue;
}
}
else if ((sheet.GetRow(Rowindex).GetCell(j).StringCellValue.Length == 11 || sheet.GetRow(Rowindex).GetCell(j).StringCellValue.Length == 9) &&
((DateTime.TryParseExact((sheet.GetRow(Rowindex).GetCell(j).StringCellValue), "dd-MMM-yyyy", null, DateTimeStyles.None, out date)) ||
(DateTime.TryParseExact((sheet.GetRow(Rowindex).GetCell(j).StringCellValue), "dd-MMM-yy", null, DateTimeStyles.None, out date))
))
{
dt.Rows[Rowindex - 4][j] = DateTime.Parse(sheet.GetRow(Rowindex).GetCell(j).StringCellValue).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue;
}
else
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sheet.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (Rowindex + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Date Format Must Be \"DD/MM/YYYY\"";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
}
}
else
{
var res = from k in lstint
where k == j
select k;
if (res.Count() > 0)
{
double parsedValue;
if (!double.TryParse(sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim(), out parsedValue) && sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim() != "")
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sheet.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (Rowindex + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Invalid Numeric value";//"Invalid Numberic value";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
else
{
dt.Rows[Rowindex - 4][j] = sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim();
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue;
}
}
else
{
dt.Rows[Rowindex - 4][j] = sheet.GetRow(Rowindex).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim();
RowsValue += sheet.GetRow(Rowindex).GetCell(j).StringCellValue;
}
}
if (ExtraCol)
{
dt.Rows[Rowindex - 4]["ItemIGSTValue"] = 0;
dt.Rows[Rowindex - 4]["ItemcGSTValue"] = 0;
dt.Rows[Rowindex - 4]["ItemSGSTValue"] = 0;
}
}
}
}
}
if (Rowindex == 3 && ExtraCol)
{
dt.Columns.Add("ItemIGSTValue");
dt.Columns.Add("ItemCGSTValue");
dt.Columns.Add("ItemSGSTValue");
}
Rowindex++;
}
}
return(RemoveEmptyRows(dt));
}
private DataTable GetDTFromExcel2007(String Path, int colindex1, int colindex2, bool ExtraCol, string SheetName, int i, XSSFWorkbook wb, List <int> lstint)
{
XSSFSheet sh;
DataTable DT = new DataTable();
DT.Rows.Clear();
DT.Columns.Clear();
sh = (XSSFSheet)wb.GetSheet(SheetName);
if (sh != null)
{
while (sh.GetRow(i) != null)
{
string RowsValue = "";
DT.Rows.Add();
for (int j = 0; j < 37; j++)
{
var cell = sh.GetRow(i).GetCell(j);
if (cell != null)
{
if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric || cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
if (i == 3)
{
DT.Columns.Add(sh.GetRow(i).GetCell(j).NumericCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", ""), typeof(string));
RowsValue += sh.GetRow(i).GetCell(j).NumericCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", "");
}
else
{
if (colindex1 == j || colindex2 == j)
{
DT.Rows[i - 4][j] = DateTime.FromOADate(sh.GetRow(i).GetCell(j).NumericCellValue).ToString("yyyy/MM/dd");
RowsValue += sh.GetRow(i).GetCell(j).NumericCellValue;
}
else
{
DT.Rows[i - 4][j] = sh.GetRow(i).GetCell(j).NumericCellValue.ToString().Replace(",", "").Trim();
RowsValue += sh.GetRow(i).GetCell(j).NumericCellValue;
if (ExtraCol)
{
DT.Rows[i - 4]["ItemIGSTValue"] = 0;
DT.Rows[i - 4]["ItemcGSTValue"] = 0;
DT.Rows[i - 4]["ItemSGSTValue"] = 0;
}
}
}
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
if (i == 3)
{
DT.Columns.Add(sh.GetRow(i).GetCell(j).StringCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", ""), typeof(string));
RowsValue += sh.GetRow(i).GetCell(j).StringCellValue.ToString().Replace(" ", "").Replace("(S+C+I+Cess)", "").Replace("(Km)", "");
}
else
{
DateTime date;
if (colindex1 == j || colindex2 == j)
{
if (sh.GetRow(i).GetCell(j).StringCellValue == "")
{
DT.Rows[i - 4][j] = "";
}
else
{
if (sh.GetRow(i).GetCell(j).StringCellValue.Length == 10)
{
if (!DateTime.TryParse(CLSCommon.DateFormat(sh.GetRow(i).GetCell(j).StringCellValue), new System.Globalization.CultureInfo("en-GB"), System.Globalization.DateTimeStyles.None, out date))
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sh.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (i + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Date Format Must Be \"DD/MM/YYYY\"";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
else
{
DT.Rows[i - 4][j] = DateTime.Parse(CLSCommon.DateFormat(sh.GetRow(i).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim())).ToString("yyyy/MM/dd");
RowsValue += sh.GetRow(i).GetCell(j).StringCellValue;
}
}
else if ((sh.GetRow(i).GetCell(j).StringCellValue.Length == 11 || sh.GetRow(i).GetCell(j).StringCellValue.Length == 9) &&
((DateTime.TryParseExact((sh.GetRow(i).GetCell(j).StringCellValue), "dd-MMM-yyyy", null, DateTimeStyles.None, out date)) ||
(DateTime.TryParseExact((sh.GetRow(i).GetCell(j).StringCellValue), "dd-MMM-yy", null, DateTimeStyles.None, out date))
))
{
DT.Rows[i - 4][j] = DateTime.Parse(sh.GetRow(i).GetCell(j).StringCellValue).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
RowsValue += sh.GetRow(i).GetCell(j).StringCellValue;
}
else
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sh.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (i + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Date Format Must Be \"DD/MM/YYYY\"";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
}
}
else
{
var res = from k in lstint
where k == j
select k;
if (res.Count() > 0)
{
double parsedValue;
if (!double.TryParse(sh.GetRow(i).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim(), out parsedValue) && sh.GetRow(i).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim() != "")
{
dtError.Rows.Add(1);
dtError.Rows[dtError.Rows.Count - 1]["Section"] = sh.SheetName;
dtError.Rows[dtError.Rows.Count - 1]["LineNo"] = (i + 1).ToString();
dtError.Rows[dtError.Rows.Count - 1]["Error"] = "Invalid Numeric value";
dtError.Rows[dtError.Rows.Count - 1]["ColumnName"] = lstcol[j];
}
else
{
DT.Rows[i - 4][j] = sh.GetRow(i).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim();
RowsValue += sh.GetRow(i).GetCell(j).StringCellValue;
}
}
else
{
DT.Rows[i - 4][j] = sh.GetRow(i).GetCell(j).StringCellValue.ToString().Replace(",", "").Trim();
RowsValue += sh.GetRow(i).GetCell(j).StringCellValue;
}
}
if (ExtraCol)
{
DT.Rows[i - 4]["ItemIGSTValue"] = 0;
DT.Rows[i - 4]["ItemcGSTValue"] = 0;
DT.Rows[i - 4]["ItemSGSTValue"] = 0;
}
}
}
}
}
if (i == 3 && ExtraCol)
{
DT.Columns.Add("ItemIGSTValue");
DT.Columns.Add("ItemCGSTValue");
DT.Columns.Add("ItemSGSTValue");
}
i++;
}
}
return(RemoveEmptyRows(DT));
}