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