Click here to Skip to main content
15,894,825 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 shifteds to NPOI a third party free ware tool and the code that i used is as follows :

C#
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!!!
Posted
Updated 30-Mar-15 2:58am
v2

 
Share this answer
 
Try EPPlus.

Otherwise, looking at the code, I am puzzled.
You are trying to add a row to dt, which isn't initialised in the code you have shown.
Then in the comment afterwards you note that ds.Tables[0]= null. if dt = ds.Tables[0] (= null) then you definitely will fail to add a row to it.

Stumbling in the dark until you give us more information though.
 
Share this answer
 
v2
Comments
Richard Deeming 30-Mar-15 12:28pm    
NB: EPPlus only works with 2007-format files (.xlsx). If you need to work with 2003-format files (.xls), NPOI is the best option.
Parth Mashroo 31-Mar-15 1:29am    
Yes i have to only work with .xlsx file so can you provide an example for epplus but i have multiple sheets for example nearly 10 sheets will be there in one excel file so will it be helpful in reading all those sheet and inserting those sheets data into database then pl help me with some code!!
and one more doubt it wont make my website hefty and slow? bcz odbc and oledb takes so much time in importing of data nearly 9-12 sec to read all data and inserting of those data into database!!
Mark Farmiloe 30-Mar-15 12:33pm    
If he can work with xlsx then it will be easier and much more future-proof as it the format is open.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900