private void Import_To_Grid(string FilePath, string Extension) { string conStr = ""; switch (Extension) { case ".xls": //Excel 97-03 conStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", FilePath); break; case ".xlsx": //Excel 07 conStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", FilePath); break; } conStr = String.Format(conStr, FilePath); OleDbConnection connExcel = new OleDbConnection(conStr); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oda = new OleDbDataAdapter(); cmdExcel.Connection = connExcel; //Get the name of First Sheet connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName; connExcel.Close(); try { for (int k = 0; k < dtExcelSchema.Rows.Count; k++) { SheetName = dtExcelSchema.Rows[k]["TABLE_NAME"].ToString(); DataTable dt = new DataTable(); //Read Data from First Sheet connExcel.Open(); cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"; oda.SelectCommand = cmdExcel; oda.Fill(dt); connExcel.Close(); InventoryClass obj1 = new InventoryClass(); //Check For type of Invoice if (btnSalesInvoice.Checked == true) //Sales Invoice { string str = ""; for (int i = 0; i < dt.Rows.Count; i++) { string CurrStr = dt.Rows[i][0].ToString(); if (str != CurrStr) { str = CurrStr; obj1.Invoice_No = CurrStr; obj1.Invoice_Date = System.Convert.ToDateTime(GenUtil.str2DDMMYYYYExcel(dt.Rows[i][1].ToString())); obj1.SSA_OR_SSI_NAME = dt.Rows[i][2].ToString(); obj1.SALES_RETURN_NUMBER = dt.Rows[i][3].ToString(); obj1.SALES_RETURN_GR_DATE = GenUtil.str2DDMMYYYYExcel(dt.Rows[i][4].ToString()); obj1.SECONDARY_CUSTOMER_NUMBER = dt.Rows[i][5].ToString(); obj1.SEC_CUSTOMER_NAME = dt.Rows[i][6].ToString(); obj1.STOCKIST_GST_NUM = dt.Rows[i][7].ToString(); obj1.CUSTOMER_GST_NUM = dt.Rows[i][8].ToString(); obj1.SEC_CUSTOMER_HIERARCHY = dt.Rows[i][9].ToString(); obj1.SECONDARY_CUSTOMER_CITY = dt.Rows[i][10].ToString(); obj1.INVOICE_TYPE = dt.Rows[i][11].ToString(); obj1.SGST_AMOUNT = dt.Rows[i][24].ToString(); obj1.CGST_AMOUNT = dt.Rows[i][25].ToString(); obj1.IGST_AMOUNT = dt.Rows[i][26].ToString(); obj1.TOTAL_TAX_AMOUNT = dt.Rows[i][27].ToString(); obj1.NET_AMOUNT = dt.Rows[i][28].ToString(); obj1.ADHOC_DISCOUNT = dt.Rows[i][29].ToString(); obj1.HO_AI_DISC_JULY17 = dt.Rows[i][30].ToString(); obj1.HYUNDAI_DIS_17_18 = dt.Rows[i][31].ToString(); obj1.MGO_VC_DIS_16_17 = dt.Rows[i][32].ToString(); obj1.SALES_TYPE = dt.Rows[i][33].ToString(); obj1.CHALLAN_NO = Convert.ToInt32(dt.Rows[i][34]); obj1.VEHICLE_NO = dt.Rows[i][35].ToString(); obj1.Grand_Total = dt.Rows[i][36].ToString(); obj1.Discount = dt.Rows[i][37].ToString(); obj1.Discount_Type = dt.Rows[i][38].ToString(); obj1.Promo_Scheme = dt.Rows[i][39].ToString(); obj1.Slip_No = dt.Rows[i][40].ToString(); obj1.Cash_Discount = dt.Rows[i][41].ToString(); obj1.Cash_Disc_Type = dt.Rows[i][42].ToString(); obj1.schdiscount = dt.Rows[i][43].ToString(); obj1.foediscount = dt.Rows[i][44].ToString(); obj1.foediscounttype = dt.Rows[i][45].ToString(); obj1.foediscountrs = dt.Rows[i][46].ToString(); obj1.SecSPDisc = dt.Rows[i][47].ToString(); obj1.SALEABLE_QTY_IN_LTR_OR_KG = dt.Rows[i][21].ToString(); obj1.InsertSalesTemp(); //Save invoice info } if (str == CurrStr) { obj1.Invoice_No = CurrStr; obj1.PRODUCT_CODE = Convert.ToInt32(dt.Rows[i][12]); obj1.PRODUCT_NAME = dt.Rows[i][13].ToString(); obj1.HSN_NO = Convert.ToInt32(dt.Rows[i][14]); obj1.PACK_CODE = Convert.ToInt32(dt.Rows[i][15]); obj1.PACK_NAME = dt.Rows[i][16].ToString(); obj1.SKU_CODE = Convert.ToInt32(dt.Rows[i][17]); obj1.SALEABLE_QTY = dt.Rows[i][18].ToString(); obj1.FREE_QTY = dt.Rows[i][19].ToString(); obj1.SAMPLE_QTY = dt.Rows[i][20].ToString(); obj1.SALEABLE_QTY_IN_LTR_OR_KG = ""; obj1.FREE_QTY_IN_LTR_OR_KG = dt.Rows[i][22].ToString(); obj1.SAMPLE_QTY_IN_LTR_OR_KG = dt.Rows[i][23].ToString(); obj1.SecInvoiceNo = GetInvoiceNo(); int m = i; obj1.sno = m += 1; obj1.Invoice_Date = System.Convert.ToDateTime(GenUtil.str2DDMMYYYYExcel(dt.Rows[i][1].ToString())); obj1.sch = dt.Rows[i][49].ToString(); obj1.foe = dt.Rows[i][48].ToString(); obj1.schtype = ""; obj1.SecSPDisc = ""; obj1.SecSPDiscType = ""; obj1.foediscounttype = ""; obj1.InsertSalesTempProd(); //Save Product Details } } MessageBox.Show("Data Inserted for sheet " + SheetName + " Successfully"); } #region Purchase Invoice else if (btnPurchaseInvoice.Checked == true) //Purchase Invoice { string str = ""; for (int i = 1; i < dt.Rows.Count; i++) { string CurrStr = dt.Rows[i][0].ToString(); if (str != CurrStr) { str = CurrStr; obj1.Invoice_No = CurrStr; obj1.Invoice_Date = System.Convert.ToDateTime(GenUtil.str2DDMMYYYY(dt.Rows[i][1].ToString())); obj1.Challan_Id = Int32.Parse(dt.Rows[i][3].ToString()); obj1.City_Name = dt.Rows[i][7].ToString(); obj1.Geopgrophical_State = dt.Rows[i][8].ToString(); obj1.Stockist_SAP_Code = dt.Rows[i][9].ToString(); obj1.Stockist_Name = dt.Rows[i][10].ToString(); obj1.Source_Of_Supply = dt.Rows[i][11].ToString(); obj1.GSTIN = dt.Rows[i][12].ToString(); obj1.HSN_Code = dt.Rows[i][13].ToString(); obj1.Bill_Type = dt.Rows[i][24].ToString(); obj1.Mode_of_Payment = dt.Rows[i][25].ToString(); obj1.VehicleNo = dt.Rows[i][26].ToString(); obj1.Grand_Total = dt.Rows[i][47].ToString(); obj1.discount = dt.Rows[i][40].ToString(); obj1.Discount_Type = dt.Rows[i][41].ToString(); obj1.NET_AMT_IN_PAISE = dt.Rows[i][23].ToString(); obj1.Promo_Scheme = dt.Rows[i][46].ToString(); obj1.Cash_Discount = dt.Rows[i][39].ToString(); obj1.Cash_Disc_Type = dt.Rows[i][38].ToString(); obj1.IGST_Tax = dt.Rows[i][17].ToString(); obj1.Tradeval = dt.Rows[i][35].ToString(); obj1.Trade_Discount = dt.Rows[i][36].ToString(); obj1.Ebird = dt.Rows[i][30].ToString(); obj1.Ebird_Discount = dt.Rows[i][31].ToString(); obj1.Foc_Discount = dt.Rows[i][28].ToString(); obj1.Foc_Discount_Type = dt.Rows[i][29].ToString(); obj1.fixed_Discount = dt.Rows[i][37].ToString(); obj1.fixed_Discount_Type = dt.Rows[i][33].ToString(); obj1.Qty_Ltr_Kg = dt.Rows[i][3].ToString();//total qty ltr obj1.New_fixeddisc = dt.Rows[i][43].ToString(); obj1.New_fixeddiscAmount = dt.Rows[i][44].ToString(); obj1.CGST_Tax = dt.Rows[i][16].ToString(); obj1.SGST_Tax = dt.Rows[i][15].ToString(); obj1.Birdless = dt.Rows[i][32].ToString(); obj1.Tradeless = dt.Rows[i][34].ToString(); obj1.InsertPurchaseMasterTemp(); //Save Purchase invoice info } if (str == CurrStr) { obj1.Invoice_No = CurrStr; obj1.Item_Qty = Int32.Parse(dt.Rows[i][2].ToString()); obj1.Qty_Ltr_Kg = dt.Rows[i][3].ToString(); obj1.SKU_Code = Int32.Parse(dt.Rows[i][5].ToString()); obj1.SKU_Name = dt.Rows[i][6].ToString(); obj1.RSP_CDP = dt.Rows[i][14].ToString(); obj1.SGST_Tax = dt.Rows[i][15].ToString(); obj1.CGST_Tax = dt.Rows[i][16].ToString(); obj1.IGST_Tax = dt.Rows[i][17].ToString(); obj1.Total_Tax = dt.Rows[i][18].ToString(); obj1.ZSSD = dt.Rows[i][19].ToString(); obj1.ZCON = dt.Rows[i][20].ToString(); obj1.ZDFI = dt.Rows[i][21].ToString(); obj1.ZDCB = dt.Rows[i][22].ToString(); obj1.NET_AMT_IN_PAISE = dt.Rows[i][23].ToString(); obj1.SecInvoiceNo = GetInvoiceNo1(); obj1.Invoice_Date = System.Convert.ToDateTime(GenUtil.str2DDMMYYYY(dt.Rows[i][1].ToString())); obj1.PACK_NAME = dt.Rows[i][48].ToString(); obj1.foc = dt.Rows[i][51].ToString(); int m = 0; obj1.sno = m += 1; obj1.InsertPurchaseDetailsTemp(); //Save Purchase invoice Details } } MessageBox.Show("Data Inserted for sheet " + SheetName + " Successfully"); } #endregion else { MessageBox.Show("Please Select Type Of Invoice"); return; } } } catch (Exception ex) { throw; } }