help to show info form excel 2007 file

Hi

I need some help with this, I have a excel 2007 file, that I want to open in vb.net. I want to use a windows form, I need it to fist to give me a list of only column A from row 1-500,
then on my form I will have all off my names in text that is in each column, so when I click on a name in the list, it must display all the info of that row in my form.

I don't know how to start with this.

I do not want to change anything in my excel file just to display it.

Thanks

The following example opens Customer.xlsx in the bin\debug folder of a project using OleDb connection. Using an OleDb Command Object we select the first 500 rows, column A and B, display column A in ListBox1 and display column B in a data bound TextBox as the user scrolls thru ListBox1. Since the data is not actively attached to the sheet there is no need to make the TextBox read-only unless you want too. Please note that in extended properties of the connection you may need to change IMEX and/or HDR settings (for more information http://www.connectionstrings.com/excel).

Code:

---

Imports System.Data.OleDb
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt As New DataTable
        Using cn As New OleDb.OleDbConnection
            Dim Builder As New OleDbConnectionStringBuilder With _
                { _
                    .DataSource = IO.Path.Combine(Application.StartupPath, "Customers.xlsx"), _
                    .Provider = "Microsoft.ACE.OLEDB.12.0" _
                }
            Builder.Add("Extended Properties", "Excel 12.0; IMEX=1;HDR=No;")
            cn.ConnectionString = Builder.ConnectionString
            cn.Open()
            Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
                cmd.CommandText = "SELECT TOP 500 F1 As CustomerName, F2 As ContactName FROM [Sheet1$]"
                Dim dr As System.Data.IDataReader = cmd.ExecuteReader
                dt.Load(dr)
                ListBox1.DisplayMember = "CustomerName"
                ListBox1.DataSource = dt
                txtContactName.DataBindings.Add("Text", dt, "ContactName")
            End Using
        End Using
    End Sub
End Class

---

Hi kevininstructor

Thanks for the code, It is working.

Hi kevininstructor

Can you please explain the following line, Why using F1, what does it do.

cmd.CommandText = "SELECT TOP 319 F1 As ServerName, F2 As Room, F3 As Row, F4 as Rack FROM [EveryThing$]"

Quote:

---

Originally Posted by hendrikbez

Hi kevininstructor

Can you please explain the following line, Why using F1, what does it do.

cmd.CommandText = "SELECT TOP 319 F1 As ServerName, F2 As Room, F3 As Row, F4 as Rack FROM [EveryThing$]"

---

When the first row is data not column names Excel gives them names using F plus a numeric value. So if HDR=Yes that means your first row contains column names and Fn is not used.

Thank You

Just one more question.

On this I have a columns "pod" and "Rack" and then all the servers that is in this rack and pod.
How do I get it to Let me first click pod and then rack and then show me all the servers in this rack and pod.

Quote:

---

Originally Posted by hendrikbez

Thank You

Just one more question.

On this I have a columns "pod" and "Rack" and then all the servers that is in this rack and pod.
How do I get it to Let me first click pod and then rack and then show me all the servers in this rack and pod.

---

I have no idea unless you upload a copy of the file or a good size screenshot (not as good as an actual file).

Quote:

---

Originally Posted by kevininstructor

I have no idea unless you upload a copy of the file or a good size screenshot (not as good as an actual file).

---

Here is the xls file, is this the one you need.

First off anytime you know that you need to read data from a sheet like the one uploaded know full well you may run into reserve word exceptions in your SQL i.e. from as a column heading I would guess is a snake ready to strike.

In the code which follows I have placed your file (renamed it as I don't work with file names with spaces if I can help it) in C:\Data, reads in the data from the sole sheet. First time I uses SELECT * then tweaked it down as shown below. For the connection a language extension helper was utilize which will setup a commonly used connection for xls or xlsx. I filtered out rows where there were no server name.

Code:

---

WithEvents bsExcelData As New BindingSource
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Using cn As New OleDb.OleDbConnection
        cn.SetExcelConnectionString("C:\Data\ServerRoomLayout.xls", UseHeader.Yes, ExcelImex.TryScan)
        Console.WriteLine(cn.ConnectionString)
        cn.Open()
        Using cmd As New OleDb.OleDbCommand With
            {
                .Connection = cn,
                .CommandText =
                <SQL>
                    SELECT
                        [Server Name] As Server,
                        Room,
                        [Row #] As [RowNo],
                        [Rack #] As Rack,
                        [POD #] As POD,
                        [From] As Origin,
                        [to] As Dest,
                        [How many rows in rack] As RowCount,
                        [IP Address] As IPAddress,
                        VLAN,
                        Model,
                        [Type],
                        [Operating System] As OS,
                        Application,
                        [App# Owner] As AppNoOwner,
                        Ram
                    FROM
                        [EveryThing$]
                    WHERE [Server Name] IS NOT NULL
                </SQL>.Value
            }
            Dim dt As New DataTable
            dt.Load(cmd.ExecuteReader)
            bsExcelData.DataSource = dt
            DataGridView1.DataSource = bsExcelData
        End Using
    End Using
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    If bsExcelData.Current IsNot Nothing Then
        Dim dr = CType(bsExcelData.Current, DataRowView)
        MessageBox.Show(dr.Item("Server").ToString)
    End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    bsExcelData.Filter = "Server='ATEN 17 LCD'"
End Sub

---

Create OleDbExtensions.vb and place the following into it.

Code:

---

Imports System.Data.OleDb

Public Module OleDbExtensions
    Public Enum UseHeader
        ''' <summary>
        ''' Indicates that the first row contains columnnames, no data
        ''' </summary>
        ''' <remarks></remarks>
        Yes
        ''' <summary>
        ''' Indicates that the first row does not contain columnnames
        ''' </summary>
        ''' <remarks></remarks>
        No
    End Enum
    Public Enum ExcelImex
        TryScan = 0
        Resolve = 1
    End Enum
    ''' <summary>
    ''' Used to make connection to Excel easy
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="FileName"></param>
    ''' <param name="Header"></param>
    ''' <param name="IMEX"></param>
    ''' <remarks>
    ''' </remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    <System.Runtime.CompilerServices.Extension()> _
    Public Sub SetExcelConnectionString(ByRef sender As OleDbConnection, ByVal FileName As String, ByVal Header As UseHeader, ByVal IMEX As ExcelImex)
        Dim Mode As String = CInt(IMEX).ToString
        Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = FileName}
        If IO.Path.GetExtension(FileName).ToUpper = ".XLSX" Then
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", "Excel 12.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
        Else
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", "Excel 8.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
        End If
        sender.ConnectionString = Builder.ConnectionString
    End Sub
End Module

---

So you now have your data displayed via Button1, Button2 shows a simple example of getting a column from the current row, Button3 a simple filter example (could be done also by casting bsData data source to a DataTable then use the Default view RowFilter to filter. Also can sort at the SQL level or via the BindingSource Sort property.

The tools are there combine them with SQL and you are on your way.

Here is another twist, in the code I just posted I used

Code:

---

WHERE [Server Name] IS NOT NULL

---

and noticed there were still rows where Server Name was empty which leads me to believe there is at least a space in these cells so if this is important you might look at fixing it.

kevininstructor

Thank you, I will play with this, it will help me quit a bit, I just looked an my xls the column server name have info form a2 - a516, I don't see any empty rows
for server name, but thank you again, will let you know how it is going.

Quote:

---

Originally Posted by hendrikbez

kevininstructor

Thank you, I will play with this, it will help me quit a bit, I just looked an my xls the column server name have info form a2 - a516, I don't see any empty rows
for server name, but thank you again, will let you know how it is going.

---

To the naked eye the last server is on A-516 but Excel reports A-742. To locate the last row while viewing the sheet press CTRL+Page-Down.
To get rid of the blanks http://www.theexceladdict.com/_t/t031008.htm

kevininstructor

I have put in the code, but I am getting two errors, I do not understand them so well.

1st error in in this code Server.vb

SetExcelConnectionString' is not a member of 'System.data.OleDb.OleDbConnection

Code:

---

 
cn.SetExcelConnectionString("C:\Do_it\ServerRoomLayout.xls", UseHeader.Yes, ExcelImex.TryScan)

---

Here are the info of server.vb

Code:

---

Imports System.Data.OleDb

Public Class ServerRoom

    WithEvents bsExcelData As New BindingSource

    Private Sub ServerRoom_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt As New DataTable
        Using cn As New OleDb.OleDbConnection
            Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = IO.Path.Combine(Application.StartupPath, "Server room layout.xls"), .Provider = "Microsoft.ACE.OLEDB.12.0"}
            Builder.Add("Extended Properties", "Excel 12.0; IMEX=1;HDR=No;")
            cn.ConnectionString = Builder.ConnectionString
            cn.Open()
            Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
                cmd.CommandText = "SELECT TOP 516 F1 As ServerName, F2 As Room, F3 As Row, F4 as Rack, F5 as Pod, F6 as Vanaf, F7 as na, F9 as Ipaddress, F10 as Vlan, F11 as Model, F12 as Type, F13 as Operating_System, F14 as Application, F15 as app_owner, F16 as Ram FROM [EveryThing$]"
                Dim dr As System.Data.IDataReader = cmd.ExecuteReader
                dt.Load(dr)
                LstServerName.DisplayMember = "ServerName"
                LstServerName.DataSource = dt
                txtRoom.DataBindings.Add("Text", dt, "Room")
                TxtRow.DataBindings.Add("Text", dt, "Row")
                txtRack.DataBindings.Add("Text", dt, "Rack")
                txtPod.DataBindings.Add("Text", dt, "Pod")
                txtFrom.DataBindings.Add("Text", dt, "vanaf")
                txtTo.DataBindings.Add("Text", dt, "na")
                TxtIPAddress.DataBindings.Add("Text", dt, "Ipaddress")
                txtVlan.DataBindings.Add("Text", dt, "Vlan")
                txtModel.DataBindings.Add("Text", dt, "Model")
                txtType.DataBindings.Add("Text", dt, "Type")
                TxtOS.DataBindings.Add("Text", dt, "Operating_System")
                TxtApp.DataBindings.Add("Text", dt, "Application")
                TxtAppOwner.DataBindings.Add("Text", dt, "app_owner")
                TxtRam.DataBindings.Add("Text", dt, "Ram")
            End Using
        End Using
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Using cn As New OleDb.OleDbConnection
            cn.SetExcelConnectionString("C:\Do_it\ServerRoomLayout.xls", UseHeader.Yes, ExcelImex.TryScan)
            Console.WriteLine(cn.ConnectionString)
            cn.Open()
            Using cmd As New OleDb.OleDbCommand With
                {
                    .Connection = cn,
                    .CommandText =
                    <SQL>
                    SELECT
                        [Server Name] As Server,
                        Room,
                        [Row #] As [RowNo],
                        [Rack #] As Rack,
                        [POD #] As POD,
                        [From] As Origin,
                        [to] As Dest,
                        [How many rows in rack] As RowCount,
                        [IP Address] As IPAddress,
                        VLAN,
                        Model,
                        [Type],
                        [Operating System] As OS,
                        Application,
                        [App# Owner] As AppNoOwner,
                        Ram
                    FROM
                        [EveryThing$]
                    WHERE [Server Name] IS NOT NULL
                </SQL>.Value
                }
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
                bsExcelData.DataSource = dt
                DataGridView1.DataSource = bsExcelData
            End Using
        End Using
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        bsExcelData.Filter = "Server='ATEN 17 LCD'"
    End Sub

    Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
        Application.Exit()
    End Sub
End Class

---

Then in OleDbExtensions.vb I get this
Module "oleDbExtensions' an class "OleDbExtensions', declared in "C:\Documents and Settings\Administrator\my documents\visual 2010\projects\serverroom\Serverroom\OleDbExtensions.Designer.vb', confllict in namespace "Serverroom"

Code:

---

Public Module OleDbExtensions

---

My code in OleDbExtensions.vb

Code:

---


Imports System.Data.OleDb

Public Module OleDbExtensions
    Public Enum UseHeader
        ''' <summary>
        ''' Indicates that the first row contains columnnames, no data
        ''' </summary>
        ''' <remarks></remarks>
        Yes
        ''' <summary>
        ''' Indicates that the first row does not contain columnnames
        ''' </summary>
        ''' <remarks></remarks>
        No
    End Enum
    Public Enum ExcelImex
        TryScan = 0
        Resolve = 1
    End Enum
    ''' <summary>
    ''' Used to make connection to Excel easy
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="FileName"></param>
    ''' <param name="Header"></param>
    ''' <param name="IMEX"></param>
    ''' <remarks>
    ''' </remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    <System.Runtime.CompilerServices.Extension()> _
    Public Sub SetExcelConnectionString(ByRef sender As OleDbConnection, ByVal FileName As String, ByVal Header As UseHeader, ByVal IMEX As ExcelImex)
        Dim Mode As String = CInt(IMEX).ToString
        Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = FileName}
        If IO.Path.GetExtension(FileName).ToUpper = ".XLSX" Then
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", "Excel 12.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
        Else
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", "Excel 8.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
        End If
        sender.ConnectionString = Builder.ConnectionString
    End Sub
End Module

---

The first error is caused by the second error, the second error is the error. Seeing OleDbExtensions.Designer.vb is wrong, a designer file are for forms not code modules. Sounds like you created a form rather than a code module. If all else fails clean the project, zip it and attach it here.

Hi

Sorry for only replaying now, I was quit busy at work, I have played with it, and it is working.
Just one question, I want to say press on button one, and it must show me the room,
then button 2 and it must show me room 1 and pod 1, if
I press button 3 it must show me room 1, pod1 and Row a and so on, I want to
keep the info of all of them in sequence. or if I can do it only with one button it is also ok.

I don't have time to revisit everything so hopefully the following will get you the informaton you want.

Speaking generically, when you want something from the current row we cast bsExcelData (the BindingSource) current property to a DataRowView then get field information.

Some examples mixed together, you should pick what is right for you.

Code:

---

If bsExcelData.Current IsNot Nothing Then
    Dim CurrentRow As DataRowView = DirectCast(bsExcelData.Current, DataRowView)
    ' Get one field
    MessageBox.Show(CurrentRow.Item("FieldNameToGetData").ToString)
    ' Show all fields
    MessageBox.Show(String.Join(",", CurrentRow.Row.ItemArray))
    '
    ' Show another method to get one field by type
    '
    MessageBox.Show(CurrentRow.Row.Field(Of String)("FieldNameToGetData"))
    MessageBox.Show(CurrentRow.Row.Field(Of Int32)("FieldNameToGetData").ToString)
End If

---