How to Read Excel File AT VB.NET

Hi anyone know how do i read excel file at VB.net & display the result in a label or something..

e.g i know cell A2 is a string of text - e.g Hello this is the P/L for the month

i want grab that and put in a label etc.

Thanks , it would be good if can refer me to url on using Microsoft Excel Library Object & its coding to connect, close connection , grab data from cell and input back the data.

Regards,
Bao

Re: How to Read Excel File AT VB.NET

With the proper connection string (http://www.connectionstrings.com/?carrier=excel) you can query it like any datasource. Using the Excel Interop is, frankly, sometimes a pain.

Re: How to Read Excel File AT VB.NET

if by setting a connection string to excel.

how do i query it like any data source? i never try before querying excel, is there any tutorial or sample

Thanks,

Baoky

Re: How to Read Excel File AT VB.NET

Heres some code I use to connect to an excel data range :-

Using m_connexcel As OleDbConnection = New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                  "Data Source={0};" & _
                                                                  "Extended Properties=""Excel 8.0;HDR=NO""", SPREADSHEET_NAME))
                        m_connexcel.Open()
                        Using cmd As OleDbCommand = New OleDbCommand(String.Format("SELECT  * FROM [{0}]", DATARANGE), m_connexcel)
                            Using oRDR As OleDbDataReader = cmd.ExecuteReader
                                While (oRDR.Read)
                                   If Not oRDR.IsDBNull(oRDR.GetOrdinal("F1")) Then
                                      MsgBox(oRDR.GetString(oRDR.GetOrdinal("F1")))
                                   End If
                               End While
                            End Using
                        End Using
                        m_connexcel.Close()
End Using

Set 'HDR' in the connection string to 'YES' if you want to use the first row as the field names, otherwise the fields will be called
F1, F2, F3 etc...

Re: How to Read Excel File AT VB.NET

How do i query it after i connect to the excel file.

e.g , i want query from A1 to A20 or A1 to A10+ B1 to B10

Thanks

Rgds,

Bao

Re: How to Read Excel File AT VB.NET

You basically wrap the data you want to query in a named data range (Insert Menu -> Name -> Define) and then you can select the info using a SQL style select statement as above.

Re: How to Read Excel File AT VB.NET

i used the codes in this http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm a message box appears that says system.data.oledb.oledbexception: cannot update. database or object is read-only. what seems to be the problem?

Re: How to Read Excel File AT VB.NET

Did you have the excel file open when your program was running?

Re: How to Read Excel File AT VB.NET

Hi,

Best way to deal with Excel in VB is by using 3rd party component. It's much faster and easier way then Excel Automation. Try using GemBox spreadsheet component for exporting Excel. Component is free for commercial use but with limit of 150 rows.

Here you can see a list of reason why GemBox.Spreadsheet is much better tool to work with Excel then Excel Automation.

Filip
GemBox.Spreadsheet - easiest way to create, read and write Excel files in .NET

Re: How to Read Excel File AT VB.NET

Hi, How do I read an Excel file that is located in a client PC?

Thanks

Re: How to Read Excel File AT VB.NET

You'd have to get them to upload the file to the server before hand, and then read the Excel file on the server.

Re: How to Read Excel File AT VB.NET

You can try SmartXLS,it is a third party spreadsheet component which can write, read, calculate Excel compatible files without the need for Microsoft Excel.