Imports Bytescout.Spreadsheet
Imports System.IO

Module Module1
    Sub Main()
        ' sample XLS to XML conversion
        SampleXLStoXMLConversion()
        ' sample XML to XLS conversion
        SampleXMLtoXLSConversion()
    End Sub

    ''' <summary>
    ''' shows how to convert existing XLS (Excel) _document into XML using Bytescout.Spreadsheet and Bytescout.Spreadsheet.Utils.SimpleXMLConverter
    ''' </summary>
    Sub SampleXLStoXMLConversion()
        Dim document As New Spreadsheet()
document.LoadFromFile("AdvancedReport.xls")

        ' read XLS and save as XML
        Dim tools As New SimpleXMLConverter(document)
        tools.SaveXML("AdvancedReport.xml")

        document.Close()
    End Sub

    ''' <summary>
    ''' shows how to convert XML data into XLS excel using Bytescout.Spreadsheet and Bytescout.Spreadsheet.Utils.SimpleXMLConverter
    ''' </summary>
    Sub SampleXMLtoXLSConversion()
        ' read XML and convert into XLS (Excel) and save

        Dim document As New Spreadsheet()

        Dim tools As New SimpleXMLConverter(document)

        tools.LoadXML("AdvancedReport.xml")

        ' remove output file if already exists
        If File.Exists("AdvancedReportFromXML.xls") Then
            File.Delete("AdvancedReportFromXML.xls")
        End If

        document.SaveAs("AdvancedReportFromXML.xls")

        document.Close()

        ' open in default spreadsheets viewer/editor
        Process.Start("AdvancedReportFromXML.xls")
    End Sub

End Module

Imports Bytescout.Spreadsheet

Public Class SimpleXMLConverter

    Dim _document As Spreadsheet

    Sub New(ByRef document As Spreadsheet)
        _document = document
    End Sub

    Sub LoadXML(ByVal path As String)
        Dim dataSet As New DataSet()
        dataSet.ReadXml(path)

        For Each table As DataTable In dataSet.Tables

            Dim worksheet As Worksheet = _document.Workbook.Worksheets.Add(table.TableName)

            ' Add columns
            worksheet.Columns.Insert(0, table.Columns.Count)

            ' Add rows for data
            worksheet.Rows.Insert(0, table.Rows.Count)

            ' Fill data
            For i As Integer = 0 To table.Rows.Count - 1
                For j As Integer = 0 To table.Columns.Count - 1
                    worksheet.Cell(i + 1, j).Value = table.Rows(i)(j).ToString()
                Next
            Next
        Next
    End Sub

    Sub SaveXML(ByVal path As String)

        Dim dataSet As New DataSet()

        For i As Integer = 0 To _document.Workbook.Worksheets.Count - 1

            Dim worksheet As Worksheet = _document.Workbook.Worksheets(i)

            Dim table As DataTable = dataSet.Tables.Add(worksheet.Name)

            For column As Integer = 0 To worksheet.UsedRangeColumnMax
                table.Columns.Add(String.Format("Column_{0}", column))
            Next


            For row As Integer = 0 To worksheet.UsedRangeRowMax
                Dim data() As Object
                Array.Resize(data, worksheet.UsedRangeColumnMax + 1)

                For column As Integer = 0 To worksheet.UsedRangeColumnMax
                    data(column) = worksheet.Cell(row, column).Value
                Next

                table.Rows.Add(data)
            Next
        Next

        dataSet.WriteXml(path)
    End Sub

    Sub PrintDataSet(ByRef ds As DataSet)

        Console.WriteLine("DataSet name: {0}", ds.DataSetName)
        For Each table As DataTable In ds.Tables

            Dim rowCount As Integer = table.Rows.Count
            Dim columnCount As Integer = table.Columns.Count

            Console.WriteLine("\nTable: {0} ({1} rows)", table.TableName, rowCount)

            For Each column As DataColumn In table.Columns
                Console.Write("{0}\t", column.ColumnName)
            Next

            Console.WriteLine()

            For i As Integer = 0 To rowCount - 1
                For column As Integer = 0 To columnCount - 1
                    Console.Write("{0}\t", table.Rows(i)(column))
                Next

                Console.WriteLine()
            Next
        Next
    End Sub

End Class

��'------------------------------------------------------------------------------

