Resources » .NET programming » Visual Studio

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


Posted Date: 26-May-2012  Last Updated:   Category: Visual Studio    
Author: Member Level: Silver    Points: 50


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


Did you like this resource? Share it with your friends and show your love!

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

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

Feedbacks      

Post 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:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    sheeba Angela
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India