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;
 }
 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());
 }
  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;
  }
  public void testGetColNumFromRef() {
    String cellRef = "A1";
    CellReference cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getCol());

    cellRef = "AA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26, cellReference.getCol());

    cellRef = "AB1";
    cellReference = new CellReference(cellRef);
    assertEquals(27, cellReference.getCol());

    cellRef = "BA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 + 26, cellReference.getCol());

    cellRef = "CA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 + 26 + 26, cellReference.getCol());

    cellRef = "ZA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26, cellReference.getCol());

    cellRef = "ZZ1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 + 25, cellReference.getCol());

    cellRef = "AAA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 + 26, cellReference.getCol());

    cellRef = "A1100";
    cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getCol());

    cellRef = "BC15";
    cellReference = new CellReference(cellRef);
    assertEquals(54, cellReference.getCol());
  }
Exemple #5
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("\\$", "");
 }
Exemple #6
0
 public static int[] getCellPosition(String cellIndex) {
   CellReference cell = new CellReference(cellIndex);
   return new int[] {cell.getRow(), cell.getCol()};
 }
  public void testGetCellRefParts() {
    CellReference cellReference;
    String[] parts;

    String cellRef = "A1";
    cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("1", parts[1]);
    assertEquals("A", parts[2]);

    cellRef = "AA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("1", parts[1]);
    assertEquals("AA", parts[2]);

    cellRef = "AA100";
    cellReference = new CellReference(cellRef);
    assertEquals(26, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("100", parts[1]);
    assertEquals("AA", parts[2]);

    cellRef = "AAA300";
    cellReference = new CellReference(cellRef);
    assertEquals(702, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("300", parts[1]);
    assertEquals("AAA", parts[2]);

    cellRef = "ZZ100521";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 + 25, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("100521", parts[1]);
    assertEquals("ZZ", parts[2]);

    cellRef = "ZYX987";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 * 26 + 25 * 26 + 24 - 1, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("987", parts[1]);
    assertEquals("ZYX", parts[2]);

    cellRef = "AABC10065";
    cellReference = new CellReference(cellRef);
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("10065", parts[1]);
    assertEquals("AABC", parts[2]);
  }
  /**
   * 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;
      }
    }
  }