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