Reading the contents if the excel sheet RRS feed

  • Question




    i have a third party application which generates a detailed report in the form of an excel sheet with number of sheets in one excelfile... now i need information from only 4 sheets from the file... how can i go to those sheets (based on the sheet names) and read the contents of the cell.Furthere i want to display the contents of these sheets in my custom application with in a list box...i don't want to use ODBC method for this prob...Pls let me know how to go abt it..




    Friday, April 25, 2008 6:25 AM

All replies

  • well you can either use the COM API's. but that is tricky and no need to do this, or use the better way which is to connect to the sheet using OleDb, query the sheet you want to obtain values from, then get the result and fill the listbox.


    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";




    "SELECT * FROM [SheetName$]"


    will select all contents from the sheetname given, the dollar sign is required.


    Does this help so far, is this what you are after, before we continue?

    Friday, April 25, 2008 6:36 AM

    hi  ahmedilyas


    First of all thanks for ur answer... but i need a method to do my work with doesnot use OleDb.. can u pls provide me with that method





    Friday, April 25, 2008 7:57 AM
  • Sure but why do you not want to use OleDB? It is better/safer than using the COM approach....I understand you don't want to use it but perhaps we can clear up your reasons for not wanting to use it Smile

    Friday, April 25, 2008 8:21 AM
  • hi


    i am doing my final year BE proj in a company... and the company ppl have asked me not to use OleDB(coz it is system dependent) and as u know v have to do as these ppl tell us to do...


    Friday, April 25, 2008 10:10 AM

    Generally speaking, the JET engine OLEDB uses is built within Windows.... so you should be ok for say using Excel no need to install excel really. Even if you do have it installed, I believe it may also install the drivers for you to communicate with excel, im not quite sure about this


    But otherwise, you would need to install Excel, then add a reference to the Excel PIA and interact with it, but since all this is unmanaged, you want to make sure you dispose of the objects correctly and be efficient.


    I cant remember of by heart but you would need to:


    1) create an instance of excel

    2) load the spreadsheet into it

    3) get the amount of rows and cells and iterate through each row and each cell and do whatever you need to get the value you need

    4) if you are looking for a single value, I believe there is a function called Find or FindNext you can invoke which will return back results based on your query..


    Try investigating on the outlined sequences and see where you get Smile

    Friday, April 25, 2008 10:35 AM
  • thanks  ahmedilyas i will see if i can find out anything abt it..




    Friday, April 25, 2008 11:37 AM
  • Just to add to what ahmedilyas said, there is no system independent method for accessing an Excel file. You can use OLEDB, ODBC or Excel automation but unless the platform supports these technologies you're out of luck. AFAIK, only Windows supports all of these technologies.

    If you use the automation method (which requires that Excel be installed) there is an MS KB article link below:

    How to automate Microsoft Excel from Visual Basic .NET

    Monday, April 28, 2008 12:23 PM
  • If you need to read specific cells and do not want to use Jet/OLEDB or Excel, you could try my component that allows to read Excel content without any above technologies and it allows to read in dividual cell values from inside of spreadsheet.
    Tuesday, April 29, 2008 10:00 AM