Solved

Help with converting an xml file in excel format using VB.NET

Posted on 2016-10-09
41
74 Views
Last Modified: 2016-10-27
Hi,

How do you convert an xml file to excel using VB.NET.

I need to access an xml file from my application's folder, convert it to an excel file and save it to a selected folder.

Thanks,

Victor
0
Comment
Question by:vcharles
  • 19
  • 11
  • 11
41 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835863
Try this..
Include System.XML as a Namespace.


XLSDocument doc = new XLSDocument();
SimpleXMLConverter converts = new SimpleXMLConverter(doc);  -- XML converter
converts.LoadXML("C:\Pawan\YourXMLFile.xml");  ---- Location to get XML file
doc.SaveAs("C:\Pawan\YourExcelFile.xls");  ---Folder in which you wanted the output file.
doc.Close();  

Open in new window

0
 

Author Comment

by:vcharles
ID: 41835886
Hi,

Can you please send me the VB.NET version.

Thanks,

Victor
0
 

Author Comment

by:vcharles
ID: 41835891
Hi,

I tried to convert to Vb.NET using code below but application does not recognize XLSDocument.

Dim doc As New XLSDocument
        SimpleXMLConverter(converts = New SimpleXMLConverter(doc)) '  -- XML converter
        converts.LoadXML(Application.StartupPath + "\File1.xml") '  ---- Location to get XML file
        doc.SaveAs(Application.StartupPath + "\File1.xls") ';  ---Folder in which you wanted the output file.
doc.Close();  

Thanks,

Victor
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835901
Ohh.. VB.Net does not support many things.. try..

Dim exPawan As Excel.Application
exPawan.WorkBooks.Open(XML FILE....)
exPawan.WorkBooks.Item(1).SaveAs("",XLFileFormat.xlworkbookdefault)
0
 

Author Comment

by:vcharles
ID: 41835909
Hi,

