List of usage examples for org.apache.poi.ss.usermodel CreationHelper createHyperlink
Hyperlink createHyperlink(HyperlinkType type);
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 {//w w w . ja v a2 s.c om 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:packtest.HyperlinkExample.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); //cell style for hyperlinks //by default hyperlinks are blue and underlined CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font);/*w w w .ja va 2s. co m*/ Cell cell; Sheet sheet = wb.createSheet("Hyperlinks"); //URL cell = sheet.createRow(0).createCell((short) 0); cell.setCellValue("URL Link"); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress("http://poi.apache.org/"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a file in the current directory cell = sheet.createRow(1).createCell((short) 0); cell.setCellValue("File Link"); link = createHelper.createHyperlink(Hyperlink.LINK_FILE); link.setAddress("link1.xls"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //e-mail link cell = sheet.createRow(2).createCell((short) 0); cell.setCellValue("Email Link"); link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a place in this workbook //create a target sheet and cell Sheet sheet2 = wb.createSheet("Target Sheet"); sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell"); cell = sheet.createRow(3).createCell((short) 0); cell.setCellValue("Worksheet Link"); Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link2.setAddress("'Target Sheet'!A1"); cell.setHyperlink(link2); cell.setCellStyle(hlink_style); FileOutputStream out = new FileOutputStream(Utils.getPath("hyperinks.xlsx")); wb.write(out); out.close(); }
From source file:uk.ac.ebi.generic.util.ExcelWorkBook.java
License:Apache License
public ExcelWorkBook(String[] titles, Object[][] tableData, String sheetTitle) throws Exception { this.wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet XSSFSheet sheet = wb.createSheet(sheetTitle); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);// w w w .j a v a2 s . c o m sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //header row XSSFRow headerRow = sheet.createRow(0); //headerRow.setHeightInPoints(40); XSSFCell headerCell; for (int j = 0; j < titles.length; j++) { headerCell = headerRow.createCell(j); headerCell.setCellValue(titles[j]); //headerCell.setCellStyle(styles.get("header")); } // data rows // Create a row and put some cells in it. Rows are 0 based. // Then set value for that created cell for (int k = 0; k < tableData.length; k++) { XSSFRow row = sheet.createRow(k + 1); // data starts from row 1 for (int l = 0; l < tableData[k].length; l++) { XSSFCell cell = row.createCell(l); String cellStr = null; try { cellStr = tableData[k][l].toString(); } catch (Exception e) { cellStr = ""; } //System.out.println("cell " + l + ": " + cellStr); // make hyperlink in cell if ((cellStr.startsWith("http://") || cellStr.startsWith("https://")) && !cellStr.contains("|")) { //need to encode URI for this version of ExcelWorkBook cellStr = URIUtil.encodePath(cellStr, "UTF-8"); cellStr = cellStr.replace("%3F", "?"); // so that url link would work //System.out.println("cellStr: " + cellStr); XSSFHyperlink url_link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); url_link.setAddress(cellStr); cell.setCellValue(cellStr); cell.setHyperlink(url_link); } else { cell.setCellValue(cellStr); } //System.out.println((String)tableData[k][l]); } } }
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; }/*from ww w. ja v a 2s.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); } } }
From source file:utilities.XLSResultsManager.java
License:Open Source License
private void closeRecord(ArrayList<CellItem> record, Sheet sheet, Map<String, CellStyle> styles, boolean embedImages) throws IOException { CreationHelper createHelper = wb.getCreationHelper(); Row row = sheet.createRow(rowIndex++); if (embedImages) { row.setHeight((short) 1000); }//from w w w. ja v a 2s . co m for (int i = 0; i < record.size(); i++) { CellItem ci = record.get(i); Cell cell = row.createCell(i); if (ci.v != null && (ci.v.startsWith("https://") || ci.v.startsWith("http://"))) { if (embedImages) { if (ci.v.endsWith(".jpg") || ci.v.endsWith(".png")) { int idx = ci.v.indexOf("attachments"); int idxName = ci.v.lastIndexOf('/'); if (idx > 0 && idxName > 0) { String fileName = ci.v.substring(idxName); String stem = basePath + "/" + ci.v.substring(idx, idxName); String imageName = stem + "/thumbs" + fileName + ".jpg"; try { InputStream inputStream = new FileInputStream(imageName); byte[] imageBytes = IOUtils.toByteArray(inputStream); int pictureureIdx = wb.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG); inputStream.close(); ClientAnchor anchor = createHelper.createClientAnchor(); anchor.setCol1(i); anchor.setRow1(rowIndex - 1); anchor.setCol2(i + 1); anchor.setRow2(rowIndex); anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE); //sheet.setColumnWidth(i, 20 * 256); Drawing drawing = sheet.createDrawingPatriarch(); Picture pict = drawing.createPicture(anchor, pictureureIdx); //pict.resize(); } catch (Exception e) { log.info("Error: Missing image file: " + imageName); } } } } cell.setCellStyle(styles.get("link")); if (isXLSX) { XSSFHyperlink url = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); url.setAddress(ci.v); cell.setHyperlink(url); } else { HSSFHyperlink url = new HSSFHyperlink(HSSFHyperlink.LINK_URL); url.setAddress(ci.v); cell.setHyperlink(url); } cell.setCellValue(ci.v); } else { /* * Write the value as double or string */ boolean cellWritten = false; if (ci.type == CellItem.DECIMAL || ci.type == CellItem.INTEGER && ci.v != null) { try { double vDouble = Double.parseDouble(ci.v); cell.setCellStyle(styles.get("default")); cell.setCellValue(vDouble); cellWritten = true; } catch (Exception e) { // Ignore } } else if (ci.type == CellItem.DATETIME) { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { java.util.Date date = dateFormat.parse(ci.v); cell.setCellStyle(styles.get("datetime")); cell.setCellValue(date); cellWritten = true; } catch (Exception e) { // Ignore } } else if (ci.type == CellItem.DATE) { DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); try { java.util.Date date = dateFormat.parse(ci.v); cell.setCellStyle(styles.get("date")); cell.setCellValue(date); cellWritten = true; } catch (Exception e) { // Ignore } } if (!cellWritten) { cell.setCellStyle(styles.get("default")); cell.setCellValue(ci.v); } } } }