Imports System.Drawing
Imports System.Xml
Imports Bytescout.Spreadsheet
Imports Bytescout.Spreadsheet.Constants

Module Module1

	Sub Main()

		' Load XML document
		Dim xmlDocument = New XmlDocument()
		xmlDocument.Load("sample.xml")

		' Read columns information from XML data
		Dim columns = New List(Of String)()
		Dim columnNodeList = xmlDocument.SelectNodes("/Report/Columns/Column")
		For Each node As XmlNode In columnNodeList
			columns.Add(node.Attributes("Name").Value)
		Next

		' Read row nodes from XML data
		Dim rowNodeList = xmlDocument.SelectNodes("/Report/ReportData")


		' Create new spreadsheet
		Dim spreadsheet = New Spreadsheet()
		spreadsheet.RegistrationName = "demo"
		spreadsheet.RegistrationKey = "demo"
		' Add worksheet
		Dim worksheet = spreadsheet.Worksheets.Add()

		' Add column headers
		For c As Integer = 0 To columns.Count - 1
			worksheet(0, c).Value = columns(c)
			worksheet(0, c).FillPattern = PatternStyle.Solid
			worksheet(0, c).FillPatternForeColor = Color.LightGray
		Next

		Dim rowIndex = 1

		' Add rows
		For Each rowNode As XmlNode In rowNodeList

			' Get cell values from XML data
			For Each childNode As XmlNode In rowNode.ChildNodes

				' Get cell info from XML data
				Dim columnIndex = columns.IndexOf(childNode.Name)
				Dim cellValue = childNode.InnerText

				Dim cell = worksheet(rowIndex, columnIndex)

				' Set cell text
				Cell.Value = cellValue
				' Set cell text alignment
				cell.AlignmentHorizontal = IIf(columnIndex = 0, AlignmentHorizontal.Left, AlignmentHorizontal.Right)
			Next

			' Add the row to the table
			rowIndex = rowIndex + 1
		Next

		' Fit columns width to cell data
		For c As Integer = 0 To columns.Count - 1
			worksheet.Columns(c).AutoFit()
		Next

		' Save document to file
		spreadsheet.SaveAsXLS("result.xls")

		' Cleanup 
		spreadsheet.Dispose()

		' Open document in Excel
		Process.Start("result.xls")

	End Sub

End Module

'------------------------------------------------------------------------------
' <auto-generated>
'     This code was generated by a tool.
'     Runtime Version:4.0.30319.42000
'
'     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", "4.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("ConvertXmlToXls.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:4.0.30319.42000
'
'     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", "11.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.ConvertXmlToXls.My.MySettings
            Get
                Return Global.ConvertXmlToXls.My.MySettings.Default
            End Get
        End Property
    End Module
End Namespace