View Javadoc

1   /**
2    * Licensed under the Artistic License; you may not use this file
3    * except in compliance with the License.
4    * You may obtain a copy of the License at
5    *
6    *      http://displaytag.sourceforge.net/license.html
7    *
8    * THIS PACKAGE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR
9    * IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED
10   * WARRANTIES OF MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
11   */
12  package org.displaytag.render;
13  
14  import java.util.Calendar;
15  import java.util.Date;
16  import java.util.Iterator;
17  
18  import org.apache.commons.lang.ObjectUtils;
19  import org.apache.commons.lang.StringEscapeUtils;
20  import org.apache.commons.lang.StringUtils;
21  import org.apache.commons.lang.math.NumberUtils;
22  import org.apache.poi.hssf.usermodel.HSSFCell;
23  import org.apache.poi.hssf.usermodel.HSSFCellStyle;
24  import org.apache.poi.hssf.usermodel.HSSFDataFormat;
25  import org.apache.poi.hssf.usermodel.HSSFFont;
26  import org.apache.poi.hssf.usermodel.HSSFRow;
27  import org.apache.poi.hssf.usermodel.HSSFSheet;
28  import org.apache.poi.hssf.usermodel.HSSFWorkbook;
29  import org.apache.poi.hssf.util.HSSFColor;
30  import org.apache.poi.hssf.util.Region;
31  import org.displaytag.decorator.TableDecorator;
32  import org.displaytag.decorator.hssf.DecoratesHssf;
33  import org.displaytag.model.Column;
34  import org.displaytag.model.HeaderCell;
35  import org.displaytag.model.Row;
36  import org.displaytag.model.TableModel;
37  
38  
39  /**
40   * A table writer that formats a table in Excel's spreadsheet format, and writes it to an HSSF workbook.
41   * @author Jorge L. Barroso
42   * @version $Revision$ ($Author$)
43   * @see org.displaytag.render.TableWriterTemplate
44   */
45  public class HssfTableWriter extends TableWriterAdapter
46  {
47  
48      /**
49       * The workbook to which the table is written.
50       */
51      private HSSFWorkbook wb;
52  
53      /**
54       * Generated sheet.
55       */
56      private HSSFSheet sheet;
57  
58      /**
59       * Current row number.
60       */
61      private int rowNum;
62  
63      /**
64       * Current row.
65       */
66      private HSSFRow currentRow;
67  
68      /**
69       * Current column number.
70       */
71      private int colNum;
72  
73      /**
74       * Current cell.
75       */
76      private HSSFCell currentCell;
77  
78      /**
79       * Percent Excel format.
80       */
81      private short pctFormat = HSSFDataFormat.getBuiltinFormat("0.00%");
82  
83      /**
84       * This table writer uses an HSSF workbook to write the table.
85       * @param wb The HSSF workbook to write the table.
86       */
87      public HssfTableWriter(HSSFWorkbook wb)
88      {
89          this.wb = wb;
90      }
91  
92      /**
93       * @see org.displaytag.render.TableWriterTemplate#writeTableOpener(org.displaytag.model.TableModel)
94       */
95      protected void writeTableOpener(TableModel model) throws Exception
96      {
97          this.sheet = wb.createSheet("-");
98          this.rowNum = 0;
99      }
100 
101     /**
102      * @see org.displaytag.render.TableWriterTemplate#writeCaption(org.displaytag.model.TableModel)
103      */
104     protected void writeCaption(TableModel model) throws Exception
105     {
106         HSSFCellStyle style = this.wb.createCellStyle();
107         HSSFFont bold = this.wb.createFont();
108         bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
109         bold.setFontHeightInPoints((short) 14);
110         style.setFont(bold);
111         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
112 
113         this.colNum = 0;
114         this.currentRow = this.sheet.createRow(this.rowNum++);
115         this.currentCell = this.currentRow.createCell((short) this.colNum);
116         this.currentCell.setCellStyle(style);
117         String caption = model.getCaption();
118         this.currentCell.setCellValue(caption);
119         this.rowSpanTable(model);
120     }
121 
122     /**
123      * Obtain the region over which to merge a cell.
124      * @param first Column number of first cell from which to merge.
125      * @param last Column number of last cell over which to merge.
126      * @return The region over which to merge a cell.
127      */
128     private Region getMergeCellsRegion(short first, short last)
129     {
130         return new Region(this.currentRow.getRowNum(), first, this.currentRow.getRowNum(), last);
131     }
132 
133     /**
134      * @see org.displaytag.render.TableWriterTemplate#writeTableHeader(org.displaytag.model.TableModel)
135      */
136     protected void writeTableHeader(TableModel model) throws Exception
137     {
138         this.currentRow = this.sheet.createRow(this.rowNum++);
139         this.colNum = 0;
140         HSSFCellStyle headerStyle = this.getHeaderFooterStyle();
141         for (Iterator iterator = model.getHeaderCellList().iterator(); iterator.hasNext();)
142         {
143             HeaderCell headerCell = (HeaderCell) iterator.next();
144             String columnHeader = headerCell.getTitle();
145             if (columnHeader == null)
146             {
147                 columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
148             }
149 
150             this.writeHeaderFooter(columnHeader, this.currentRow, headerStyle);
151         }
152     }
153 
154     /**
155      * @see org.displaytag.render.TableWriterTemplate#writeDecoratedRowStart(org.displaytag.model.TableModel)
156      */
157     protected void writeDecoratedRowStart(TableModel model)
158     {
159         model.getTableDecorator().startRow();
160     }
161 
162     /**
163      * @see org.displaytag.render.TableWriterTemplate#writeRowOpener(org.displaytag.model.TableModel)
164      */
165     protected void writeRowOpener(Row row) throws Exception
166     {
167         this.currentRow = this.sheet.createRow(rowNum++);
168         this.colNum = 0;
169     }
170 
171     /**
172      * Write a column's opening structure to a HSSF document.
173      * @see org.displaytag.render.TableWriterTemplate#writeColumnOpener(org.displaytag.model.Column)
174      */
175     protected void writeColumnOpener(Column column) throws Exception
176     {
177         column.getOpenTag(); // has side effect, setting its stringValue, which affects grouping logic.
178         this.currentCell = this.currentRow.createCell((short) this.colNum++);
179         this.currentCell.setEncoding(HSSFCell.ENCODING_UTF_16);
180     }
181 
182     /**
183      * @see org.displaytag.render.TableWriterTemplate#writeColumnValue(Object,org.displaytag.model.Column)
184      */
185     protected void writeColumnValue(Object value, Column column) throws Exception
186     {
187         if (value instanceof Number)
188         {
189             Number num = (Number) value;
190             // Percentage
191             if (value.toString().indexOf("%") > -1)
192             {
193                 this.currentCell.setCellValue(num.doubleValue() / 100);
194                 HSSFCellStyle cellStyle = this.wb.createCellStyle();
195                 cellStyle.setDataFormat(this.pctFormat);
196                 this.currentCell.setCellStyle(cellStyle);
197             }
198             else
199             {
200                 this.currentCell.setCellValue(num.doubleValue());
201             }
202         }
203         else if (value instanceof Date)
204         {
205             this.currentCell.setCellValue((Date) value);
206         }
207         else if (value instanceof Calendar)
208         {
209             this.currentCell.setCellValue((Calendar) value);
210         }
211         else
212         {
213             this.currentCell.setCellValue(this.escapeColumnValue(value));
214         }
215 
216     }
217 
218     /**
219      * Decorators that help render the table to an HSSF table must implement DecoratesHssf.
220      * @see org.displaytag.render.TableWriterTemplate#writeDecoratedRowFinish(org.displaytag.model.TableModel)
221      */
222     protected void writeDecoratedRowFinish(TableModel model) throws Exception
223     {
224         TableDecorator decorator =  model.getTableDecorator();
225         if (decorator instanceof DecoratesHssf)
226         {
227             DecoratesHssf hdecorator = (DecoratesHssf) decorator;
228             hdecorator.setSheet(this.sheet);
229         }
230         decorator.finishRow();
231         this.rowNum = this.sheet.getLastRowNum();
232         this.rowNum++;
233     }
234 
235     /**
236      * @see org.displaytag.render.TableWriterTemplate#writePostBodyFooter(org.displaytag.model.TableModel)
237      */
238     protected void writePostBodyFooter(TableModel model) throws Exception
239     {
240         this.colNum = 0;
241         this.currentRow = this.sheet.createRow(this.rowNum++);
242         this.writeHeaderFooter(model.getFooter(), this.currentRow, this.getHeaderFooterStyle());
243         this.rowSpanTable(model);
244     }
245 
246     /**
247      * Make a row span the width of the table.
248      * @param model The table model representing the rendered table.
249      */
250     private void rowSpanTable(TableModel model)
251     {
252         this.sheet.addMergedRegion(this.getMergeCellsRegion(this.currentCell.getCellNum(), (short) (model
253             .getNumberOfColumns() - 1)));
254     }
255 
256     /**
257      * @see org.displaytag.render.TableWriterTemplate#writeDecoratedTableFinish(org.displaytag.model.TableModel)
258      */
259     protected void writeDecoratedTableFinish(TableModel model)
260     {
261         model.getTableDecorator().finish();
262     }
263 
264     // patch from Karsten Voges
265     /**
266      * Escape certain values that are not permitted in excel cells.
267      * @param rawValue the object value
268      * @return the escaped value
269      */
270     protected String escapeColumnValue(Object rawValue)
271     {
272         if (rawValue == null)
273         {
274             return null;
275         }
276         String returnString = ObjectUtils.toString(rawValue);
277         // escape the String to get the tabs, returns, newline explicit as \t \r \n
278         returnString = StringEscapeUtils.escapeJava(StringUtils.trimToEmpty(returnString));
279         // remove tabs, insert four whitespaces instead
280         returnString = StringUtils.replace(StringUtils.trim(returnString), "\\t", "    ");
281         // remove the return, only newline valid in excel
282         returnString = StringUtils.replace(StringUtils.trim(returnString), "\\r", " ");
283         // unescape so that \n gets back to newline
284         returnString = StringEscapeUtils.unescapeJava(returnString);
285         return returnString;
286     }
287 
288     /**
289      * Is this value numeric? You should probably override this method to handle your locale.
290      * @param rawValue the object value
291      * @return true if numeric
292      */
293     protected boolean isNumber(String rawValue)
294     {
295         if (rawValue == null)
296         {
297             return false;
298         }
299         String rawV = rawValue;
300         if (rawV.indexOf('%') > -1)
301         {
302             rawV = rawV.replace('%', ' ').trim();
303         }
304         if (rawV.indexOf('$') > -1)
305         {
306             rawV = rawV.replace('$', ' ').trim();
307         }
308         if (rawV.indexOf(',') > -1)
309         {
310             rawV = StringUtils.replace(rawV, ",", "");
311         }
312         return NumberUtils.isNumber(rawV.trim());
313     }
314 
315     /**
316      * Writes a table header or a footer.
317      * @param value Header or footer value to be rendered.
318      * @param row The row in which to write the header or footer.
319      * @param style Style used to render the header or footer.
320      */
321     private void writeHeaderFooter(String value, HSSFRow row, HSSFCellStyle style)
322     {
323         this.currentCell = row.createCell((short) this.colNum++);
324         this.currentCell.setCellValue(value);
325         this.currentCell.setCellStyle(style);
326         this.currentCell.setEncoding(HSSFCell.ENCODING_UTF_16);
327     }
328 
329     /**
330      * Obtain the style used to render a header or footer.
331      * @return The style used to render a header or footer.
332      */
333     private HSSFCellStyle getHeaderFooterStyle()
334     {
335         HSSFCellStyle style = this.wb.createCellStyle();
336         style.setFillPattern(HSSFCellStyle.FINE_DOTS);
337         style.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
338         HSSFFont bold = this.wb.createFont();
339         bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
340         bold.setColor(HSSFColor.WHITE.index);
341         style.setFont(bold);
342         return style;
343     }
344 }