Solved

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

Posted on 2016-10-09
41
51 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 17

Expert Comment

by:Pawan Kumar Khowal
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

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

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41836112
Pls check the last one I updated.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:vcharles
ID: 41836113
Hi Pawan,

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

rVicto
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

17 Experts available now in Live!

Get 1:1 Help Now