none
Loading xml data extracted from Excel to a form RRS feed

All replies

  • As promised here is a simple method.

    First off, I moved the file name variable to form level then added a form level variable TableNameForData for the table name because we use it twice.

    Next, in form load event we first use File.Exists to ensure we have the xml file to load. From here I use a new DataSet to gain access to a load method, ReadXml. We can do the same at with a DataTable but need to schema and that is more work than we need to get into.

    From here I thought it would be wise to sort the names in the xml, now in a DataTable within a DataSet so we create a DataView and set the Sort property to FirstName.

    Note I set ValueMember of the ComboBox, as indicated in comments this is optional but more for learning.

    Button2 goes back to if needed, get at the ValueMember and also shows how to determine if there is actually data in the ComboBox.

    Imports System.Data.OleDb
    Public Class Form1
    
        Private FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "MyFile1.xml")
        Private TableNameForData As String = "PartOfSheet1"
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim dt As DataTable = GetRange("Sheet1", "A2:C3")
    
            dt.TableName = TableNameForData
            dt.Columns(0).ColumnName = "FirstName"
            dt.Columns(1).ColumnName = "LastName"
            dt.Columns(2).ColumnName = "JoinYear"
            Dim ds As New DataSet With {.DataSetName = "MyData"}
            ds.Tables.Add(dt)
            ds.WriteXml(FileName)
        End Sub
        Public Function GetRange(ByVal SheetName As String, ByVal Range As String) As DataTable
            Dim dt As New DataTable
            Dim Connection As Connections = New Connections
            Dim FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "MyFile1.xlsx")
    
    
            Using cn As New OleDbConnection With {.ConnectionString = Connection.NoHeaderConnectionString(FileName)}
                cn.Open()
    
                Dim cmd As OleDbCommand = New OleDbCommand(
                    <Text>
                    SELECT * FROM [<%= SheetName %>$<%= Range %>]
                    </Text>.Value,
                    cn
                )
    
                dt.Load(cmd.ExecuteReader)
    
            End Using
    
            Return dt
        End Function
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If IO.File.Exists(FileName) Then
                ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
                Dim ds As New DataSet
                ds.ReadXml(FileName)
                ComboBox1.DisplayMember = "FirstName"
    
                ' Next line us optional
                ComboBox1.ValueMember = "LastName"
    
                ' The next two lines are to sort the first name field A-Z which is more professional then un-sorted
    
                ' Get the instance of the DataView for the DataTable from the DataSet
                Dim dv As DataView = ds.Tables(TableNameForData).AsDataView
                ' Sort A-Z
                dv.Sort = "FirstName"
    
                ComboBox1.DataSource = dv
    
            End If
        End Sub
        ''' <summary>
        ''' This will only work if you have set ValueMember in Form Load event
        ''' above to LastName
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            If ComboBox1.DataSource IsNot Nothing Then
                MessageBox.Show(ComboBox1.Text & " " & ComboBox1.SelectedValue.ToString)
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    • Proposed as answer by Fouad Roumieh Friday, May 29, 2015 5:44 PM
    Friday, May 29, 2015 4:46 PM
    Moderator
  • thanks,

    this is the part is was looking for (although everything else is also very,very helpful):

    Dim ds As New DataSet
                ds.ReadXml(FileName)

    was only familiar with xmlserializer method (sample code below) and wasn't sure how to read from datatables and datasets

     Private Sub loadrmlist()
            Dim xmlser As New XmlSerializer(rmlist.GetType)
            Dim rstrm As New IO.FileStream(myfilepath, FileMode.Open)
            rmlist = CType(xmlser.Deserialize(rstrm), List(Of binfo))
            rstrm.Close()
     For Each x In rmlist
                If Not ComboBox1.Items.Contains(x.bcom) Then
                    ComboBox1.Items.Add(x.bcom)
                    Next

     End Sub

    p.s.

    will work on my forum etiquette

    Friday, May 29, 2015 5:12 PM
  • Hopefully that solves the issue. If there are more question please note I will be in meeting for the next couple of hours.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    Friday, May 29, 2015 5:14 PM
    Moderator
  • You can read the XML and create a result of objects in a List Of(T) that happens automatically and bind the results to a control like a combobox. A List Of(T)_ is biddable to the control.

    http://data.bangtech.com/vb/linq2xml.htm

    Friday, May 29, 2015 6:16 PM
  • Here is a drop in for what DA924 made the suggestion on

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        If IO.File.Exists(FileName) Then
            ComboBox1.DisplayMember = "FirstName"
            ComboBox1.ValueMember = "FullName"
            ComboBox1.DataSource =
                (
                    From T In XDocument.Load(FileName)...<PartOfSheet1>
                    Let theName = T.<FirstName>.Value & " " & T.<LastName>.Value
                    Select New With
                           {
                               .FirstName = T.<FirstName>.Value,
                               .LastName = T.<LastName>.Value,
                               .FullName = theName
                           }
                      ).OrderBy(Function(t) t.FirstName
                ).ToList
        End If
    End Sub

    Now we can collapse the code for the data source which is exactly the same as the first example above

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        If IO.File.Exists(FileName) Then
            ComboBox1.DisplayMember = "FirstName"
            ComboBox1.ValueMember = "FullName"
            ComboBox1.DataSource = (From T In XDocument.Load(FileName)...<PartOfSheet1> Let theName = T.<FirstName>.Value & " " & T.<LastName>.Value Select New With {.FirstName = T.<FirstName>.Value, .LastName = T.<LastName>.Value, .FullName = theName}).OrderBy(Function(t) t.FirstName).ToList
        End If
    End Sub

    Using the above idea or via a DataSet is personal choice.

    Also, note the use of "let" which allows the first and last names to be concatenated and held to use so no string concatenation is needed down the road as in my DataSet example. With that in mine we could add an additional data column to the DataTable and setup an Expressions to concatenate the first and last name. It's all a matter of what suits your style and needs.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    Friday, May 29, 2015 7:31 PM
    Moderator