How to get data from DevExpress datagrid to xml file
Export data in datagrid to Xml/Excel : In this article, I will show how to get the data in a DevExpress datagrid to xml file. First of all, we need to read the data from the database and save it in a dataset, ds. Then we have to show the data in the datagrid. When we click the Export Button, the data in the datagrid is copied to an Xml file.My next article will tell you how to convert xml file to Excel file (.xlsx) file.
See the code samples below which demonstrate how to get data from DevExpress DataGtid controls in to an XML file. I have demonstrated the full details with code samples.
GetDataFromDatabase will include the code to retrieve data from database and calling updateadapter.
Dim dv As New DataView ' Declare globally
'Within Read Function
Dim ds As New DataSet = GetDataFromDatabase()
Dim dvManager As New DataViewManager(ds)
dv = dvManager.CreateDataView(ds.Tables(0))
GridView1.DataSource = dv
To Export Data:
Private Sub btnExport_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnExport.ItemClick
Dim fn As String = ""
Dim saveFileDialog1 As New SaveFileDialog
saveFileDialog1.InitialDirectory = "I:\"
saveFileDialog1.Title = "Save as Excel Files"
saveFileDialog1.CheckPathExists = True
saveFileDialog1.DefaultExt = "xml"
saveFileDialog1.Filter = "Excel Files (*.xls)|"
saveFileDialog1.FilterIndex = 2
saveFileDialog1.RestoreDirectory = True
saveFileDialog1.AddExtension = True
If (saveFileDialog1.ShowDialog() = DialogResult.OK) Then
fn = saveFileDialog1.FileName
End If
Dim dt As DataTable = dv.ToTable
ExportToXml(fn, dt)
Dim fnnew As String = fn.Substring(0, fn.Length - 3)
fnnew = fnnew & "xlsx"
XmlToExcel(fn, fnnew)
End Sub
The function ExportToXml:
Private Sub ExportToXml(ByVal filename As String, ByVal dt As DataTable)
Dim wtr As StreamWriter = Nothing
Try
Dim sb As New StringBuilder()
Dim columns As New List(Of String)()
Dim headers As New List(Of String)()
Dim ccount As Integer = 0
Dim initial As String = "
ccount = GridView1.VisibleColumns.Count
For i = 0 To ccount - 1
If dt.Columns(i).ColumnName = "__Offset__" Then
Continue For
End If
headers.Add(GridView1.VisibleColumns(i).GetCaption)
columns.Add(GridView1.VisibleColumns(i).FieldName)
Next
sb.Append("" & vbLf)
" & vbCr & vbLf & "
For i = 0 To headers.Count - 1
sb.Append("
Next
sb.Append("
For i = 0 To headers.Count - 1
sb.Append("
Next
sb.Append("
wtr = New StreamWriter(filename)
wtr.WriteLine(initial)
Dim xdoc As New XmlDocument()
Dim xnode As XmlNode = xdoc.CreateElement("Temp")
xdoc.AppendChild(xnode)
Dim dvView As New DataView
dvView.Table = dt
For i = 0 To dvView.Count - 1
sb.Append("
For j = 0 To columns.Count - 1
If dvView(i)(columns(j)) Is DBNull.Value Or dvView(i)(columns(j)) Is Nothing Then
xnode.InnerText = String.Empty
Else
xnode.InnerText = dvView(i)(columns(j)).ToString
End If
sb.Append("
Next
sb.Append("
If ((i + 1) Mod 1000) = 0 Then
wtr.WriteLine(sb)
wtr.Flush()
sb = New StringBuilder()
End If
Next
sb.Append("
wtr.WriteLine(sb)
wtr.Flush()
wtr.Close()
wtr.Dispose()
wtr = Nothing
Catch ex As Exception
MessageBox.Show(ex.ToString)
Throw ex
Finally
If wtr IsNot Nothing Then
Try
wtr.Flush()
wtr.Close()
wtr.Dispose()
wtr = Nothing
Catch ex As Exception
End Try
End If
End Try
End Sub
My next article will show how to convert Xml file to Excel file. So getting all the code will help you to get the data in datagrid to excel.