protected void btnImport_Click(object sender, EventArgs e) { SaveExcel_Database(); DataTable dt = (DataTable)ViewState["ItemDetl"]; DataTable dtdistinct = dt.AsEnumerable().GroupBy(x => x["ID"]).Select(x => x.First()).CopyToDataTable(); dtdistinct.AsEnumerable().ToList().ForEach(r => r.SetField("Name", dt.AsEnumerable().Where(p=>p["ID"].ToString()==r["ID"].ToString()).Count())); } private void SaveExcel_Database() { try { string path = ""; string path1 = ""; if (fld.HasFile) { string str = "~/Inventory/UploadSheet"; string filename = fld.FileName; if (!Directory.Exists(Server.MapPath(str))) { Directory.CreateDirectory(Server.MapPath(str)); //Directory.SetAccessControl( path1 = Server.MapPath(str + "/"); path = path1 + filename; fld.SaveAs(path1 + filename); } else { path1 = Server.MapPath(str + "/"); path = path1 + filename; fld.SaveAs(path1 + filename); } string Extension = Path.GetExtension(fld.PostedFile.FileName); BudgetExcelUpload(Extension, path); } if ((System.IO.File.Exists(path1))) { System.IO.File.Delete(path1); } if ((System.IO.File.Exists(path))) { System.IO.File.Delete(path); } } catch { } } private string GetExcelSheetNames(string conStr) { OleDbConnection objConn = null; System.Data.DataTable dt = null; string aa = ""; try { objConn = new OleDbConnection(conStr); // Open connection with the database. objConn.Open(); // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString().Trim(); i++; } // Loop through all of the sheets if you want too… for (int j = 0; j < excelSheets.Length; j++) { // Query each excel sheet. aa = dt.Rows[0][2].ToString().Trim(); } } catch { } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } return aa; } public void BudgetExcelUpload(string Extesion, string path) { DataTable dtableItem = new DataTable(); try { string Extension1 = Extesion; string path1 = path; string conStr = ""; switch (Extension1) { case ".xls": //Excel 97-03 conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + path1 + ";" + @"Extended Properties=" + Convert.ToChar(34).ToString().Trim() + @"Excel 8.0;HDR=YES" + Convert.ToChar(34).ToString().Trim(); break; case ".xlsx": //Excel 07 conStr = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + path1 + ";" + @"Extended Properties=" + Convert.ToChar(34).ToString().Trim() + @"Excel 12.0;HDR=YES" + Convert.ToChar(34).ToString().Trim(); break; } //DataTable dtableSalaryMaster = new DataTable(); OleDbConnection oconn = new OleDbConnection(conStr); string worksheetName = GetExcelSheetNames(conStr); OleDbDataAdapter cmd = new OleDbDataAdapter(); DataSet excelDataSet1 = new DataSet(); //====================================================== // FETCHING EMPLOYEE DETAILS FROM EXCEL SHEET //====================================================== cmd = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + worksheetName + "] ", oconn); cmd.Fill(dtableItem); ViewState["ItemDetl"] = dtableItem; } catch { } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)