locked
asp.net and excel RRS feed

  • Question

  • User165199113 posted

    Hi,

    I am doing a asp.net 2.0 application, and connecting excel using oledb. I want to get a single cell value from excel. i have written query like this "Select * from Sheet1$A1".


    But i am getting error that Sheet1$A1 not found. Is there any mistake?

    I do not want to use excel object.

    Thanks,




    Tuesday, June 30, 2009 7:35 PM

All replies

  • User187056398 posted

    You haven't posted any code so no one can say what you may be doing incorrectly.

    You can try this:

          // using System.Data.OleDb
            OleDbConnection ExcelConection = null;
            OleDbCommand ExcelCommand = null;
            OleDbDataReader ExcelReader = null;
            OleDbConnectionStringBuilder OleStringBuilder = null;
    
            try
            {
                OleStringBuilder =
                    new OleDbConnectionStringBuilder(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';");
                OleStringBuilder.DataSource = MapPath(@"~\App_Datav\MyExcelWorksheet.xls");
    
                ExcelConection = new OleDbConnection();
                ExcelConection.ConnectionString = OleStringBuilder.ConnectionString;
    
                ExcelCommand = new OleDbCommand();
                ExcelCommand.Connection = ExcelConection;
                ExcelCommand.CommandText = "Select * From [Sheet1$]";
    
                ExcelConection.Open();
                ExcelReader = ExcelCommand.ExecuteReader();
    
                GridView1.DataSource = ExcelReader;
                GridView1.DataBind();
            }
            catch (Exception Args)
            {
                LabelErrorMsg.Text = "Could not open Excel file: " + Args.Message;
            }
            finally
            {
                if (ExcelCommand != null)
                    ExcelCommand.Dispose();
                if (ExcelReader != null)
                    ExcelReader.Dispose();
                if (ExcelConection != null)
                    ExcelConection.Dispose();
            }
      


     

    Tuesday, June 30, 2009 8:11 PM
  • User165199113 posted

    everything is fine, i am doing the same way. my sql query is "Select * From [Sheet1$A1].

    I want to get the cell value of A1, but it is not working.

    Tuesday, June 30, 2009 10:39 PM
  • User187056398 posted

    Again, you haven't posted any code or even an error message.   The syntax you have posted will cause an error.  If you wish people to help you, you need to supply complete information.

     

    Here is a guess:  Your select syntax is incorrect, try [Sheet1$A1:A1] and you must have HDR=No in the connection string.

     

    Wednesday, July 1, 2009 10:20 AM