Thursday, May 5, 2011

Export data from excel sheet in

Here is the code to export data from excel sheet and put it on dataset in

Import this using statements first
using System.Configuration;
using System.Data.OleDb;
using System.Data;

//Retrieve the full path from the FileUpload control
       string filePath = fuPathOfFile.PostedFile.FileName;
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

        //string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        // Create the connection object
        OleDbConnection oledbConn = new OleDbConnection(connString);
            // Open connection
            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

            // Create new OleDbDataAdapter

            OleDbDataAdapter oleda = new OleDbDataAdapter();

            oleda.SelectCommand = cmd;

            // Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();

            // Fill the DataSet from the data extracted from the worksheet.

        catch (Exception ex)

            // Close connection

If you are working in a 64 bit machine with excel 2010, then there is a chance that you will get the below error:
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
Solution for this error is download the exe from the below url and install it. Then it will work fine.

1 comment: