PDF reports from RowSet

I am newly looking into generating PDF reports from a web app. The report should download on the client side so that they can view in browser (perhaps auto open) or open in their preferred pdf viewer.

The reports would largely consist of database query results in tabular format such as with borders e.g. from a RowSet. Can anyone provide a simple example of doing this without third-party plugins? I came across PDFDocument which may be useful? Although an example would be helpful:

[PDFDocument — Xojo documentation](PDFDocument — Xojo documentation

In addition to data tables, I’d like to include images and other text content on the page (more as a template e.g. page header and footer). The database query may also flow across multiple pages so not sure if it can handle this automatically in the pdf report.

Any helpful pointers/examples would be massively appreciated.

I used to have a Method that converted a RowSet into a PDF using DynaPDFMBS, but now I use a Method that converts a RowSet into HTML, then I use wkHTML2PDF to convert this into a PDF, adding headers and footers if required.

This creates the RowSet Headings in HTML:

Protected Function getTableHeadingsHTML(rs As RowSet) As String
  Var HTMLArray(-1) As String
  if rs = nil or rs.AfterLastRow then Return ""
  'Report Table Start
  HTMLArray.AddRow("<table align=""center"" border=""1"" cellpadding=""1"" cellspacing=""1"" style=""width:500px;"">")
  HTMLArray.AddRow("    <tbody>")
  HTMLArray.AddRow("        <tr>")
  for tempInt As Integer = 1 to rs.ColumnCount
    HTMLArray.AddRow("        <td style=""text-align: center;""><b>" + CommonStrings.getCorrectHeadingNameWAD(rs.ColumnAt(tempInt -1).Name) + "</b></td>")
  HTMLArray.AddRow("        </tr>")
  Return String.FromArray(HTMLArray, EndOfLine)
End Function

This adds the RowSet values to the HTML:

Protected Function getTableValuesHTML(rs As RowSet) As String
  Var HTMLArray(-1) As String
  if rs = nil or rs.AfterLastRow then Return ""
  While Not rs.AfterLastRow
    HTMLArray.AddRow("        <tr>")
    for tempInt As Integer = 0 to rs.ColumnCount - 1
      HTMLArray.AddRow("            <td style=""text-align: center;"">" + rs.ColumnAt(tempInt).StringValue + "</td>")
    HTMLArray.AddRow("        </tr>")
  HTMLArray.AddRow("    </tbody>")
  Return String.FromArray(HTMLArray, EndOfLine)
End Function

I would love Xojo to add HTML to PDF capability, but it is very unlikely.

somehow you need a flag that a new page begins.
a count of fix rows or the y location or a new group starts.

pdfgraphics have a method NextPage
for images see DrawPicture

Thanks that’s interesting…
Is wkHTML2PDF something we can use within Xojo to make the PDF from the HTML you generated for example?

Yes, you can call wkHTML2PDF from a Shell in Windows, macOS or Linux.

You pass the Shell the wkHTML2PDF EXE path, the source HTML path, the output PDF path plus any parameters listed on the wkHTML2PDF web site.

Great…I will look into this.
Does that mean wkHTML2PDF will handle multiple pages automatically from the HTML?
Also, do you have to specify a download location of the PDF? Can it auto open in client side browser? Or ask them where to save?

Yes, it can handle multi-page HTML/PDF files with ease. Yes, you will need to specify the FolderItem to save the PDF. You must still download the PDF as a WebFile to the client’s browser. No, you cannot get the browser to ask where to save the downloaded PDF file, it will always save to the Downloads folder. No, you cannot tell the client to open this PDF, they must open it from their Downloads folder manually.

1 Like

@David_Cox How can I pass html into wkhtmlpdf directly (or using a string variable like your method returns in above example) instead of from a url? Not sure if this is a valid way, but I tried the following:

echo "<b>hello world</b>" | wktmltopdf - C:\Users\This.User\Downloads\test.pdf

I am getting an ExitCode of 0 after executing the shell command on the shell object, however it is not saving in the above location (when testing web app on development PC localhost).
Any ideas what’s going wrong?

You cannot. Place the HTML inside a file and pass the file’s ShellPath as a parameter.