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.HSSFRichTextString;
27  import org.apache.poi.hssf.usermodel.HSSFRow;
28  import org.apache.poi.hssf.usermodel.HSSFSheet;
29  import org.apache.poi.hssf.usermodel.HSSFWorkbook;
30  import org.apache.poi.hssf.util.CellRangeAddress;
31  import org.apache.poi.hssf.util.HSSFColor;
32  import org.displaytag.decorator.TableDecorator;
33  import org.displaytag.decorator.hssf.DecoratesHssf;
34  import org.displaytag.model.Column;
35  import org.displaytag.model.HeaderCell;
36  import org.displaytag.model.Row;
37  import org.displaytag.model.TableModel;
38  
39  
40  /**
41   * A table writer that formats a table in Excel's spreadsheet format, and writes it to an HSSF workbook.
42   * @author Jorge L. Barroso
43   * @version $Revision$ ($Author$)
44   * @see org.displaytag.render.TableWriterTemplate
45   */
46  public class HssfTableWriter extends TableWriterAdapter
47  {
48  
49      /**
50       * The workbook to which the table is written.
51       */
52      private HSSFWorkbook wb;
53  
54      /**
55       * Generated sheet.
56       */
57      private HSSFSheet sheet;
58  
59      /**
60       * Current row number.
61       */
62      private int rowNum;
63  
64      /**
65       * Current row.
66       */
67      private HSSFRow currentRow;
68  
69      /**
70       * Current column number.
71       */
72      private int colNum;
73  
74      /**
75       * Current cell.
76       */
77      private HSSFCell currentCell;
78  
79      /**
80       * Percent Excel format.
81       */
82      private short pctFormat = HSSFDataFormat.getBuiltinFormat("0.00%");
83  
84      /**
85       * This table writer uses an HSSF workbook to write the table.
86       * @param wb The HSSF workbook to write the table.
87       */
88      public HssfTableWriter(HSSFWorkbook wb)
89      {
90          this.wb = wb;
91      }
92  
93      /**
94       * @see org.displaytag.render.TableWriterTemplate#writeTableOpener(org.displaytag.model.TableModel)
95       */
96      protected void writeTableOpener(TableModel model) throws Exception
97      {
98          this.sheet = wb.createSheet("-");
99          this.rowNum = 0;
100     }
101 
102     /**
103      * @see org.displaytag.render.TableWriterTemplate#writeCaption(org.displaytag.model.TableModel)
104      */
105     protected void writeCaption(TableModel model) throws Exception
106     {
107         HSSFCellStyle style = this.wb.createCellStyle();
108         HSSFFont bold = this.wb.createFont();
109         bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
110         bold.setFontHeightInPoints((short) 14);
111         style.setFont(bold);
112         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
113 
114         this.colNum = 0;
115         this.currentRow = this.sheet.createRow(this.rowNum++);
116         this.currentCell = this.currentRow.createCell(this.colNum);
117         this.currentCell.setCellStyle(style);
118         String caption = model.getCaption();
119         this.currentCell.setCellValue(new HSSFRichTextString(caption));
120         this.rowSpanTable(model);
121     }
122 
123     /**
124      * Obtain the region over which to merge a cell.
125      * @param first Column number of first cell from which to merge.
126      * @param last Column number of last cell over which to merge.
127      * @return The region over which to merge a cell.
128      */
129     private CellRangeAddress getMergeCellsRegion(int first, int last)
130     {
131         return new CellRangeAddress(this.currentRow.getRowNum(), first, this.currentRow.getRowNum(), last);
132     }
133 
134     /**
135      * @see org.displaytag.render.TableWriterTemplate#writeTableHeader(org.displaytag.model.TableModel)
136      */
137     protected void writeTableHeader(TableModel model) throws Exception
138     {
139         this.currentRow = this.sheet.createRow(this.rowNum++);
140         this.colNum = 0;
141         HSSFCellStyle headerStyle = this.getHeaderFooterStyle();
142         for (Iterator iterator = model.getHeaderCellList().iterator(); iterator.hasNext();)
143         {
144             HeaderCell headerCell = (HeaderCell) iterator.next();
145             String columnHeader = headerCell.getTitle();
146             if (columnHeader == null)
147             {
148                 columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
149             }
150 
151             this.writeHeaderFooter(columnHeader, this.currentRow, headerStyle);
152         }
153     }
154 
155     /**
156      * @see org.displaytag.render.TableWriterTemplate#writeDecoratedRowStart(org.displaytag.model.TableModel)
157      */
158     protected void writeDecoratedRowStart(TableModel model)
159     {
160         model.getTableDecorator().startRow();
161     }
162 
163     /**
164      * @see org.displaytag.render.TableWriterTemplate#writeRowOpener(org.displaytag.model.TableModel)
165      */
166     protected void writeRowOpener(Row row) throws Exception
167     {
168         this.currentRow = this.sheet.createRow(rowNum++);
169         this.colNum = 0;
170     }
171 
172     /**
173      * Write a column's opening structure to a HSSF document.
174      * @see org.displaytag.render.TableWriterTemplate#writeColumnOpener(org.displaytag.model.Column)
175      */
176     protected void writeColumnOpener(Column column) throws Exception
177     {
178         column.getOpenTag(); // has side effect, setting its stringValue, which affects grouping logic.
179         this.currentCell = this.currentRow.createCell(this.colNum++);
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(new HSSFRichTextString(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.getColumnIndex(), (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(this.colNum++);
324         this.currentCell.setCellValue(new HSSFRichTextString(value));
325         this.currentCell.setCellStyle(style);
326     }
327 
328     /**
329      * Obtain the style used to render a header or footer.
330      * @return The style used to render a header or footer.
331      */
332     private HSSFCellStyle getHeaderFooterStyle()
333     {
334         HSSFCellStyle style = this.wb.createCellStyle();
335         style.setFillPattern(HSSFCellStyle.FINE_DOTS);
336         style.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
337         HSSFFont bold = this.wb.createFont();
338         bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
339         bold.setColor(HSSFColor.WHITE.index);
340         style.setFont(bold);
341         return style;
342     }
343 
344     /**
345      * @see org.displaytag.render.TableWriterAdapter#writeBottomBanner(org.displaytag.model.TableModel)
346      */
347     protected void writeBottomBanner(TableModel model) throws Exception
348     {
349         // adjust the column widths
350         int colCount = 0;
351         while (colCount <= colNum)
352         {
353             sheet.autoSizeColumn((short) colCount++);
354         }
355     }
356 }