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