private static void confirmError(
HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, int expectedErrorCode) {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
assertEquals(result.getCellType(), HSSFCell.CELL_TYPE_ERROR);
assertEquals(expectedErrorCode, result.getErrorValue());
}
private static void confirmResult(
HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, int expectedResult) {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
assertEquals(result.getCellType(), HSSFCell.CELL_TYPE_NUMERIC);
assertEquals(expectedResult, result.getNumberValue(), 0.0);
}
/**
* Example from
* http://office.microsoft.com/en-us/excel-help/intercept-function-HP010062512.aspx?CTT=5&origin=HA010277524
*/
public void testFromFile() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("intercept.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet example1 = wb.getSheet("Example 1");
HSSFCell a8 = example1.getRow(7).getCell(0);
assertEquals("INTERCEPT(A2:A6,B2:B6)", a8.getCellFormula());
fe.evaluate(a8);
assertEquals(0.048387097, a8.getNumericCellValue(), 0.000000001);
}
private Integer getNumericValueFromCell(HSSFCell cell, HSSFFormulaEvaluator formulaEvaluator)
throws BusinessException {
Integer value = null;
if (cell != null) {
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
value = (int) cell.getNumericCellValue();
} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
String strValue = cell.getRichStringCellValue().getString();
try {
value = Integer.valueOf(strValue);
} catch (NumberFormatException e) {
throw new BusinessException("Formato de Planilha inválido para importação");
}
} else if (HSSFCell.CELL_TYPE_FORMULA == cell.getCellType()) {
CellValue cellValue = formulaEvaluator.evaluate(cell);
value = (int) cellValue.getNumberValue();
} else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) {
value = null;
} else {
throw new BusinessException("Formato de Planilha inválido para importação");
}
}
return value;
}
@Override
public void load() throws IOException {
if (dataFile != null) {
// do not load the file twice.
return;
}
InputStream in = null;
try {
in = new FileInputStream(inputFile);
if (useXLSX) {
dataFile = new XSSFWorkbook(in);
} else {
dataFile = new HSSFWorkbook(in);
}
} finally {
FileUtil.closeQuietely(in);
}
initActiveSheet();
try {
if (useXLSX) {
XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) dataFile);
} else {
HSSFFormulaEvaluator.evaluateAllFormulaCells((HSSFWorkbook) dataFile);
}
} catch (Exception ex) {
LogMgr.logError("ExcelReader.load()", "Could not refresh formulas!", ex);
}
}
private static void confirmCellEval(
HSSFSheet sheet,
int rowIx,
int colIx,
HSSFFormulaEvaluator fe,
String expectedFormula,
double expectedResult) {
HSSFCell cell = sheet.getRow(rowIx).getCell(colIx);
assertEquals(expectedFormula, cell.getCellFormula());
CellValue cv = fe.evaluate(cell);
assertEquals(expectedResult, cv.getNumberValue(), 0.0);
}
public String getZhFrom4GYsb() throws IOException {
// 预算表中 第三行 B列的名称为： 单项工程名称:SXZH001TL新建、共址2G、共址其他运营商的(F)（D)宏站基站
// path1: 预算表 path2 3g4g基础信息
String results = "";
String result2 = "";
FileInputStream fise = new FileInputStream(excelPath);
HSSFWorkbook wb = new HSSFWorkbook(fise);
HSSFWorkbook wb2 = new HSSFWorkbook(new FileInputStream(excelPath2));
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator e2 = new HSSFFormulaEvaluator(wb2);
// TODO: 将此处的文件名替换为从参数读取的文件名
String[] strArray = new String[2];
String[] paths = this.excelPath.split("\\\\");
strArray[0] = paths[paths.length - 1];
// System.out.println(strArray[0]);
String[] paths1 = this.excelPath2.split("\\\\");
// strArray[1] = paths1[paths1.length-1];
// System.out.println(strArray[1]);
HSSFFormulaEvaluator[] evals = new HSSFFormulaEvaluator[2];
evals[0] = e;
evals[1] = e2;
Sheet sheet = wb.getSheetAt(7);
Row r = null;
r = sheet.getRow(2);
Cell cell2 = r.getCell(1);
Cell cell8 = r.getCell(8);
// System.out.println(cell8.toString());
String[] path1 = cell8.toString().split("\'");
String path12 = path1[3];
String[] path2 = path12.split("]");
String path21 = path2[0];
String path3 = path21.substring(1);
strArray[1] = path3;
// System.out.println(path3);
HSSFFormulaEvaluator.setupEnvironment(strArray, evals);
if (cell2.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
results = e.evaluate(cell2).getStringValue();
// results = cell2.getStringCellValue();
} else if (cell2.getCellType() == HSSFCell.CELL_TYPE_STRING) {
results = cell2.toString();
}
System.out.println(result2);
// TL TLD TLFD SXZH017TL 前面有7位 TL- conditions
// TL-1、TL-2、TL-3 TLD-1 TLD-2 TLD-3
if (results.contains("TLFD")) {
int end = results.indexOf("TLFD");
end = end + 4;
int begin = end - 12;
result2 = results.substring(begin + 1, end);
} else if (results.contains("TL-")) {
int end = results.indexOf("TL-");
int count = 11;
boolean flag = true;
end = end + 3;
System.out.println(results.substring(end - 1, end));
for (int k = 1; k < 5; k++) {
end = end + 1;
System.out.println(results.substring(end - 1, end));
flag = isNumeric(results.substring(end - 1, end));
if (flag) {
count++;
} else {
break;
}
}
int begin = end - count;
result2 = results.substring(begin + 1, end);
} else if (results.contains("TLD-")) {
int end = results.indexOf("TLD-");
int count = 12;
boolean flag = true;
end = end + 4;
System.out.println(results.substring(end - 1, end));
for (int k = 1; k < 5; k++) {
end = end + 1;
System.out.println(results.substring(end - 1, end));
flag = isNumeric(results.substring(end - 1, end));
if (flag) {
count++;
} else {
break;
}
}
int begin = end - count;
result2 = results.substring(begin + 1, end);
} else if (results.contains("TLD")) {
int end = results.indexOf("TLD");
end = end + 3;
int begin = end - 11;
result2 = results.substring(begin + 1, end);
} else if (results.contains("TL")) {
// System.out.println(results+"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
int end = results.indexOf("TL");
// System.out.println(end);
end = end + 2;
// System.out.println(results.substring(end-1, end));
int begin = end - 10;
result2 = results.substring(begin + 1, end);
}
wb2.close();
wb.close();
fise.close();
return result2;
}
