1 /*
2 *
3 * The DbUnit Database Testing Framework
4 * Copyright (C)2002-2008, DbUnit.org
5 *
6 * This library is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU Lesser General Public
8 * License as published by the Free Software Foundation; either
9 * version 2.1 of the License, or (at your option) any later version.
10 *
11 * This library is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 * Lesser General Public License for more details.
15 *
16 * You should have received a copy of the GNU Lesser General Public
17 * License along with this library; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
19 *
20 */
21 package org.dbunit.dataset.excel;
22
23 import java.io.IOException;
24 import java.io.OutputStream;
25 import java.math.BigDecimal;
26 import java.util.Date;
27
28 import org.apache.poi.hssf.usermodel.HSSFCell;
29 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
30 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
31 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
32 import org.apache.poi.hssf.usermodel.HSSFRow;
33 import org.apache.poi.hssf.usermodel.HSSFSheet;
34 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
35 import org.dbunit.dataset.Column;
36 import org.dbunit.dataset.DataSetException;
37 import org.dbunit.dataset.IDataSet;
38 import org.dbunit.dataset.ITable;
39 import org.dbunit.dataset.ITableIterator;
40 import org.dbunit.dataset.ITableMetaData;
41 import org.dbunit.dataset.datatype.DataType;
42 import org.slf4j.Logger;
43 import org.slf4j.LoggerFactory;
44
45 /**
46 * Writes an {@link IDataSet} to an XLS file or OutputStream.
47 *
48 * @author gommma (gommma AT users.sourceforge.net)
49 * @author Last changed by: $Author: gommma $
50 * @version $Revision: 915 $ $Date: 2008-12-07 14:17:25 +0100 (dom, 07 dic 2008) $
51 * @since 2.4.0
52 */
53 public class XlsDataSetWriter
54 {
55 public static final String ZEROS = "0000000000000000000000000000000000000000000000000000";
56
57 /**
58 * A special format pattern used to create a custom {@link HSSFDataFormat} which
59 * marks {@link Date} values that are stored via POI to an XLS file.
60 * Note that it might produce problems if a normal numeric value uses this format
61 * pattern incidentally.
62 */
63 public static final String DATE_FORMAT_AS_NUMBER_DBUNIT = "####################";
64 /**
65 * Logger for this class
66 */
67 private static final Logger logger = LoggerFactory.getLogger(XlsDataSetWriter.class);
68
69 private HSSFCellStyle dateCellStyle;
70
71 /**
72 * Write the specified dataset to the specified Excel document.
73 */
74 public void write(IDataSet dataSet, OutputStream out)
75 throws IOException, DataSetException
76 {
77 logger.debug("write(dataSet={}, out={}) - start", dataSet, out);
78
79 HSSFWorkbook workbook = new HSSFWorkbook();
80
81 this.dateCellStyle = createDateCellStyle(workbook);
82
83 int index = 0;
84 ITableIterator iterator = dataSet.iterator();
85 while(iterator.next())
86 {
87 // create the table i.e. sheet
88 ITable table = iterator.getTable();
89 ITableMetaData metaData = table.getTableMetaData();
90 HSSFSheet sheet = workbook.createSheet(metaData.getTableName());
91
92 // write table metadata i.e. first row in sheet
93 workbook.setSheetName(index, metaData.getTableName());
94
95 HSSFRow headerRow = sheet.createRow(0);
96 Column[] columns = metaData.getColumns();
97 for (int j = 0; j < columns.length; j++)
98 {
99 Column column = columns[j];
100 HSSFCell cell = headerRow.createCell(j);
101 cell.setCellValue(new HSSFRichTextString(column.getColumnName()));
102 }
103
104 // write table data
105 for (int j = 0; j < table.getRowCount(); j++)
106 {
107 HSSFRow row = sheet.createRow(j + 1);
108 for (int k = 0; k < columns.length; k++)
109 {
110 Column column = columns[k];
111 Object value = table.getValue(j, column.getColumnName());
112 if (value != null)
113 {
114 HSSFCell cell = row.createCell(k);
115 if(value instanceof Date){
116 setDateCell(cell, (Date)value, workbook);
117 }
118 else if(value instanceof BigDecimal){
119 setNumericCell(cell, (BigDecimal)value, workbook);
120 }
121 else if(value instanceof Long){
122 setDateCell(cell, new Date( ((Long)value).longValue()), workbook);
123 }
124 else {
125 cell.setCellValue(new HSSFRichTextString(DataType.asString(value)));
126 }
127 }
128 }
129 }
130
131 index++;
132 }
133
134 // write xls document
135 workbook.write(out);
136 out.flush();
137 }
138
139 protected static HSSFCellStyle createDateCellStyle(HSSFWorkbook workbook) {
140 HSSFDataFormat format = workbook.createDataFormat();
141 short dateFormatCode = format.getFormat(DATE_FORMAT_AS_NUMBER_DBUNIT);
142 HSSFCellStyle dateCellStyle = workbook.createCellStyle();
143 dateCellStyle.setDataFormat(dateFormatCode);
144 return dateCellStyle;
145 }
146
147 protected void setDateCell(HSSFCell cell, Date value, HSSFWorkbook workbook)
148 {
149 // double excelDateValue = HSSFDateUtil.getExcelDate(value);
150 // cell.setCellValue(excelDateValue);
151 // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
152
153 long timeMillis = value.getTime();
154 cell.setCellValue( (double)timeMillis );
155 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
156 cell.setCellStyle(this.dateCellStyle);
157
158 // System.out.println(HSSFDataFormat.getBuiltinFormats());
159 // TODO Find out correct cell styles for date objects
160 // HSSFCellStyle cellStyleDate = workbook.createCellStyle();
161 // cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
162 //
163 // HSSFCellStyle cellStyleDateTimeWithSeconds = workbook.createCellStyle();
164 // cellStyleDateTimeWithSeconds.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
165 //
166 // HSSFDataFormat dataFormat = workbook.createDataFormat();
167 // HSSFCellStyle cellStyle = workbook.createCellStyle();
168 // cellStyle.setDataFormat(dataFormat.getFormat("dd/mm/yyyy hh:mm:ss"));
169 //
170 // SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
171 // SimpleDateFormat formatter2 = new SimpleDateFormat("dd/MM/yyyy");
172 // SimpleDateFormat formatter3 = new SimpleDateFormat("HH:mm:ss.SSS");
173 //
174 //
175 // Date dateValue = (Date)value;
176 // Calendar cal = null;
177 //
178 // // If it is a date value that does not have seconds
179 // if(dateValue.getTime() % 60000 == 0){
180 //// cellStyle = cellStyleDate;
181 // cal=Calendar.getInstance();
182 // cal.setTimeInMillis(dateValue.getTime());
183 //
184 // cell.setCellValue(cal);
185 // cell.setCellStyle(cellStyle);
186 //// cell.setCellValue(cal);
187 // }
188 // else {
189 //// HSSFDataFormatter formatter = new HSSFDataFormatter();
190 //
191 // // If we have seconds assume that it is only h:mm:ss without date
192 // // TODO Clean implementation where user can control date formats would be nice
193 //// double dateDouble = dateValue.getTime() % (24*60*60*1000);
194 // cal = get1900Cal(dateValue);
195 //
196 // String formatted = formatter3.format(dateValue);
197 // //TODO Format ...
198 //// cellStyle = cellStyleDateTimeWithSeconds;
199 // System.out.println("date formatted:"+formatted);
200 //// HSSFRichTextString s = new HSSFRichTextString(formatted);
201 //// cell.setCellValue(s);
202 // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
203 // cell.setCellValue((double)dateValue.getTime());
204 // cell.setCellStyle(cellStyleDateTimeWithSeconds);
205 // }
206
207 }
208
209 protected void setNumericCell(HSSFCell cell, BigDecimal value, HSSFWorkbook workbook)
210 {
211 if(logger.isDebugEnabled())
212 logger.debug("setNumericCell(cell={}, value={}, workbook={}) - start",
213 new Object[] {cell, value, workbook} );
214
215 cell.setCellValue( ((BigDecimal)value).doubleValue() );
216
217 HSSFDataFormat df = workbook.createDataFormat();
218 int scale = ((BigDecimal)value).scale();
219 short format;
220 if(scale <= 0){
221 format = df.getFormat("####");
222 }
223 else {
224 String zeros = createZeros(((BigDecimal)value).scale());
225 format = df.getFormat("####." + zeros);
226 }
227 if(logger.isDebugEnabled())
228 logger.debug("Using format '{}' for value '{}'.", String.valueOf(format), value);
229
230 HSSFCellStyle cellStyleNumber = workbook.createCellStyle();
231 cellStyleNumber.setDataFormat(format);
232 cell.setCellStyle(cellStyleNumber);
233 }
234
235
236 // public static Date get1900(Date date) {
237 // Calendar cal = Calendar.getInstance();
238 // cal.setTimeInMillis(date.getTime() % (24*60*60*1000));
239 // cal.set(1900, 0, 1); // 1.1.1900
240 // return cal.getTime();
241 // }
242 //
243 // public static Calendar get1900Cal(Date date) {
244 // Calendar cal = Calendar.getInstance();
245 // cal.clear();
246 //// long hoursInMillis = date.getTime() % (24*60*60*1000);
247 //// long smallerThanDays = date.getTime() % (24*60*60*1000);
248 //// cal.setTimeInMillis(date.getTime() % (24*60*60*1000));
249 // cal.set(Calendar.SECOND, (int) (date.getTime() % (24*60*60*1000)) / (1000) );
250 // cal.set(Calendar.MINUTE, (int) (date.getTime() % (24*60*60*1000)) / (1000*60) );
251 // cal.set(Calendar.HOUR, (int) (date.getTime() % (24*60*60*1000)) / (1000*60*60) );
252 //// cal.set(1900, 0, 1); // 1.1.1900
253 // System.out.println(cal.isSet(Calendar.DATE));
254 // return cal;
255 // }
256
257 private static String createZeros(int count) {
258 return ZEROS.substring(0, count);
259 }
260
261 }