Results 1 to 8 of 8

Thread: Load DataGridView and export XML

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2010
    Posts
    63

    Load DataGridView and export XML

    I have a datagridview with 6 columns

    I want with this data to create a xml file.

    Using this code

    Dim doc = New XmlDocument()
    Dim element1 = doc.CreateElement(element)
    doc.LoadXml("<" & root & " />")
    For Each row In source.Rows
    For Each cell In row.Cells
    element1.AppendChild(doc.CreateElement(cell.OwningColumn.HeaderText))
    element1.LastChild.InnerText = cell.FormattedValue
    Next
    doc.FirstChild.AppendChild(element1)
    Next

    I take this

    <Test1>
    <F1>Vagelis1</F1>
    <F2>Vagelis2</F2>
    <F3>test1</F3>
    <F4>test2</F4>
    <F5>test3</F5>
    <F6>Test4</F6>
    <F1>Vagelis11</F1>
    <F2>Vagelis12</F2>
    <F3>test11</F3>
    <F4>test22</F4>
    <F5>test33</F5>
    <F6>Test44</F6>
    <F1>Vagelis111</F1>
    <F2>Vagelis112</F2>
    <F3>test111</F3>
    <F4>test222</F4>
    <F5>test333</F5>
    <F6>Test444</F6>
    <F1>Vagelis1111</F1>
    <F2>Vagelis1112</F2>
    <F3>test1111</F3>
    <F4>test2222</F4>
    <F5>test3333</F5>
    <F6>Test4444</F6>
    </Test1>

    What i have to do to take this

    Code:
    <GrpHdr>
      <MsgId>Vagelis1</MsgId>
      <CreDtTm>Vagelis2</CreDtTm>
      <NbOfTxs>test1</NbOfTxs>
      <CtrlSum>test2</CtrlSum>
      <InitgPty>
        <Id>
          <OrgId>
            <Othr>
              <Id>test3</Id>
              <Issr>Test4</Issr>
            </Othr>
          </OrgId>
        </Id>
      </InitgPty>
      <MsgId>Vagelis11</MsgId>
      <CreDtTm>Vagelis12</CreDtTm>
      <NbOfTxs>test11</NbOfTxs>
      <CtrlSum>test22</CtrlSum>
      <InitgPty>
        <Id>
          <OrgId>
            <Othr>
              <Id>test33</Id>
              <Issr>Test44</Issr>
            </Othr>
          </OrgId>
        </Id>
      </InitgPty>
    </GrpHdr>

    Thanks and regards.

  2. #2
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Load DataGridView and export XML

    If I am understanding you correctly, you want to essentially create an XML file from data/schema structure of a datagridview?

    Read documentation for DataTable.WriteXML method

  3. #3

    Thread Starter
    Member
    Join Date
    Nov 2010
    Posts
    63

    Re: Load DataGridView and export XML

    First of all thanks for your quick answer.

    In the past i had create some small projects in VB but i have NO idea about XML.
    I want to export what i show in the code with this schema.
    I cant find anything to help me solve my problem.
    I prefer some line with code but to guide me in the wright direction it is good also.
    with a quick look that I saw DataTable.WriteXML I did not understand a lot but after the job I will look at it more carefully
    Another point is i have no idea hot to import a schema in the vb. Maybe guide me to this also.

    Thanks again

    PS:Sorry for my English

  4. #4
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Load DataGridView and export XML

    Show me how you are loading your datagridview

  5. #5

    Thread Starter
    Member
    Join Date
    Nov 2010
    Posts
    63

    Re: Load DataGridView and export XML

    I have a form where the user select a xls or xlsx file.
    Select the specific sheet.
    Also i have a checkbox if the Excel file in the first line has or has not header
    and with the button 3 i create the xml i show in the beginning.
    Code:
    Imports System.Data.OleDb
    Imports System.Xml
    Imports System.Xml.Linq
    
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            OpenFileDialog1.Title = "Παρακαλώ επιλέξτε αρχείο"
            OpenFileDialog1.InitialDirectory = "C:/"
            OpenFileDialog1.ShowDialog()
            strPathFile.Text = OpenFileDialog1.FileName.ToString()
            ComboBox1.Items.Clear()
            GetExcelSheetNames(strPathFile.Text)
        End Sub
    
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim hasHeader As String
            If CheckBox1.Checked = True Then
                hasHeader = "Yes"
            Else
                hasHeader = "No"
            End If
            Dim strconn As String = ConnectionString(strPathFile.Text, hasHeader, 1)
            Dim conn As New OleDbConnection(strconn)
            Dim Query As String = "Select * from [" & ComboBox1.Text & "]"
            Dim sda As OleDbDataAdapter = New OleDbDataAdapter(Query, strconn)
            Dim dt As DataTable = New DataTable()
            sda.Fill(dt)
            DataGridView1.DataSource = dt
        End Sub
    
        Public Function ConnectionString(ByVal pFileName As String, ByVal pHeader As String, ByVal pImex As Integer) As String
            Dim Builder As New OleDbConnectionStringBuilder()
            If pFileName.ToUpper() = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", "Excel 8.0;IMEX=" & pImex & ";HDR=" & pHeader & ";")
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", "Excel 12.0;IMEX=" & pImex & ";HDR=" & pHeader & ";")
            End If
            Builder.DataSource = pFileName
            Return Builder.ConnectionString
        End Function
    
    
        Private Sub GridToXml(ByVal source As DataGridView, ByVal root As String, ByVal element As String) ' As String
            Dim doc = New XmlDocument()
            Dim element1 = doc.CreateElement(element)
            doc.LoadXml("<" & root & " />")
            For Each row In source.Rows
                For Each cell In row.Cells
                    element1.AppendChild(doc.CreateElement(cell.OwningColumn.HeaderText))
                    element1.LastChild.InnerText = cell.FormattedValue
    
                Next
                doc.FirstChild.AppendChild(element1)
            Next
            MsgBox(doc.OuterXml)
            'Return doc.OuterXml
        End Sub
    
        Private Sub GetExcelSheetNames(ByVal fileName As String)
            Dim strconn As String = ConnectionString(strPathFile.Text, "No", 1)
            Dim conn As New OleDbConnection(strconn)
            conn.Open()
            Dim dtSheets As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim listSheet As New List(Of String)
            Dim drSheet As DataRow
            For Each drSheet In dtSheets.Rows
                listSheet.Add(drSheet("TABLE_NAME").ToString())
            Next
            For Each sheet As String In listSheet
                ComboBox1.Items.Add(sheet)
            Next
            conn.Close()
        End Sub
    
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
            Call GridToXml(DataGridView1, "Test1", "Test2")
        End Sub
    PS: THANKS AGAIN kpmc

  6. #6
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Load DataGridView and export XML

    In your case you would do this to get your data to XML. Recommend you read the documentation

    Code:
                Dim XMLFile As String = "C:\TargetPath\TargetFile.XML"
                dt.WriteXml(XMLFile, XmlWriteMode.WriteSchema)

  7. #7

    Thread Starter
    Member
    Join Date
    Nov 2010
    Posts
    63

    Re: Load DataGridView and export XML

    To be absolutely sure: you want this code in the button 3 correct???
    I will try this asap (as soon as possible) when i go in my house.....


    If you can help me a little bite more this will be a huge help for me.

    Please try to understand me

  8. #8
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Load DataGridView and export XML

    no, you dont need button 3.

    Button 2 would work, but not how I would do it. You have to call that method while the "dt" is initialized and not disposed

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width