1
2
3
4
5 package oscon2006.common;
6
7 import java.io.*;
8 import java.util.*;
9 import java.sql.*;
10 import org.apache.poi.hssf.*;
11 import org.apache.poi.hssf.usermodel.*;
12 import au.com.bytecode.opencsv.*;
13
14 public class CsvBuilder
15 {
16 private Object data;
17 private String targetWorksheet;
18
19 public CsvBuilder()
20 {
21
22 }
23
24 /**
25 *
26 * @param wb
27 *
28 * @see #setTargetWorksheet(String)
29 *
30 */
31 public void setData(Object obj)
32 {
33 this.data = obj;
34 }
35
36
37 public void setTargetWorksheet(String value)
38 {
39 this.targetWorksheet = value;
40 }
41
42 public File build(String csvFilename)
43 throws IOException
44 {
45
46 File csvFile = new File(csvFilename);
47 FileOutputStream fos = null;
48
49 try
50 {
51 fos = new FileOutputStream(csvFile);
52
53 build(fos);
54 }
55 finally
56 {
57 fos.close();
58 }
59
60 return csvFile;
61 }
62
63
64 public Object getData()
65 {
66 return this.data;
67 }
68
69 public byte[] build() throws IOException
70 {
71 ByteArrayOutputStream baos = new ByteArrayOutputStream();
72 this.build(baos);
73 return baos.toByteArray();
74 }
75
76 public void build(OutputStream out)
77 throws IOException
78 {
79 if (this.getData() == null)
80 {
81 throw new IllegalStateException("data is null");
82 }
83
84 if (this.getData() instanceof HSSFWorkbook)
85 {
86 processHSSFWorkbook(
87 (HSSFWorkbook) this.getData(),
88 out);
89 }
90 else if (this.getData() instanceof ResultSet)
91 {
92 processResultSet(
93 (ResultSet) this.getData(),
94 out);
95 }
96 else
97 {
98 throw new IllegalStateException("unknown data type");
99 }
100
101 out.flush();
102
103 }
104
105 protected void processHSSFWorkbook(HSSFWorkbook workbook, OutputStream out)
106 throws IOException
107 {
108 OutputStreamWriter oswriter = new OutputStreamWriter(out);
109
110 CSVWriter writer = new CSVWriter(oswriter);
111
112 try
113 {
114
115 HSSFSheet sheet = null;
116
117 if (this.targetWorksheet == null)
118 {
119 targetWorksheet = workbook.getSheetName(0);
120 }
121
122 sheet = workbook.getSheet(targetWorksheet);
123
124 if (sheet == null)
125 {
126 throw new RuntimeException("sheete name does not exist: " + targetWorksheet);
127 }
128
129 processHSSFSheet(sheet, targetWorksheet, writer);
130
131 }
132 catch (Exception ex)
133 {
134 throw new RuntimeException(ex);
135 }
136 finally
137 {
138 writer.close();
139 }
140
141 }
142
143 protected void processHSSFSheet(HSSFSheet sheet, String sheetName, CSVWriter writer)
144 throws IOException
145 {
146
147
148 for (int x = sheet.getFirstRowNum(); x <= sheet.getLastRowNum(); x++)
149 {
150 HSSFRow row = sheet.getRow(x);
151
152 String[] rowValues = getRowValues(row);
153
154 writer.writeNext(rowValues);
155 }
156 }
157
158 protected void processResultSet(ResultSet rs, OutputStream out)
159 throws IOException
160 {
161 OutputStreamWriter oswriter = new OutputStreamWriter(out);
162
163 CSVWriter writer = new CSVWriter(oswriter);
164
165 try
166 {
167 writer.writeAll(rs, true);
168 }
169 catch (Exception ex)
170 {
171 throw new RuntimeException(ex);
172 }
173 finally
174 {
175 writer.close();
176 }
177
178 }
179
180 protected String[] getRowValues(HSSFRow r)
181 {
182 String[] values = new String[r.getLastCellNum() - r.getFirstCellNum() + 1];
183 for (short n = r.getFirstCellNum(); n < r.getLastCellNum(); n++)
184 {
185 HSSFCell cell = r.getCell(n);
186 values[n] = getValue(cell);
187 }
188 return values;
189 }
190
191 protected String getValue(HSSFCell cell)
192 {
193 if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
194 {
195 return "";
196 }
197 else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
198 {
199 return Boolean.valueOf(cell.getBooleanCellValue()).toString();
200 }
201 else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
202 {
203 return cell.getNumericCellValue() + "";
204 }
205 else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
206 {
207 return cell.getStringCellValue();
208 }
209 else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
210 {
211 return "";
212 }
213 else
214 {
215 return "";
216 }
217
218 }
219 }