none
Problem With Reading Excel Data using OLEDB. RRS feed

  • Question

  • Hi

    Envoirnment:

    1.  1) .net Framework 2.0
    2. IIS .net Framework 2.0
    3. VS 2008
    4. Office 2007

    Scenario:

    The requirement is creating a ASP.NET web service using C# which will update the value of an Excel file and then read the updated value from the Excel file using OLEDB. I am able to update the Excel file using user input value but i am not able to read the updated value . The updated value in this scenario is the value calculated using Formulas in EXCEL. I have also tried using Microsoft.Interop.Excel Object library. But i wanted to this using OLEDB.

    So please suggest some solution for this...

     

    Thanks in Advance.

    Swapnil

     

     

    Tuesday, July 19, 2011 11:07 AM

Answers

  • Hi Swapnil,

     

    Thanks for posting in the MSDN Forum.

    Please see whether the following snippet can solve your issue.

     

                string connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 14.0;HDR=YES;""", @"C:\**********************.xlsx");

     

                try

                {

                    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

     

                    using (DbConnection connection = factory.CreateConnection())

                    {

                        connection.ConnectionString = connectionString;

                        connection.Open();

                        DbDataAdapter adapter = factory.CreateDataAdapter();

                        DbCommand cmd = factory.CreateCommand();

                        cmd.CommandText = "Select 2 from [Sheet1$]";

                        adapter.SelectCommand = cmd;

                        DataSet ds = new DataSet();

                        adapter.Fill(ds);

                        foreach (DataRow dr in ds.Tables[0].Rows)

                        {

                            MessageBox.Show(dr[0].ToString());

                        }

                    }

                }

                catch (Exception ex)

                {

                    MessageBox.Show(ex.Message);

                }

     

    If you still have any questions, please feel free to let me know.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Friday, July 22, 2011 5:47 AM
    Moderator