-
Feb 23rd, 2018, 11:05 AM
#1
Thread Starter
Member
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.
-
Feb 23rd, 2018, 11:30 AM
#2
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
-
Feb 23rd, 2018, 11:52 AM
#3
Thread Starter
Member
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
-
Feb 23rd, 2018, 11:55 AM
#4
Re: Load DataGridView and export XML
Show me how you are loading your datagridview
-
Feb 23rd, 2018, 12:03 PM
#5
Thread Starter
Member
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
-
Feb 23rd, 2018, 12:14 PM
#6
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)
-
Feb 23rd, 2018, 12:23 PM
#7
Thread Starter
Member
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
-
Feb 23rd, 2018, 12:41 PM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|