' <auto-generated>

'     This code was generated by a tool.

'     Runtime Version:2.0.50727.1433

'

'     Changes to this file may cause incorrect behavior and will be lost if

'     the code is regenerated.

' </auto-generated>

'------------------------------------------------------------------------------



Option Strict On

Option Explicit On





Namespace My.Resources

    

    'This class was auto-generated by the StronglyTypedResourceBuilder

    'class via a tool like ResGen or Visual Studio.

    'To add or remove a member, edit your .ResX file then rerun ResGen

    'with the /str option, or rebuild your VS project.

    '<summary>

    '  A strongly-typed resource class, for looking up localized strings, etc.

    '</summary>

    <Global.System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "2.0.0.0"),  _

     Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _

     Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute(),  _

     Global.Microsoft.VisualBasic.HideModuleNameAttribute()>  _

    Friend Module Resources

        

        Private resourceMan As Global.System.Resources.ResourceManager

        

        Private resourceCulture As Global.System.Globalization.CultureInfo

        

        '<summary>

        '  Returns the cached ResourceManager instance used by this class.

        '</summary>

        <Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

        Friend ReadOnly Property ResourceManager() As Global.System.Resources.ResourceManager

            Get

                If Object.ReferenceEquals(resourceMan, Nothing) Then

                    Dim temp As Global.System.Resources.ResourceManager = New Global.System.Resources.ResourceManager("XLS_to_XML_and_XML_to_XLS__VB.NET_.Resources", GetType(Resources).Assembly)

                    resourceMan = temp

                End If

                Return resourceMan

            End Get

        End Property

        

        '<summary>

        '  Overrides the current thread's CurrentUICulture property for all

        '  resource lookups using this strongly typed resource class.

        '</summary>

        <Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

        Friend Property Culture() As Global.System.Globalization.CultureInfo

            Get

                Return resourceCulture

            End Get

            Set(ByVal value As Global.System.Globalization.CultureInfo)

                resourceCulture = value

            End Set

        End Property

    End Module

End Namespace

��'------------------------------------------------------------------------------

' <auto-generated>

'     This code was generated by a tool.

'     Runtime Version:2.0.50727.1433

'

'     Changes to this file may cause incorrect behavior and will be lost if

'     the code is regenerated.

' </auto-generated>

'------------------------------------------------------------------------------



Option Strict On

Option Explicit On





Namespace My



    <Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute(),  _

     Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "8.0.0.0"),  _

     Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

    Partial Friend NotInheritable Class MySettings

        Inherits Global.System.Configuration.ApplicationSettingsBase

        

        Private Shared defaultInstance As MySettings = CType(Global.System.Configuration.ApplicationSettingsBase.Synchronized(New MySettings), MySettings)

        

#Region "My.Settings Auto-Save Functionality"

#If _MyType = "WindowsForms" Then

        Private Shared addedHandler As Boolean



        Private Shared addedHandlerLockObject As New Object



        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)> _

        Private Shared Sub AutoSaveSettings(ByVal sender As Global.System.Object, ByVal e As Global.System.EventArgs)

            If My.Application.SaveMySettingsOnExit Then

                My.Settings.Save()

            End If

        End Sub

#End If

#End Region

        

        Public Shared ReadOnly Property [Default]() As MySettings

            Get

                

#If _MyType = "WindowsForms" Then

                   If Not addedHandler Then

                        SyncLock addedHandlerLockObject

                            If Not addedHandler Then

                                AddHandler My.Application.Shutdown, AddressOf AutoSaveSettings

                                addedHandler = True

                            End If

                        End SyncLock

                    End If

#End If

                Return defaultInstance

            End Get

        End Property

    End Class

End Namespace



Namespace My

    

    <Global.Microsoft.VisualBasic.HideModuleNameAttribute(),  _

     Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _

     Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute()>  _

    Friend Module MySettingsProperty

        

        <Global.System.ComponentModel.Design.HelpKeywordAttribute("My.Settings")>  _

        Friend ReadOnly Property Settings() As Global.XLS_to_XML_and_XML_to_XLS__VB.NET_.My.MySettings

            Get

                Return Global.XLS_to_XML_and_XML_to_XLS__VB.NET_.My.MySettings.Default

            End Get

        End Property

    End Module

End Namespace