List of usage examples for org.apache.poi.ss.usermodel Cell getBooleanCellValue
boolean getBooleanCellValue();
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ??? ??????<br>//ww w .ja v a 2 s .c om * <br> * ?[CELL_TYPE_ERROR]??<br> * xls? ?HSSFErrorConstants?<br> * xlsx? Excel??ex.#DIV/0!?#N/A?#REF! * * @param cell * @return */ public static Object getCellValue(Cell cell) { Object value = null; if (cell != null) { switch (cell.getCellTypeEnum()) { case BLANK: break; case BOOLEAN: value = cell.getBooleanCellValue(); break; case ERROR: value = cell.getErrorCellValue(); break; case NUMERIC: // ?? if (isCellDateFormatted(cell)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } break; case STRING: value = cell.getStringCellValue(); break; case FORMULA: FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper() .createFormulaEvaluator(); // ? CellValue cellValue = evaluator.evaluate(cell); CellType cellType = cellValue.getCellTypeEnum(); // ???? switch (cellType) { case BLANK: break; case BOOLEAN: value = cell.getBooleanCellValue(); break; case ERROR: if (cell instanceof XSSFCell) { // XSSF?????? XSSFCell xssfCell = (XSSFCell) cell; CTCell ctCell = xssfCell.getCTCell(); value = ctCell.getV(); } else if (cell instanceof HSSFCell) { // HSSF?????? value = cell.getErrorCellValue(); } break; case NUMERIC: // ?? if (isCellDateFormatted(cell)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } break; case STRING: value = cell.getStringCellValue(); break; default: break; } default: break; } } return value; }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ?// w ww . ja v a2s .com * * @param fromCell * @param toCell */ public static void copyCell(Cell fromCell, Cell toCell) { if (fromCell != null) { // CellType cellType = fromCell.getCellTypeEnum(); switch (cellType) { case BLANK: break; case FORMULA: String cellFormula = fromCell.getCellFormula(); toCell.setCellFormula(cellFormula); break; case BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case ERROR: toCell.setCellErrorValue(fromCell.getErrorCellValue()); break; case NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case STRING: toCell.setCellValue(fromCell.getRichStringCellValue()); break; default: } // if (fromCell.getCellStyle() != null && fromCell.getSheet().getWorkbook().equals(toCell.getSheet().getWorkbook())) { toCell.setCellStyle(fromCell.getCellStyle()); } // if (fromCell.getCellComment() != null) { toCell.setCellComment(fromCell.getCellComment()); } } }
From source file:org.bbreak.excella.core.util.PoiUtilTest.java
License:Open Source License
@Test public void testPoiUtil3() throws IOException, ParseException { Workbook workbook = getWorkbook();/*w ww . j a v a 2 s . c om*/ Sheet sheet_1 = workbook.getSheetAt(0); Sheet sheet_2 = workbook.getSheetAt(1); Sheet sheet_3 = workbook.getSheetAt(2); Sheet sheet_4 = workbook.getSheetAt(3); Sheet sheet_5 = workbook.getSheetAt(4); Sheet sheet_6 = workbook.getSheetAt(5); Sheet sheet_7 = workbook.getSheetAt(6); // =============================================== // copyCell( Cell fromCell, Cell toCell) // =============================================== // No.1 ? Cell fromCellNumeric = sheet_1.getRow(0).getCell(0); Cell fromCellFormula = sheet_1.getRow(1).getCell(0); Cell fromCellString = sheet_1.getRow(2).getCell(0); Cell fromCellBoolean = sheet_1.getRow(3).getCell(0); Cell fromCellError = sheet_1.getRow(4).getCell(0); Cell fromCellDate = sheet_1.getRow(5).getCell(0); Cell fromCellBlank = sheet_1.getRow(6).getCell(0); Cell toCellNumeric = sheet_1.getRow(0).createCell(9); Cell toCellFormula = sheet_1.getRow(1).createCell(9); Cell toCellString = sheet_1.getRow(2).createCell(9); Cell toCellBoolean = sheet_1.getRow(3).createCell(9); Cell toCellError = sheet_1.getRow(4).createCell(9); Cell toCellDate = sheet_1.getRow(5).createCell(9); Cell toCellBlank = sheet_1.getRow(6).createCell(9); Cell fromCellNumericFrml = sheet_1.getRow(7).getCell(0); Cell fromCellStringFrml = sheet_1.getRow(8).getCell(0); Cell fromCellBooleanFrml = sheet_1.getRow(9).getCell(0); Cell fromCellErrorFrml = sheet_1.getRow(10).getCell(0); Cell fromCellDateFrml = sheet_1.getRow(11).getCell(0); Cell fromCellBlankFrml = sheet_1.getRow(12).getCell(0); Cell toCellNumericFrml = sheet_1.getRow(7).createCell(9); Cell toCellStringFrml = sheet_1.getRow(8).createCell(9); Cell toCellBooleanFrml = sheet_1.getRow(9).createCell(9); Cell toCellErrorFrml = sheet_1.getRow(10).createCell(9); Cell toCellDateFrml = sheet_1.getRow(11).createCell(9); Cell toCellBlankFrml = sheet_1.getRow(12).createCell(9); PoiUtil.copyCell(fromCellNumeric, toCellNumeric); PoiUtil.copyCell(fromCellFormula, toCellFormula); PoiUtil.copyCell(fromCellString, toCellString); PoiUtil.copyCell(fromCellBoolean, toCellBoolean); PoiUtil.copyCell(fromCellError, toCellError); PoiUtil.copyCell(fromCellDate, toCellDate); PoiUtil.copyCell(fromCellBlank, toCellBlank); PoiUtil.copyCell(fromCellNumericFrml, toCellNumericFrml); PoiUtil.copyCell(fromCellStringFrml, toCellStringFrml); PoiUtil.copyCell(fromCellBooleanFrml, toCellBooleanFrml); PoiUtil.copyCell(fromCellErrorFrml, toCellErrorFrml); PoiUtil.copyCell(fromCellDateFrml, toCellDateFrml); PoiUtil.copyCell(fromCellBlankFrml, toCellBlankFrml); try { // ? TestUtil.checkCell(fromCellNumeric, toCellNumeric); TestUtil.checkCell(fromCellFormula, toCellFormula); TestUtil.checkCell(fromCellString, toCellString); TestUtil.checkCell(fromCellBoolean, toCellBoolean); TestUtil.checkCell(fromCellError, toCellError); TestUtil.checkCell(fromCellDate, toCellDate); TestUtil.checkCell(fromCellBlank, toCellBlank); TestUtil.checkCell(fromCellNumericFrml, toCellNumericFrml); TestUtil.checkCell(fromCellStringFrml, toCellStringFrml); TestUtil.checkCell(fromCellBooleanFrml, toCellBooleanFrml); TestUtil.checkCell(fromCellErrorFrml, toCellErrorFrml); TestUtil.checkCell(fromCellDateFrml, toCellDateFrml); TestUtil.checkCell(fromCellBlankFrml, toCellBlankFrml); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.2 fromCell?null Cell toCell = sheet_1.getRow(0).createCell(10); PoiUtil.copyCell(null, toCell); // No.3 toCell?null try { PoiUtil.copyCell(fromCellNumeric, null); fail(); } catch (NullPointerException ex) { // toCell?null???? } // No.4 ? Cell toCellNumeric2 = sheet_2.getRow(0).createCell(0); PoiUtil.copyCell(fromCellNumeric, toCellNumeric2); // =============================================== // copyRange( Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum, int toColumnNum, boolean clearFromRange) // =============================================== // No.5 ? PoiUtil.copyRange(sheet_1, new CellRangeAddress(0, 0, 0, 0), sheet_2, 0, 3, false); try { TestUtil.checkCell(sheet_1.getRow(0).getCell(0), sheet_2.getRow(0).getCell(3)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.6 PoiUtil.copyRange(sheet_1, new CellRangeAddress(1, 12, 0, 1), sheet_2, 9, 0, false); try { TestUtil.checkCell(sheet_1.getRow(1).getCell(0), sheet_2.getRow(9).getCell(0)); TestUtil.checkCell(sheet_1.getRow(2).getCell(0), sheet_2.getRow(10).getCell(0)); TestUtil.checkCell(sheet_1.getRow(3).getCell(0), sheet_2.getRow(11).getCell(0)); TestUtil.checkCell(sheet_1.getRow(4).getCell(0), sheet_2.getRow(12).getCell(0)); TestUtil.checkCell(sheet_1.getRow(5).getCell(0), sheet_2.getRow(13).getCell(0)); TestUtil.checkCell(sheet_1.getRow(6).getCell(0), sheet_2.getRow(14).getCell(0)); TestUtil.checkCell(sheet_1.getRow(7).getCell(0), sheet_2.getRow(15).getCell(0)); TestUtil.checkCell(sheet_1.getRow(8).getCell(0), sheet_2.getRow(16).getCell(0)); TestUtil.checkCell(sheet_1.getRow(9).getCell(0), sheet_2.getRow(17).getCell(0)); TestUtil.checkCell(sheet_1.getRow(10).getCell(0), sheet_2.getRow(18).getCell(0)); TestUtil.checkCell(sheet_1.getRow(11).getCell(0), sheet_2.getRow(19).getCell(0)); TestUtil.checkCell(sheet_1.getRow(12).getCell(0), sheet_2.getRow(20).getCell(0)); TestUtil.checkCell(sheet_1.getRow(1).getCell(1), sheet_2.getRow(9).getCell(1)); TestUtil.checkCell(sheet_1.getRow(2).getCell(1), sheet_2.getRow(10).getCell(1)); TestUtil.checkCell(sheet_1.getRow(3).getCell(1), sheet_2.getRow(11).getCell(1)); TestUtil.checkCell(sheet_1.getRow(4).getCell(1), sheet_2.getRow(12).getCell(1)); TestUtil.checkCell(sheet_1.getRow(5).getCell(1), sheet_2.getRow(13).getCell(1)); TestUtil.checkCell(sheet_1.getRow(6).getCell(1), sheet_2.getRow(14).getCell(1)); TestUtil.checkCell(sheet_1.getRow(7).getCell(1), sheet_2.getRow(15).getCell(1)); TestUtil.checkCell(sheet_1.getRow(8).getCell(1), sheet_2.getRow(16).getCell(1)); TestUtil.checkCell(sheet_1.getRow(9).getCell(1), sheet_2.getRow(17).getCell(1)); TestUtil.checkCell(sheet_1.getRow(10).getCell(1), sheet_2.getRow(18).getCell(1)); TestUtil.checkCell(sheet_1.getRow(11).getCell(1), sheet_2.getRow(19).getCell(1)); TestUtil.checkCell(sheet_1.getRow(12).getCell(1), sheet_2.getRow(20).getCell(1)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.7 ?null PoiUtil.copyRange(null, new CellRangeAddress(0, 0, 0, 0), sheet_2, 0, 0, false); PoiUtil.copyRange(sheet_1, null, sheet_2, 0, 0, false); PoiUtil.copyRange(sheet_1, new CellRangeAddress(0, 0, 0, 0), null, 0, 0, false); // No.8 ?? try { PoiUtil.copyRange(sheet_1, new CellRangeAddress(-1, 0, 0, 0), sheet_2, 0, 0, false); } catch (IllegalArgumentException ex) { // ??????? } // No.9 ?? PoiUtil.copyRange(sheet_1, new CellRangeAddress(23, 23, 0, 1), sheet_2, 22, 0, false); try { TestUtil.checkCell(sheet_1.getRow(23).getCell(0), sheet_2.getRow(22).getCell(0)); TestUtil.checkCell(sheet_1.getRow(23).getCell(1), sheet_2.getRow(22).getCell(1)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.10 ?? PoiUtil.copyRange(sheet_1, new CellRangeAddress(25, 26, 0, 0), sheet_2, 24, 0, false); try { TestUtil.checkCell(sheet_1.getRow(25).getCell(0), sheet_2.getRow(24).getCell(0)); TestUtil.checkCell(sheet_1.getRow(26).getCell(0), sheet_2.getRow(25).getCell(0)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.11 null PoiUtil.copyRange(sheet_1, new CellRangeAddress(30, 30, 0, 1), sheet_2, 29, 0, false); try { TestUtil.checkCell(sheet_1.getRow(30).getCell(0), sheet_2.getRow(29).getCell(0)); TestUtil.checkCell(sheet_1.getRow(30).getCell(1), sheet_2.getRow(29).getCell(1)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.12 null PoiUtil.copyRange(sheet_1, new CellRangeAddress(34, 34, 0, 3), sheet_2, 33, 0, false); assertNull(sheet_2.getRow(33)); // No.13 ?? Cell copyFrom1 = sheet_2.getRow(40).getCell(0); Cell copyFrom2 = sheet_2.getRow(40).getCell(1); Cell copyFrom3 = sheet_2.getRow(40).getCell(2); Cell copyFrom4 = sheet_2.getRow(41).getCell(0); Cell copyFrom5 = sheet_2.getRow(41).getCell(1); Cell copyFrom6 = sheet_2.getRow(41).getCell(2); PoiUtil.copyRange(sheet_2, new CellRangeAddress(40, 41, 0, 2), sheet_2, 41, 1, false); try { TestUtil.checkCell(copyFrom1, sheet_2.getRow(41).getCell(1)); TestUtil.checkCell(copyFrom2, sheet_2.getRow(41).getCell(2)); TestUtil.checkCell(copyFrom3, sheet_2.getRow(41).getCell(3)); TestUtil.checkCell(copyFrom4, sheet_2.getRow(42).getCell(1)); TestUtil.checkCell(copyFrom5, sheet_2.getRow(42).getCell(2)); TestUtil.checkCell(copyFrom6, sheet_2.getRow(42).getCell(3)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.14 ??? copyFrom1 = sheet_2.getRow(49).getCell(0); PoiUtil.copyRange(sheet_2, new CellRangeAddress(49, 49, 0, 0), sheet_2, 49, 2, true); assertNull(sheet_2.getRow(49).getCell(0)); try { TestUtil.checkCell(copyFrom1, sheet_2.getRow(49).getCell(2)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // No.15 ?? copyFrom1 = new CellClone(sheet_2.getRow(55).getCell(0)); copyFrom2 = new CellClone(sheet_2.getRow(55).getCell(1)); copyFrom3 = new CellClone(sheet_2.getRow(55).getCell(2)); copyFrom4 = new CellClone(sheet_2.getRow(56).getCell(0)); copyFrom5 = new CellClone(sheet_2.getRow(56).getCell(1)); copyFrom6 = new CellClone(sheet_2.getRow(56).getCell(2)); PoiUtil.copyRange(sheet_2, new CellRangeAddress(55, 56, 0, 2), sheet_2, 56, 1, true); assertNull(sheet_2.getRow(55).getCell(0)); assertNull(sheet_2.getRow(55).getCell(1)); assertNull(sheet_2.getRow(55).getCell(2)); assertNull(sheet_2.getRow(56).getCell(0)); try { TestUtil.checkCell(copyFrom1, sheet_2.getRow(56).getCell(1)); TestUtil.checkCell(copyFrom2, sheet_2.getRow(56).getCell(2)); TestUtil.checkCell(copyFrom3, sheet_2.getRow(56).getCell(3)); TestUtil.checkCell(copyFrom4, sheet_2.getRow(57).getCell(1)); TestUtil.checkCell(copyFrom5, sheet_2.getRow(57).getCell(2)); TestUtil.checkCell(copyFrom6, sheet_2.getRow(57).getCell(3)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // insertRangeDown( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.16 insertRangeDown copyFrom1 = sheet_3.getRow(1).getCell(1); copyFrom2 = sheet_3.getRow(1).getCell(2); copyFrom3 = sheet_3.getRow(2).getCell(1); copyFrom4 = sheet_3.getRow(2).getCell(2); PoiUtil.insertRangeDown(sheet_3, new CellRangeAddress(1, 2, 1, 2)); assertNull(sheet_3.getRow(1).getCell(1)); assertNull(sheet_3.getRow(1).getCell(2)); assertNull(sheet_3.getRow(2).getCell(1)); assertNull(sheet_3.getRow(2).getCell(2)); try { TestUtil.checkCell(copyFrom1, sheet_3.getRow(3).getCell(1)); TestUtil.checkCell(copyFrom2, sheet_3.getRow(3).getCell(2)); TestUtil.checkCell(copyFrom3, sheet_3.getRow(4).getCell(1)); TestUtil.checkCell(copyFrom4, sheet_3.getRow(4).getCell(2)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // insertRangeRight( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.17 insertRangeRight copyFrom1 = sheet_3.getRow(6).getCell(5); copyFrom2 = sheet_3.getRow(6).getCell(6); copyFrom3 = sheet_3.getRow(7).getCell(5); copyFrom4 = sheet_3.getRow(7).getCell(6); PoiUtil.insertRangeRight(sheet_3, new CellRangeAddress(6, 7, 5, 6)); assertNull(sheet_3.getRow(6).getCell(5)); assertNull(sheet_3.getRow(6).getCell(6)); assertNull(sheet_3.getRow(7).getCell(5)); assertNull(sheet_3.getRow(7).getCell(6)); try { TestUtil.checkCell(copyFrom1, sheet_3.getRow(6).getCell(7)); TestUtil.checkCell(copyFrom2, sheet_3.getRow(6).getCell(8)); TestUtil.checkCell(copyFrom3, sheet_3.getRow(7).getCell(7)); TestUtil.checkCell(copyFrom4, sheet_3.getRow(7).getCell(8)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // deleteRangeUp( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.18 deleteRangeUp copyFrom1 = sheet_3.getRow(13).getCell(9); copyFrom2 = sheet_3.getRow(13).getCell(10); copyFrom3 = sheet_3.getRow(14).getCell(9); copyFrom4 = sheet_3.getRow(14).getCell(10); PoiUtil.deleteRangeUp(sheet_3, new CellRangeAddress(11, 12, 9, 10)); assertNull(sheet_3.getRow(13).getCell(9)); assertNull(sheet_3.getRow(13).getCell(10)); assertNull(sheet_3.getRow(14).getCell(9)); assertNull(sheet_3.getRow(14).getCell(10)); try { TestUtil.checkCell(copyFrom1, sheet_3.getRow(11).getCell(9)); TestUtil.checkCell(copyFrom2, sheet_3.getRow(11).getCell(10)); TestUtil.checkCell(copyFrom3, sheet_3.getRow(12).getCell(9)); TestUtil.checkCell(copyFrom4, sheet_3.getRow(12).getCell(10)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // deleteRangeLeft( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.19 deleteRangeLeft copyFrom1 = sheet_3.getRow(16).getCell(15); copyFrom2 = sheet_3.getRow(16).getCell(14); copyFrom3 = sheet_3.getRow(17).getCell(15); copyFrom4 = sheet_3.getRow(17).getCell(14); PoiUtil.deleteRangeLeft(sheet_3, new CellRangeAddress(16, 17, 13, 14)); assertNull(sheet_3.getRow(16).getCell(15)); assertNull(sheet_3.getRow(16).getCell(16)); assertNull(sheet_3.getRow(17).getCell(15)); assertNull(sheet_3.getRow(17).getCell(16)); try { TestUtil.checkCell(copyFrom1, sheet_3.getRow(16).getCell(13)); TestUtil.checkCell(copyFrom2, sheet_3.getRow(16).getCell(14)); TestUtil.checkCell(copyFrom3, sheet_3.getRow(17).getCell(13)); TestUtil.checkCell(copyFrom4, sheet_3.getRow(17).getCell(14)); } catch (CheckException ex) { System.out.println(ex.getCheckMessagesToString()); fail(); } // =============================================== // clearRange( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.20 ???? PoiUtil.clearRange(sheet_4, new CellRangeAddress(0, 2, 0, 0)); assertNull(sheet_4.getRow(0).getCell(0)); assertNull(sheet_4.getRow(1).getCell(0)); assertNull(sheet_4.getRow(2).getCell(0)); assertEquals("4", sheet_4.getRow(3).getCell(0).getStringCellValue()); // No.21 ???? PoiUtil.clearRange(sheet_4, new CellRangeAddress(4, 5, 0, 1)); assertNull(sheet_4.getRow(4).getCell(0)); assertNull(sheet_4.getRow(5).getCell(0)); assertNull(sheet_4.getRow(4).getCell(1)); assertNull(sheet_4.getRow(5).getCell(1)); assertEquals("5C", sheet_4.getRow(4).getCell(2).getStringCellValue()); assertEquals("6C", sheet_4.getRow(5).getCell(2).getStringCellValue()); // No.22 ??? PoiUtil.clearRange(sheet_4, new CellRangeAddress(8, 8, 0, 1)); assertNull(null, sheet_4.getRow(8).getCell(0)); // No.23 ??? try { PoiUtil.clearRange(sheet_4, new CellRangeAddress(10, 10, 0, 0)); fail(); } catch (IllegalArgumentException ex) { // ???????????? } // ???????? assertEquals("11", sheet_4.getRow(10).getCell(0).getStringCellValue()); assertNotNull(sheet_4.getRow(10).getCell(1).getStringCellValue()); // No.24 ??? try { PoiUtil.clearRange(sheet_4, new CellRangeAddress(12, 12, 0, 0)); fail(); } catch (IllegalArgumentException ex) { // ???????????? } // ???????? assertEquals("13", sheet_4.getRow(12).getCell(0).getStringCellValue()); assertNotNull(sheet_4.getRow(13).getCell(0).getStringCellValue()); // =============================================== // clearCell( Sheet sheet, CellRangeAddress rangeAddress) // =============================================== // No.25 clearCell PoiUtil.clearCell(sheet_4, new CellRangeAddress(15, 16, 0, 0)); assertNull(sheet_4.getRow(15).getCell(0)); assertNull(sheet_4.getRow(15).getCell(0)); // =============================================== // setHyperlink( Cell cell, int type, String address) // =============================================== // No.26 setHyperlink Cell cellHyperlink = sheet_5.getRow(0).getCell(0); String address = "http://sourceforge.jp/projects/excella-core/"; PoiUtil.setHyperlink(cellHyperlink, HyperlinkType.URL, address); Hyperlink hyperLink = cellHyperlink.getHyperlink(); if (hyperLink instanceof HSSFHyperlink) { assertEquals(address, ((HSSFHyperlink) hyperLink).getTextMark()); } else if (hyperLink instanceof XSSFHyperlink) { assertEquals(address, ((XSSFHyperlink) hyperLink).getAddress()); } // =============================================== // setCellValue( Cell cell, Object value) // =============================================== // No.27 setCellValue Cell cellString = sheet_5.getRow(1).getCell(0); Cell cellNumber = sheet_5.getRow(1).getCell(1); Cell cellFloat = sheet_5.getRow(1).getCell(2); Cell cellDate = sheet_5.getRow(1).getCell(3); Cell cellBoolean = sheet_5.getRow(1).getCell(4); Cell cellNull = sheet_5.getRow(1).getCell(5); String stringValue = "aaa"; Number numberValue = new Double(10); Float floatValue = new Float(10f); Date dateValue = new Date(); Boolean booleanValue = Boolean.TRUE; PoiUtil.setCellValue(cellString, stringValue); PoiUtil.setCellValue(cellNumber, numberValue); PoiUtil.setCellValue(cellFloat, floatValue); PoiUtil.setCellValue(cellDate, dateValue); PoiUtil.setCellValue(cellBoolean, booleanValue); PoiUtil.setCellValue(cellNull, null); assertEquals(stringValue, cellString.getStringCellValue()); assertEquals(numberValue, cellNumber.getNumericCellValue()); assertEquals(new Double(String.valueOf(floatValue)), (Double) cellFloat.getNumericCellValue()); assertEquals(dateValue, cellDate.getDateCellValue()); assertEquals(booleanValue, cellBoolean.getBooleanCellValue()); assertNull(PoiUtil.getCellValue(cellNull)); // No.28 ?null try { PoiUtil.setCellValue(null, stringValue); fail(); } catch (NullPointerException ex) { // ?null???? } // =============================================== // getLastColNum( Sheet sheet) // =============================================== // No.29 int lastColNum1 = PoiUtil.getLastColNum(sheet_6); assertEquals(-1, lastColNum1); // No.30 ? int lastColNum2 = PoiUtil.getLastColNum(sheet_7); assertEquals(10, lastColNum2); }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * ?????//from ww w .j a v a2 s .co m * * @param cell * @return ??? */ private static String getCellValue(Cell cell) { String value = null; if (cell != null) { switch (cell.getCellTypeEnum()) { case BLANK: value = cell.getStringCellValue(); break; case BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); break; case ERROR: value = String.valueOf(cell.getErrorCellValue()); break; case NUMERIC: value = String.valueOf(cell.getNumericCellValue()); break; case STRING: value = cell.getStringCellValue(); break; case FORMULA: value = cell.getCellFormula(); default: value = ""; } } return value; }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java
License:Open Source License
public Object getCellData(Cell cell) { Object cellData = null;/*from www .j av a 2 s . c om*/ if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cellData = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: cellData = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellData = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: cellData = cell.getStringCellValue(); break; default: break; } } return cellData; }
From source file:org.cgiar.ccafs.marlo.utils.ReadExcelFile.java
License:Open Source License
public Object getCellData(Cell cell) { Object cellData = null;// w w w .j a va 2 s. c o m if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cellData = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: cellData = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellData = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: cellData = cell.getStringCellValue(); break; default: break; } } else { cellData = ""; } return cellData; }
From source file:org.databene.formats.xls.XLSUtil.java
License:Open Source License
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//from w w w . ja va 2 s .c o m return mapNumberType(cell.getNumericCellValue()); 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 = createFormulaEvaluator(cell); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case HSSFCell.CELL_TYPE_STRING: return convertString(cellValue, emptyMarker, stringPreprocessor); case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) return HSSFDateUtil.getJavaDate(cellValue.getNumberValue()); else return mapNumberType(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()); } }
From source file:org.datanucleus.store.excel.ExcelUtils.java
License:Open Source License
/** * Convenience method to check if a cell value matches the provided value and type. * @param cell The cell/*from w ww .j a v a 2s. co m*/ * @param fieldType The type to compare it with * @param fieldValue The value of the type to compare it with (can be null) * @return Whether the cell matches */ protected static boolean cellMatches(Cell cell, Class fieldType, Object fieldValue) { if (cell == null) { return false; } if (String.class.isAssignableFrom(fieldType) && cell.getRichStringCellValue().getString().equals(fieldValue)) { return true; } else if ((fieldType == int.class || fieldType == Integer.class) && ((Integer) fieldValue).intValue() == (int) cell.getNumericCellValue()) { return true; } else if ((fieldType == long.class || fieldType == Long.class) && ((Long) fieldValue).longValue() == (long) cell.getNumericCellValue()) { return true; } else if ((fieldType == short.class || fieldType == Short.class) && ((Short) fieldValue).shortValue() == (short) cell.getNumericCellValue()) { return true; } else if ((fieldType == float.class || fieldType == Float.class) && ((Float) fieldValue).floatValue() == (float) cell.getNumericCellValue()) { return true; } else if ((fieldType == double.class || fieldType == Double.class) && ((Double) fieldValue).doubleValue() == cell.getNumericCellValue()) { return true; } else if ((fieldType == boolean.class || fieldType == Boolean.class) && ((Boolean) fieldValue).booleanValue() == cell.getBooleanCellValue()) { return true; } else if ((fieldType == byte.class || fieldType == Byte.class) && ((Byte) fieldValue).byteValue() == (byte) cell.getNumericCellValue()) { return true; } else if ((fieldType == char.class || fieldType == Character.class) && ((Character) fieldValue).charValue() == cell.getRichStringCellValue().getString().charAt(0)) { return true; } else if ((Date.class.isAssignableFrom(fieldType) && ((Date) fieldValue).getTime() == cell.getDateCellValue().getTime())) { return true; } return false; }
From source file:org.datanucleus.store.excel.fieldmanager.FetchFieldManager.java
License:Open Source License
public boolean fetchBooleanField(int fieldNumber) { Cell cell = sheet.getRow(rowNumber).getCell(getColumnMapping(fieldNumber).getColumn(0).getPosition()); if (cell == null) { return false; }/*from www . jav a 2 s . co m*/ return cell.getBooleanCellValue(); }
From source file:org.datanucleus.store.excel.fieldmanager.FetchFieldManager.java
License:Open Source License
protected Object fetchObjectFieldInternal(int fieldNumber, AbstractMemberMetaData mmd, ClassLoaderResolver clr, RelationType relationType) { MemberColumnMapping mapping = getColumnMapping(fieldNumber); if (relationType == RelationType.NONE) { Column col = mapping.getColumn(0); if (mapping.getTypeConverter() != null) { TypeConverter conv = mapping.getTypeConverter(); if (mapping.getNumberOfColumns() == 1) { Cell cell = sheet.getRow(rowNumber) .getCell(getColumnMapping(fieldNumber).getColumn(0).getPosition()); if (cell == null) { return null; }/* w ww .ja v a 2s . c om*/ Object value = null; Class datastoreType = TypeConverterHelper.getDatastoreTypeForTypeConverter(conv, mmd.getType()); if (datastoreType == String.class) { value = conv.toMemberType(cell.getRichStringCellValue().getString()); } else if (Number.class.isAssignableFrom(datastoreType)) { value = conv.toMemberType(cell.getNumericCellValue()); } else if (Boolean.class.isAssignableFrom(datastoreType)) { value = conv.toMemberType(cell.getBooleanCellValue()); } else if (Date.class.isAssignableFrom(datastoreType)) { value = conv.toMemberType(cell.getDateCellValue()); } else { NucleusLogger.DATASTORE_PERSIST.warn("TypeConverter for member " + mmd.getFullFieldName() + " converts to " + datastoreType.getName() + " - not yet supported"); } if (op != null) { return SCOUtils.wrapSCOField(op, fieldNumber, value, true); } return value; } // Member stored in multiple columns and convertable using TypeConverter boolean isNull = true; Object valuesArr = null; Class[] colTypes = ((MultiColumnConverter) conv).getDatastoreColumnTypes(); if (colTypes[0] == int.class) { valuesArr = new int[mapping.getNumberOfColumns()]; } else if (colTypes[0] == long.class) { valuesArr = new long[mapping.getNumberOfColumns()]; } else if (colTypes[0] == double.class) { valuesArr = new double[mapping.getNumberOfColumns()]; } else if (colTypes[0] == float.class) { valuesArr = new double[mapping.getNumberOfColumns()]; } else if (colTypes[0] == String.class) { valuesArr = new String[mapping.getNumberOfColumns()]; } // TODO Support other types else { valuesArr = new Object[mapping.getNumberOfColumns()]; } for (int i = 0; i < mapping.getNumberOfColumns(); i++) { Cell cell = sheet.getRow(rowNumber).getCell(mapping.getColumn(i).getPosition()); if (cell == null) { Array.set(valuesArr, i, null); } else { isNull = false; if (colTypes[i] == int.class) { Object cellValue = getValueFromCellOfType(cell, Integer.class, mapping.getColumn(i).getJdbcType()); Array.set(valuesArr, i, ((Integer) cellValue).intValue()); } else if (colTypes[i] == long.class) { Object cellValue = getValueFromCellOfType(cell, Long.class, mapping.getColumn(i).getJdbcType()); Array.set(valuesArr, i, ((Long) cellValue).longValue()); } else { Object cellValue = getValueFromCellOfType(cell, colTypes[i], mapping.getColumn(i).getJdbcType()); Array.set(valuesArr, i, cellValue); } } } if (isNull) { return null; } Object memberValue = conv.toMemberType(valuesArr); if (op != null && memberValue != null) { memberValue = SCOUtils.wrapSCOField(op, fieldNumber, memberValue, true); } return memberValue; } Cell cell = sheet.getRow(rowNumber).getCell(mapping.getColumn(0).getPosition()); if (cell == null) { return null; } Object value = getValueFromCellOfType(cell, mmd.getType(), col.getJdbcType()); // Wrap the field if it is SCO if (op != null) { return SCOUtils.wrapSCOField(op, fieldNumber, value, true); } return value; } else if (RelationType.isRelationSingleValued(relationType)) { // Persistable object stored as String reference of the identity Cell cell = sheet.getRow(rowNumber).getCell(mapping.getColumn(0).getPosition()); if (cell == null) { return null; } String idStr = cell.getRichStringCellValue().getString(); if (idStr == null) { return null; } if (idStr.startsWith("[") && idStr.endsWith("]")) { idStr = idStr.substring(1, idStr.length() - 1); Object obj = null; AbstractClassMetaData memberCmd = ec.getMetaDataManager().getMetaDataForClass(mmd.getType(), clr); try { if (memberCmd.usesSingleFieldIdentityClass() && idStr.indexOf(':') > 0) { // Uses persistent identity obj = IdentityUtils.getObjectFromPersistableIdentity(idStr, memberCmd, ec); } else { // Uses legacy identity obj = IdentityUtils.getObjectFromIdString(idStr, memberCmd, ec, true); } } catch (NucleusObjectNotFoundException nfe) { NucleusLogger.GENERAL.warn("Object=" + op + " field=" + mmd.getFullFieldName() + " has id=" + idStr + " but could not instantiate object with that identity"); return null; } return obj; } return null; } else if (RelationType.isRelationMultiValued(relationType)) { // Collection/Map/Array Cell cell = sheet.getRow(rowNumber).getCell(mapping.getColumn(0).getPosition()); if (cell == null) { return null; } String cellStr = cell.getRichStringCellValue().getString(); if (cellStr == null) { return null; } if (cellStr.startsWith("[") && cellStr.endsWith("]")) { cellStr = cellStr.substring(1, cellStr.length() - 1); String[] components = MetaDataUtils.getInstance().getValuesForCommaSeparatedAttribute(cellStr); if (Collection.class.isAssignableFrom(mmd.getType())) { Collection<Object> coll; try { Class instanceType = SCOUtils.getContainerInstanceType(mmd.getType(), mmd.getOrderMetaData() != null); coll = (Collection<Object>) instanceType.newInstance(); } catch (Exception e) { throw new NucleusDataStoreException(e.getMessage(), e); } boolean changeDetected = false; if (components != null) { AbstractClassMetaData elementCmd = mmd.getCollection() .getElementClassMetaData(ec.getClassLoaderResolver(), ec.getMetaDataManager()); for (int i = 0; i < components.length; i++) { // TODO handle Collection<interface> Object element = null; try { if (elementCmd.usesSingleFieldIdentityClass() && components[i].indexOf(':') > 0) { // Uses persistent identity element = IdentityUtils.getObjectFromPersistableIdentity(components[i], elementCmd, ec); } else { // Uses legacy identity element = IdentityUtils.getObjectFromIdString(components[i], elementCmd, ec, true); } coll.add(element); } catch (NucleusObjectNotFoundException nfe) { // Object no longer exists. Deleted by user? so ignore changeDetected = true; } } } if (op != null) { coll = (Collection) SCOUtils.wrapSCOField(op, fieldNumber, coll, true); if (changeDetected) { op.makeDirty(mmd.getAbsoluteFieldNumber()); } } return coll; } else if (Map.class.isAssignableFrom(mmd.getType())) { AbstractClassMetaData keyCmd = mmd.getMap().getKeyClassMetaData(clr, ec.getMetaDataManager()); AbstractClassMetaData valCmd = mmd.getMap().getValueClassMetaData(clr, ec.getMetaDataManager()); Map map; try { Class instanceType = SCOUtils.getContainerInstanceType(mmd.getType(), false); map = (Map) instanceType.newInstance(); } catch (Exception e) { throw new NucleusDataStoreException(e.getMessage(), e); } boolean changeDetected = false; if (components != null) { for (int i = 0; i < components.length; i++) { String keyCmpt = components[i]; i++; String valCmpt = components[i]; // Strip square brackets from entry bounds String keyStr = keyCmpt.substring(1, keyCmpt.length() - 1); String valStr = valCmpt.substring(1, valCmpt.length() - 1); boolean keySet = true; boolean valSet = true; Object key = null; if (keyCmd != null) { try { // TODO handle Map<interface, ?> if (keyCmd.usesSingleFieldIdentityClass() && keyStr.indexOf(':') > 0) { // Uses persistent identity key = IdentityUtils.getObjectFromPersistableIdentity(keyStr, keyCmd, ec); } else { // Uses legacy identity key = IdentityUtils.getObjectFromIdString(keyStr, keyCmd, ec, true); } } catch (NucleusObjectNotFoundException nfe) { // Object no longer exists. Deleted by user? so ignore changeDetected = true; keySet = false; } } else { String keyTypeName = mmd.getMap().getKeyType(); Class keyType = ec.getClassLoaderResolver().classForName(keyTypeName); if (Enum.class.isAssignableFrom(keyType)) { key = Enum.valueOf(keyType, keyStr); } else if (keyType == String.class) { key = keyStr; } else { // TODO Support other map key types throw new NucleusException( "Don't currently support retrieval of Maps with keys of type " + keyTypeName + " (field=" + mmd.getFullFieldName() + ")"); } } Object val = null; if (valCmd != null) { try { // TODO handle Collection<?, interface> if (valCmd.usesSingleFieldIdentityClass() && valStr.indexOf(':') > 0) { // Uses persistent identity val = IdentityUtils.getObjectFromPersistableIdentity(valStr, valCmd, ec); } else { // Uses legacy identity val = IdentityUtils.getObjectFromIdString(valStr, valCmd, ec, true); } } catch (NucleusObjectNotFoundException nfe) { // Object no longer exists. Deleted by user? so ignore changeDetected = true; valSet = false; } } else { String valTypeName = mmd.getMap().getValueType(); Class valType = ec.getClassLoaderResolver().classForName(valTypeName); if (Enum.class.isAssignableFrom(valType)) { val = Enum.valueOf(valType, valStr); } else if (valType == String.class) { val = valStr; } else { // TODO Support other map value types throw new NucleusException( "Don't currently support retrieval of Maps with values of type " + valTypeName + " (field=" + mmd.getFullFieldName() + ")"); } } if (keySet && valSet) { map.put(key, val); } } } if (op != null) { map = (Map) SCOUtils.wrapSCOField(op, fieldNumber, map, true); if (changeDetected) { op.makeDirty(mmd.getAbsoluteFieldNumber()); } } return map; } else if (mmd.getType().isArray()) { Object array = null; boolean changeDetected = false; int pos = 0; if (components != null) { AbstractClassMetaData elementCmd = mmd.getCollection() .getElementClassMetaData(ec.getClassLoaderResolver(), ec.getMetaDataManager()); array = Array.newInstance(mmd.getType().getComponentType(), components.length); for (int i = 0; i < components.length; i++) { // TODO handle interface[] Object element = null; try { if (elementCmd.usesSingleFieldIdentityClass() && components[i].indexOf(':') > 0) { // Uses persistent identity element = IdentityUtils.getObjectFromPersistableIdentity(components[i], elementCmd, ec); } else { // Uses legacy identity element = IdentityUtils.getObjectFromIdString(components[i], elementCmd, ec, true); } Array.set(array, pos++, element); } catch (NucleusObjectNotFoundException nfe) { // Object no longer exists. Deleted by user? so ignore changeDetected = true; } } } else { array = Array.newInstance(mmd.getType().getComponentType(), 0); } if (changeDetected) { if (pos < Array.getLength(array)) { // Some elements not found, so resize the array Object arrayOld = array; array = Array.newInstance(mmd.getType().getComponentType(), pos); for (int j = 0; j < pos; j++) { Array.set(array, j, Array.get(arrayOld, j)); } } if (op != null) { array = SCOUtils.wrapSCOField(op, fieldNumber, array, true); if (changeDetected) { op.makeDirty(mmd.getAbsoluteFieldNumber()); } } } return array; } } } throw new NucleusException("Dont currently support retrieval of type " + mmd.getTypeName()); }