XlsTable.java
/*
*
* The DbUnit Database Testing Framework
* Copyright (C)2002-2004, DbUnit.org
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*
*/
package org.dbunit.dataset.excel;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.dbunit.dataset.AbstractTable;
import org.dbunit.dataset.Column;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.DefaultTableMetaData;
import org.dbunit.dataset.ITableMetaData;
import org.dbunit.dataset.datatype.DataType;
import org.dbunit.dataset.datatype.DataTypeException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @author Manuel Laflamme
* @author Last changed by: $Author$
* @version $Revision$ $Date$
* @since Feb 21, 2003
*/
class XlsTable extends AbstractTable
{
/**
* Logger for this class
*/
private static final Logger logger = LoggerFactory.getLogger(XlsTable.class);
private final ITableMetaData _metaData;
private final Sheet _sheet;
private final DecimalFormatSymbols symbols = new DecimalFormatSymbols();
public XlsTable(String sheetName, Sheet sheet) throws DataSetException
{
int rowCount = sheet.getLastRowNum();
if (rowCount >= 0 && sheet.getRow(0) != null)
{
_metaData = createMetaData(sheetName, sheet.getRow(0));
}
else
{
_metaData = new DefaultTableMetaData(sheetName, new Column[0]);
}
_sheet = sheet;
// Needed for later "BigDecimal"/"Number" conversion
symbols.setDecimalSeparator('.');
}
static ITableMetaData createMetaData(String tableName, Row sampleRow)
{
logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow);
List columnList = new ArrayList();
for (int i = 0; ; i++)
{
Cell cell = sampleRow.getCell(i);
if (cell == null)
{
break;
}
String columnName = cell.getRichStringCellValue().getString();
if (columnName != null)
{
columnName = columnName.trim();
}
// Bugfix for issue ID 2818981 - if a cell has a formatting but no name also ignore it
if(columnName.length()<=0)
{
logger.debug("The column name of column # {} is empty - will skip here assuming the last column was reached", i);
break;
}
Column column = new Column(columnName, DataType.UNKNOWN);
columnList.add(column);
}
Column[] columns = (Column[])columnList.toArray(new Column[0]);
return new DefaultTableMetaData(tableName, columns);
}
////////////////////////////////////////////////////////////////////////////
// ITable interface
public int getRowCount()
{
logger.debug("getRowCount() - start");
return _sheet.getLastRowNum();
}
public ITableMetaData getTableMetaData()
{
logger.debug("getTableMetaData() - start");
return _metaData;
}
public Object getValue(int row, String column) throws DataSetException
{
logger.debug("getValue(row={}, columnName={}) - start", row, column);
assertValidRowIndex(row);
int columnIndex = getColumnIndex(column);
Cell cell = _sheet.getRow(row + 1).getCell(columnIndex);
if (cell == null)
{
return null;
}
CellType type = cell.getCellType();
switch (type)
{
case NUMERIC:
CellStyle style = cell.getCellStyle();
if (DateUtil.isCellDateFormatted(cell))
{
return getDateValue(cell);
}
else if(XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString()))
{
// The special dbunit date format
return getDateValueFromJavaNumber(cell);
}
else
{
return getNumericValue(cell);
}
case STRING:
return cell.getRichStringCellValue().getString();
case FORMULA:
throw new DataTypeException("Formula not supported at row=" +
row + ", column=" + column);
case BLANK:
return null;
case BOOLEAN:
return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
case ERROR:
throw new DataTypeException("Error at row=" + row +
", column=" + column);
default:
throw new DataTypeException("Unsupported type at row=" + row +
", column=" + column);
}
}
protected Object getDateValueFromJavaNumber(Cell cell)
{
logger.debug("getDateValueFromJavaNumber(cell={}) - start", cell);
double numericValue = cell.getNumericCellValue();
BigDecimal numericValueBd = new BigDecimal(String.valueOf(numericValue));
numericValueBd = stripTrailingZeros(numericValueBd);
return numericValueBd.longValue();
}
protected Object getDateValue(Cell cell)
{
logger.debug("getDateValue(cell={}) - start", cell);
double numericValue = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(numericValue);
return date.getTime();
//TODO use a calendar for XLS Date objects when it is supported better by POI
// HSSFCellStyle style = cell.getCellStyle();
// HSSFDataFormatter formatter = new HSSFDataFormatter();
// Format f = formatter.createFormat(cell);
// String formatted = fomatter.formatCellValue(cell);
//System.out.println("###"+formatted);
// Date dateValue = cell.getDateCellValue();
}
/**
* Removes all trailing zeros from the end of the given BigDecimal value
* up to the decimal point.
* @param value The value to be stripped
* @return The value without trailing zeros
*/
private BigDecimal stripTrailingZeros(BigDecimal value)
{
if(value.scale()<=0){
return value;
}
String valueAsString = String.valueOf(value);
int idx = valueAsString.indexOf(".");
if(idx==-1){
return value;
}
for(int i=valueAsString.length()-1; i>idx; i--){
if(valueAsString.charAt(i)=='0'){
valueAsString = valueAsString.substring(0, i);
}
else if(valueAsString.charAt(i)=='.'){
valueAsString = valueAsString.substring(0, i);
// Stop when decimal point is reached
break;
}
else{
break;
}
}
BigDecimal result = new BigDecimal(valueAsString);
return result;
}
protected BigDecimal getNumericValue(Cell cell)
{
logger.debug("getNumericValue(cell={}) - start", cell);
String formatString = cell.getCellStyle().getDataFormatString();
String resultString = null;
double cellValue = cell.getNumericCellValue();
if((formatString != null))
{
if(!formatString.equals("General") && !formatString.equals("@")) {
logger.debug("formatString={}", formatString);
DecimalFormat nf = new DecimalFormat(formatString, symbols);
resultString = nf.format(cellValue);
}
}
BigDecimal result;
if(resultString != null) {
try {
result = new BigDecimal(resultString);
}
catch(NumberFormatException e) {
logger.debug("Exception occurred while trying create a BigDecimal. value={}", resultString);
// Probably was not a BigDecimal format retrieved from the excel. Some
// date formats are not yet recognized by HSSF as DateFormats so that
// we could get here.
result = toBigDecimal(cellValue);
}
}
else {
result = toBigDecimal(cellValue);
}
return result;
}
/**
* @param cellValue
* @return
* @since 2.4.6
*/
private BigDecimal toBigDecimal(double cellValue)
{
String resultString = String.valueOf(cellValue);
// To ensure that intergral numbers do not have decimal point and trailing zero
// (to restore backward compatibility and provide a string representation consistent with Excel)
if (resultString.endsWith(".0")) {
resultString=resultString.substring(0,resultString.length()-2);
}
BigDecimal result = new BigDecimal(resultString);
return result;
}
public String toString()
{
StringBuilder sb = new StringBuilder();
sb.append(getClass().getName()).append("[");
sb.append("_metaData=").append(
this._metaData == null ? "null" : this._metaData.toString());
sb.append(", _sheet=").append(
this._sheet == null ? "null" : "" + this._sheet);
sb.append(", symbols=").append(
this.symbols == null ? "null" : "" + this.symbols);
sb.append("]");
return sb.toString();
}
}