Xojo: Creating Excel Reports with ExcelWriter

By | 2017-04-01

Introduction

Developing with Xojo gives us two options to control and manage Excel workbooks without MS Excel.

One of the option is to use the MBS XL Plugin from Monkeybread Software. It’s the most expensive solution as the plugin is part of the commercial package MBS Xojo Plugins. The MBS XL requires also us to have a license of the commercial LibXL library in order work. The MBS XL Plugin is at the same time the most powerful of the two options.

The other option is to use the ExcelWriter from Einhugur. Since it’s still in beta as of this writing, it will be delivered as part of Einhugur Plugin Pack (for Xojo) when it’s available in public. ExcelWriter do not requires us to have installed the libxlswriter library. The library has already been incorporated in the ExcelWriter.

If we only want to create basic Excel workbooks with the file format XLSX then ExcelWriter is the right option. Although MBS XL plugin offer more alternatives than ExcelWriter it can only produce basic workbooks. Since ExcelWriter is the less expensive option of the two it’s presented here.

Compared with other development platforms the alternatives for Xojo are very primitive. I have no explanation why the big gap between Xojo and other platforms exist. But Xojo has a small group of users compared with, for instance, Java and .NET. Perhaps the Xojo developers don’t have the same requirements as other developer groups may have, for instance working with Excel workbooks.

The Case

The case tries to cover the main aspects of ExcelWriter. What is true is that ExcelWriter cannot offer more than what the underlying library, libxlswriter, offers. The case creates a simple report with a table and a chart.


Dim f As FolderItem

Const stFileName As String = "Report"

'Creating a date and time value to the name of the workbook.
Dim d as New Date
Dim vaDate As Variant
Dim vaTime As Variant

vaDate = d.ShortDate
vaTime = d.ShortTime

Dim vaResult As Variant
vaResult = Replaceall(vaDate,"-","") + Replace(vaTime, ":","")

f = SpecialFolder.Documents.Child("Xojo Excel Samples\" + stFileName + vaResult + ".xlsx")

Dim XLWriter As ExcelWriterWorkbook = ExcelWriterWorkbook.Create(f, ZStream.GetZipEngineHandle())
Dim XLWSheet As ExcelWriterWorksheet = XLWriter.AddWorksheet()

Dim XLChart As ExcelWriterChart = XLWriter.AddChart(ExcelWriterChart.ChartTypeValues.LINE)
Dim XLChartSeries1 As ExcelWriterSeries

Dim XLBoldFormat As ExcelWriterFormat= XLWriter.AddFormat()
Dim XLDateFormat As ExcelWriterFormat = XLWriter.AddFormat()
Dim XLGridFormat As ExcelWriterFormat = XLWriter.AddFormat()

Dim XLOptions As New ExcelWriterObjectOptions

'It's possible to add several formats.
XLGridFormat.SetBorder(ExcelWriterFormat.SideValues.ALL, ExcelWriterFormat.BorderStyleValues.DASH_DOT_DOT)
XLGridFormat.SetBorderColor(ExcelWriterFormat.SideValues.ALL, &cFF8080)
XLGridFormat.SetPresentationFormat("#,##0")

'Add data
XLWSheet.CellText(0,0, XLBoldFormat) = "Year"
XLWSheet.CellText(0,1) = "2000"
XLWSheet.CellText(0,2) = "1999"
XLWSheet.CellText(0,3) = "2000"
XLWSheet.CellText(0,4) = "1999"
XLWSheet.CellText(0,5) = "2017"
XLWSheet.CellText(0,6) = "2017"
XLWSheet.CellText(0,7) = "2000"
XLWSheet.CellText(0,8) = "2016"
XLWSheet.CellText(0,9) = "2016"
XLWSheet.CellText(0,10) = "1999"

XLWSheet.CellText(1,0, XLBoldFormat) = "Brand"
XLWSheet.CellText(1,1) = "VW"
XLWSheet.CellText(1,2) = "Ford"
XLWSheet.CellText(1,3) = "Toyota"
XLWSheet.CellText(1,4) = "Ford"
XLWSheet.CellText(1,5) = "VW"
XLWSheet.CellText(1,6) = "Nissan"
XLWSheet.CellText(1,7) = "BMW"
XLWSheet.CellText(1,8) = "VW"
XLWSheet.CellText(1,9) = "Toyota"
XLWSheet.CellText(1,10) = "BMW"

XLWSheet.CellText(2,0, XLBoldFormat) = "Sales"
XLWSheet.CellNumericValue(2,1,XLGridFormat) = 10000
XLWSheet.CellNumericValue(2,2,XLGridFormat) = 15000
XLWSheet.CellNumericValue(2,3,XLGridFormat) = 25000
XLWSheet.CellNumericValue(2,4,XLGridFormat) = 50000
XLWSheet.CellNumericValue(2,5,XLGridFormat) = 30000
XLWSheet.CellNumericValue(2,6,XLGridFormat) = 30000
XLWSheet.CellNumericValue(2,7,XLGridFormat) = 20000
XLWSheet.CellNumericValue(2,8,XLGridFormat) = 35000
XLWSheet.CellNumericValue(2,9,XLGridFormat) = 17500
XLWSheet.CellNumericValue(2,10,XLGridFormat) = 15000

XLWSheet.CellFormula(2,11, XLBoldFormat)="=SUM(C2:C10)*1"

'Do some formatting.
XLWSheet.ColumnWidth(2) = 10
XLWSheet.SetTabColor (&cFFFF80)

XLWSheet.ColumnWidth(3) = 15
XLWSheet.CellText(3,0, XLBoldFormat) = "Report Date"

XLDateFormat.SetPresentationFormat("yyyy-mm-dd")
XLWSheet.CellDateValue(3,1,XLDateFormat) = New Date

'Zoom in percent.
XLWSheet.Zoom = 90

'Turning on AutoFilter.
XLWSheet.SetAutoFilter(0,0,2,10)

'Add Graphics.
XLOptions.Width = 600
XLOptions.Height = 300

XLChartSeries1 = XLChart.AddSeries("Sheet1!C2:C10")
XLChartSeries1.SetName("Sales Year")

'The following is a bug.
XLChart.X_Axis.SetName("US$")
XLChart.Y_Axis.SetName("Year")
XLChart.TitleVisible = True

XLWSheet.InsertChart( XLChart, 5,1,XLOptions)

'Add some values to some properties of the workbook.
XLWriter.Properties.Author = "Dennis Wallentin"
XLWriter.Properties.Category = "Sales"
XLWriter.Properties.Company="Lonely Inc"

'Save the workbook and release memory.
call XLWriter.Close()

After the above code has been executed the following screenshot shows the outcome:

ExcelWriter is very fast and reliable and can manage larger set of data. But as we also can see, what we get is basics results. As it it, ExcelWriter is the best option for Xojo developers to work with Excel workbooks.

Enjoy!

Dennis

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.