1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package org.dbunit.dataset.excel;
22
23 import java.math.BigDecimal;
24 import java.text.DecimalFormat;
25 import java.text.DecimalFormatSymbols;
26 import java.util.ArrayList;
27 import java.util.Date;
28 import java.util.List;
29 import java.util.TimeZone;
30
31 import org.apache.poi.ss.usermodel.Cell;
32 import org.apache.poi.ss.usermodel.CellStyle;
33 import org.apache.poi.ss.usermodel.CellType;
34 import org.apache.poi.ss.usermodel.DateUtil;
35 import org.apache.poi.ss.usermodel.Row;
36 import org.apache.poi.ss.usermodel.Sheet;
37 import org.dbunit.dataset.AbstractTable;
38 import org.dbunit.dataset.Column;
39 import org.dbunit.dataset.DataSetException;
40 import org.dbunit.dataset.DefaultTableMetaData;
41 import org.dbunit.dataset.ITableMetaData;
42 import org.dbunit.dataset.datatype.DataType;
43 import org.dbunit.dataset.datatype.DataTypeException;
44 import org.slf4j.Logger;
45 import org.slf4j.LoggerFactory;
46
47
48
49
50
51
52
53 class XlsTable extends AbstractTable
54 {
55
56
57
58
59 private static final Logger logger = LoggerFactory.getLogger(XlsTable.class);
60
61 private final ITableMetaData _metaData;
62 private final Sheet _sheet;
63
64 private final DecimalFormatSymbols symbols = new DecimalFormatSymbols();
65
66
67 public XlsTable(String sheetName, Sheet sheet) throws DataSetException
68 {
69 int rowCount = sheet.getLastRowNum();
70 if (rowCount >= 0 && sheet.getRow(0) != null)
71 {
72 _metaData = createMetaData(sheetName, sheet.getRow(0));
73 }
74 else
75 {
76 _metaData = new DefaultTableMetaData(sheetName, new Column[0]);
77 }
78
79 _sheet = sheet;
80
81
82 symbols.setDecimalSeparator('.');
83 }
84
85 static ITableMetaData createMetaData(String tableName, Row sampleRow)
86 {
87 logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow);
88
89 List columnList = new ArrayList();
90 for (int i = 0; ; i++)
91 {
92 Cell cell = sampleRow.getCell(i);
93 if (cell == null)
94 {
95 break;
96 }
97
98 String columnName = cell.getRichStringCellValue().getString();
99 if (columnName != null)
100 {
101 columnName = columnName.trim();
102 }
103
104
105 if(columnName.length()<=0)
106 {
107 logger.debug("The column name of column # {} is empty - will skip here assuming the last column was reached", i);
108 break;
109 }
110
111 Column column = new Column(columnName, DataType.UNKNOWN);
112 columnList.add(column);
113 }
114 Column[] columns = (Column[])columnList.toArray(new Column[0]);
115 return new DefaultTableMetaData(tableName, columns);
116 }
117
118
119
120
121 public int getRowCount()
122 {
123 logger.debug("getRowCount() - start");
124
125 return _sheet.getLastRowNum();
126 }
127
128 public ITableMetaData getTableMetaData()
129 {
130 logger.debug("getTableMetaData() - start");
131
132 return _metaData;
133 }
134
135 public Object getValue(int row, String column) throws DataSetException
136 {
137 logger.debug("getValue(row={}, columnName={}) - start", row, column);
138
139 assertValidRowIndex(row);
140
141 int columnIndex = getColumnIndex(column);
142 Cell cell = _sheet.getRow(row + 1).getCell(columnIndex);
143 if (cell == null)
144 {
145 return null;
146 }
147
148 CellType type = cell.getCellType();
149 switch (type)
150 {
151 case NUMERIC:
152 CellStyle style = cell.getCellStyle();
153 if (DateUtil.isCellDateFormatted(cell))
154 {
155 return getDateValue(cell);
156 }
157 else if(XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString()))
158 {
159
160 return getDateValueFromJavaNumber(cell);
161 }
162 else
163 {
164 return getNumericValue(cell);
165 }
166
167 case STRING:
168 return cell.getRichStringCellValue().getString();
169
170 case FORMULA:
171 throw new DataTypeException("Formula not supported at row=" +
172 row + ", column=" + column);
173
174 case BLANK:
175 return null;
176
177 case BOOLEAN:
178 return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
179
180 case ERROR:
181 throw new DataTypeException("Error at row=" + row +
182 ", column=" + column);
183
184 default:
185 throw new DataTypeException("Unsupported type at row=" + row +
186 ", column=" + column);
187 }
188 }
189
190 protected Object getDateValueFromJavaNumber(Cell cell)
191 {
192 logger.debug("getDateValueFromJavaNumber(cell={}) - start", cell);
193
194 double numericValue = cell.getNumericCellValue();
195 BigDecimal numericValueBd = new BigDecimal(String.valueOf(numericValue));
196 numericValueBd = stripTrailingZeros(numericValueBd);
197 return numericValueBd.longValue();
198 }
199
200 protected Object getDateValue(Cell cell)
201 {
202 logger.debug("getDateValue(cell={}) - start", cell);
203
204 double numericValue = cell.getNumericCellValue();
205 Date date = DateUtil.getJavaDate(numericValue);
206 return date.getTime();
207
208
209
210
211
212
213
214
215 }
216
217
218
219
220
221
222
223 private BigDecimal stripTrailingZeros(BigDecimal value)
224 {
225 if(value.scale()<=0){
226 return value;
227 }
228
229 String valueAsString = String.valueOf(value);
230 int idx = valueAsString.indexOf(".");
231 if(idx==-1){
232 return value;
233 }
234
235 for(int i=valueAsString.length()-1; i>idx; i--){
236 if(valueAsString.charAt(i)=='0'){
237 valueAsString = valueAsString.substring(0, i);
238 }
239 else if(valueAsString.charAt(i)=='.'){
240 valueAsString = valueAsString.substring(0, i);
241
242 break;
243 }
244 else{
245 break;
246 }
247 }
248 BigDecimal result = new BigDecimal(valueAsString);
249 return result;
250 }
251
252 protected BigDecimal getNumericValue(Cell cell)
253 {
254 logger.debug("getNumericValue(cell={}) - start", cell);
255
256 String formatString = cell.getCellStyle().getDataFormatString();
257 String resultString = null;
258 double cellValue = cell.getNumericCellValue();
259
260 if((formatString != null))
261 {
262 if(!formatString.equals("General") && !formatString.equals("@")) {
263 logger.debug("formatString={}", formatString);
264 DecimalFormat nf = new DecimalFormat(formatString, symbols);
265 resultString = nf.format(cellValue);
266 }
267 }
268
269 BigDecimal result;
270 if(resultString != null) {
271 try {
272 result = new BigDecimal(resultString);
273 }
274 catch(NumberFormatException e) {
275 logger.debug("Exception occurred while trying create a BigDecimal. value={}", resultString);
276
277
278
279 result = toBigDecimal(cellValue);
280 }
281 }
282 else {
283 result = toBigDecimal(cellValue);
284 }
285 return result;
286 }
287
288
289
290
291
292
293 private BigDecimal toBigDecimal(double cellValue)
294 {
295 String resultString = String.valueOf(cellValue);
296
297
298 if (resultString.endsWith(".0")) {
299 resultString=resultString.substring(0,resultString.length()-2);
300 }
301 BigDecimal result = new BigDecimal(resultString);
302 return result;
303
304 }
305
306 public String toString()
307 {
308 StringBuilder sb = new StringBuilder();
309 sb.append(getClass().getName()).append("[");
310 sb.append("_metaData=").append(
311 this._metaData == null ? "null" : this._metaData.toString());
312 sb.append(", _sheet=").append(
313 this._sheet == null ? "null" : "" + this._sheet);
314 sb.append(", symbols=").append(
315 this.symbols == null ? "null" : "" + this.symbols);
316 sb.append("]");
317 return sb.toString();
318 }
319 }
320