예제 #1
0
 private Cell getOrCreateCell(CellReference cellRef) {
   Cell cell = spreadsheet.getCell(cellRef.getRow(), cellRef.getCol());
   if (cell == null) {
     cell = spreadsheet.createCell(cellRef.getRow(), cellRef.getCol(), "");
   }
   return cell;
 }
예제 #2
0
  /**
   * Gets the total number of rows in the selection. (Note: in this version autofiltering is
   * ignored)
   *
   * @return
   */
  public int getRowCount() {

    CellReference from = getStartCellReference();
    CellReference to = getEndCellReference();

    int rowCount = -1;
    if (from != null && to != null) {
      rowCount = to.getRow() - from.getRow();
    }
    return rowCount;
  }
예제 #3
0
 protected AreaPtgBase(AreaReference ar) {
   CellReference firstCell = ar.getFirstCell();
   CellReference lastCell = ar.getLastCell();
   setFirstRow(firstCell.getRow());
   setFirstColumn(firstCell.getCol() == -1 ? 0 : firstCell.getCol());
   setLastRow(lastCell.getRow());
   setLastColumn(lastCell.getCol() == -1 ? 0xFF : lastCell.getCol());
   setFirstColRelative(!firstCell.isColAbsolute());
   setLastColRelative(!lastCell.isColAbsolute());
   setFirstRowRelative(!firstCell.isRowAbsolute());
   setLastRowRelative(!lastCell.isRowAbsolute());
 }
예제 #4
0
  public void testGetRowNumFromRef() {
    String cellRef = "A1";
    CellReference cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getRow());

    cellRef = "A12";
    cellReference = new CellReference(cellRef);
    assertEquals(11, cellReference.getRow());

    cellRef = "AS121";
    cellReference = new CellReference(cellRef);
    assertEquals(120, cellReference.getRow());
  }
예제 #5
0
  public static Date getBaseDateFromExcelWithPoi(File file) {
    InputStream in = null;
    try {
      in = new FileInputStream(file);
      Workbook workbook = WorkbookFactory.create(in);
      Sheet sheet = workbook.getSheetAt(0);
      Name name = workbook.getName("雷線基準日");
      CellReference cellRef = new CellReference(name.getRefersToFormula());
      Row row = sheet.getRow(cellRef.getRow());
      Cell baseDateCell = row.getCell(cellRef.getCol());
      // System.out.println("cellが日付か:"
      // + PoiUtil.isCellDateFormatted(baseDateCell));
      Date baseDate = baseDateCell.getDateCellValue();
      return baseDate;

    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (in != null)
        try {
          in.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
    }
    return null;
  }
예제 #6
0
 public static String offsetCellIndex(String cellIndex, int rowOffset, int colOffset) {
   CellReference cell = new CellReference(cellIndex);
   CellReference newCell = new CellReference(cell.getRow() + rowOffset, cell.getCol() + colOffset);
   return newCell.formatAsString().replaceAll("\\$", "");
 }
예제 #7
0
 public static int[] getCellPosition(String cellIndex) {
   CellReference cell = new CellReference(cellIndex);
   return new int[] {cell.getRow(), cell.getCol()};
 }
  /**
   * 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;
      }
    }
  }