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
