XSLT+XSL-FO + FOP = PDF

In a recent short discussion on FriendFeed, Benjamin Good asked me what are the reporting tool I've used. On my potential list there was:

• Jasper Reports
• Apache PDFBox
• the recent Eclipse BRIT

.But the only tool have used so far to produce a PDF document is a XSL-FO document converted with Apache FOP. XSL-FO is an XML vocabulary for specifying formatting semantics and FOP is a print formatter driven by XSL formatting objects (XSL-FO) and an output independent formatter. It is a Java application that reads a formatting object (FO) tree and renders the resulting pages to a specified output. Output formats currently supported include PDF, PS, PCL, AFP, XML, Print, AWT and PNG, and to a lesser extent, RTF and TXT. The primary output target is PDF.

Image via oracle

Here is an example: Say I've got the following MYSQL result formatted as XML (option --xml on the command line).

<resultset statement="select chrom,chromStart,name,avHet from snp129 where avHet&gt;0 limit 300 ">
<row>
<field name="chrom">chr1</field>
<field name="chromStart">4332</field>
<field name="name">rs3210717</field>
<field name="avHet" xsi:nil="true"/>
</row>

<row>
<field name="chrom">chr1</field>
<field name="chromStart">4363</field>
<field name="name">rs1045871</field>
<field name="avHet">0.4278</field>
</row>

(...)

<row>
<field name="chrom">chr1</field>
<field name="chromStart">1023856</field>
<field name="name">rs6698368</field>
<field name="avHet">0.459861</field>
</row>
</resultset>

To convert this result as a XSL-FO Table we need the following XSLT stylesheet (available here). This stylesheet will recognize the rs### numbers and will create a link to the NCBI for each SNP.

<xsl:stylesheet version="1.0">
<!--

Motivation:
transforms a mysql resultset XML to XSL-FO
Author:
Pierre Lindenbaum PhD plindenbaum@yahoo.fr
http://plindenbaum.blogspot.com
Usage:
fop -xml source-mysql.xml -xsl mysql2fo.xsl -pdf result.pdf

-->
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/">
<fo:root>
<xsl:comment>Created with mysql2fo Pierre Lindenbaum http://plindenbaum.blogspot.com</xsl:comment>
<fo:layout-master-set>



<fo:simple-page-master master-name="main" margin-top="36pt" margin-bottom="36pt" page-width="210mm" page-height="297mm" margin-left="1cm" margin-right="1cm">
<fo:region-body margin-bottom="50pt" margin-right="50pt"/>
</fo:simple-page-master>
</fo:layout-master-set>


<fo:page-sequence master-reference="main">


<fo:flow flow-name="xsl-region-body">
<xsl:apply-templates select="resultset"/>
</fo:flow>
</fo:page-sequence>

</fo:root>
</xsl:template>


<xsl:template match="resultset">
<xsl:choose>
<xsl:when test="count(row)>0">
<xsl:variable name="cols" select="count(row[1]/field)"/>
<fo:block><xsl:value-of select="@statement"/></fo:block>




<fo:table table-layout="fixed" border-collapse="collapse" width="100%" font-size="12pt" font-family="Arial">
<xsl:for-each select="row[1]/field">
<xsl:element name="fo:table-column">
<xsl:attribute name="column-width"><xsl:value-of select="100.0 div $cols"/><xsl:text>%</xsl:text></xsl:attribute>
</xsl:element>
</xsl:for-each>
<fo:table-header color="white" background-color="blue" font-weight="bold">
<fo:table-row>
<xsl:for-each select="row[1]/field">
<xsl:element name="fo:table-cell">
<xsl:attribute name="padding">2pt</xsl:attribute>
<xsl:attribute name="background-color">brown</xsl:attribute>
<xsl:attribute name="color">white</xsl:attribute>
<xsl:attribute name="font-weight">bold</xsl:attribute>
<xsl:attribute name="font-size">14pt</xsl:attribute>
<xsl:attribute name="text-align">center</xsl:attribute>
<fo:block><xsl:value-of select="@name"/></fo:block>
</xsl:element>
</xsl:for-each>
</fo:table-row>
</fo:table-header>
<fo:table-body>
<xsl:for-each select="row">
<xsl:element name="fo:table-row">
<xsl:attribute name="background-color">
<xsl:choose>
<xsl:when test="position() mod 2 = 1">#FCF6CF</xsl:when>
<xsl:otherwise>#FEFEF2</xsl:otherwise>
</xsl:choose>
</xsl:attribute>

