View Javadoc

1   package org.displaytag.export.excel;
2   
3   import java.io.OutputStream;
4   import java.util.Calendar;
5   import java.util.Date;
6   import java.util.Iterator;
7   
8   import javax.servlet.jsp.JspException;
9   
10  import org.apache.commons.lang.ObjectUtils;
11  import org.apache.commons.lang.StringEscapeUtils;
12  import org.apache.commons.lang.StringUtils;
13  import org.apache.poi.hssf.usermodel.HSSFCell;
14  import org.apache.poi.hssf.usermodel.HSSFCellStyle;
15  import org.apache.poi.hssf.usermodel.HSSFFont;
16  import org.apache.poi.hssf.usermodel.HSSFRow;
17  import org.apache.poi.hssf.usermodel.HSSFSheet;
18  import org.apache.poi.hssf.usermodel.HSSFWorkbook;
19  import org.apache.poi.hssf.util.HSSFColor;
20  import org.displaytag.Messages;
21  import org.displaytag.exception.BaseNestableJspTagException;
22  import org.displaytag.exception.SeverityEnum;
23  import org.displaytag.export.BinaryExportView;
24  import org.displaytag.model.Column;
25  import org.displaytag.model.ColumnIterator;
26  import org.displaytag.model.HeaderCell;
27  import org.displaytag.model.Row;
28  import org.displaytag.model.RowIterator;
29  import org.displaytag.model.TableModel;
30  
31  
32  /**
33   * Excel exporter using POI HSSF.
34   * @author Fabrizio Giustina
35   * @author rapruitt
36   * @version $Revision: 1086 $ ($Author: rapruitt $)
37   */
38  public class ExcelHssfView implements BinaryExportView
39  {
40  
41      /**
42       * TableModel to render.
43       */
44      private TableModel model;
45  
46      /**
47       * export full list?
48       */
49      private boolean exportFull;
50  
51      /**
52       * include header in export?
53       */
54      private boolean header;
55  
56      /**
57       * decorate export?
58       */
59      private boolean decorated;
60  
61      /**
62       * @see org.displaytag.export.ExportView#setParameters(TableModel, boolean, boolean, boolean)
63       */
64      public void setParameters(TableModel tableModel, boolean exportFullList, boolean includeHeader,
65          boolean decorateValues)
66      {
67          this.model = tableModel;
68          this.exportFull = exportFullList;
69          this.header = includeHeader;
70          this.decorated = decorateValues;
71      }
72  
73      /**
74       * @return "application/vnd.ms-excel"
75       * @see org.displaytag.export.BaseExportView#getMimeType()
76       */
77      public String getMimeType()
78      {
79          return "application/vnd.ms-excel"; //$NON-NLS-1$
80      }
81  
82      /**
83       * @see org.displaytag.export.BinaryExportView#doExport(OutputStream)
84       */
85      public void doExport(OutputStream out) throws JspException
86      {
87          try
88          {
89              HSSFWorkbook wb = new HSSFWorkbook();
90              HSSFSheet sheet = wb.createSheet("-");
91  
92              int rowNum = 0;
93              int colNum = 0;
94  
95              if (this.header)
96              {
97                  // Create an header row
98                  HSSFRow xlsRow = sheet.createRow(rowNum++);
99  
100                 HSSFCellStyle headerStyle = wb.createCellStyle();
101                 headerStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
102                 headerStyle.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
103                 HSSFFont bold = wb.createFont();
104                 bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
105                 bold.setColor(HSSFColor.WHITE.index);
106                 headerStyle.setFont(bold);
107 
108                 Iterator iterator = this.model.getHeaderCellList().iterator();
109 
110                 while (iterator.hasNext())
111                 {
112                     HeaderCell headerCell = (HeaderCell) iterator.next();
113 
114                     String columnHeader = headerCell.getTitle();
115 
116                     if (columnHeader == null)
117                     {
118                         columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
119                     }
120 
121                     HSSFCell cell = xlsRow.createCell((short) colNum++);
122                     cell.setEncoding(HSSFCell.ENCODING_UTF_16);
123                     cell.setCellValue(columnHeader);
124                     cell.setCellStyle(headerStyle);
125                 }
126             }
127 
128             // get the correct iterator (full or partial list according to the exportFull field)
129             RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
130             // iterator on rows
131 
132             while (rowIterator.hasNext())
133             {
134                 Row row = rowIterator.next();
135                 HSSFRow xlsRow = sheet.createRow(rowNum++);
136                 colNum = 0;
137 
138                 // iterator on columns
139                 ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
140 
141                 while (columnIterator.hasNext())
142                 {
143                     Column column = columnIterator.nextColumn();
144 
145                     // Get the value to be displayed for the column
146                     Object value = column.getValue(this.decorated);
147 
148                     HSSFCell cell = xlsRow.createCell((short) colNum++);
149                     cell.setEncoding(HSSFCell.ENCODING_UTF_16);
150 
151                     writeCell(value, cell);
152                 }
153             }
154             wb.write(out);
155         }
156         catch (Exception e)
157         {
158             throw new ExcelGenerationException(e);
159         }
160     }
161 
162     /**
163      * Write the value to the cell.  Override this method if you have complex data types that may need to be exported.
164      * @param value the value of the cell
165      * @param cell the cell to write it to
166      */
167     protected void writeCell(Object value, HSSFCell cell) {
168         if (value instanceof Number)
169         {
170             Number num = (Number) value;
171             cell.setCellValue(num.doubleValue());
172         }
173         else if (value instanceof Date)
174         {
175             cell.setCellValue((Date) value);
176         }
177         else if (value instanceof Calendar)
178         {
179             cell.setCellValue((Calendar) value);
180         }
181         else
182         {
183             cell.setCellValue(escapeColumnValue(value));
184         }
185     }
186 
187     // patch from Karsten Voges
188     /**
189      * Escape certain values that are not permitted in excel cells.
190      * @param rawValue the object value
191      * @return the escaped value
192      */
193     protected String escapeColumnValue(Object rawValue)
194     {
195         if (rawValue == null)
196         {
197             return null;
198         }
199         String returnString = ObjectUtils.toString(rawValue);
200         // escape the String to get the tabs, returns, newline explicit as \t \r \n
201         returnString = StringEscapeUtils.escapeJava(StringUtils.trimToEmpty(returnString));
202         // remove tabs, insert four whitespaces instead
203         returnString = StringUtils.replace(StringUtils.trim(returnString), "\\t", "    ");
204         // remove the return, only newline valid in excel
205         returnString = StringUtils.replace(StringUtils.trim(returnString), "\\r", " ");
206         // unescape so that \n gets back to newline
207         returnString = StringEscapeUtils.unescapeJava(returnString);
208         return returnString;
209     }
210 
211     /**
212      * Wraps IText-generated exceptions.
213      * @author Fabrizio Giustina
214      * @version $Revision: 1086 $ ($Author: rapruitt $)
215      */
216     static class ExcelGenerationException extends BaseNestableJspTagException
217     {
218 
219         /**
220          * D1597A17A6.
221          */
222         private static final long serialVersionUID = 899149338534L;
223 
224         /**
225          * Instantiate a new PdfGenerationException with a fixed message and the given cause.
226          * @param cause Previous exception
227          */
228         public ExcelGenerationException(Throwable cause)
229         {
230             super(ExcelHssfView.class, Messages.getString("ExcelView.errorexporting"), cause); //$NON-NLS-1$
231         }
232 
233         /**
234          * @see org.displaytag.exception.BaseNestableJspTagException#getSeverity()
235          */
236         public SeverityEnum getSeverity()
237         {
238             return SeverityEnum.ERROR;
239         }
240     }
241 
242 }