예제 #1
0
 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);
       }
     }
   }
 }
예제 #2
0
  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;
  }
예제 #3
0
  /**
   * 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;
  }
예제 #4
0
 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;
 }
예제 #5
0
파일: OoxmlUtil.java 프로젝트: zhangrui1/SK
 /**
  * セルに設定された計算式を評価して値を取得する
  *
  * @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;
 }
예제 #6
0
 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());
   }
 }
예제 #7
0
  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;
  }
예제 #8
0
  /**
   * 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);
  }
예제 #9
0
  @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;
 }
예제 #11
0
  /**
   * 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;
      }
    }
  }