<xsl:for-each select="field">
<fo:table-cell padding="2pt" border="1pt solid black">
<xsl:choose>
<xsl:when test="@xsi:nil='true'">
<fo:block><fo:inline font-style="italic" color="gray">NULL</fo:inline></fo:block>
</xsl:when>
<xsl:otherwise>
<fo:block><xsl:call-template name="content"><xsl:with-param name="text" select="."/></xsl:call-template></fo:block>
</xsl:otherwise>
</xsl:choose>
</fo:table-cell>
</xsl:for-each>
</xsl:element>
</xsl:for-each>
</fo:table-body>
</fo:table>
</xsl:when>
<xsl:otherwise>
<fo:flow flow-name="xsl-region-body">
<fo:block font-size="14pt" line-height="16pt">
No Result
</fo:block>
</fo:flow>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

<!-- analyse the content and tries to create an hyperlink -->
<xsl:template name="content">
<xsl:param name="text"/>
<xsl:choose>
<xsl:when test="string-length($text)>2 and starts-with($text,'rs') and string-length(translate(substring($text,3),'0123456789',''))=0">
<xsl:element name="fo:basic-link">
<xsl:attribute name="color">blue</xsl:attribute>
<xsl:attribute name="external-destination">http://www.ncbi.nlm.nih.gov/projects/SNP/snp_ref.cgi?rs=<xsl:value-of select="substring($text,3)"/></xsl:attribute>
<xsl:value-of select="$text"/>
</xsl:element>
</xsl:when>
<xsl:when test="starts-with($text,'http://') or starts-with($text,'https://') or starts-with($text,'mailto://') or starts-with($text,'ftp://')">
<xsl:element name="fo:basic-link">
<xsl:attribute name="color">blue</xsl:attribute>
<xsl:attribute name="external-destination"><xsl:value-of select="$text"/></xsl:attribute>
<xsl:value-of select="$text"/>
</xsl:element>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>

The transformed XSL-FO document:

<fo:root>
<!-- Created with mysql2fo Pierre Lindenbaum http://plindenbaum.blogspot.com -->
<fo:layout-master-set>
<fo:simple-page-master master-name="main" margin-top="36pt" margin-bottom="36pt" page-width="210mm" page-height="297mm" margin-left="1cm" margin-right="1cm">
<fo:region-body margin-bottom="50pt" margin-right="50pt"/>
</fo:simple-page-master>
</fo:layout-master-set>
<fo:page-sequence master-reference="main">
<fo:flow flow-name="xsl-region-body">
<fo:block>select chrom,chromStart,name,avHet from snp129 where avHet>0 limit 300 </fo:block>
<fo:table table-layout="fixed" border-collapse="collapse" width="100%" font-size="12pt" font-family="Arial">
<fo:table-column column-width="25%"/>
<fo:table-column column-width="25%"/>
<fo:table-column column-width="25%"/>
<fo:table-column column-width="25%"/>
<fo:table-header color="white" background-color="blue" font-weight="bold">
<fo:table-row>
<fo:table-cell padding="2pt" background-color="brown" color="white" font-weight="bold" font-size="14pt" text-align="center">
<fo:block>chrom</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" background-color="brown" color="white" font-weight="bold" font-size="14pt" text-align="center">
<fo:block>chromStart</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" background-color="brown" color="white" font-weight="bold" font-size="14pt" text-align="center">
<fo:block>name</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" background-color="brown" color="white" font-weight="bold" font-size="14pt" text-align="center">
<fo:block>avHet</fo:block>
</fo:table-cell>
</fo:table-row>
</fo:table-header>
<fo:table-body>
<fo:table-row background-color="#FCF6CF">
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>chr1</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>4332</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>
<fo:basic-link color="blue" external-destination="http://www.ncbi.nlm.nih.gov/projects/SNP/snp_ref.cgi?rs=3210717">rs3210717</fo:basic-link>
</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>
<fo:inline font-style="italic" color="gray">NULL</fo:inline>
</fo:block>
</fo:table-cell>
</fo:table-row>
<fo:table-row background-color="#FEFEF2">
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>chr1</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>4363</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>
<fo:basic-link color="blue" external-destination="http://www.ncbi.nlm.nih.gov/projects/SNP/snp_ref.cgi?rs=1045871">rs1045871</fo:basic-link>
</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>0.4278</fo:block>
</fo:table-cell>
</fo:table-row>
<fo:table-row background-color="#FCF6CF">
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>chr1</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>1023856</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>
<fo:basic-link color="blue" external-destination="http://www.ncbi.nlm.nih.gov/projects/SNP/snp_ref.cgi?rs=6698368">rs6698368</fo:basic-link>
</fo:block>
</fo:table-cell>
<fo:table-cell padding="2pt" border="1pt solid black">
<fo:block>0.459861</fo:block>
</fo:table-cell>
</fo:table-row>
</fo:table-body>
</fo:table>
</fo:flow>
</fo:page-sequence>
</fo:root>

All in one: we use FOP to transform the mysql result with the stylesheet and to create a PDF document

fop -xml source-mysql.xml -xsl mysql2fo.xsl -pdf result.pdf

Here is the result uploaded on Scribd:

XSL-FO test

That's it.
Pierre