ByteScout Spreadsheet SDK – VB.NET – Convert XML to XLS

  • Home
  • /
  • Articles
  • /
  • ByteScout Spreadsheet SDK – VB.NET – Convert XML to XLS

ByteScout Spreadsheet SDK – VB.NET – Convert XML to XLS

Module1.vb

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


  Click here to get your Free Trial version of the SDK

prev
next