List of usage examples for org.apache.poi.ss.usermodel Cell getHyperlink
Hyperlink getHyperlink();
From source file:de.jlo.talendcomp.excel.SpreadsheetReferencedCellInput.java
License:Apache License
private String getStringCellValue(Cell cell) { String value = null;/*from w ww .j av a2 s . c o m*/ if (cell != null) { CellType cellType = cell.getCellTypeEnum(); if (cellType == CellType.FORMULA) { value = getDataFormatter().formatCellValue(cell, getFormulaEvaluator()); } else if (cellType == CellType.STRING) { if (returnURLInsteadOfName) { Hyperlink link = cell.getHyperlink(); if (link != null) { if (concatenateLabelUrl) { String url = link.getAddress(); if (url == null) { url = ""; } String label = link.getLabel(); if (label == null) { label = ""; } value = label + "|" + url; } else { value = link.getAddress(); } } else { value = cell.getStringCellValue(); } } else { value = cell.getStringCellValue(); } } else if (cellType == CellType.NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); value = defaultDateFormat.format(d); } else { value = numberFormat.format(cell.getNumericCellValue()); } } else if (cellType == CellType.BOOLEAN) { value = cell.getBooleanCellValue() ? "true" : "false"; } else if (cellType == CellType.BLANK) { value = null; } } return value; }
From source file:invoiceapplication.CopyRowOriginal.java
public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) { // Get the source / new row Row newRow = worksheet.getRow(destRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); // If the row exists in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true); } else {/*from w w w . j av a 2 s . com*/ newRow = worksheet.createRow(destRowNum); } copyAnyMergedRegions(worksheet, sourceRow, newRow); // Loops through source column to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { //Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // if the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Use old cell style newCell.setCellStyle(oldCell.getCellStyle()); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellValue(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; } } }
From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java
License:Apache License
private String cellStringValue(Cell cell) { if (cell == null) { return null; }// ww w . ja va2s . com String text = dataFormatter.formatCellValue(cell); if (text != null && !text.startsWith("HYPERLINK(")) { text = text.trim(); if (text.length() == 0) { text = null; } } else { Hyperlink link = cell.getHyperlink(); if (link != null) { text = link.getLabel(); if (text == null) { text = link.getAddress(); } if (text != null) { text = text.trim(); } } } return text; }
From source file:jp.ryoyamamoto.poiutils.Cells.java
License:Apache License
private static void copyHyperlink(Cell source, Cell target) { if (source.getHyperlink() == null) { removeHyperlink(target);/*w ww . j a va 2 s . c om*/ } else { target.setHyperlink(source.getHyperlink()); } }
From source file:jp.ryoyamamoto.poiutils.Cells.java
License:Apache License
private static void removeHyperlink(Cell cell) { if (cell == null || cell.getHyperlink() == null) { return;/*from w w w . j a v a 2s . c o m*/ } Sheet sheet = getSheet(cell); List<Hyperlink> hyperlinks = Sheets.getHyperlinks(sheet); hyperlinks.remove(cell.getHyperlink()); }
From source file:net.sf.ahtutils.report.util.DataUtil.java
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()); }//w w w.j a v a2 s . com 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; }
From source file:org.bbreak.excella.core.util.PoiUtilTest.java
License:Open Source License
@Test public void testPoiUtil3() throws IOException, ParseException { Workbook workbook = getWorkbook();//from w w w. j a v a2s. com 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.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings({ "unchecked", "rawtypes" })
private static <T> T readRow(ExcelReadContext<T> context, Row row,
Map<Integer, Map<String, ExcelReadFieldMappingAttribute>> fieldMapping, Class<T> targetClass,
ExcelReadRowProcessor<T> processor, boolean isTrimSpace) {
try {//from w ww. java 2s . c o m
context.setCurRowData(targetClass.newInstance());
} catch (Exception e1) {
throw new RuntimeException(e1);
}
int curRowIndex = context.getCurRowIndex();
for (Entry<Integer, Map<String, ExcelReadFieldMappingAttribute>> fieldMappingEntry : fieldMapping
.entrySet()) {
int curColIndex = fieldMappingEntry.getKey();// excel index;
// proc cell
context.setCurColIndex(curColIndex);
Cell cell = null;
if (row != null) {
cell = row.getCell(curColIndex);
}
context.setCurCell(cell);
Map<String, ExcelReadFieldMappingAttribute> fields = fieldMappingEntry.getValue();
for (Map.Entry<String, ExcelReadFieldMappingAttribute> fieldEntry : fields.entrySet()) {
String fieldName = fieldEntry.getKey();
ExcelReadFieldMappingAttribute attribute = fieldEntry.getValue();
// proccess link
String linkField = attribute.getLinkField();
if (linkField != null) {
String address = null;
if (cell != null) {
Hyperlink hyperlink = cell.getHyperlink();
if (hyperlink != null) {
address = hyperlink.getAddress();
}
}
if (isTrimSpace && address != null) {
address = address.trim();
if (address.length() == 0) {
address = null;
}
}
if (Map.class.isAssignableFrom(targetClass)) {// map
((Map) context.getCurRowData()).put(linkField, address);
} else {// java bean
try {
setProperty(context.getCurRowData(), linkField, address);
} catch (Exception e1) {
ExcelReadException e = new ExcelReadException(e1);
e.setRowIndex(curRowIndex);
e.setColIndex(curColIndex);
e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION);
throw e;
}
}
}
Object value = _readCell(cell);
if (value != null && value instanceof String && isTrimSpace) {
value = ((String) value).trim();
if (((String) value).length() == 0) {
value = null;
}
}
if (value == null && attribute.isRequired()) {
ExcelReadException e = new ExcelReadException("Cell value is null");
e.setRowIndex(curRowIndex);
e.setColIndex(curColIndex);
e.setCode(ExcelReadException.CODE_OF_CELL_VALUE_REQUIRED);
throw e;
}
//
try {
if (Map.class.isAssignableFrom(targetClass)) {// map
value = procValueConvert(context, row, cell, attribute, fieldName, value);
((Map) context.getCurRowData()).put(fieldName, value);
} else {// java bean
value = procValueConvert(context, row, cell, attribute, fieldName, value);
setProperty(context.getCurRowData(), fieldName, value);
}
} catch (Exception e1) {
ExcelReadException e = new ExcelReadException(e1);
e.setRowIndex(curRowIndex);
e.setColIndex(curColIndex);
e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION);
throw e;
}
}
}
return context.getCurRowData();
}
From source file:org.joeffice.spreadsheet.cell.CellUtils.java
License:Apache License
public static void copyCell(Cell oldCell, Cell newCell) { newCell.setCellStyle(oldCell.getCellStyle()); if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); }//w w w.ja v a2 s . c o m if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } }
From source file:org.projectforge.excel.ExportSheet.java
License:Open Source License
private static Row copyRow(Sheet worksheet, int rowNum) { Row sourceRow = worksheet.getRow(rowNum); //Save the text of any formula before they are altered by row shifting String[] formulasArray = new String[sourceRow.getLastCellNum()]; for (int i = 0; i < sourceRow.getLastCellNum(); i++) { if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) formulasArray[i] = sourceRow.getCell(i).getCellFormula(); }//from w ww . j ava2 s .com worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1); Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1 // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell; // If the old cell is null jump to next cell if (oldCell == null) { continue; } else { newCell = newRow.createCell(i); } // Copy style from old cell and apply to new cell CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(formulasArray[i]); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; default: break; } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } return newRow; }