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.
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
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.
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.
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).
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.
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.
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
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.
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
Reply With Quote
Originally Posted by hendrikbez
