6

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

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?

fmsf
  • 36,317
  • 49
  • 147
  • 195
ping
  • 1,229
  • 3
  • 21
  • 41

2 Answers2

7

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.

tucuxi
  • 17,561
  • 2
  • 43
  • 74
  • Upvoted. Sounds good. I'd like to open a bounty on this question to check if there are more alternatives. Thanks! – ping Aug 04 '12 at 08:49
  • How is creating a 'styled template' different from using JXLS ? If not please edit your answer to include mention of JXLS. – ping Aug 04 '12 at 11:41
  • 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? :-) – tucuxi Aug 04 '12 at 11:46
1

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

Horcrux7
  • 23,758
  • 21
  • 98
  • 156
  • Propriety software is not an option for me as long as there are open source libraries that get the job done. Thanks though. – ping Aug 04 '12 at 11:36