01: package com.quantum.flatfiles.converter;
02:
03: import java.io.FileOutputStream;
04: import java.io.IOException;
05: import java.util.Date;
06: import java.util.Iterator;
07:
08: import com.quantum.sql.SQLDate;
09: import com.quantum.sql.SQLNull;
10: import com.quantum.sql.SQLResultSetResults;
11:
12: import org.apache.poi.hssf.usermodel.HSSFCell;
13: import org.apache.poi.hssf.usermodel.HSSFRow;
14: import org.apache.poi.hssf.usermodel.HSSFSheet;
15: import org.apache.poi.hssf.usermodel.HSSFWorkbook;
16:
17: /**
18: * @author BC Holmes
19: */
20: class ResultSetToSpreadSheetConverter implements Converter {
21: public void convert(String spreadSheetFileName,
22: SQLResultSetResults results) throws IOException {
23: HSSFWorkbook workbook = createWorkbook(results);
24: FileOutputStream output = new FileOutputStream(
25: spreadSheetFileName);
26: try {
27: workbook.write(output);
28: } finally {
29: output.close();
30: }
31: }
32:
33: private HSSFWorkbook createWorkbook(SQLResultSetResults results) {
34: SQLResultSetResults.Column[] columns = results.getColumns();
35: HSSFWorkbook workbook = new HSSFWorkbook();
36: HSSFSheet sheet = workbook.createSheet();
37: int rowNumber = createColumnHeadings(results, sheet);
38:
39: for (Iterator i = results.getRowIterator(); i.hasNext();) {
40: SQLResultSetResults.Row row = (SQLResultSetResults.Row) i
41: .next();
42: HSSFRow spreadSheetRow = sheet.createRow(rowNumber++);
43: for (int j = 1, length = columns == null ? 0
44: : columns.length; j <= length; j++) {
45: Object cellValue = row.get(j);
46: HSSFCell cell = spreadSheetRow
47: .createCell((short) (j - 1));
48: if (cellValue == null || cellValue instanceof SQLNull) {
49: // skip it
50: } else if (cellValue instanceof String) {
51: cell.setCellValue((String) cellValue);
52: } else if (cellValue instanceof SQLDate) {
53: cell.setCellValue(((SQLDate) cellValue).getDate());
54: } else if (cellValue instanceof Date) {
55: cell.setCellValue((Date) cellValue);
56: } else if (cellValue instanceof Number) {
57: cell.setCellValue(((Number) cellValue)
58: .doubleValue());
59: } else if (cellValue instanceof Boolean) {
60: cell.setCellValue(((Boolean) cellValue).toString());
61: } else {
62: cell.setCellValue(cellValue.toString());
63: }
64: }
65: }
66: return workbook;
67: }
68:
69: /**
70: * @param results
71: * @param sheet
72: * @param rowNumber
73: * @return
74: */
75: private int createColumnHeadings(SQLResultSetResults results,
76: HSSFSheet sheet) {
77: HSSFRow row = sheet.createRow(0);
78: for (int i = 1, length = results == null ? 0 : results
79: .getColumnCount(); i <= length; i++) {
80: HSSFCell cell = row.createCell((short) (i - 1));
81: cell.setCellValue(results.getColumnName(i));
82: }
83: return 1;
84: }
85:
86: /**
87: * @see com.quantum.flatfiles.converter.Converter#getFileExtension()
88: */
89: public String getFileExtension() {
90: return ".xls";
91: }
92: }
|