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)
f.Delete()
End If
Dim fxml As New System.IO.FileInfo(XMLFile)
If fxml.Exists Then
ex.Workbooks.Open(XMLFile)
ex.Workbooks.Item(1).CheckCompatibility = True
ex.DisplayAlerts = False
ex.Workbooks.Item(1).SaveAs(XLSXFile,Excel.XlFileFormat.xlWorkbookDefault)
ex.DisplayAlerts = False
ex.Workbooks.Close()
SetAttr(XMLFile, vbNormal)
fxml.Delete()
Else
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.Quit()
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