Trying code below but getting error message is not declared for XLFileFormat and File1.xls

    Dim exPawan As Application
        exPawan.WorkBooks.Open(Application.StartupPath + "\File1.xml")
        exPawan.WorkBooks.Item(1).SaveAs("Application.StartupPath + "\File1.xls"), XLFileFormat.xlworkbookdefault)

Victor
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835912
0
 

Author Comment

by:vcharles
ID: 41835919
Hi,

Thanks for the links but it for converting to xml, I'm trying to convert xml to excel.

Victor
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835924
Wait let me modify the old version only.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835933
Try..

--

Private Function XmlToExcel(ByVal InputXmlFile As String, ByVal OutputExcelFile As String)
        
        Dim excelFile As New Excel.Application
        excelFile.Workbooks.Open(InputXmlFile)
		excelFile.Workbooks.Item(1).SaveAs(OutputExcelFile,Excel.XlFileFormat.xlWorkbookDefault)		
		excelFile.Workbooks.Close()
		SetAttr(InputXmlFile, vbNormal)
        excelFile.Quit()
        excelFile = Nothing
        
		Dim f2 As New System.IO.FileInfo(OutputExcelFile)
        SetAttr(XLSFile, FileAttribute.Normal)
		
        Return True
    
End Function

--

Open in new window



If you face any issues with the above code please follow this blog.

http://www.dotnetspider.com/resources/43869-How-convert-xml-file-Excel-file-xlsx.aspx

Enjoy!
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41835983
Hi Victor;

try the following code snippet gotten from [here].
Imports System.Xml
Imports System.Data
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        Dim ds As New DataSet
        Dim xmlFile As XmlReader
        Dim i, j As Integer

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        xmlFile = XmlReader.Create("Product.xml", New XmlReaderSettings())
        ds.ReadXml(xmlFile)

        For i = 0 To ds.Tables(0).Rows.Count - 1
            For j = 0 To ds.Tables(0).Columns.Count - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                ds.Tables(0).Rows(i).Item(j)
            Next
        Next

        xlWorkSheet.SaveAs("xml2excel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835992
Pls try the following code from here

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

Open in new window

0
 

Author Comment

by:vcharles
ID: 41836044
Hi Fernanado,

Imports Excel = Microsoft.Office.Interop.Excel is not recognized by VS 2010. I should be receiving VS 2015 in a couple of weeks, if that's the issue, it will be solved when I received the latest version. Meanwhile would appreciate if I can have a solution for VS 2010.

Thanks,

Victor
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41836046
Hi Vcharles,

Have you tried the last code I have given. Thanks !
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41836047
Add a reference to the dll Microsoft.Office.Interop.Excel in the project and that should fix the issue.
0
 

Author Comment

by:vcharles
ID: 41836068
Hi,

I tried the code but received errors for dv and ExportToXml as not declared.

 Dim dt As DataTable = dv.ToTable
 ExportToXml(fn, dt)

Victor
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41836069
0
 

Author Comment

by:vcharles
ID: 41836100
Hi,

I'm talking about the code below from your link.

 Private Sub Button10_Click(sender As System.Object, e As System.EventArgs) Handles Button10.Click
        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 **Error (dv not defined)
        ExportToXml(fn, dt) *Error(ExportToXml not defined)
        Dim fnnew As String = fn.Substring(0, fn.Length - 3)
        fnnew = fnnew & "xlsx"
        XmlToExcel(fn, fnnew)
    End Sub


    Private Function XmlToExcel(ByVal XMLFile As String, ByVal XLSXFile As String)

        Dim ex As New Application

        Dim f As New System.IO.FileInfo(XLSFile) ** Error
        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) * Error (Excel not defined)
            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
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41836105
ohh updated code you..


--

 Private Sub Button10_Click(sender As System.Object, e As System.EventArgs) Handles Button10.Click
        Dim Xmlfile = 'C:\XmlFileName.xml'
		Dim ExcelFile = 'C:\ExcelFile.xls'
		XmlToExcel(Xmlfile, ExcelFile)
    End Sub
	
	 Private Function XmlToExcel(ByVal XMLFile As String, ByVal XLSXFile As String)

        Dim ex As New Application

        Dim f As New System.IO.FileInfo(XLSFile) ** Error
        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) * Error (Excel not defined)
            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


--

Open in new window

0
 

Author Comment

by:vcharles
ID: 41836110
Hi Fernando,

I tried your code, but received error message: Interop Type ' ApplicationClass" can not be embedded use application interface instead.

on line:

xlApp = New Excel.ApplicationClass

Thanks,
Victor
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41836112
Pls check the last one I updated.
0
 

Author Comment

by:vcharles
ID: 41836113
Hi Pawan,

I don't understand your last message "ohh updated code you.."

rVicto
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41836114
Here is the latest one..Pls try..


 Private Sub Button10_Click(sender As System.Object, e As System.EventArgs) Handles Button10.Click
        Dim Xmlfile AS String = 'C:\XmlFileName.xml'
		Dim ExcelFile AS String = 'C:\ExcelFile.xls'
		XmlToExcel(Xmlfile, ExcelFile)
    End Sub
	
	 Private Function XmlToExcel(ByVal XMLFile As String, ByVal XLSXFile As String)

        Dim ex As New Application

        Dim f As New System.IO.FileInfo(XLSFile) ** Error
        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) * Error (Excel not defined)
            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

Open in new window

0
 

Author Comment

by:vcharles
ID: 41836198
Hi,

I will get back to you tomorrow.

Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41836356
Hi Victor;

Change this line of code
xlApp = New Excel.ApplicationClass

Open in new window

To this
xlApp = New Excel.Application

Open in new window

It should work with no issues, I tested on my system.
0
 

Author Comment

by:vcharles
ID: 41836778
Hi Fernando,

I made the change but received the following error:

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

on line:
 xlApp = New Excel.Application

Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41836971
Hi Victor;

