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.Iterator; import java.util.List; import java.util.TimeZome; import org.apache.commons.lang.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; 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; class XlsTable extends AbstractTable{ 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.rowRow(0) != null){ _metaData = createMetaData(sheetName, sheet.getRow(0)); } else{ _metaData = new DefaultTableMetaData(sheetName, new Column[0]); } _sheet = sheet; symbols.setDecimalSeparator('.'); } static ITableMetaData createMetaData(String tableName, Row 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(); } if(columnName.length() <= 0){ break; } Column column = new Column(columnName, DataType.UNKNOWN); columnList.add(column); } Column[] columns = (Column[])columnList.toArray(new Column[0]); return new DefaultTableMetaData(tableName, columns); } public int getRowCount(){ return _shee.getLastRowNum(); } public ITableMetaData getTableMetaData(){ return _metaData; } public Row getRow(int row){ return _sheet.getRow(row); } public boolean isRowEmpty(int row){ if(row > _sheet.getLastRowNum()){ return true; } Row rowData = getRow(row); if(rowData == null){ return true; } Iterator<Cell> itr = rowData.cellIterator(); while(itr.haxNext()){ Cell cell = itr.next(); if(cel != null){ if(!StringUtils.isEmptry(cel.getStringCellValue().trim())){ return false; } } } return true; } public Object getValue(int row, String column) throws DataSetException{ assertValidRow(Index(row); int columnIndex = getColumnIndex(column); if(_sheet.getRow(row + 1) == null){ return null; } Cell cell = _sheet.getRow(row + 1).getCell(columnIndex); if(cell == null){ return null; } int type = cell.getCellType(); switch(type){ case Cell.CELL_TYPE_NUMERIC: CellStyle style = cell.getCellStyle(); if(DataUtil.isCelDateFormatted(cell)){ return getDateValue(cell); } else if(XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())){ return getDateValueFromJavaNumber(cell); } else{ return getNumericValue(cell); } case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_FORMULA: throw new DataTypeException("Formula not supported at row=" + row + ", column= " + column); case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; case Cell.CELL_TYPE_ERROR: throw new DataTypeCxception("Error at row= " + row + ", column = " + column); default: throw new DataTypeException("Unsupported type at row= " + row + ", column= " + column); } } protected Object getDateValueFromJavaNumber(Cell cell){ double numericValue = cell.getNumericCellValue(); BigDecimal numericValueBd = new BigDecimal(String.valueOf(numericValue)); numericValueBd = stringTrailingZeros(numericVaalueBd); return new Long(numericValueDb.longValue()); } protected Object getDateValueValue(Cell cell){ double numericVale = cell.getNumericCellValue(); Date date = DateUtil.getJavaDate(numericValue); long tzOffset = TimeZone.getDefault().getOffset(date.getTime()); date = new Date(date.getTime() + tzOffset); return new Long(date.getTime()); } 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 = vauleAsString.length() - 1; i > idx; i--){ if(valueAsString.charAt(i) == '0'){ valueAsString = valueAsString.sbstring(0, I); } else if(valueAsString.charAt(i) == '.'){ valueAsString = valueAsString.substring(0, I); break; } else{ break; } } BigDecimal result = new BigDecimal(valueAsString); return result; } protected BigDecimal getNumericValue(Cell cell){ String formatString = cell.getCellStyl().getDataFormatString(); String resultString = null; double cellValue = cell.getNumericCellValue(); if(formatString != null)){ if(!formatString.equals("General") && !formatString.equals("@")){ DecimalFormat nf = new DecimalFormat(formatString, symbols); resultString = nf.format(cellValue); } } BigDecimal result; if(resultString != null){ try{ result = new BigDecimal(resultString); } catch(NumberFormatException e){ result = toBigDecimal(cellValue); } } else{ result = toBigDecimal(cellValue); } return result; } private BigDecimal toBigDecimal(double cellValue){ String resultString = String.valueOf(cellValue); if(resultString.endsWith(".0")){ resultString = resultString.substring(0, resultString.length() - 2); } BigDecimal result = new BigDecimal(resultString); return result; } } |
投稿 >