protected void btnClear_Click(object sender, EventArgs e)
{
lblmsg.Text = "";
GridDdl.DataSource = null;
GridDdl.DataBind();
InternalExcellstHeader.Clear();
ExcellstHeader.Clear();
}
public void InternalExcelFileColumn()
{
string FileName = "Salesdataformat.xlsx";
string Extension = ".xlsx";
string FolderPath = "~/ExcelFile/";
string FilePath = Server.MapPath(FolderPath + FileName);
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
.ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, "Yes");
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
connExcel.Open();
//cmdExcel.CommandText = "SELECT [SNO#],[Month],[Year],[ClientGstin] ,[Legal Name] ,[TradeName] ,[AggrturnOver] ,[AggrturnOverAprJune] ,[ClientStatecode] ,[Billgstin] ,[InvoiceNo] ,[InvoiceDate] ,[InvoiceValue] ,[GoodsSacInd] ,[HsnSacCode] ,[HsnSacDesc] ,[ItemRate] ,[ItemTaxableVal] ,[IgstTax] ,[CgstTax] ,[SGstTax] ,[Cess] ,[SuppyStatecode] ,[Suppystateename] ,[ReverseChargInd] ,[E-commerceInd] ,[EcommGstin] ,[shippBillExportNo] ,[shippBillExportDate] ,[ExportInd] ,[NillExmNonGstInd] ,[SaleAmdRecInd] ,[OrignalInvoNo] ,[OrignalGstIn] ,[OrignalInvoDt] ,[AdvanceSalInd] ,[AdvRecvOrAdjustInd] ,[ind] From [Sheet1$] where [Month] IS NOT NULL"; // fix column name
//oda.SelectCommand = cmdExcel;
oda = new OleDbDataAdapter("Select * FROM [Sheet1$] where 1=2", connExcel); // for all field
oda.Fill(dt);
connExcel.Close();
// excel_con.Close();
int colCount = dt.Columns.Count;
for (int i = 0; i < colCount; i++) // add Excel Header in List
{
string a = dt.Columns[i].ToString().Trim();
//a == "SNO#" || a == "Month" || a == "Year" || a == "ClientGstin" || a == "Legal Name" || a == "TradeName" || a == "AggrturnOver" || a == "AggrturnOverAprJune" || a == "ClientStatecode" || a == "ind" ||
if ((a.Substring(0, 1) == "F" && a.Length == 3))
{
}
else
{
InternalExcellstHeader.Add(a);
}
}
GridDdl.DataSource = InternalExcellstHeader;
GridDdl.DataBind();
}
public void GetField() // Get Table Field
{
dtField = new DataTable();
ds = new DataSet();
// ds = CLSCommon.CallApiGet("api/ExcelMapping/GetField?Ind=" + 1);
dtField = ds.Tables[1];
if (dtField.Rows.Count > 0)
{
GridDdl.DataSource = InternalExcellstHeader;
GridDdl.DataBind();
}
}