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

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();  

Select allOpen in new window

Hi,

Can you please send me the VB.NET version.

Thanks,

Victor

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

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)

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

Okies .... entire code from Microsoft, below URLs will help.

https://code.msdn.microsoft.com/office/How-to-convert-excel-file-7a9bb404

https://support.microsoft.com/en-us/kb/319180

Enjoy!!

Hi,

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

Victor

Wait let me modify the old version only.

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

--

Select allOpen 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!

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

Select allOpen in new window

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

Select allOpen in new window

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

Hi Vcharles,

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

Add a reference to the dll Microsoft.Office.Interop.Excel in the project and that should fix the issue.

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

No no I am taking about the below URL ..

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

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

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


--

Select allOpen in new window

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

Pls check the last one I updated.

Hi Pawan,

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

rVicto

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

Select allOpen in new window

Hi,

I will get back to you tomorrow.

Thanks,

Victor

Hi Victor;

Change this line of code

xlApp = New Excel.ApplicationClass

Select allOpen in new window

To this

xlApp = New Excel.Application

Select allOpen in new window

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

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

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.

Hi Fernando,

The platform and target CPU is x86.

Victor

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?

Hi,


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

Victor

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?

Fernando,

The answers to your questions are yes.

Victor

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.

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.

Fernando,

Enclosed are the two files.

Thanks,

Victor
screenshotB.docx
screenshot.docx

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.

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

Hi Pawan,

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

Thanks,

Victor

Hi Fernando,

Yes, but still received the same error message.

Victor

Victor, what is the status of this question? Please update or close the question. Thanks.

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