1
2
3
4
5
6 package oscon2006.common;
7
8 import org.apache.poi.hssf.usermodel.*;
9 import java.sql.*;
10 import java.io.*;
11 import java.util.*;
12 import java.math.*;
13 import javax.swing.table.TableModel;
14 import au.com.bytecode.opencsv.*;
15
16 /**
17 *
18 *
19 *
20 * @author Sean C. Sullivan
21 *
22 *
23 *
24 */
25 public class XlsBuilder
26 {
27 private HSSFCellStyle defaultStyle;
28 private int rownum = -1;
29 private HSSFRow currentRow;
30 private HSSFSheet currentSheet;
31 private HSSFWorkbook wb;
32 private static final int DEFAULT_COLUMN_WIDTH = 16;
33 private static final int DEFAULT_ROW_HEIGHT = 200;
34 private Object data;
35 private int[] columnWidths;
36
37 public XlsBuilder()
38 {
39 super();
40 }
41
42 public void setColumnWidths(int... widths)
43 {
44 columnWidths = widths;
45 }
46
47 public int[] getColumnWidths()
48 {
49 return columnWidths;
50 }
51
52 protected HSSFRow newRow()
53 {
54 currentRow = currentSheet.createRow(++rownum);
55 return currentRow;
56 }
57
58 protected HSSFSheet newSheet(String name)
59 {
60 currentSheet = wb.createSheet();
61
62 currentSheet.setAutobreaks(false);
63 currentSheet.setDefaultColumnWidth( (short) DEFAULT_COLUMN_WIDTH);
64 currentSheet.setDefaultRowHeight( (short) DEFAULT_ROW_HEIGHT);
65 currentSheet.setSelected(true);
66
67 if (this.getColumnWidths() != null)
68 {
69 int[] widths = this.getColumnWidths();
70 for (int i = 0; i < widths.length; i++)
71 {
72 currentSheet.setColumnWidth( (short) i, (short) (256 * widths[i]));
73 }
74 }
75
76 wb.setSheetName(0, name);
77
78 return currentSheet;
79 }
80
81 protected HSSFCell createCell(int index)
82 {
83 HSSFCell c = currentRow.createCell( (short) index);
84 c.setCellStyle(getDefaultCellStyle());
85 return c;
86 }
87
88
89 public void build(String filename) throws IOException
90 {
91 FileOutputStream fos = null;
92 try
93 {
94 fos = new FileOutputStream(filename);
95 build(fos);
96 }
97 finally
98 {
99 if (fos != null)
100 {
101 fos.close();
102 }
103 }
104 }
105
106 public byte[] build() throws IOException
107 {
108 ByteArrayOutputStream baos = new ByteArrayOutputStream();
109 build(baos);
110 return baos.toByteArray();
111 }
112
113 public void build(OutputStream out) throws IOException
114 {
115 HSSFWorkbook wb = buildWorkbook();
116 wb.write(out);
117 out.flush();
118 }
119
120 protected Object getData()
121 {
122 return this.data;
123 }
124
125 protected HSSFWorkbook buildWorkbook() throws IOException
126 {
127 if (this.getData() instanceof ResultSet)
128 {
129 return buildWorkbook( (ResultSet) this.getData() );
130 }
131 else if (this.getData() instanceof CSVReader)
132 {
133 return buildWorkbook( (CSVReader) this.getData() );
134 }
135 else if (this.getData() instanceof TableModel)
136 {
137 return buildWorkbook( (TableModel) this.getData() );
138 }
139 else
140 {
141 throw new IllegalStateException("getData() returned " + this.getData());
142 }
143
144 }
145
146
147 protected HSSFWorkbook buildWorkbook(CSVReader reader)
148 throws IOException
149 {
150 String[] row = null;
151
152 wb = new HSSFWorkbook();
153
154 newSheet("data");
155
156
157 while ( (row = reader.readNext()) != null)
158 {
159 newRow();
160 for (int i = 0; i < row.length; i++)
161 {
162 HSSFCell cell = createCell( i );
163 cell.setCellValue( row[i] );
164 }
165 }
166
167 return wb;
168 }
169
170
171 protected HSSFWorkbook buildWorkbook(TableModel model)
172 throws java.io.IOException
173 {
174
175 final int rowCount = model.getRowCount();
176 final int columnCount = model.getColumnCount();
177
178 wb = new HSSFWorkbook();
179
180 newSheet("data");
181
182 newRow();
183
184 int colCount = model.getColumnCount();
185 for (int colIndex = 0; colIndex < colCount; colIndex++)
186 {
187 HSSFCell cell = createCell( colIndex );
188 String s = model.getColumnName(colIndex);
189 cell.setCellValue( s );
190 }
191
192 for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
193 {
194 newRow();
195
196 for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
197 {
198 HSSFCell cell = createCell( columnIndex );
199 Object value = model.getValueAt(rowIndex, columnIndex);
200 if (value == null)
201 {
202 cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
203 }
204 else if (value instanceof Number)
205 {
206 Number n = (Number) value;
207 cell.setCellValue(n.doubleValue());
208 }
209 else if (value instanceof Boolean)
210 {
211 Boolean b = (Boolean) value;
212 cell.setCellValue(b.booleanValue());
213 }
214 else if (value instanceof java.util.Calendar)
215 {
216 HSSFCellStyle style = wb.createCellStyle();
217 style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
218 Calendar c = (Calendar) value;
219 cell.setCellValue(c);
220 cell.setCellStyle(style);
221 }
222 else if (value instanceof java.util.Date)
223 {
224 HSSFCellStyle style = wb.createCellStyle();
225 style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
226 java.util.Date d = (java.util.Date) value;
227 cell.setCellValue(d);
228 cell.setCellStyle(style);
229 }
230 else
231 {
232 cell.setCellValue(String.valueOf(value));
233 }
234 }
235 }
236
237 return wb;
238
239 }
240
241
242 protected HSSFWorkbook buildWorkbook(java.sql.ResultSet rs) throws IOException
243 {
244
245 try
246 {
247 ResultSetMetaData metadata = rs.getMetaData();
248
249 final int colCount = metadata.getColumnCount();
250
251 wb = new HSSFWorkbook();
252
253 newSheet("data");
254
255 newRow();
256
257 for (int colIndex = 1; colIndex <= colCount; colIndex++)
258 {
259 HSSFCell cell = createCell( (colIndex - 1) );
260 String colName = metadata.getColumnLabel(colIndex);
261 if ( (colName == null) || (colName.length() == 0))
262 {
263 colName = metadata.getColumnName(colIndex);
264 }
265 cell.setCellValue(colName);
266 }
267
268 while (rs.next())
269 {
270 newRow();
271
272 for (int colIndex = 1; colIndex <= colCount; colIndex++)
273 {
274
275 int colType = metadata.getColumnType(colIndex);
276
277 HSSFCell cell = createCell( colIndex - 1 );
278
279 HSSFCellStyle style = null;
280
281 switch (colType)
282 {
283 case Types.BIT:
284 Object bit = rs.getObject(colIndex);
285 String s = String.valueOf(bit);
286 cell.setCellValue(s);
287 break;
288 case Types.BOOLEAN:
289 boolean b = rs.getBoolean(colIndex);
290 if ( ! rs.wasNull())
291 {
292 cell.setCellValue(b);
293 }
294 break;
295 case Types.CLOB:
296 String clob = read(rs.getClob(colIndex));
297 cell.setCellValue(clob);
298 break;
299 case Types.BIGINT:
300 case Types.DECIMAL:
301 case Types.DOUBLE:
302 case Types.FLOAT:
303 case Types.REAL:
304 case Types.NUMERIC:
305 cell.setCellValue(rs.getBigDecimal(colIndex).doubleValue());
306 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
307 break;
308 case Types.INTEGER:
309 case Types.TINYINT:
310 case Types.SMALLINT:
311 int value = rs.getInt(colIndex);
312 if ( ! rs.wasNull())
313 {
314 cell.setCellValue(value);
315 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
316 }
317 break;
318 case Types.JAVA_OBJECT:
319 Object obj = rs.getObject(colIndex);
320 if (obj != null)
321 {
322 cell.setCellValue(String.valueOf(obj));
323 }
324 break;
325 case Types.DATE:
326 style = wb.createCellStyle();
327 style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
328 cell.setCellValue(rs.getDate(colIndex));
329 cell.setCellStyle(style);
330 break;
331 case Types.TIME:
332 style = wb.createCellStyle();
333 style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
334 Time t = rs.getTime(colIndex);
335 cell.setCellValue(t);
336 cell.setCellStyle(style);
337 break;
338 case Types.TIMESTAMP:
339 style = wb.createCellStyle();
340 style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
341 Timestamp tstamp = rs.getTimestamp(colIndex);
342 cell.setCellValue(tstamp);
343 cell.setCellStyle(style);
344 break;
345 case Types.LONGVARCHAR:
346 case Types.VARCHAR:
347 case Types.CHAR:
348 String st = rs.getString(colIndex);
349 cell.setCellValue(st);
350 break;
351 case Types.NULL:
352 cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
353 break;
354 default:
355 cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
356 }
357 }
358 }
359 }
360 catch (SQLException ex)
361 {
362 throw new RuntimeException(ex);
363 }
364
365 return wb;
366 }
367
368 public void setDefaultCellStyle(HSSFCellStyle style)
369 {
370 this.defaultStyle = style;
371 }
372
373 protected HSSFCellStyle getDefaultCellStyle()
374 {
375 if (defaultStyle == null)
376 {
377 defaultStyle = wb.createCellStyle();
378 }
379 return defaultStyle;
380 }
381
382 /**
383 *
384 * @param data a java.sql.ResultSet or javax.swing.table.TableModel
385 *
386 */
387 public void setData(Object data)
388 {
389 this.data = data;
390 }
391
392 private static String read(Clob c) throws SQLException, IOException
393 {
394 StringBuffer sb = new StringBuffer( (int) c.length());
395 Reader r = c.getCharacterStream();
396 char[] cbuf = new char[2048];
397 int n = 0;
398 while ( (n = r.read(cbuf, 0, cbuf.length)) != -1)
399 {
400 if (n > 0)
401 {
402 sb.append(cbuf, 0, n);
403 }
404 }
405 return sb.toString();
406 }
407 }