Click here to Skip to main content
15,918,108 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,


I need to save the excel sheet data to data table

Can any one help me how can I do this?


Thanks
John
Posted

You could have easily searched the internet and found countless examples of this, so I presume it must be broken right now. Fortunately I just happen to have a link that will help if it gets back up and running.

Click me![^]
 
Share this answer
 
You can use the below approach for inserting xls data into database.

C#
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
                    {
                        path = openFileDialog.InitialDirectory + openFileDialog.FileName;
                    }
                    string constring = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0 Xml";
                    OleDbConnection con = new OleDbConnection(constring);
                    try
                    {
                        con.Open();
                        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        String[] excelSheets = new String[dt.Rows.Count];
                        i = 0;
                        foreach (DataRow row in dt.Rows)
                        {
                            excelSheets[i] = row["TABLE_NAME"].ToString();
                            i++;
                        }
 
                        for (int j = 0; j < excelSheets.Length; j++)
                        {
                            string query = "SELECT * FROM [" +excelSheets[j]+ "]; ";
                            OleDbDataAdapter adp = new OleDbDataAdapter(query, constring);
 
                            DataSet ds = new DataSet();
                            string hCode = string.Empty;
                            string description = string.Empty;
                            adp.Fill(ds);
 
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                hCode = Convert.ToString(dr[1]);
                                description = Convert.ToString(dr[2]);
                                if (!(string.IsNullOrEmpty(hCode) && string.IsNullOrEmpty(description)))
                                {
 
                                    dboject.InsertSiteNameIntoDatabase(hCode, description);
 
                                }
                            }
 

                        }
                        lblDisplay.Text = "Inserted successfully";
                    
                    }
                    catch (Exception ex)
                    {
                        dboject.VMSLog(ex.Message);
 
                    }
                    finally
                    {
                        con.Close();
 
                    }
 
Share this answer
 
Comments
Nooa 21-Nov-13 19:42pm    
as a small information: the charlength per cell is 256 here. is still havn't figured out how to fix that.
keerth516 22-Nov-13 3:09am    
Can you explain what you are trying to achieve it??

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