예제 #1
0
 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());
 }
예제 #2
0
 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);
 }
예제 #3
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;
  }
예제 #5
0
  @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);
 }
예제 #7
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;
  }