List of usage examples for org.apache.poi.common.usermodel HyperlinkType URL
HyperlinkType URL
To view the source code for org.apache.poi.common.usermodel HyperlinkType URL.
Click Source Link
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void setCellHyperLink(Cell cell, String url) { if (url.contains("://")) { Hyperlink link = creationHelper.createHyperlink(HyperlinkType.URL); link.setAddress(url);/*from w ww. j a v a 2 s. c o m*/ cell.setHyperlink(link); } else if (url.startsWith("mailto:")) { Hyperlink link = creationHelper.createHyperlink(HyperlinkType.EMAIL); link.setAddress(url); cell.setHyperlink(link); } else { Hyperlink link = creationHelper.createHyperlink(HyperlinkType.FILE); link.setAddress(url); cell.setHyperlink(link); } if (cell.getCellTypeEnum() == CellType.BLANK) { cell.setCellValue(url); } }
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 w w . j a va2s. 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.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
void writeField(Object v, ValueMetaInterface vMeta, ExcelWriterStepField excelField, Row xlsRow, int posX, Object[] row, int fieldNr, boolean isTitle) throws KettleException { try {//from ww w .j a v a 2s.co m boolean cellExisted = true; // get the cell Cell cell = xlsRow.getCell(posX); if (cell == null) { cellExisted = false; cell = xlsRow.createCell(posX); } // if cell existed and existing cell's styles should not be changed, don't if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) { // if the style of this field is cached, reuse it if (!isTitle && data.getCachedStyle(fieldNr) != null) { cell.setCellStyle(data.getCachedStyle(fieldNr)); } else { // apply style if requested if (excelField != null) { // determine correct cell for title or data rows String styleRef = null; if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) { styleRef = excelField.getStyleCell(); } else if (isTitle && !Utils.isEmpty(excelField.getTitleStyleCell())) { styleRef = excelField.getTitleStyleCell(); } if (styleRef != null) { Cell styleCell = getCellFromReference(styleRef); if (styleCell != null && cell != styleCell) { cell.setCellStyle(styleCell.getCellStyle()); } } } // set cell format as specified, specific format overrides cell specification if (!isTitle && excelField != null && !Utils.isEmpty(excelField.getFormat()) && !excelField.getFormat().startsWith("Image")) { setDataFormat(excelField.getFormat(), cell); } // cache it for later runs if (!isTitle) { data.cacheStyle(fieldNr, cell.getCellStyle()); } } } // create link on cell if requested if (!isTitle && excelField != null && data.linkfieldnrs[fieldNr] >= 0) { String link = data.inputRowMeta.getValueMeta(data.linkfieldnrs[fieldNr]) .getString(row[data.linkfieldnrs[fieldNr]]); if (!Utils.isEmpty(link)) { CreationHelper ch = data.wb.getCreationHelper(); // set the link on the cell depending on link type Hyperlink hyperLink = null; if (link.startsWith("http:") || link.startsWith("https:") || link.startsWith("ftp:")) { hyperLink = ch.createHyperlink(HyperlinkType.URL); hyperLink.setLabel("URL Link"); } else if (link.startsWith("mailto:")) { hyperLink = ch.createHyperlink(HyperlinkType.EMAIL); hyperLink.setLabel("Email Link"); } else if (link.startsWith("'")) { hyperLink = ch.createHyperlink(HyperlinkType.DOCUMENT); hyperLink.setLabel("Link within this document"); } else { hyperLink = ch.createHyperlink(HyperlinkType.FILE); hyperLink.setLabel("Link to a file"); } hyperLink.setAddress(link); cell.setHyperlink(hyperLink); // if cell existed and existing cell's styles should not be changed, don't if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) { if (data.getCachedLinkStyle(fieldNr) != null) { cell.setCellStyle(data.getCachedLinkStyle(fieldNr)); } else { // CellStyle style = cell.getCellStyle(); Font origFont = data.wb.getFontAt(cell.getCellStyle().getFontIndex()); Font hlink_font = data.wb.createFont(); // reporduce original font characteristics hlink_font.setBold(origFont.getBold()); hlink_font.setCharSet(origFont.getCharSet()); hlink_font.setFontHeight(origFont.getFontHeight()); hlink_font.setFontName(origFont.getFontName()); hlink_font.setItalic(origFont.getItalic()); hlink_font.setStrikeout(origFont.getStrikeout()); hlink_font.setTypeOffset(origFont.getTypeOffset()); // make it blue and underlined hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); CellStyle style = cell.getCellStyle(); style.setFont(hlink_font); cell.setCellStyle(style); data.cacheLinkStyle(fieldNr, cell.getCellStyle()); } } } } // create comment on cell if requrested if (!isTitle && excelField != null && data.commentfieldnrs[fieldNr] >= 0 && data.wb instanceof XSSFWorkbook) { String comment = data.inputRowMeta.getValueMeta(data.commentfieldnrs[fieldNr]) .getString(row[data.commentfieldnrs[fieldNr]]); if (!Utils.isEmpty(comment)) { String author = data.commentauthorfieldnrs[fieldNr] >= 0 ? data.inputRowMeta.getValueMeta(data.commentauthorfieldnrs[fieldNr]).getString( row[data.commentauthorfieldnrs[fieldNr]]) : "Kettle PDI"; cell.setCellComment(createCellComment(author, comment)); } } // cell is getting a formula value or static content if (!isTitle && excelField != null && excelField.isFormula()) { // formula case cell.setCellFormula(vMeta.getString(v)); } else { // static content case switch (vMeta.getType()) { case ValueMetaInterface.TYPE_DATE: if (v != null && vMeta.getDate(v) != null) { cell.setCellValue(vMeta.getDate(v)); } break; case ValueMetaInterface.TYPE_BOOLEAN: if (v != null) { cell.setCellValue(vMeta.getBoolean(v)); } break; case ValueMetaInterface.TYPE_STRING: case ValueMetaInterface.TYPE_BINARY: if (v != null) { cell.setCellValue(vMeta.getString(v)); } break; case ValueMetaInterface.TYPE_BIGNUMBER: case ValueMetaInterface.TYPE_NUMBER: case ValueMetaInterface.TYPE_INTEGER: if (v != null) { cell.setCellValue(vMeta.getNumber(v)); } break; default: break; } } } catch (Exception e) { logError("Error writing field (" + data.posX + "," + data.posY + ") : " + e.toString()); logError(Const.getStackTracker(e)); throw new KettleException(e); } }
From source file:utilities.XLSReportsManager.java
License:Open Source License
private void processDataListForXLS(ArrayList<ArrayList<KeyValue>> dArray, Sheet sheet, Sheet settingsSheet, Map<String, CellStyle> styles, ArrayList<Column> cols, String tz, ArrayList<KeyValue> settings) throws IOException { CreationHelper createHelper = wb.getCreationHelper(); for (int index = 0; index < dArray.size(); index++) { Row row = sheet.createRow(rowNumber++); ArrayList<KeyValue> record = dArray.get(index); for (Column col : cols) { Cell cell = row.createCell(col.colIndex); String value = "error"; if (col.dataIndex >= 0) { value = record.get(col.dataIndex).v; }// ww w . j av a 2 s . c o m cell.setCellStyle(styles.get("default")); if (value != null && (value.startsWith("https://") || value.startsWith("http://"))) { cell.setCellStyle(styles.get("link")); Hyperlink url = createHelper.createHyperlink(HyperlinkType.URL); url.setAddress(value); cell.setHyperlink(url); /* if(isXLSX) { XSSFHyperlink url = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.URL); url.setAddress(value); cell.setHyperlink(url); } else { HSSFHyperlink url = new HSSFHyperlink(HyperlinkType.URL); url.setAddress(value); cell.setHyperlink(url); } */ } boolean cellWritten = false; if (col.type.equals("datetime")) { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { java.util.Date date = dateFormat.parse(value); cell.setCellStyle(styles.get("datetime")); cell.setCellValue(date); cellWritten = true; } catch (Exception e) { // Ignore } } else if (col.type.equals("date")) { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); try { java.util.Date date = dateFormat.parse(value); cell.setCellStyle(styles.get("date")); cell.setCellValue(date); cellWritten = true; } catch (Exception e) { // Ignore } } if (!cellWritten) { // Try to write as number by default try { double vDouble = Double.parseDouble(value); cell.setCellStyle(styles.get("default")); cell.setCellValue(vDouble); cellWritten = true; } catch (Exception e) { // Ignore } } if (!cellWritten) { cell.setCellStyle(styles.get("default")); cell.setCellValue(value); } } } // Populate settings sheet int settingsRowIdx = 0; Row settingsRow = settingsSheet.createRow(settingsRowIdx++); Cell k = settingsRow.createCell(0); Cell v = settingsRow.createCell(1); k.setCellStyle(styles.get("header")); k.setCellValue("Time Zone:"); v.setCellValue(tz); // Show filter settings settingsRowIdx++; settingsRow = settingsSheet.createRow(settingsRowIdx++); Cell f = settingsRow.createCell(0); f.setCellStyle(styles.get("header2")); f.setCellValue("Filters:"); if (settings != null) { for (KeyValue kv : settings) { settingsRow = settingsSheet.createRow(settingsRowIdx++); k = settingsRow.createCell(1); v = settingsRow.createCell(2); k.setCellStyle(styles.get("header")); k.setCellValue(kv.k); v.setCellValue(kv.v); } } }