Every PHP developer will sooner or later face a request to output a document as a PDF, XLS, DOC or ODF. Exporting each format by hand is as time consuming as it is dull – especially things like point-by-point positioning of elements in a PDF. So how can we get around this tedious chore using PHP?
One option is the Livedocx service with Zend_Service_LiveDocx support in ZendFramework. You create a template, provide the data and select the output format. Nice, but you are depending on a third party webservice. Some clients and employers might understandably be unhappy that their data will be processed somewhere else.
Ok, so what else can PHP offer? There are a few libraries out there which you can use for each individual format export, like Zend_Pdf and Pear Spreadsheet_Excel_Writer. The disadvantage here is that the process of creating more complex documents is slow and painful; you can only export to the one selected format.
These methods won’t always be practical, so what else can we try? Consider the MVC design pattern, using a database to provide the the data as a model, template reports as the view and of course PHP handling as your controller, deciding what goes where. This design would allow us to maintain, change and modify templates independently from our data and without any need to change any code; on top of that it lends itself well to testing and releasing work.
As mentioned, current support for reports and reporting in PHP isn’t ideal, so why not look in the world outside PHP? Java contains a few really nice and powerful reporting systems (Jasper Reports, Birt, Pentao), all of which come with visual report designers and provide really clever solutions for any possible reports and documents which you might need to populate.
My favourite, which I’ve been using for couple of years now, is Jasper Reports. This features a reports generating engine (Jasper reports), visual report designer (iReport) and web server for storing and executing reports (Jasper server).
In this article I’ll show you how easily we can create multi format documents using Java and how pass our data from PHP.
Reports design
Report design is done in a WYSIWYG editor called iReport. It’s a desktop Java application, available for Windows, Linux and Mac. The editor is based on the popular NetBeans IDE, so anybody familiar with this environment will be happy. Whether you’ve used this environment before or not you should find the app very user friendly and well documented. Reports are stored as XML files, later compiled to an internal .jasper format. You can pass parameters to a report (for example date, from – to) and use these parameters in a SQL query to load in the data for your report.
Data
As a data source for your report, you can use a SQL query. Personally I prefer to call stored procedures which holds all the business logic, you don’t have to change a report when you’re tweaking data in a report. When a report is executed, Jasper reports connects to a database, executing SQL commands using data returned from the database.
Running report
Because Jasper reports is a Java library, you can’t call it directly from PHP (forget about PHP Java extensions, you’ll be entering a world of pain).
Ultimately, you have three options:
- Install and use Jasper server webservice – it comes with a PHP driver, it works but needs a little bit of code cleaning to make it work with PHP 5.3.
- PHP/Java Bridge – Java application allow you to call Java static methods from PHP, this works but you have to install it on a server and most likely run a second Java web application server.
- Executing a Java application from the command line – the simplest solution, but probably not the fastest.
In our case I will use the third option, just to keep things simple and also to allow anybody to easily test it, or even use it.
To call Java from PHP use this simple method to create a report in ReportCreator class, which just creates a command string, executing the command and checking the result:
<?php
/**
*
* Bridge class calling java tool to populate compiled jasper reports
*
*/
class ReportCreator{
const REPORT_INVALID_PARAMS = 8;
const REPORT_DOESNOT_EXISTS = 2;
const REPORT_CREATED_REPORT_EXISTS = 3;
const REPORT_NOT_SUPORTED_OUTPUT_FORMAT = 4;
const REPORT_INVALID_CONFIGURATION_FILE = 5;
const REPORT_INVALID_REPORT_PARAMETERS = 6;
const REPORT_ERROR_EXECUTION = 7;
const REPORT_ITERNAL_ERROR = 1;
protected $reportName;
protected $generatedReportName;
protected $reportFormat;
protected $availableExporters = array('pdf','xls','docx','ods','odt','html');
protected $reportsDir;
protected $reportsExportDir;
/**
*
*
* @param string $reportName
* @param string $generatedReportName
* @param string $reportFormat
*/
function __construct($reportName, $generatedReportName, $reportFormat){
$this->reportFormat = $reportFormat;
$this->generatedReportName = $generatedReportName;
$this->reportName = $reportName;
$this->reportsDir = realpath(dirname(__FILE__)).DIRECTORY_SEPARATOR ;
$this->reportsExportDir = realpath(dirname(__FILE__)).DIRECTORY_SEPARATOR ;
$this->init();
}
private function init(){
if(!file_exists($this->reportsDir.$this->reportName)){
throw new InvalidArgumentException("Report doesn't exists ".$this->reportsDir.$this->reportName);
}
$pathInfo = pathinfo($this->reportsDir.$this->reportName);
if($pathInfo['extension'] != "jrxml") throw new InvalidArgumentException(" .jrxml must be provided and same jasperreports version used.");
if(!in_array($this->reportFormat, $this->availableExporters)){
throw new InvalidArgumentException("Unsupported export format");
}
}
/**
*
* Calling java .jar creator and population report
* @param array $parameters
* @throws Exception
*/
public function createReport(array $parameters = null){
$config = new MyConfig();
$output = array();
if(!file_exists("reporting.jar")) throw new Exception("reporting.jar file not found");
$command = "java -jar reporting.jar ";
$command .= "\"".$this->reportsDir.$this->reportName."\" \"".$this->reportsExportDir.$this->generatedReportName."\" ".$this->reportFormat." \"".addslashes(json_encode($config))."\"";
if(!is_null($parameters)){
$serialliazedParams = addslashes(json_encode($parameters));
$command .= " \"".$serialliazedParams."\"";
}
exec($command, $output, $returnValue);
if(is_array($output)) $output = implode($output,' ');
switch($returnValue){
case self::REPORT_DOESNOT_EXISTS:
throw new Exception("ERROR - report doesn't exists \n".$output, self::REPORT_DOESNOT_EXISTS);
break;
case self::REPORT_CREATED_REPORT_EXISTS:
throw new Exception("ERROR - generated report already exists \n".$output, self::REPORT_CREATED_REPORT_EXISTS);
break;
case self::REPORT_NOT_SUPORTED_OUTPUT_FORMAT:
throw new Exception("ERROR - not supported output format \n".$output, self::REPORT_NOT_SUPORTED_OUTPUT_FORMAT);
break;
case self::REPORT_INVALID_PARAMS:
throw new Exception("ERROR - invalid parameters for report creation \n".$output, self::REPORT_INVALID_PARAMS);
break;
case self::REPORT_INVALID_CONFIGURATION_FILE:
throw new Exception("ERROR - not valid configuration file \n".$output, self::REPORT_INVALID_CONFIGURATION_FILE);
break;
case self::REPORT_INVALID_REPORT_PARAMETERS:
throw new Exception("ERROR - invalid report parameters \n".$output, self::REPORT_INVALID_REPORT_PARAMETERS);
break;
case self::REPORT_ERROR_EXECUTION:
throw new Exception("ERROR - problem during executing report \n".$output, self::REPORT_ERROR_EXECUTION);
break;
case self::REPORT_ITERNAL_ERROR:
throw new Exception("ERROR - iternal error, something wrong with report population - invalid params? \n".$output, self::REPORT_ITERNAL_ERROR);
break;
default:
return(true);
break;
}
}
}Note addslashes(json_encode($parameters)). This allow us to pass configuration or report parameters to Java. Here I’m using json, with json_encode, this means you can pass arrays or objects. You need to use addslashes because it’s passed as a parameter and quotes are causing problems.
A simple Java class which reads parameters, executes report and stores results to a provided location:
package com.loftdigital.reporting;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.DynaBean;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRExporter;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.design.JasperDesign;
import net.sf.jasperreports.engine.export.JRHtmlExporter;
import net.sf.jasperreports.engine.export.JRPdfExporter;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
import net.sf.jasperreports.engine.export.oasis.JROdsExporter;
import net.sf.jasperreports.engine.export.oasis.JROdtExporter;
import net.sf.jasperreports.engine.export.ooxml.JRDocxExporter;
import net.sf.jasperreports.engine.xml.JRXmlLoader;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;
public class Report {
static Connection conn = null;
static JasperReport jasperReport;
static JasperPrint jasperPrint;
public static void main(String[] args) throws Exception {
String reportFileName = null;
String reportName = null;
String reportFormat = null;
DynaBean configBrean = null;
DynaBean paramBean = null;
ArrayList<String> supportedFormats = new ArrayList<String>();
supportedFormats.add("xls");
supportedFormats.add("docx");
supportedFormats.add("pdf");
supportedFormats.add("ods");
supportedFormats.add("odt");
supportedFormats.add("html");
if(args.length < 4){
System.out.println("Required: report file name, format, json config, - optional json report params");
System.exit(8);
}
reportFileName = args[0];
if(new File(reportFileName).exists() == false){
System.out.println("Report doesn't exist");
System.exit(2);
}
reportName = args[1];
if(new File(reportName).exists() == true){
System.out.println("Generated report already exist");
System.exit(3);
}
reportFormat = args[2];
if(!supportedFormats.contains(reportFormat)){
System.out.println("Not supported format" + reportFormat);
System.exit(4);
}
try{
JSONObject jsonObject = JSONObject.fromObject(args[3]);
configBrean = (DynaBean) JSONObject.toBean( jsonObject );
}
catch(JSONException exception){
System.out.println("Not valid config - " + exception.getMessage() + " - " + args[3] );
System.exit(5);
}
if(args.length == 5){
try{
JSONObject jsonObject = JSONObject.fromObject(args[4]);
paramBean = (DynaBean) JSONObject.toBean( jsonObject );
}
catch(JSONException exception){
System.out.println("Not valid params - " + exception.getMessage());
System.exit(6);
}
}
String userName = (String) configBrean.get("db_user");
String password = (String) configBrean.get("db_pass");
String url = "jdbc:mysql://" + (String) configBrean.get("db_host") + "/" + (String) configBrean.get("db_name");
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(url, userName, password);
JRExporter exporter = null;
try {
JasperDesign jasperDesign = JRXmlLoader.load(reportFileName);
JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
jasperPrint = JasperFillManager.fillReport(jasperReport, BeanUtils.describe(paramBean), conn);
if(reportFormat.equals("pdf")){
exporter = new JRPdfExporter();
}
else if(reportFormat.equals("xls")){
exporter = new JRXlsExporter();
exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
if(paramBean != null){
@SuppressWarnings("unchecked")
ArrayList<String> sheetNamesArray = (ArrayList<String>) paramBean.get("shteetNames");
String[] sheetNames = new String[sheetNamesArray.size()];
int i = 0;
for( String s : sheetNamesArray){
sheetNames[i] = s;
i++;
}
exporter.setParameter(JRXlsExporterParameter.SHEET_NAMES, sheetNames);
}
}
else if(reportFormat.equals("docx")){
exporter = new JRDocxExporter();
}
else if(reportFormat.equals("ods")){
exporter = new JROdsExporter();
}
else if(reportFormat.equals("odt")){
exporter = new JROdtExporter();
}
else if(reportFormat.equals("html")){
exporter = new JRHtmlExporter();
}
else{
throw new IllegalArgumentException("Not supported format" + reportFormat);
}
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME , reportName);
exporter.exportReport();
} catch (JRException e) {
System.out.println("Error creating report - " + e.getMessage());
System.exit(7);
}
System.out.println("Report created");
System.exit(0);
}
}Things like database connections and configurations are passed from PHP (in our case $config object) and used (configBrean.get(“db_host”)), you don’t have to be a hardcode dev to work with live environment variables.
