1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.extremecomponents.table.view;
17
18 import java.util.HashMap;
19 import java.util.Iterator;
20 import java.util.List;
21 import java.util.Map;
22
23 import org.apache.commons.lang.StringUtils;
24 import org.apache.commons.logging.Log;
25 import org.apache.commons.logging.LogFactory;
26 import org.apache.poi.hssf.usermodel.HSSFCell;
27 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
28 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
29 import org.apache.poi.hssf.usermodel.HSSFFont;
30 import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
31 import org.apache.poi.hssf.usermodel.HSSFRow;
32 import org.apache.poi.hssf.usermodel.HSSFSheet;
33 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
34 import org.apache.poi.hssf.util.HSSFColor;
35 import org.extremecomponents.table.model.TableModel;
36 import org.extremecomponents.table.model.TableModelUtils;
37 import org.extremecomponents.table.model.TableProperties;
38 import org.extremecomponents.table.model.ViewHandler;
39
40
41 /***
42 * com.extremecomp.table.view.XlsView.java -
43 *
44 * @author paul horn
45 */
46 public class XlsView implements View
47 {
48 private static Log logger = LogFactory.getLog(XlsView.class);
49 public static final int WIDTH_MULT = 280;
50 public static final int MIN_CHARS = 8;
51 public static final short DEFAULT_FONT_HEIGHT = 8;
52 public static final double NON_NUMERIC = -.99999;
53 public static final String DEFAULT_MONEY_FORMAT = "$###,###,##0.00";
54 public static final String DEFAULT_PERCENT_FORMAT = "##0.0%";
55
56 private HSSFWorkbook wb;
57 private HSSFSheet sheet;
58 private HSSFPrintSetup ps;
59 private Map styles;
60 private short rownum = 1;
61 private short cellnum = 0;
62 private HSSFRow row;
63 private String moneyFormat;
64 private String percentFormat;
65
66 public XlsView()
67 {
68 TableProperties props = new TableProperties();
69 moneyFormat = props.getProperty(TableProperties.EXPORT + "format.money");
70 if (StringUtils.isEmpty(moneyFormat))
71 {
72 moneyFormat = DEFAULT_MONEY_FORMAT;
73 }
74 percentFormat = props.getProperty(TableProperties.EXPORT + "format.percent");
75 if (StringUtils.isEmpty(percentFormat))
76 {
77 percentFormat = DEFAULT_PERCENT_FORMAT;
78 }
79 }
80
81
82 public void beforeBody(TableModel model)
83 {
84 logger.debug("XlsView.init()");
85
86 wb = new HSSFWorkbook();
87 sheet = wb.createSheet();
88 wb.setSheetName(0, "Export Workbook");
89
90 styles = initStyles(wb);
91 ps = sheet.getPrintSetup();
92
93 sheet.setAutobreaks(true);
94 ps.setFitHeight((short) 1);
95 ps.setFitWidth((short) 1);
96
97 createHeader(model);
98 }
99
100 public void body(TableModel model, String value, boolean isFirstColumn, boolean isLastColumn)
101 {
102 if (isFirstColumn)
103 {
104 rownum++;
105 cellnum = 0;
106 row = sheet.createRow(rownum);
107 }
108
109 HSSFCell cell = row.createCell(cellnum);
110 writeToCellFormatted(cell, value);
111 cellnum++;
112 }
113
114 public Object afterBody(TableModel model)
115 {
116 return wb;
117 }
118 private void createHeader(TableModel model)
119 {
120 double columnCount = model.getColumnMetaData().columnCount();
121 short rownum = 0;
122 short cellnum = 0;
123 HSSFRow row = sheet.createRow(rownum);
124
125 List header = TableModelUtils.getHeader(model, ViewHandler.VALUE);
126
127 for (Iterator iter = header.iterator(); iter.hasNext();)
128 {
129 String title = (String) iter.next();
130
131 HSSFCell cell = row.createCell(cellnum);
132 cell.setCellStyle((HSSFCellStyle) styles.get("titleStyle"));
133 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
134 cell.setCellValue(title);
135 cellnum++;
136 }
137 }
138
139 private void writeToCellFormatted(HSSFCell cell, String value)
140 {
141 double numeric = NON_NUMERIC;
142
143 try
144 {
145 numeric = Double.parseDouble(value);
146 }
147 catch (Exception e)
148 {
149 numeric = NON_NUMERIC;
150 }
151
152 if (value.startsWith("$") || value.endsWith("%") || value.startsWith("($"))
153 {
154 boolean moneyFlag = (value.startsWith("$") || value.startsWith("($"));
155 boolean percentFlag = value.endsWith("%");
156
157 value = StringUtils.replace(value, "$", "");
158 value = StringUtils.replace(value, "%", "");
159 value = StringUtils.replace(value, ",", "");
160 value = StringUtils.replace(value, "(", "-");
161 value = StringUtils.replace(value, ")", "");
162
163 try
164 {
165 numeric = Double.parseDouble(value);
166 }
167 catch (Exception e)
168 {
169 numeric = NON_NUMERIC;
170 }
171
172 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
173
174 if (moneyFlag)
175 {
176
177 cell.setCellStyle((HSSFCellStyle) styles.get("moneyStyle"));
178 }
179 else if (percentFlag)
180 {
181
182 numeric = numeric / 100;
183 cell.setCellStyle((HSSFCellStyle) styles.get("percentStyle"));
184 }
185 }
186 else if (numeric != NON_NUMERIC)
187 {
188
189 cell.setCellStyle((HSSFCellStyle) styles.get("numericStyle"));
190 }
191 else
192 {
193
194 cell.setCellStyle((HSSFCellStyle) styles.get("textStyle"));
195 }
196
197 fixWidthAndPopulate(cell, numeric, value);
198 }
199
200 private void fixWidthAndPopulate(HSSFCell cell, double numeric, String value)
201 {
202 int valWidth = 0;
203
204 if (numeric != NON_NUMERIC)
205 {
206 cell.setCellValue(numeric);
207 valWidth = (cell.getNumericCellValue() + "").length() * WIDTH_MULT;
208 }
209 else
210 {
211 cell.setCellValue(value);
212 valWidth = (cell.getStringCellValue() + "").length() * WIDTH_MULT;
213
214 if (valWidth < (WIDTH_MULT * MIN_CHARS))
215 {
216 valWidth = WIDTH_MULT * MIN_CHARS;
217 }
218 }
219
220 if (valWidth > sheet.getColumnWidth(cell.getCellNum()))
221 {
222 sheet.setColumnWidth(cell.getCellNum(), (short) valWidth);
223 }
224 }
225
226 private Map initStyles(HSSFWorkbook wb)
227 {
228 return initStyles(wb, DEFAULT_FONT_HEIGHT);
229 }
230
231 private Map initStyles(HSSFWorkbook wb, short fontHeight)
232 {
233 Map result = new HashMap();
234 HSSFCellStyle titleStyle = wb.createCellStyle();
235 HSSFCellStyle textStyle = wb.createCellStyle();
236 HSSFCellStyle boldStyle = wb.createCellStyle();
237 HSSFCellStyle numericStyle = wb.createCellStyle();
238 HSSFCellStyle numericStyleBold = wb.createCellStyle();
239 HSSFCellStyle moneyStyle = wb.createCellStyle();
240 HSSFCellStyle moneyStyleBold = wb.createCellStyle();
241 HSSFCellStyle percentStyle = wb.createCellStyle();
242 HSSFCellStyle percentStyleBold = wb.createCellStyle();
243
244 result.put("titleStyle", titleStyle);
245 result.put("textStyle", textStyle);
246 result.put("boldStyle", boldStyle);
247 result.put("numericStyle", numericStyle);
248 result.put("numericStyleBold", numericStyleBold);
249 result.put("moneyStyle", moneyStyle);
250 result.put("moneyStyleBold", moneyStyleBold);
251 result.put("percentStyle", percentStyle);
252 result.put("percentStyleBold", percentStyleBold);
253
254 HSSFDataFormat format = wb.createDataFormat();
255
256
257 HSSFFont font = wb.createFont();
258 font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
259 font.setColor(HSSFColor.BLACK.index);
260 font.setFontName(HSSFFont.FONT_ARIAL);
261 font.setFontHeightInPoints(fontHeight);
262
263 HSSFFont fontBold = wb.createFont();
264 fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
265 fontBold.setColor(HSSFColor.BLACK.index);
266 fontBold.setFontName(HSSFFont.FONT_ARIAL);
267 fontBold.setFontHeightInPoints(fontHeight);
268
269
270 moneyStyle.setFont(font);
271 moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
272 moneyStyle.setDataFormat(format.getFormat(moneyFormat));
273
274
275 moneyStyleBold.setFont(fontBold);
276 moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
277 moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));
278
279
280 percentStyle.setFont(font);
281 percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
282 percentStyle.setDataFormat(format.getFormat(percentFormat));
283
284
285 percentStyleBold.setFont(fontBold);
286 percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
287 percentStyleBold.setDataFormat(format.getFormat(percentFormat));
288
289
290 numericStyle.setFont(font);
291 numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
292
293
294 numericStyleBold.setFont(fontBold);
295 numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
296
297
298 titleStyle.setFont(font);
299 titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
300 titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
301 titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
302 titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
303 titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
304 titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
305 titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
306 titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
307 titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
308 titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
309 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
310 titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
311
312
313 textStyle.setFont(font);
314 textStyle.setWrapText(true);
315
316
317 boldStyle.setFont(fontBold);
318 boldStyle.setWrapText(true);
319
320 return result;
321 }
322 }