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!!!