June 19, 2012

Convert xml file to excel using vb.net code

Private Function XmlToExcelfile(ByVal XMLSourcefileAs String, ByVal ExcelfileAs String)
 Dim Exapp As New Excel.Application  
     Dim fileInf As New System.IO.FileInfo(XLSFile)  
     If fileInf.Exists Then  
       SetAttr(XLSXFile, vbNormal)  
     End If  
     Dim fxml As New System.IO.FileInfo(XMLFile)  
     If fxml.Exists Then  
       exapp.Workbooks.Item(1).CheckCompatibility = True  
       exapp.DisplayAlerts = False  
       exapp.DisplayAlerts = False  
       SetAttr(XMLFile, vbNormal)  
       MessageBox.Show("XML File does not exists")  
     End If  
     Dim file2 As New System.IO.FileInfo(XLSXFile)  
     If file2.Exists Then  
       SetAttr(XLSFile, FileAttribute.Normal)  
     End If  
     Exapp= Nothing  
     MessageBox.Show("xml file Exported Successfully to Excel")  
     Return True  
   End Function  
 Call this code on button click  
  Dim oldfile As String = ""  
       Dim DialogtosaveAs New SaveFileDialog  
       Dialogtosave.InitialDirectory = "I:\"  
       Dialogtosave.Title = "Save as Excel Files"  
       Dialogtosave.CheckPathExists = True  
       Dialogtosave.DefaultExt = "xml"  
       Dialogtosave.Filter = "Excel Files (*.xls)|"  
       Dialogtosave.FilterIndex = 2  
       Dialogtosave.RestoreDirectory = True  
       Dialogtosave.AddExtension = True  
       If (Dialogtosave.ShowDialog() = DialogResult.OK) Then  
         oldfile = Dialogtosave.FileName  
       End If  
       Dim dt As DataTable = dv.ToTable  
       ExportToXml(oldfile, dt)  
       Dim oldfilenew As String = oldfile.Substring(0, oldfile.Length - 3)  
       oldfilenew = oldfilenew & "xlsx"  
       XmlToExcel(oldfile, oldfilenew)