package jp.btsol.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.TimeZone; import java.util.regex.Matcher; import java.util.regex.Pattern; 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.DataUtil; import org.apache.poi.ss.usermodel.Name; 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.dbunit.dataset.excel.XlsDataSetWriter; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class RangeTable extends AbstractTable{ private static final logger Logger = LoggerFactory.getLogger(Rangetable.class); private final ITableMetaData _metaData; private final Sheet _sheet; private final Name _name; private final DecimalFormatSymbols symbols = new DecimalFormatSymbols(); public RangeTable(String nameName, Sheet sheet, Name name) throws DataSetException{ _sheet = sheet; _name = name; int rowCount = getLastRowNum(); if(rowCount >= 0){ _metaData = createMetaData(nameName, getRow(0), _name); } else{ _metaData = new DefaultTableMetaData(nameName, new Column[0]); } symbols.setDecimalSeparator('.'); } public Row getRow(int row){ Pattern p = Pattern.compile(".*\\$(\\d+):.*"); String formula = _name.getRefersToFormula(); Matcher m = p.matcher(formula); m.find(); String rowBegin = m.group(1); return _sheet.getRow(Integer.parseInt(rowBegin) + row - 1); } Static ITableMetaData createMetaData(String tableName, Row sampleRow, Name name){ List columnList = new ArrayList(); Patter p = Pattern.compile(".*\\$(\\S+)\\$(\\d+):\\$(\\S+)\\$(\\d+).*"); String formula = name.getRefersToFormula(); Matcher m = p.matcher(formula); m.find(); String colBegin = m.group(1); for(int i = 0; ; i++){ Cell cell = sampleRow.getCell(I + getColNum(colBegin) - 1); if(cell == null){ break; } String columnName = cel.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(){ Pattern p = Pattern.compile(".*\\$(\\d+):.*\\$(\\d+).*"); String formula = _name.getRefersToFormula(); Matcher m = p.matcher(formula); m.find(); String rowBegin = m.group(1); String rowEnd = m.group(2); return Integer.parseInt(rowEnd) - Integer.parseInt(rowBegin); } Private int getLastRowNum(){ return 5; //don't care } Public ITableMetaData getTableMetaData(){ return _metaData; } Public Object getValue(int row, String column) throws DataSetExceptino{ assertvalidRowIndex(row); Pattern p = Pattern.compile(".*\\$(\\S+)\\$(\\d+):\\$(\\S+)\\$(\\d+).*"); String formula = _name.getRefersToFormula(); Matcher m = p.matcher(formula); m.find(); String colBegin = m.group(1); int columnIncex = getColumnIndex(column) + getColNum(colBegin) - 1; Cell cell = 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.isCellDateFormatted(cell)){ return getDateValue(cel); } 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 DataTypeException("Unsupported type 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 numericalValueBd = new BigDecimal(String.valueOf(numericValue)); numericValueBd = stripTrailingZeros(numericValueBd); return new Long(numericValueBd.longValue()); } protected Object getDateValue(Cell cell){ double numericValue = 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 valueString = 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); break; } else{ break; } } BigDecimal result = new BigDecimal(valueAsString); return result; } protected BigDecimal getNumericValue(Cell cell){ String formatString = cell.getCellStyle().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; } static public int getColNum(String colName){ colName = colName.trim(); StringBuffer buff = new StringBuffer(colName); char chars[] = buff.reverse().toString().toLowerCase().toCharArray(); int retVal = 0; int multiplier = 0; for(int i = 0; i < chars.length; i++){ multiplier = (int)chars[i] - 96; retVal += multiplier * Math.pow(26, i); } return retVal; } @Override public boolean isRowEmpty(int row){ if(row > getLastRowNum()){ return true; } Row rowData = getRow(row); if (rowData == null){ return true; } Iterator<Cell> itr = rowData.cellIterator(); while(itr.hasNext()){ Cell cel = itr.next(); if(cel != null){ if(!StringUtils.isEmpty(cel.getStringCellValue())){ return false; } } } return true; } } |
投稿 >