What is the architecture of the machine you are developing on, a 32 or 64 bit? If you are developing on a 64 bit machine then try the following because you may have the 32 bit version of the Office dll. You’ll need to compile your application under X86 CPU mode (not the default any CPU, change the setting in Visual Studio IDE), then the x86(32-bit) application can run under 32-bit emulation (that is WOW64 mode) on 64-bit machine.
0
 

Author Comment

by:vcharles
ID: 41837066
Hi Fernando,

The platform and target CPU is x86.

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41837073
So the computer you are developing on is a 32 bit machine, correct?
Visual Studio IDE is set for the Solution Platform as x86 and is NOT set to "Any CPU", correct?
0
 

Author Comment

by:vcharles
ID: 41837075
Hi,


The target framework is .NET Framework 4 (client profile) should it be only .NET Framework 4?

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41837080
Hi Victor;

Please answer these two questions. Thanks.
So the computer you are developing on is a 32 bit machine, correct?

Visual Studio IDE is set for the Solution Platform as x86 and is NOT set to "Any CPU", correct?
0
 

Author Comment

by:vcharles
ID: 41837081
Fernando,

The answers to your questions are yes.

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41837106
Sorry Victor, then I do not know why it is saying that it is not registered because in the cases I have come across with that error it has been a miss match between the target platform architecture and the dll it is trying to find. For example a 64 bit machine trying to use a 32 bit dll.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41837242
Victor, please post a screen shot of the following. Hold down the Windows Key and at the same time press the letter R, in the dialog box that opens enter the following msinfo32 and press Enter key. Take a screen shot of the initial information that is displayed.

Also take a screen shot of the following dialog window, In Visual Studio on the menu bar right click on the Build -> Configuration Manager ... Take screen shot.
0
 

Author Comment

by:vcharles
ID: 41837324
Fernando,

Enclosed are the two files.

Thanks,

Victor
screenshotB.docx
screenshot.docx
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41837367
For your information your system type / platform is x64 which is a 64 bit system and the answer to this question
So the computer you are developing on is a 32 bit machine, correct?
You answer should have been No and not Yes as you stated.

Seeming that the project in Visual Studio is set to x86 it will need the 32 bit dll for excel. So if you are still seeing the error most likely the dll you are using are for 64 bit OS. In VS change it from x86 to Any CPU and see if that corrects the problem.
0
 

Author Comment

by:vcharles
ID: 41837538
Thanks, I realized that when I followed your instructions.
Still getting same error, will try the code again in VS 2015 when back in office in two weeks, for now as a temp solution for a demo using a C1TrueDBGrid to export the data in  excel.

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

    '   FilteredDTA.WriteXml(System.Windows.Forms.Application.StartupPath + "\AOP40.xml")
        'Export to Excel
        Dim bs As New BindingSource()
        fslinkBEL = New System.IO.FileStream(System.Windows.Forms.Application.StartupPath + "\AOP40.xml", IO.FileMode.Open)
        dtsetlinkBEL.Clear()
        dtsetlinkBEL.ReadXml(fslinkBEL)
        fslinkBEL.Close()
        bs.DataSource = dtsetlinkBEL.Tables(0)
        C1TrueDBGrid1.DataSource = bs
        C1TrueDBGrid1.ExportToExcel(System.Windows.Forms.Application.StartupPath + "\AOP40.XLS")
        MsgBox("Records saved")
    End Sub

Thanks,

Victor
0
 

Author Comment

by:vcharles
ID: 41837540
Hi Pawan,

Still getting errors in your code, will try it with VS2015.

Thanks,

Victor
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41837550
Victor did you change x86 to Any CPU in VS and tried it again?
0
 

Author Comment

by:vcharles
ID: 41837585
Hi Fernando,

Yes, but still received the same error message.

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41860282
Victor, what is the status of this question? Please update or close the question. Thanks.
0
 

Author Comment

by:vcharles
ID: 41861922
Hi Fernando,

Having issues with installing VS 2015 to run old projects, for now will close this case. Hopefully errors will be solved when using new laptop with VS 2015.

Rhanks,

Victor
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now