View Javadoc

1   /*
2    *
3    * The DbUnit Database Testing Framework
4    * Copyright (C)2002-2004, 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.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.hssf.usermodel.HSSFCell;
32  import org.apache.poi.hssf.usermodel.HSSFCellStyle;
33  import org.apache.poi.hssf.usermodel.HSSFDateUtil;
34  import org.apache.poi.hssf.usermodel.HSSFRow;
35  import org.apache.poi.hssf.usermodel.HSSFSheet;
36  import org.dbunit.dataset.AbstractTable;
37  import org.dbunit.dataset.Column;
38  import org.dbunit.dataset.DataSetException;
39  import org.dbunit.dataset.DefaultTableMetaData;
40  import org.dbunit.dataset.ITableMetaData;
41  import org.dbunit.dataset.datatype.DataType;
42  import org.dbunit.dataset.datatype.DataTypeException;
43  import org.slf4j.Logger;
44  import org.slf4j.LoggerFactory;
45  
46  /**
47   * @author Manuel Laflamme
48   * @author Last changed by: $Author: jbhurst $
49   * @version $Revision: 1148 $ $Date: 2010-01-23 23:17:13 +0100 (sab, 23 gen 2010) $
50   * @since Feb 21, 2003
51   */
52  class XlsTable extends AbstractTable
53  {
54  
55      /**
56       * Logger for this class
57       */
58      private static final Logger logger = LoggerFactory.getLogger(XlsTable.class);
59  
60      private final ITableMetaData _metaData;
61      private final HSSFSheet _sheet;
62      
63      private final DecimalFormatSymbols symbols = new DecimalFormatSymbols();
64      
65  
66      public XlsTable(String sheetName, HSSFSheet sheet) throws DataSetException
67      {
68          int rowCount = sheet.getLastRowNum();
69          if (rowCount >= 0 && sheet.getRow(0) != null)
70          {
71              _metaData = createMetaData(sheetName, sheet.getRow(0));
72          }
73          else
74          {
75              _metaData = new DefaultTableMetaData(sheetName, new Column[0]);
76          }
77  
78          _sheet = sheet;
79          
80          // Needed for later "BigDecimal"/"Number" conversion
81          symbols.setDecimalSeparator('.');
82      }
83  
84      static ITableMetaData createMetaData(String tableName, HSSFRow sampleRow)
85      {
86          logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow);
87  
88          List columnList = new ArrayList();
89          for (int i = 0; ; i++)
90          {
91              HSSFCell cell = sampleRow.getCell(i);
92              if (cell == null)
93              {
94                  break;
95              }
96  
97              String columnName = cell.getRichStringCellValue().getString();
98              if (columnName != null)
99              {
100             	columnName = columnName.trim();
101             }
102             
103             // Bugfix for issue ID 2818981 - if a cell has a formatting but no name also ignore it
104             if(columnName.length()<=0)
105             {
106                 logger.debug("The column name of column # {} is empty - will skip here assuming the last column was reached", String.valueOf(i));
107                 break;
108             }
109             
110             Column column = new Column(columnName, DataType.UNKNOWN);
111             columnList.add(column);
112         }
113         Column[] columns = (Column[])columnList.toArray(new Column[0]);
114         return new DefaultTableMetaData(tableName, columns);
115     }
116 
117     ////////////////////////////////////////////////////////////////////////////
118     // ITable interface
119 
120     public int getRowCount()
121     {
122         logger.debug("getRowCount() - start");
123 
124         return _sheet.getLastRowNum();
125     }
126 
127     public ITableMetaData getTableMetaData()
128     {
129         logger.debug("getTableMetaData() - start");
130 
131         return _metaData;
132     }
133 
134     public Object getValue(int row, String column) throws DataSetException
135     {
136         if(logger.isDebugEnabled())
137             logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);
138 
139         assertValidRowIndex(row);
140 
141         int columnIndex = getColumnIndex(column);
142         HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
143         if (cell == null)
144         {
145             return null;
146         }
147 
148         int type = cell.getCellType();
149         switch (type)
150         {
151             case HSSFCell.CELL_TYPE_NUMERIC:
152                 HSSFCellStyle style = cell.getCellStyle();
153                 if (HSSFDateUtil.isCellDateFormatted(cell))
154                 {
155                     return getDateValue(cell);
156                 }
157                 else if(XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString()))
158                 {
159                     // The special dbunit date format
160                     return getDateValueFromJavaNumber(cell);
161                 }
162                 else 
163                 {
164                     return getNumericValue(cell);
165                 }
166 
167             case HSSFCell.CELL_TYPE_STRING:
168                 return cell.getRichStringCellValue().getString();
169 
170             case HSSFCell.CELL_TYPE_FORMULA:
171                 throw new DataTypeException("Formula not supported at row=" +
172                         row + ", column=" + column);
173 
174             case HSSFCell.CELL_TYPE_BLANK:
175                 return null;
176 
177             case HSSFCell.CELL_TYPE_BOOLEAN:
178                 return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
179 
180             case HSSFCell.CELL_TYPE_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(HSSFCell 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 new Long(numericValueBd.longValue());
198 //        return new Long(numericValueBd.unscaledValue().longValue());
199     }
200     
201     protected Object getDateValue(HSSFCell cell) 
202     {
203         logger.debug("getDateValue(cell={}) - start", cell);
204         
205         double numericValue = cell.getNumericCellValue();
206         Date date = HSSFDateUtil.getJavaDate(numericValue);
207         // Add the timezone offset again because it was subtracted automatically by Apache-POI (we need UTC)
208         long tzOffset = TimeZone.getDefault().getOffset(date.getTime());
209         date = new Date(date.getTime() + tzOffset);
210         return new Long(date.getTime());
211         
212         //TODO use a calendar for XLS Date objects when it is supported better by POI
213 //        HSSFCellStyle style = cell.getCellStyle();
214 //        HSSFDataFormatter formatter = new HSSFDataFormatter();
215 //        Format f = formatter.createFormat(cell);
216 //      String formatted = fomatter.formatCellValue(cell);
217 //System.out.println("###"+formatted);
218 //        Date dateValue = cell.getDateCellValue();
219     }
220 
221     /**
222      * Removes all trailing zeros from the end of the given BigDecimal value
223      * up to the decimal point.
224      * @param value The value to be stripped
225      * @return The value without trailing zeros
226      */
227     private BigDecimal stripTrailingZeros(BigDecimal value)
228     {
229         if(value.scale()<=0){
230             return value;
231         }
232         
233         String valueAsString = String.valueOf(value);
234         int idx = valueAsString.indexOf(".");
235         if(idx==-1){
236             return value;
237         }
238         
239         for(int i=valueAsString.length()-1; i>idx; i--){
240             if(valueAsString.charAt(i)=='0'){
241                 valueAsString = valueAsString.substring(0, i);
242             }
243             else if(valueAsString.charAt(i)=='.'){
244                 valueAsString = valueAsString.substring(0, i);
245                 // Stop when decimal point is reached
246                 break;
247             }
248             else{
249                 break;
250             }
251         }
252         BigDecimal result = new BigDecimal(valueAsString);
253         return result;
254     }
255     
256     protected BigDecimal getNumericValue(HSSFCell cell)
257     {
258         logger.debug("getNumericValue(cell={}) - start", cell);
259 
260         String formatString = cell.getCellStyle().getDataFormatString();
261         String resultString = null;
262         double cellValue = cell.getNumericCellValue();
263 
264         if((formatString != null))
265         {
266             if(!formatString.equals("General") && !formatString.equals("@")) {
267                 logger.debug("formatString={}", formatString);
268                 DecimalFormat nf = new DecimalFormat(formatString, symbols);
269                 resultString = nf.format(cellValue);
270             }
271         }
272         
273         BigDecimal result;
274         if(resultString != null) {
275             try {
276                 result = new BigDecimal(resultString);
277             }
278             catch(NumberFormatException e) {
279                 logger.debug("Exception occurred while trying create a BigDecimal. value={}", resultString);
280                 // Probably was not a BigDecimal format retrieved from the excel. Some
281                 // date formats are not yet recognized by HSSF as DateFormats so that
282                 // we could get here.
283                 result = toBigDecimal(cellValue);
284             }
285         }
286         else {
287             result = toBigDecimal(cellValue);
288         }
289         return result;
290     }
291 
292     /**
293      * @param cellValue
294      * @return
295      * @since 2.4.6
296      */
297     private BigDecimal toBigDecimal(double cellValue) 
298     {
299         String resultString = String.valueOf(cellValue);
300         // To ensure that intergral numbers do not have decimal point and trailing zero
301         // (to restore backward compatibility and provide a string representation consistent with Excel)
302         if (resultString.endsWith(".0")) {
303             resultString=resultString.substring(0,resultString.length()-2);
304         }
305         BigDecimal result = new BigDecimal(resultString);
306         return result;
307         
308     }
309     
310 }
311