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 }