Skip to content

PDFTable from a RowSet

Published January 17, 2023 by Javier Menendez

Xojo recently introduced the ability to add (aka draw) tables in PDFDocument using the PDFTable class and the PDFTableDataSource class interface together. Perhaps you already know how to use it, but what if you want to add a table using data from a RowSet? Continue reading and I will show you a technique that will have you doing just that!

But before we dive into the code, this example project requires the “EddiesElectronics.sqlite” SQLite database file. You can find the Eddie’s Electronics example, along with the other example projects provided with Xojo, in the Examples section of the Project Chooser. Because I am such a kind person, you can also download it directly from this link.

And, if you want, it is also possible to download the resulting Xojo Project file (for Desktop) from this link.

I’m pretty sure you will be able to adapt the example project so it works for you with other databases too!

Helper class to the rescue!

All the work of drawing the table in the PDF document will be done by a helper class. That is, a class that we are going to use to “delegate” the work. With Xojo open and a new Desktop project created, the first thing we are going to do is add a new Class to the project (Insert > Class).

With the added Class selected in the Navigator, go to the associated Inspector Panel and use the following values:

  • Name: PDFRenderer
  • Interfaces: Click on the “Choose” button and select the PDFTableDataSource class interface

The just added Class will be populated with all the methods defined by the PDFTableDataSource class interface. Do not worry about them now.

Next, let’s add a few properties to our PDFRenderer class:

  • Name: data
  • Type: RowSet
  • Scope: Protected
  • Name: document
  • Type: PDFDocument
  • Scope: Protected
  • Name: DrawingFromRowSet
  • Type: Boolean
  • Scope: Protected
  • Name: Headers()
  • Type: String
  • Scope: Protected

We are going to use the DrawingFromRowSet as a flag, so the PDFRenderer knows when the table is going to be created from a RowSet. You could use any other data source, you are limited to RowSet. That’s just something you will need to implement yourself! Take it as an exercise.

Next, add a new Method to the PDFRenderer class (while still selected in the Navigator) using the following values in the associated Inspector Panel:

  • Name: Constructor
  • Parameters: document As PDFDocument
  • Scope: Public

And type the following line of code in the associated Code Editor:

Self.Document = document

Yep, it is truly that simple. Assign the received PDFDocument object to the “Document” property of the object created from the PDFRenderer class.

Add a second method using the following values:

  • Name: DrawTable
  • Parameters: rs As RowSet, headers() As String
  • Scope: Public

And type the following code in the associated Code Editor:

// This is the method we call on the Class to draw
// a table on a PDFDocument based on a RowSet

If document <> Nil And rs <> Nil Then

  // Assign the received RowSet to the "Data" property
  Self.data = rs

  // Assign the received headers to the "Headers" property
  Self.Headers = Headers

  // Hey! We are going to create a table from a RowSet
  // so we use a Boolean property as a flag for that
  // (Yeah, we can do it using other techniques, but this
  // is easy enough for this purpose… while leave this
  // helper class "open" enough for drawing tables based on other
  // data sources).
  Self.DrawingFromRowSet = True

  // How many columns are we going to draw?
  // Well… as many as columns in the RowSet.
  Var totalColumns As Integer = rs.ColumnCount

  // This is going to be the "drawable area" on the page
  // for the table = total page width less the left and right margins
  Var totalWidth As Double = document.Graphics.Width - 40 // 40 = 20 points left/right margins

  // Creating the PDFTable object here!
  Var table As New PDFTable

  // We want to repeat the headers on every page
  table.HasRepeatingHeader = True

  // Setting the column count for the table
  table.ColumnCount = totalColumns

  // …and the width for every column.
  table.ColumnWidths = CalculateColumnWidths(TotalWidth, totalColumns)

  // The object from this class will be the responsible
  // for handling all the methods associated with the
  // PDFTableDataSouce Class Interface
  table.DataSource = Self

  // Setting the Top and Bottom margins for the drawing
  // of the table on every PDF page
  table.TopMargin = 20
  table.BottomMargin = 20

  // …and finally we instruct the PDFDocument object
  // to draw the table!
  document.AddTable(table, 20, 0)

  // Lastly, clearing the flag
  Self.DrawingFromRowSet = False

