Thursday, May 5, 2011

Export data from excel sheet in asp.net

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

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);
        try
        {
            // Open connection
            oledbConn.Open();
            // 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.
            oleda.Fill(ds);

        }
        catch (Exception ex)
        {

        }
        finally
        {
            // Close connection
            oledbConn.Close();
        } 

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.
http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=7554f536-8c28-4598-9b72-ef94e038c891&displayLang=en

1 comment: