Yoo-Jin, can you open an issue and provide your fix as a patch?
Concerning 2. b-: I think that it makes sense too (perhaps does already an issue exist for that). Cheers, Marc. -- Blog: http://mguillem.wordpress.com Yoo-Jin Lee wrote: > Hi, > > Please find sample code below: > 1. need to add poi scratchpad jar for releases before 3.0.3 > 2. updated ExcelPropertyTable > a. code to calculate formulas (I incorrectly said it processed 1000 > formulas, it is actually around 100) > b. when there is a whole number the dataDriven step would add '.0'. It > was annoying me so I've added a check to give a whole number. feel free > to take it out. > > - Yoo-Jin > > ================ pom.xml ================ > .... > <dependency> > <!-- excel --> > <groupId>org.apache.poi</groupId> > <artifactId>poi-scratchpad</artifactId> > <version>3.0.2-FINAL</version> > <scope>compile</scope> > </dependency> > > > ============ExcelPropertyTable=================== > package com.canoo.ant.table; > > import org.apache.log4j.Logger; > import org.apache.poi.hssf.usermodel.*; > import org.apache.poi.poifs.filesystem.POIFSFileSystem; > > import java.io.File; > import java.io.FileInputStream; > import java.io.FileNotFoundException; > import java.io.IOException; > import java.text.DecimalFormat; > import java.util.ArrayList; > import java.util.LinkedList; > import java.util.List; > import java.util.Properties; > > public class ExcelPropertyTable extends APropertyTable { > > private static final Logger LOG = > Logger.getLogger(ExcelPropertyTable.class); > private static final DecimalFormat NUMBER_FMT = new DecimalFormat("0"); > > public ExcelPropertyTable() { > } > > protected boolean hasJoinTable() { > final Object sheet; > try { > sheet = getWorkbook().getSheet(KEY_JOIN); > } > catch (final IOException e) { > throw new RuntimeException("Failed to read container: >" + > getContainer() + "<", e); > } > return sheet != null; > } > > private HSSFWorkbook getWorkbook() throws IOException { > final File file = getContainer(); > if (!file.exists()) { > throw new FileNotFoundException("File not found >" + > file.getAbsolutePath() + "< " + getContainer()); > } else if (!file.isFile() || !file.canRead()) { > throw new IllegalArgumentException("No a regular readable > file: >" + file.getAbsolutePath() + "<"); > } > final POIFSFileSystem excelFile = new POIFSFileSystem(new > FileInputStream(file)); > return new HSSFWorkbook(excelFile); > } > > protected List read(final String sheetName) throws IOException { > final HSSFWorkbook workbook = getWorkbook(); > final HSSFSheet sheet; > if (sheetName == null) { > sheet = workbook.getSheetAt(0); // no name specified, take > the first sheet > } else { > sheet = workbook.getSheet(sheetName); > } > if (null == sheet) { > String msg = "No sheet \"" + sheetName + "\" found in file " > + getContainer() + ". Available sheets: "; > for (int i = 0; i < workbook.getNumberOfSheets(); i++) { > if (i != 0) > msg += ", "; > msg += workbook.getSheetName(i); > } > throw new IllegalArgumentException(msg); > } > > HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, > workbook); > > final int lastRowNum = sheet.getLastRowNum(); > final List header = new ArrayList(); > final HSSFRow headerRow = sheet.getRow(0); > for (short i = 0; i < headerRow.getLastCellNum(); i++) { > final HSSFCell cell = headerRow.getCell(i); > if (cell != null) > header.add(stringValueOf(cell)); > else > header.add(null); > } > final List result = new LinkedList(); > for (int rowNo = 1; rowNo <= lastRowNum; rowNo++) { // last Row > is included > final HSSFRow row = sheet.getRow(rowNo); > evaluator.setCurrentRow(row); > > if (row != null) // surprising, but row can be null > { > final Properties props = new Properties(); > for (short i = 0; i < header.size(); i++) { > final String headerName = (String) header.get(i); > if (headerName != null) // handle empty cols > { > final HSSFCell cell = row.getCell(i); > evaluator.evaluateInCell(cell); > final String value = stringValueOf(cell); > putValue(value, headerName, props); > } > } > result.add(props); > } > } > > return result; > } > > protected void putValue(String value, Object key, Properties props) { > if (!EMPTY.equals(value)) { // do not add empty values to allow > proper default handling > props.put(key, value); > } > } > > private String stringValueOf(final HSSFCell cell) { > if (null == cell) { > return EMPTY; > } > > switch (cell.getCellType()) { > case (HSSFCell.CELL_TYPE_STRING): > return cell.getRichStringCellValue().getString(); > case (HSSFCell.CELL_TYPE_NUMERIC): > return getNumber(cell); > case (HSSFCell.CELL_TYPE_BLANK): > return ""; > case (HSSFCell.CELL_TYPE_BOOLEAN): > return "" + cell.getBooleanCellValue(); > default: > LOG.warn("Cell Type not supported: " + cell.getCellType()); > return EMPTY; > } > } > > private String getNumber(final HSSFCell cell) { > final double value = cell.getNumericCellValue(); > final int round = (int) value; > if ((value - round) != 0) > return String.valueOf(value); > else > return NUMBER_FMT.format(value); > } > } > =========================== > > ------------------------------------------------------------------------ > Instant message from any web browser! Try the new * Yahoo! Canada > Messenger for the Web BETA* > <http://ca.messenger.yahoo.com/webmessengerpromo.php> _______________________________________________ WebTest mailing list [email protected] http://lists.canoo.com/mailman/listinfo/webtest