End If

As you can see here, we are calling the “CalculateColumnWidths” method in order to get the expected string for the column widths for the PDFTable.ColumnWidths property; so add a new method to the PDFRenderer class using the following values:

  • Name: CalculateColumnWidths
  • Parameters: TotalWidth As Double, NumberOfColumns As Double
  • Return Type: String
  • Scope: Protected

And type the following snippet of code in the associated Code Editor:

Var ColumnWidth As Integer = TotalWidth / NumberOfColumns
Var s() As String

For n As Integer = 0 To NumberOfColumns-1
  s.Add(Str(ColumnWidth))
Next

Return String.FromArray(s, ",")

PDFTableDataSource Methods with RowSet data!

Let’s fill the required methods from the PDFTableDataSource class interface so they work in combination with the received RowSet.

AddNewRow Method

Add this snippet of code for the “AddNewRow” method:

If DrawingFromRowSet And data <> Nil Then

  // We are going to draw as many rows as rows are in the
  // "data" rowset
  Return rowCount <> data.RowCount

End If

As you can see, that fragment of code will only run when both conditions are meet. That is, when the “DrawingFromRowSet” is set to True and the “data” property is set to a not niled RowSet. If this is the case, we then instruct our PDFTable to set the number of rows to be drawn to the same number of rows in the RowSet.

Completed Method

This is the method that will be called once the drawing of the PDFTable has been completed. We will use it to draw the page numbers at the bottom of every page in the PDFDocument. As you probably already know, it is really easy to change the active graphic context to a given page of the PDF document using the CurrentPage property, so we will use that to iterate every one of our PDF document pages, adding the page number to it as a footer.

This is the snippet of code responsible for doing just that:

// This method is called once the table has been drawn
// so let's "print" the page number on every page
// of the PDF Document

Static pageNumber As String = "Page: "

If document <> Nil Then
  Var g As Graphics = document.Graphics

  For n As Integer = 1 To document.PageCount
    document.CurrentPage = n
    g.DrawText(pageNumber + Str(n), g.Width - g.TextWidth(pageNumber+Str(n))-20, g.Height-g.FontAscent)
  Next

End If

HeaderHeight Method

This is the method called so we can set the PDFTable the desired height for the drawing of the Header. In this case we are going to use a fixed value: 20 points. The code for that method is:

// Fixed header height
Return 20

PaintCell Method

This is the method responsible for drawing every cell of the PDFTable. It receives as parameters the Graphic context for a particular cell (so its origin is at 0,0) and the row and column values corresponding for such cell.

In this case, draw the outline of a rectangle for the cell plus the text itself retrieved from the RowSet based on the received Column value. The text will be draw vertically centered in the cell:

If DrawingFromRowSet And data <> Nil Then
  // Drawing the outer rectangle for the cell
  g.DrawRectangle(0, 0, g.Width, g.Height)

  // retrieving the text to be drawn from the RowSet,
  // using the column parameter for that.
  Var s As String = data.ColumnAt(column)

  // Centering vertically the text on the table cell
  // while the X offset is fixed at 5 points.
  g.DrawText(s, 5, g.Height / 2 + g.FontAscent / 2)

  // Have we drawn all the columns for this row?
  If column = data.ColumnCount - 1 Then

    // if that is the case, then move to the next row
    // in the RowSet!
    data.MoveToNextRow
  End If

End If

PaintHeaderContent Method

This is the method called in order to draw the Header of the table itself. It is a more simple version of the previous method. In this case, the text will be centered both vertically and horizontally on every header cell:

If column <= Self.Headers.LastIndex Then

  Var s As String = headers(column)
  g.DrawingColor = Color.Black
  g.FillRectangle(0, 0, g.Width, g.Height)
  g.DrawingColor = Color.White
  g.DrawText(s, g.Width / 2 - g.TextWidth(headers(column)) / 2, g.Height / 2 + g.FontAscent / 2)

End If

RowHeight Method

As it happen with the HeaderHeight method, this is the method called to set the height for a given row. As we did for the HeaderHeight method we are going to use a fixed value of 20 points, so type the following line of code in the associated Code Editor:

