Hi,
*avoiding 'layout' cells that do not need to be there.
*cell borders (line or rectangle object?).
*overlap-issues (if one label/field is touching/overlapping another, the '2nd' will not show up in the XLS at all).
[quote]
One tip I can share is that lines can not start at X position '0', it must be '0.1' or will not render.
[/qoute]
Using a manual definition in XML (the only way to prove that the engine messed up and not one of the GUI tools 
) I had no problems adding a horizontal or vertical line at (x=0, y=0). But I encountered problems adding a vertical line at the *end* of the band (at x=100%, there is no such representation in the designer) - such lines got ignored.
Tracker for that:
http://sourceforge.net/tracker/index...69&atid=464140
*Esablish the line object directly on the 'left' side of field/label objects. Do not overlap field/label on the right side/width (i.e. 119.9 instead of exactly 120 widths to avoid overlaps). Left margin for both horizontal and vertical lines start at 0.1, not 0.0, otherwise will not render. Note that this will cause a 'mini' column A, but does not seem to be a work around.
and
If the line itself covers *exactly* the bounds of the text-element, it will export correctly without any extra spaces.
Example (Using the Simple-XML format
Code:
<band>
<line name="top" x1="10" y1="10" x2="110" y2="10"/>
<line name="bottom" x1="10" y1="30" x2="110" y2="30"/>
<line name="left" x1="10" y1="10" x2="10" y2="30"/>
<line name="right" x1="110" y1="10" x2="110" y2="30"/>
<label x="10" y="10" width="100" height="20">Test</label>
</band>
However, you may run into a Report Designer specific issue here, as the report designer uses a different text-height algorithm than the ordinary reporting engine. A font with height 14 usually has a slightly larger effective size as there is some extra space reserved for accents and other extra-stuff. So such an text-element easily gets a height of 16 or more (depends on the font). One solution would be to place the line slightly below the text element - in the default mode the Excel exporter will then increase the height of the text element to the next line (or any other break).
Bottom borders for cells are nigh impossible. The only solution I have found is to draw a line where you normally would want to, then assuming there are no other elements below where you want the line, draw ANOTHER line below it (2 lines as the only items in the bottom of the band). Removing one or the other will cause non-render problems in Excel/XLS export, but leaving as-is would render double-lines in all other formats.
Bug of the engine. Here's the tracker:
http://sourceforge.net/tracker/index...69&atid=464140
Page layout/sizing during design does not seem to be respected in the XLS export (i.e. print preview is very different in Excel then what is visible in the designer). Cell/Column widths with regards to relative page location in the Designer does not match in XLS export. What this means is although you designed for portrait or landscape width, the actual Excel print preview will span multiple pages because the width is not the same.
The text and cell sizing is a dark chapter. In a dark and moonless night, the very first Excel developer went out into the woods to worship some ancient daemons. Armed with a half-rotten book on C programming (written on the skin of slain FORTRAN programmers with the blood of COBOL developers by an occult sect using an forgotten magical language called LISP) he summoned the most evil daemons (no, not Bill Gates, he's not that bad after all) and asked how to make this world as misserable as he felt that day. (When you feel bad, there's nothing better than sharing your pain with the world.)
Two days later he finished the first specification on how Excel will store cell sizes.
Excel stores the cell width in an abstract measurement, which is based on the width of the character 'm' of the first font that is declared in the workbook. The height of a cell is specified in points (some sort of). Every system has its own way to measure font sizes. Your JDK happily assumes font sizes based on what the font renderer backend tells it. A font height, however is not just Ascend + Descend - it is also based on the maximum glyph size and a lot of other things (for instance how the font-backend interprets all the funny numbers inside the *.ttf file). So most of the time, fonts are very plattform dependent. In JFreeReport, measuring the cell height is some sort of guessing - we try to use some magic calculations to get a result that has a high probability to look good on most systems.
There is no way, how anybody can know, how Excel will render a workbook. The only sane way would be to use Excel itself to compute the cell sizes - but that functionality is not exposed (even if you dare to start coding a COM+ component to access the Excel object).
Missing feature - padding
http://jira.pentaho.org:8080/browse/REPORTING-216
(And at some point it would be wise to tell more about the way the excel exports work. In the next posting ...)
Have fun,
said Thomas
Tips for generating Excel reports?
Reply With Quote
