Click here to Skip to main content
15,892,059 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi friends,

My requirements is, i wont to import the excel(.xls or .xlsx format) file from webpage and after that i wont to insert this excel file data into specific rows of tables in sql server database by using c#. So please provide me a code.

Thanks in advance
Posted
Updated 18-Mar-14 2:58am
v2
Comments
Kornfeld Eliyahu Peter 18-Mar-14 8:47am    
Have you done anything so far? Show some effort (code or searching)! As is it ain't a question...
Hareesh Malli 18-Mar-14 8:53am    
i googled for this solution. But i didn't find any exact solution for my problem. I found the solutions for uploading entire excel file into database in binary format. But i don't need it. I wont to insert the particular rows of excel file into database table. So can you please guide me to reach my solution
Kornfeld Eliyahu Peter 18-Mar-14 8:56am    
You may search for ODBC drivers for excel - using a connection over ODBC you will be able to open excel files like data tables a store them into SQL...
ZurdoDev 18-Mar-14 8:57am    
No, we won't just give you all the code. Where are you stuck?
Hareesh Malli 18-Mar-14 9:03am    
I am already done in importing excel file and retrieve the excel data rows into data set. Now i am struck at storing the data set data into database tables

C#
protected void btnupload_Click(object sender, EventArgs e)
        {
            if (!FileUpload1.HasFile || string.Compare(".xlsx", Path.GetExtension(FileUpload1.FileName), true) != 0)
            {
                base.DisplayAlert("Please select a proper EXCEL file to upload and try again.");
                return;
            }

            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            GC.Collect();
            OleDbConnection objConn = null;
            DataTable dt = null;
            try
            {
                // Looping through each Excel Sheet

                DataSet ds = new DataSet();
                String connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 12.0;";
                objConn = new OleDbConnection(connString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                if (dt == null)
                {
                    return;
                }

                foreach (DataRow schemaRow in dt.Rows)
                {
                    string sheet = schemaRow["TABLE_NAME"].ToString().Trim();

                    try
                    {

                        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "] ", objConn);
                        cmd.CommandType = CommandType.Text;
                        DataTable outputTable = new DataTable(sheet);
                        ds.Tables.Add(outputTable);
                        new OleDbDataAdapter(cmd).Fill(outputTable);
                        cmd.ExecuteNonQuery();
                        
                    }
 
Share this answer
 
v2
 
Share this answer
 
Comments
Hareesh Malli 18-Mar-14 9:08am    
Thanks friend
[no name] 18-Mar-14 9:20am    
Is it working for u?. if it is working accept as ans.
U are welcome!.

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