Return 20

Testing The PDFRenderer class

In order to test our PDFRenderer class in combination with a RowSet object, we need to do some additional work. First, select the Window1 window in the project Navigator and add a property using the following values:

  • Name: db
  • Type: SQLiteDatabase
  • Scope: Private

Next, click the Window1 window in the Navigator so it is displayed in the Layout Editor. Then, drag a DesktopLabel from the Library and drop it on the left/top edge of the layout observing the aligning guides so it leaves the expected margin over the left and top edges of the Window. Use the dragging handlers of the DesktopLabel on the layout to resize it to the maximum width (once again, less the expected margins). Use the following values in the associated Inspector Panel:

  • Locking: Left, Top and Right locked. Bottom, unlocked.
  • Multiline: on.
  • Text: This example project needs to use the EddiesElectronics.sqlite database file. Click on the button in order to select it!

The layout at this point should look like this:

Now, drag a DesktopButton from the Library and drop it below the previous Label so it is horizontally centered, using the following values in the associated Inspector Panel:

  • Caption: Select EddiesElectronics database file

The finished layout should look like this:

Double click the button to add the “Pressed” Event Handler. Then, add the following code in the associated Code Editor:

Try

  // Assigning SQLite object to property
  db = New SQLiteDatabase

  // Setting the database file to the SQLite object
  db.DatabaseFile = FolderItem.ShowOpenFileDialog(".sqlite")

  // Just a simple check to see if this is the expected
  // file based on the name = "EddiesElectronics.sqlite"

  If db.DatabaseFile = Nil Or db.DatabaseFile.Name <> "EddiesElectronics.sqlite" Then Return

  //…and "connecting" to it!
  db.Connect

  // Let's select all the rows from the Customers table
  Var rs As RowSet = db.SelectSQL("SELECT FirstName,LastName,Address FROM Customers")

  // Creating a new PDFDocument Instance
  Var d As New PDFDocument

  // Creating the PDFTable renderer helper object (we pass the PDFDocument object to it in the Constructor)
  Var PDFRender As New PDFRenderer(d)

  // These will be the headers drawn in the Table
  Var headers() As String = Array("Name", "Surname", "Address")

  // And let's instruct the PDFTable renderer helper to draw the table
  // based in the SQLite database rowset we got in the previous step
  PDFRender.DrawTable(rs, headers)

  // …and save the resulting PDF File to the Desktop
  Var f As FolderItem = SpecialFolder.Desktop.Child("TableFromRowSet.pdf")

  If f <> Nil Then
    d.Save(f)
    f.Open
  End If

Catch e As DatabaseException
  System.DebugLog(e.Message)
Catch e As NilObjectException
  System.DebugLog(e.Message)
End Try

As you can see, all this does is create a new SQLiteDatabase object and assign it to the “db” property. Then, we set the “DataBaseFile” property of that object to the file selected by the user. The code will do a simple check to see if the selected file is the expected “EddiesElectronics” database file. If that is the case, then it will connect to the database, getting a RowSet from a simple query that retrieves the “name”, “surname” and “address” columns from the “Customers” database table.

After creating the PDFDocument instance, the code creates a new instance from our PDFRenderer class and calls the “DrawTable” method on that object passing along both the RowSet and the Headers we want to use for drawing the table.

Lastly, the PDFDocument will be saved to disk and opened in the by default PDF viewer app.

Run the app, select the “EddiesElectronics” SQLite database file and the resulting PDF file will be opened after a few seconds. It will display the table generated from a RowSet data source!

In Summary

It is quite possible to draw tables in a PDFDocument when the data to render comes from a RowSet. Nearly everything was done through the use of a helper class. As always, this can be improved in several ways! For example, we are using a fixed height for every row of the table, but in a more realistic scenario may be using variable heights for the table rows based in the height / amount of data to be drawn. That is something that is doable too!

Javier Menendez is an engineer at Xojo and has been using Xojo since 1998. He lives in Castellón, Spain and hosts regular Xojo hangouts en español. Ask Javier questions on Twitter at @XojoES or on the Xojo Forum.

Published in Cross-Platform, Database, Desktop, Learning, Linux, Mac, Tutorials and Windows