i have so far tried so many methods starting with OLEDB,ODBC and it worked but only offline but when i published my website it showed many errors and was to slow and then i shifted to NPOI a third party freeware tool and the code that i used is as follows :
protected void btnthirdparty_Click(object sender, EventArgs e) { XSSFWorkbook wb; XSSFSheet sb; System.Data.DataTable dtnpoi = new System.Data.DataTable("Table"); dtnpoi.Columns.Add("SheetNames"); FileUpload1.SaveAs(HttpContext.Current.Server.MapPath(FileUpload1.FileName)); string file = Server.MapPath(FileUpload1.FileName); using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { wb = new XSSFWorkbook(fs); for (int s = 0; s < wb.Count; s++) { ddlsheets.Items.Add(wb.GetSheetAt(s).SheetName); } for (int i = 0; i < wb.Count; i++) { dtnpoi.Rows.Add((wb.GetSheetAt(i).SheetName)); } foreach (DataRow row in dtnpoi.Rows) { String[] excelsheets = new String[dtnpoi.Rows.Count]; int k = 0; excelsheets[k] = row["SheetNames"].ToString(); string company = excelsheets[k].ToString(); for (int j = 0; j < excelsheets[k].Length; j++) { ISheet sheet = wb.GetSheet(excelsheets[k]); for (int rowsh = 0; rowsh < sheet.LastRowNum; rowsh++) { if (sheet.GetRow(rowsh) != null) {
//--------------------The Problem Is Here---------------------------------------//
DataRow rw = dt.Rows.Add(sheet.GetRow(rowsh).GetCell(rowsh).StringCellValue); //ds.Tables[0]=null; // = sheet.GetRow(rowsh).GetCell(rowsh).ToString(); } }
for (int i = 0; i < dt.Rows.Count; i++)
{ DataRow rows = dt.Rows[i]; int coloum = dt.Columns.Count; string[] colom = new string[coloum];
for (int m = 0; m < dt.Rows.Count; m++)
{ colom[m] = row[m].ToString(); } db.USP_Insert_TestTable(company, colom[0], colom[1], colom[2], colom[3], colom[4], colom[5]); company = "";
}
}
k++; }
}
}
i get the names of the sheet properly but the problem is after getting those sheet names
i need to insert those sheet data to datatable so that i can save it to databasse but am unable to do so!!
pls help me with that!!!
Vithal WadjePosted Mar 30, 2015, 2:28 PM
http://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-excel-file-records-into-sql-server-database-using/