private void evaluateWorkbook(Workbook wb) {
FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet s = wb.getSheetAt(i);
for (Row row : s) {
for (Cell cell : row) {
eval.evaluateFormulaCell(cell);
}
}
}
}
public static Object getCellValue(Cell cell) {
Object value = new Object();
// Prevent a NullPointerException
if (cell != null) {
if (cell.getHyperlink() != null) {
Workbook workbook = new XSSFWorkbook();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Hyperlink link = cell.getHyperlink();
String address = link.getAddress();
if (logger.isTraceEnabled()) {
logger.trace(
"Found a Hyperlink to "
+ cell.getHyperlink().getAddress()
+ " in cell "
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex());
}
cell = evaluator.evaluateInCell(cell);
}
// Depending on the cell type, the value is read using Apache POI methods
switch (cell.getCellType()) {
// String are easy to handle
case Cell.CELL_TYPE_STRING:
logger.trace("Found string " + cell.getStringCellValue());
value = cell.getStringCellValue();
break;
// Since date formatted cells are also of the numeric type, this needs to be processed
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = SimpleDateFormat.getDateInstance();
logger.trace("Found date " + df.format(date));
value = date;
} else {
logger.trace("Found general number " + cell.getNumericCellValue());
value = cell.getNumericCellValue();
}
break;
}
} else {
logger.trace("Found cell with NULL value");
}
return value;
}
/**
* Evaluate if the cell contains the formula
*
* @param workbook the workbook that contains the cell
* @param cell the cell that contains the formula
* @return the object representation of formula cell value.
*/
private Object evaluateCellFormula(final HSSFWorkbook workbook, final Cell cell) {
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
Object result = null;
if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
result = cellValue.getBooleanValue();
} else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
result = cellValue.getNumberValue();
} else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
result = cellValue.getStringValue();
}
return result;
}
private Object getCellValue(Cell cell, FormulaEvaluator evaluator)
throws ExcelManipulateException {
if (cell == null) return null;
// log.debug("Read Value for: " + ExcelUtil.getCellIndex(cell.getRowIndex(),
// cell.getColumnIndex()));
Object value = null;
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
logger.debug("{}: null", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()));
return null;
}
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cellValue.getBooleanValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = DateUtil.getJavaDate(cellValue.getNumberValue());
} else value = cellValue.getNumberValue();
break;
case Cell.CELL_TYPE_STRING:
value = cellValue.getStringValue();
}
logger.debug(
"{}: {}", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()), value);
return value;
}
/**
* セルに設定された計算式を評価して値を取得する
*
* @param nRow 行番号
* @param nColumn 列番号
* @return セルの値
*/
public static Object getDataByEvaluateFormula(XSSFSheet sheet, int nRow, int nColumn) {
assert sheet != null;
XSSFRow row = getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
if (eval != null) {
CellValue value = eval.evaluate(cell);
if (value != null) {
return value.getNumberValue();
}
}
}
}
return null;
}
public static Object resolveCellValue(
Cell cell, String emptyMarker, String nullMarker, Converter<String, ?> stringPreprocessor) {
if (cell == null) return null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return convertString(cell, emptyMarker, nullMarker, stringPreprocessor);
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
double numericCellValue = cell.getNumericCellValue();
if (MathUtil.isIntegralValue(numericCellValue))
return ((Double) numericCellValue).longValue();
return numericCellValue;
}
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
return cell.getRichStringCellValue().getString();
case Cell.CELL_TYPE_FORMULA:
FormulaEvaluator evaluator =
cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
return convertString(cellValue, emptyMarker, stringPreprocessor);
case HSSFCell.CELL_TYPE_NUMERIC:
return cellValue.getNumberValue();
case Cell.CELL_TYPE_BOOLEAN:
return cellValue.getBooleanValue();
case HSSFCell.CELL_TYPE_BLANK:
case HSSFCell.CELL_TYPE_ERROR:
return null;
default:
throw new IllegalStateException("Unexpected cell type: " + cellValue.getCellType());
// CELL_TYPE_FORMULA is not supposed to be encountered here
}
default:
throw new ConfigurationError("Not a supported cell type: " + cell.getCellType());
}
}
public static String getCellStringValue(HSSFCell cell, FormulaEvaluator evaluator) {
if (cell != null) {
switch (evaluator.evaluateInCell(cell).getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return BooleanUtils.toStringTrueFalse(cell.getBooleanCellValue());
case Cell.CELL_TYPE_NUMERIC:
// FIXME 这里其实排除了 浮点类型的数据
return String.valueOf((int) cell.getNumericCellValue());
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
// CELL_TYPE_FORMULA will never occur
case Cell.CELL_TYPE_FORMULA:
break;
}
}
return StringUtils.EMPTY;
}
/**
* create a FeatureSource with the specified Query
*
* @param entry
* @param query - a query containing a filter that will be applied to the data
*/
public ExcelFeatureSource(ContentEntry entry, Query query) {
super(entry, query);
Date beginingOfExcelTime = HSSFDateUtil.getJavaDate(0);
dataStore = (ExcelDataStore) entry.getDataStore();
sheet = dataStore.getSheet();
latCol = dataStore.getLatColumnIndex();
lonCol = dataStore.getLonColumnIndex();
int rows = sheet.getPhysicalNumberOfRows();
int start = dataStore.getHeaderRowIndex() + 1;
latCol = dataStore.getLatColumnIndex();
lonCol = dataStore.getLonColumnIndex();
features = new ArrayList<SimpleFeature>();
filteredFeatures = new ArrayList<SimpleFeature>();
evaluator = dataStore.workbook.getCreationHelper().createFormulaEvaluator();
if (schema == null) {
schema = getSchema();
}
GeometryFactory geometryFactory = dataStore.getGeometryFactory();
SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema);
Row header = sheet.getRow(dataStore.getHeaderRowIndex());
for (int i = start; i < rows; i++) {
Row data = sheet.getRow(i);
double x = 0.0;
double y = 0.0;
for (int col = data.getFirstCellNum(); col < data.getLastCellNum(); col++) {
final Cell cell = data.getCell(col);
CellValue value = evaluator.evaluate(cell);
if (col == latCol) {
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
y = value.getNumberValue();
}
} else if (col == lonCol) {
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
x = value.getNumberValue();
}
} else {
// cast and handle
final String name = header.getCell(col).getStringCellValue().trim();
switch (value.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
AttributeType type = schema.getType(name);
Class<?> clazz = type.getBinding();
if (clazz == Double.class) {
builder.set(name, value.getNumberValue());
} else if (clazz == java.sql.Date.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
final Calendar cal = Calendar.getInstance();
cal.clear();
cal.setTime(javaDate);
java.sql.Date date = new java.sql.Date(cal.getTimeInMillis());
builder.set(name, date);
} else if (clazz == java.util.Date.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
builder.set(name, javaDate);
} else if (clazz == Time.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
final Calendar cal = Calendar.getInstance();
cal.clear();
cal.setTime(javaDate);
cal.set(0, 0, 0);
Time time = new Time(cal.getTimeInMillis());
builder.set(name, time);
}
break;
case Cell.CELL_TYPE_STRING:
builder.set(name, value.getStringValue().trim());
break;
case Cell.CELL_TYPE_BOOLEAN:
builder.set(name, value.getBooleanValue());
break;
default:
System.out.println(
"We don't handle "
+ cell.getCellType()
+ " type cells "
+ cell.getStringCellValue());
}
}
}
Point p = geometryFactory.createPoint(new Coordinate(x, y));
builder.set("the_geom", p);
SimpleFeature feature = builder.buildFeature(null);
features.add(feature);
}
filterFeatures(query);
}
@Override
protected SimpleFeatureType buildFeatureType() throws IOException {
SimpleFeatureTypeBuilder tb = new SimpleFeatureTypeBuilder();
tb.setName(entry.getName());
tb.setCRS(dataStore.getProjection());
Row header = sheet.getRow(dataStore.getHeaderRowIndex());
Row data = sheet.getRow(dataStore.getHeaderRowIndex() + 1);
Row nextData = sheet.getRow(dataStore.getHeaderRowIndex() + 2);
boolean latColGood = false;
boolean lonColGood = false;
for (int i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {
// go through and guess data type from cell types!
Cell cell = data.getCell(i);
String name = header.getCell(i).getStringCellValue().trim();
CellValue value = evaluator.evaluate(cell);
int type = value.getCellType();
Class<?> clazz = null;
if (latCol == i) {
// check it's a number
if (type == Cell.CELL_TYPE_NUMERIC) {
latColGood = true;
}
} else if (lonCol == i) {
// check it's a number
if (type == Cell.CELL_TYPE_NUMERIC) {
lonColGood = true;
}
} else {
switch (type) {
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
if (value.getNumberValue() < 1.0) {
clazz = Time.class;
} else if (Math.floor(cell.getNumericCellValue())
== Math.ceil(cell.getNumericCellValue())) {
// midnight or just a date
// check the next row
Cell cell2 = nextData.getCell(i);
if (Math.floor(cell2.getNumericCellValue())
== Math.ceil(cell2.getNumericCellValue())) {
// probably a simple date
clazz = java.sql.Date.class;
} else {
// actual date/time element
clazz = java.util.Date.class;
}
} else {
// actual date/time element
clazz = java.util.Date.class;
}
} else {
clazz = Double.class;
}
break;
case Cell.CELL_TYPE_STRING:
clazz = String.class;
break;
case Cell.CELL_TYPE_BOOLEAN:
clazz = Boolean.class;
break;
}
System.out.println(name + ":" + clazz);
tb.add(name, clazz);
}
}
if (latColGood && lonColGood) {
tb.add("the_geom", Point.class);
} else {
throw new IOException("failed to find a Lat and Lon column");
}
// build the type (it is immutable and cannot be modified)
final SimpleFeatureType SCHEMA = tb.buildFeatureType();
return SCHEMA;
}
/**
* Discription:[读取xls或者xlsx文档的所有页，并且以map格式输出：sheet名：sheet内容。
* 其中sheet内容是以列表存储，列表中存储的是String数组，sheet中的每一行则是一个String数组]
*
* @param wb 工作表格,文档
* @return
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public Map<String, Object> readExcelFile(Workbook wb) {
if (wb == null) {
return null;
}
DecimalFormat df = new DecimalFormat("#.##");
int sheetNumber = wb.getNumberOfSheets();
List list = new ArrayList();
Map<String, Object> excelMap = new HashMap<String, Object>();
for (int i = 0; i < sheetNumber; i++) {
Sheet sheet = wb.getSheetAt(i);
List<String[]> strs = new ArrayList<String[]>();
// 注意得到的行数是基于0的索引 遍历所有的行
for (int k = 0; k <= sheet.getLastRowNum(); k++) {
Row rows = sheet.getRow(k);
if (rows == null) {
continue;
}
String[] str = new String[rows.getLastCellNum()];
// 遍历每一列
for (int l = 0; l < rows.getLastCellNum(); l++) {
Cell cell = rows.getCell(l);
// 单元格类型
if (cell == null) {
continue;
}
int cellType = cell.getCellType();
switch (cellType) {
case 0: // 数字类型
str[l] = df.format(cell.getNumericCellValue());
break;
case 1: // String类型
str[l] = cell.getStringCellValue();
break;
case 2: // Formula Cell type 公式类型
FormulaEvaluator he = null;
try {
he = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
} catch (Exception e) {
he = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
}
if (he != null && he.evaluateFormulaCell(cell) == 0) {
str[l] = df.format(he.evaluate(cell).getNumberValue());
} else {
str[l] = he.evaluate(cell).getStringValue();
}
break;
case 3: // 空格
break;
case 4: // Boolean Cell type
str[l] = BooleanUtils.toStringTrueFalse(cell.getBooleanCellValue());
break;
case 5: // Errors
break;
default: // 其它格式的数据
break;
}
}
strs.add(str);
}
// list.add(strs);
excelMap.put(sheet.getSheetName(), strs);
}
return excelMap;
}
/**
* Read Excel format file.
*
* @param filename
*/
private void readExcel(String filename) {
try {
FileInputStream fin = new FileInputStream(filename);
if (flgDoubleQuotes) {
delimiter = "\"" + delimiter + "\"";
}
try {
Workbook wb = WorkbookFactory.create(fin);
// HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
String sheetname = sheet.getSheetName();
String fname = sheetname;
if (delimiter.equals("\t")) {
fname = fname + ".tsv";
} else {
fname = fname + ".csv";
}
File file = new File(fname);
FileOutputStream fout = new FileOutputStream(file);
OutputStreamWriter ow = new OutputStreamWriter(fout, charset);
BufferedWriter bw = new BufferedWriter(ow);
for (Iterator<Row> rowIter = sheet.rowIterator(); rowIter.hasNext(); ) {
Row row = rowIter.next();
String tmp = "";
if (flgDoubleQuotes) {
tmp = "\"";
}
if (row != null) {
for (int k = 0; k < row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
// CellValue celv = evaluator.evaluate(cell);
if (cell == null) {
tmp = tmp + delimiter;
continue;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
tmp = tmp + " " + delimiter;
break;
case Cell.CELL_TYPE_NUMERIC:
tmp = tmp + getNumericValue(cell) + delimiter;
break;
case Cell.CELL_TYPE_STRING:
tmp = tmp + getStringValue(cell) + delimiter;
break;
case Cell.CELL_TYPE_FORMULA:
try {
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
CellValue value = evaluator.evaluate(cell);
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
tmp = tmp + getNumericValue(cell) + delimiter;
} else if (value.getCellType() == Cell.CELL_TYPE_STRING) {
tmp = tmp + getStringValue(cell) + delimiter;
}
} catch (FormulaParseException e) {
// error
tmp = tmp + " " + delimiter;
System.err.println(e.getLocalizedMessage());
} catch (NotImplementedException e) {
// error
tmp = tmp + " " + delimiter;
System.err.println(e.getLocalizedMessage());
}
break;
default:
tmp = tmp + " " + delimiter;
}
}
tmp = tmp.substring(0, tmp.length() - 1);
}
bw.write(tmp + "\n");
}
bw.flush();
bw.close();
ow.close();
fout.close();
System.gc();
}
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
/**
* Test method for {@link
* sif.IO.spreadsheet.poi.POIWriter#insertTestInput(sif.model.policy.policyrule.DynamicPolicyRule,
* sif.model.inspection.DynamicInspectionRequest)} .
*
* @throws Exception
*/
@Test
public void testInsertTestInput() throws Exception {
FrontDesk desk = FrontDesk.getInstance();
// read policy & rule
File spreadsheetFile = new File(filepath);
DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));
@SuppressWarnings("unchecked")
DynamicInspectionRequest<Workbook> req =
(DynamicInspectionRequest<Workbook>)
desk.requestNewDynamicInspection("TestInputInsertionTest", spreadsheetFile);
FrontDesk.getInstance().scan();
FrontDesk.getInstance().register(policy);
FrontDesk.getInstance().setPolicy(policy);
DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0");
assertTrue("No rule with name \"greaterThan0\" was found", rule != null);
// write TestInput in PoiWorkbook
POIWriter writer = new POIWriter();
writer.insertTestInput(rule, req.getExternalSpreadsheet());
Workbook wb = req.getExternalSpreadsheet();
CellReference cellRef = new CellReference(inputCell1);
Sheet sheet2 = wb.getSheet(cellRef.getSheetName());
Row row = sheet2.getRow(cellRef.getRow());
Cell cell = row.getCell(cellRef.getCol());
System.out.println(cell.toString());
assertTrue(cell.toString().equals(expectedValue1.toString()));
cellRef = new CellReference(inputCell2);
sheet2 = wb.getSheet(cellRef.getSheetName());
row = sheet2.getRow(cellRef.getRow());
cell = row.getCell(cellRef.getCol());
System.out.println(cell.toString());
assertTrue(cell.toString().equals(expectedValue2.toString()));
cellRef = new CellReference(resultCell);
sheet2 = wb.getSheet(cellRef.getSheetName());
row = sheet2.getRow(cellRef.getRow());
cell = row.getCell(cellRef.getCol());
// Just for information print result
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
if (cell != null) {
switch (evaluator.evaluateFormulaCell(cell)) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
System.out.println(cell.getErrorCellValue());
break;
// CELL_TYPE_FORMULA will never occur
case Cell.CELL_TYPE_FORMULA:
break;
}
}
}
