What are the best practices for rendering excel sheet reports in Java?

Question

I have a need to generate an excel sheet report which looks like the following template --

I know this can be done using libraries like JExcelApi and Apache POI. However I would like to know if there are means of accomplishing this by writing a template similar to writing Apache Velocity templates for sending emails.

If there is a standard, good-practice approach for generating such reports what is it?

Houseclean

The uploaded image was what i was referring to when i said 'template' – Houseclean

Answer 1

Why not create a styled template using Excel? Use placeholders (like ${name.of.field}) for the values that should be substituted, but otherwise style everything as required. Use a different placeholder to mark the end-of-template (this will allow you to write comments or other data beyond this space, to document your template).

Then, either via JExcelApi or Apache POI,

• open the template file, and locate the end-of-template marker.
• open the target file
• for each record, copy fields from the template, substituting ${xyz}-values for their actual contents and stopping once you reach your end-of-template marker.
• close target & template

Template modification will be extremely easy. The only problem is keeping the field-names in sync.

Educable

Upvoted. Sounds good. I'd like to open a bounty on this question to check if there are more alternatives. Thanks! – Houseclean

How is creating a 'styled template' different from using JXLS ? If not please edit your answer to include mention of JXLS. – Houseclean

Open a new excel document. Format it to your heart's content. Fill it with placeholders. Call it a "template", because you will be replicating it and filling in the placeholders later. If that is what JXLS does, my proposal is not to use it: where is the need? :-) – Educable

Answer 2

You can use a standard Java reporting library like i-net Clear Reports and use the excel output format.

Fermat

Propriety software is not an option for me as long as there are open source libraries that get the job done. Thanks though. – Houseclean