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());
}
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("\\$", "");
}
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;
}
}
}
