Parth Mashroo

Parth Mashroo

  • NA
  • 59
  • 17k

How to read excel sheet data and add those data to database?

Mar 30 2015 9:10 AM
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!!!

Answers (1)