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.

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

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 :)

Show me how you are loading your datagridview

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

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)

---

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

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