Subscribe to Subscribers
Talk to Webmaster Tony John

Online Members

baskar
More...


Resources » .NET programming » Visual Studio

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


Posted Date:     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.


    Next Resource: How to get data from DevExpress datagrid to xml file
    Previous Resource: What is the need of Option Strict On in VB.NET ?
    Return to Resources
    Post New Resource
    Category: Visual Studio


    Post resources and earn money!
     
    More Resources
    Popular Tags   Tag posting guidelines   Search Tags  
    Xml to Excel  .  Datagrid to Excel  .  Devexpress  .  VB.NET  .  .xml to .xlsx  .  



    Follow us on Twitter: https://twitter.com/dotnetspider

    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
    2005 - 2012 All Rights Reserved.
    .NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
    Articles, tutorials and all other content offered here is for educational purpose only.
    We are not associated with Microsoft or its partners.