How to convert xml file to Excel file (.xlsx)

Export data in datagrid to Excel / Convert from xml to xlsx [VB.NET]: In my earlier article, I had given the code for getting data from DevExpress Datagrid to Xml file. Now let us see how to convert the Xml file to Excel file. When the xml file is obtained, pass the xml file and the xlsx file to be created to the function XmlToExcel. If the filename already exists, it will delete the previous one. It will not give any display alerts of Excel as we have set it as false.

How to convert xml file to Excel file (.xlsx)

Function XmlToExcel :

XMLFile : The name of the Xml File is passed as string
XLSXFile : The name of the XLSX file is passed as string

ex.DisplayAlerts is set to false to avoid any alert messages like compatibility check, etc.

Private Function XmlToExcel(ByVal XMLFile As String, ByVal XLSXFile As String)

Dim ex As New Excel.Application

Dim f As New System.IO.FileInfo(XLSFile)
If f.Exists Then
SetAttr(XLSXFile, vbNormal)
End If

Dim fxml As New System.IO.FileInfo(XMLFile)
If fxml.Exists Then
ex.Workbooks.Item(1).CheckCompatibility = True
ex.DisplayAlerts = False
ex.DisplayAlerts = False
SetAttr(XMLFile, vbNormal)

MessageBox.Show("XML File does not exists")
End If

Dim f2 As New System.IO.FileInfo(XLSXFile)
If f2.Exists Then
SetAttr(XLSFile, FileAttribute.Normal)
End If

ex = Nothing

MessageBox.Show("Exported Successfully to Excel")
Return True
End Function

Code in Export Button.
This part of code was provided in my earlier article as well.
For easy understanding , its given here also.

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

NB: Please do not forget to import Microsoft.Office.Interop.Excel


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: