15 March 2008

Bringing Jasper Reports, Rails and Rjb together

Whew, talk about difficult. On Windows Server 2003 anyway (business choice, not mine)

Firstly, Rjb (Ruby Java Bridge) requires, absolutely must have, the SDK not just the JRE.

Secondly, get the stuff working in Ruby before trying Rails.

This is what I did.

Install the Rjb Gem. Set JAVA_HOME. Watch out for space in "Program Files" in path, use PROGRAM~1 instead. I installed the SDK to C:\Java to avoid this.

Follow most of HowtoIntegrateJasperReports

I could not get the pipe IO stuff running on Windows - just locked up the server.
I then read anything found on Jasper and found references to PHP Java Bridge with Jasper here, this got me thinking could I use a Ruby Java Bridge to do something like the XmlJasperInterface.java source in Ruby and Rjb without the command line? Eventually. Yes!!

Here is the local ruby...
require 'rjb'

separator = Config::CONFIG['target_os'] =~ /win/ ? ';' : ':'
classpath = [
"./itext-1.3.1.jar",
"./commons-beanutils-1.7.jar",
"./commons-collections-2.1.jar",
"./commons-logging-1.0.2.jar",
"./jasperreports-2.0.4.jar",
"./jasperreports-extensions-1.3.1.jar",
"./jcommon-1.0.0.jar",
"./jdt-compiler-3.1.1.jar",
"./jfreechart-1.0.0.jar",
"./log4j-1.2.9.jar",
"./poi-3.0.1-FINAL-20070705.jar",
"./xalan.jar"
].join(separator)

# xmls is a string representing the @collection.to_xml when on rails

xmls = %Q¬<?xml version="1.0" encoding="UTF-8"?>
<customs-shipping-details>
<customs-shipping-detail>
... lots of attribute nodes
</customs-shipping-detail>
... more customs shipping detail records
</customs-shipping-details>¬

Rjb::load(classpath, ['-Xmx128m'])

j_jre = Rjb::import('net.sf.jasperreports.engine.JRException')
j_jem = Rjb::import('net.sf.jasperreports.engine.JasperExportManager')
j_jfm = Rjb::import('net.sf.jasperreports.engine.JasperFillManager')
j_jp = Rjb::import('net.sf.jasperreports.engine.JasperPrint')
j_jxds = Rjb::import('net.sf.jasperreports.engine.data.JRXmlDataSource')

j_sxis = Rjb::import('org.xml.sax.InputSource')
j_jrxu = Rjb::import('net.sf.jasperreports.engine.util.JRXmlUtils')
j_iosr = Rjb::import('java.io.StringReader')
j_map = Rjb::import('java.util.HashMap')
out = Rjb::import('java.lang.System').out

compiled_design_file_path = './templates/csd_1.jasper'
xpath_filter = '/customs-shipping-details/customs-shipping-detail'
pdf_path = './out102.pdf'
rmap = j_map.new()

xis = j_sxis.new()
xis.setCharacterStream(j_iosr.new(xmls))
jp = j_jfm.fillReport(compiled_design_file_path, rmap,
j_jxds.new_with_sig('Lorg.w3c.dom.Document;Ljava.lang.String;',
j_jrxu.parse(xis), xpath_filter))
j_jem.exportReportToPdfFile(jp,pdf_path)


The most frustrating bit was to get the JRXmlDataSource to accept String data and not stream data. The new_with_sig method of Rjb is not perfect; I could not invoke the ByteArrayInputStream constructor with a byte array using the getBytes method of a Java String and the '[B;' signature.

After hunting through the Jasper API I found that I could produce a org.w3c.dom.Document from the static parse method of the JRXmlUtils class. But the parse method needed a org.xml.sax.InputSource object.

Again, the Reader based constructor for the InputSource could not be invoked so I had to use the default and set the Reader source with the setCharacterStream method of the InputSource object. It was easy to use the StringReader object to wrap the XML data string before passing it to the setCharacterStream method.

Moving on to rails.
I created a folder under apps called reports with lib and templates subfolders. I copied the jasper templates into templates and the jar files into lib.

You need this in config/environment.rb ENV['JAVA_HOME'] = "C:\\Java\\jdk1.6.0_05", pointing to your java SDK. Obviously the Rjb gem on the Rails server too.

I created this module in the rails lib folder.

module Jasport
require 'rjb'

separator = Config::CONFIG['target_os'] =~ /win/ ? ';' : ':'
classpath = [
"#{RAILS_ROOT}/app/reports/lib/itext-1.3.1.jar",
"#{RAILS_ROOT}/app/reports/lib/commons-beanutils-1.7.jar",
"#{RAILS_ROOT}/app/reports/lib/commons-collections-2.1.jar",
"#{RAILS_ROOT}/app/reports/lib/commons-logging-1.0.2.jar",
"#{RAILS_ROOT}/app/reports/lib/jasperreports-2.0.4.jar",
"#{RAILS_ROOT}/app/reports/lib/jdt-compiler-3.1.1.jar",
"#{RAILS_ROOT}/app/reports/lib/jfreechart-1.0.0.jar",
"#{RAILS_ROOT}/app/reports/lib/log4j-1.2.9.jar",
#"#{RAILS_ROOT}/app/reports/lib/jasperreports-extensions-1.3.1.jar",
#"#{RAILS_ROOT}/app/reports/lib/jcommon-1.0.0.jar",
#"#{RAILS_ROOT}/app/reports/lib/poi-3.0.1-FINAL-20070705.jar",
"#{RAILS_ROOT}/app/reports/lib/xalan.jar"
].join(separator)

Rjb::load(classpath, ['-Xmx128m'])

class ReportGenerator

def return_pdf(compiled_design_name, xpath_filter, xml_data)
compiled_design_file_path = "#{RAILS_ROOT}/app/reports/templates/" + compiled_design_name + '.jasper'
@xis.setCharacterStream(@j_iosr.new(xml_data))
@jp = @j_jfm.fillReport(compiled_design_file_path, @rmap, @j_jxds.new_with_sig('Lorg.w3c.dom.Document;Ljava.lang.String;', @j_jrxu.parse(@xis), xpath_filter))
@j_jem.exportReportToPdf(@jp)
end

def initialize
#@j_jre = Rjb::import('net.sf.jasperreports.engine.JRException')
#@j_jp = Rjb::import('net.sf.jasperreports.engine.JasperPrint')

@j_jem = Rjb::import('net.sf.jasperreports.engine.JasperExportManager')
@j_jfm = Rjb::import('net.sf.jasperreports.engine.JasperFillManager')
@j_jxds = Rjb::import('net.sf.jasperreports.engine.data.JRXmlDataSource')
@j_sxis = Rjb::import('org.xml.sax.InputSource')
@j_jrxu = Rjb::import('net.sf.jasperreports.engine.util.JRXmlUtils')
@j_iosr = Rjb::import('java.io.StringReader')
@rmap = Rjb::import('java.util.HashMap').new
@xis = @j_sxis.new()
end
end
end

Note: I am assuming that the JVM is loaded once. I don't know about GC and the Rjb::import objects, it seems that they are ruby proxy stubs so I guess they will get garbage collected as normal. I tried the Rjb::unload method in the local trial but it seg faulted the ruby interpreter, I did not try it on Rails.

This technique opens up using other Java libraries.

10 March 2008

Excel Exporting: Multiple Worksheets

Right... onto the first nugget. How does one create multiple worksheet workbook export from Rails?

My solution was to save a sample as xml using Excel, then to break it up into chunks of static partials, a bit like this.
- workbook start
- worksheet start
- worksheet end
- worksheet start
- worksheet end
- workbook end

I chose to combine workbook start with worksheet start, worksheet end with worksheet start and worksheet end with workbook end

I created quite a few .rxml files that internally looked like this...
xml << %Q¬
<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>author</Author> <Company>Company</Company>
<Version>11.8107</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>7545</WindowHeight>
<WindowWidth>16260</WindowWidth>
<WindowTopX>165</WindowTopX>
<WindowTopY>105</WindowTopY>
<ActiveSheet>2</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font/> <Interior/> <NumberFormat/> <Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
¬

NOTE: There are more Excel Style sections than shown above.
I called this partial _wb_start.rxml.

If all of your worksheets share common header rows you can create another partial with this column data in it, like so...
xml << %Q¬
<Column ss:AutoFitWidth="0" ss:Width="100.5"/>
<Column ss:AutoFitWidth="0" ss:Width="65.5"/>
<Column ss:AutoFitWidth="0" ss:Width="134.25"/>
<Column ss:AutoFitWidth="0" ss:Width="134.25"/>
<Column ss:AutoFitWidth="0" ss:Width="105"/>
<Column ss:AutoFitWidth="0" ss:Width="105"/>
<Column ss:AutoFitWidth="0" ss:Width="28.5"/>
<Column ss:AutoFitWidth="0" ss:Width="251.5"/>
<Column ss:AutoFitWidth="0" ss:Width="63.75"/>
<Column ss:AutoFitWidth="0" ss:Width="15.5"/>
<Column ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:AutoFitWidth="0" ss:Width="465.5"/>
<Row ss:AutoFitHeight="0" ss:Height="18" ss:StyleID="s24">
<Cell ss:StyleID="s22"/>
<Cell ss:StyleID="s23"/>
<Cell ss:StyleID="s23"/>
<Cell ss:StyleID="s23"/>
<Cell ss:StyleID="s23"/>
<Cell ss:StyleID="s23"/>
</Row>
<Row>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 01</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 02</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 03</Data></Cell>
<Cell ss:StyleID="s43"><Data ss:Type="String">Col head 04</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 05</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 06</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 07</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 08</Data></Cell>
<Cell ss:StyleID="s43"><Data ss:Type="String">Col head 09</Data></Cell>
<Cell ss:StyleID="s45"/>
<Cell ss:StyleID="s46"><Data ss:Type="String">Col head 10</Data></Cell>
<Cell ss:StyleID="s46"><Data ss:Type="String">Col head 11</Data></Cell>
<Cell ss:StyleID="s46"><Data ss:Type="String">Col head 12</Data></Cell>
</Row>
¬

You would then include this partial for each worksheet, details later.

Then you need to create another partial which ends one worksheet and starts the next, or you could have a generic end-worksheet partial. Keep doing this until you have a bunch to partials which describe the start and end of the workbook and sheets.
Here is an example of a worksheet end with a worksheet start

xml << %Q¬
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.51181102362204722"/>
<Footer x:Margin="0.51181102362204722"/>
<PageMargins x:Bottom="0.59055118110236227" x:Left="0.74803149606299213"
x:Right="0.55118110236220474" x:Top="0.59055118110236227"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>0</VerticalResolution>
</Print>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>36</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<Sorting xmlns="urn:schemas-microsoft-com:office:excel">
<Sort>Part Number</Sort>
</Sorting>
</Worksheet>
<Worksheet ss:Name="RMA Form">
¬


I then created a rxml file which is rendered from the action in the controller.
xml << render(:partial => 'wb_start_ws1_start')
xml.Table do
xml << render(:partial => 'ws_headings')
#dynamic data here
end #table
xml << render(:partial => 'ws1_end_ws2_start')
xml.Table do
xml << render(:partial => 'ws_headings')
#dynamic data here
end # table
xml << render(:partial => 'ws2_end_ws3_start')
xml.Table do
xml << render(:partial => 'ws_headings')
#dynamic data here
end # table
xml << render(:partial => 'ws3_end_wb_end')

Note the bit that goes: xml << render(:partial => 'wb_start')
This is how you render the static xml into the dynamic Builder output at certain points.
The #dynamic data bit looks like this
  aattrs = [:fld1,:fld2,:fld3,:fld4]
atypes = ['String','String','Number','String']
len = aattrs.length - 1
@collection.each do obj
xml.Row do
(0..len).each do i
xml.Cell 'ss:StyleID'=>"s76" do
dat = obj.send(aattrs[i])
if dat
xml.Data dat, 'ss:Type' => atypes[i]
else
xml.Data "#N/A", 'ss:Type' => 'String'
end
end
end
# add extra cells to pad out the styles
xml.Cell 'ss:StyleID'=>"s45"
3.times do
xml.Cell 'ss:StyleID'=>"s46" #nice bordered light blue boxes
end
end


So there you have it. You can produce Excel XML multiple worksheet workbook exports from Rails with exactly the styles and number formats as a regular Excel workbook.
Far better than CSV.

Initial

I started this as a means to share some of the Rails (and other) hacks I have done in the main application I am developing; a reverse logistics tracking system from a distributors point of view, sandwiched between a European dealer network and US manufacturers.