List of usage examples for org.apache.poi.xssf.usermodel XSSFCreationHelper createHyperlink
@Override
public XSSFHyperlink createHyperlink(HyperlinkType type)
From source file:gov.nih.nci.evs.app.neopl.CSVtoExcel.java
License:Open Source License
public void runXSSF(String inputfile) { int size = checkSpecialCharacters(inputfile); int n = inputfile.lastIndexOf("."); //String outputfile = inputfile.substring(0, n) + ".xlsx"; String outputfile = getOutputFile(inputfile, ".xlsx"); try {/*from www .ja v a 2s . c o m*/ XSSFWorkbook wb = new XSSFWorkbook(); XSSFCreationHelper helper = null; XSSFCellStyle cellStyle = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); XSSFCellStyle linkCellStyle = wb.createCellStyle(); XSSFFont linkfont = wb.createFont(); //XSSFColor color = new XSSFColor(Color.LIGHT_BLUE); XSSFColor color = new XSSFColor(Color.BLUE); linkfont.setColor(color); //linkfont.setColor(XSSFColor.LIGHT_BLUE.index); linkCellStyle.setFont(linkfont); CSVReader reader = new CSVReader(new FileReader(inputfile));//CSV file String[] line; int r = 0; Cell cell = null; XSSFHyperlink url_link = null; XSSFSheet sheet = null; int page_num = 1; Row row = null; int lcv = 0; int row_count = 0; try { while ((line = reader.readNext()) != null) { if (lcv % PAGE_SIZE == 0) { r = 0; String sheetLabel = SHEET_LABEL; if (size > PAGE_SIZE) { sheetLabel = sheetLabel + " (Page " + page_num + ")"; } //System.out.println("Creating " + sheetLabel); sheet = wb.createSheet(sheetLabel); helper = sheet.getWorkbook().getCreationHelper(); url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); row = sheet.createRow((short) r); for (int i = 0; i < HEADINGS.length; i++) { String heading = HEADINGS[i]; cell = row.createCell(i); cell.setCellValue(heading); cell.setCellStyle(cellStyle); } page_num++; } else { String s4 = (String) line[4]; s4 = s4.trim(); r++; row = sheet.createRow((short) r); row_count++; cell = row.createCell(0); String ncit_code = line[0]; cell.setCellValue(ncit_code); if (NCIT_LINK) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); url_link.setAddress(getNCItHyperlink(ncit_code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } cell = row.createCell(1); String name = line[1]; cell.setCellValue(line[1]); cell = row.createCell(2); cell.setCellValue(line[2]); if (NCIM_LINK) { String s2 = line[2]; s2 = s2.trim(); if (s2.length() > 0) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); url_link.setAddress(getNCImHyperlink(s2)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } cell = row.createCell(3); String ncim_name = line[3]; cell.setCellValue(line[3]); cell = row.createCell(4); cell.setCellValue(line[4]); cell = row.createCell(5); String atom_name = (String) line[5]; cell.setCellValue(line[5]); cell = row.createCell(6); cell.setCellValue(line[6]); if (SOURCE_LINK) { if (s4.length() > 0) { String s6 = (String) line[6]; if (localNameMap.containsKey(s4)) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); s4 = (String) localNameMap.get(s4); url_link.setAddress(getSourceHyperlink(s4, s6)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } } cell = row.createCell(7); cell.setCellValue(line[7]); } lcv++; } } catch (Exception ex) { ex.printStackTrace(); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(outputfile); wb.write(fileOut); fileOut.close(); System.out.println("Output file " + outputfile + " generated."); System.out.println("row_count: " + row_count); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:org.rapidpm.data.table.export.Table2XLSX.java
License:Apache License
@Override public ByteArrayOutputStream export(final Table table) { final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet xssfSheet = workbook.createSheet(table.getTableName()); final Collection<ColumnInformation> infoList = table.getColumnInfoList(); final XSSFRow xssfHeaderRow = xssfSheet.createRow(0); for (final ColumnInformation information : infoList) { if (information.isExportable()) { final XSSFCell xssfCell = xssfHeaderRow.createCell(information.getSpaltenNr()); xssfCell.setCellValue(information.getSpaltenName()); xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); } else {/*from w w w .ja v a 2 s.co m*/ if (logger.isDebugEnabled()) { logger.debug("ColInfo not exportable " + information.getSpaltenName()); } } } final List<Row> tableRowList = table.getRowList(); for (final Row row : tableRowList) { final XSSFRow xssfRow = xssfSheet.createRow(row.getRowNr() + 1); final List<Cell> cellList = row.getCells(); for (final Cell cell : cellList) { if (cell.getColInfo().isExportable()) { final XSSFCell xssfCell = xssfRow.createCell(cell.getColInfo().getSpaltenNr()); final CellTypeEnum cellType = cell.getCellType(); if (cellType.equals(CellTypeEnum.RawData)) { xssfCell.setCellValue(cell.getFormattedValue()); xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING); //JIRA MOD-32 CellType in Abhngigkeit der ValueClass z.B. Number } else if (cellType.equals(CellTypeEnum.RawLink)) { final XSSFCreationHelper helper = workbook.getCreationHelper(); final XSSFHyperlink xssfHyperlink = helper.createHyperlink(Hyperlink.LINK_URL); xssfHyperlink.setAddress(cell.getFormattedValue()); xssfHyperlink.setLabel(cell.getLabel()); xssfCell.setCellValue(cell.getLabel()); xssfCell.setHyperlink(xssfHyperlink); final CellStyle hlink_style = createHyperLinkStyle(workbook); xssfCell.setCellStyle(hlink_style); } else { } } else { if (logger.isDebugEnabled()) { logger.debug("Cell not exportable "); } } } } final ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); try { workbook.write(outputStream); } catch (IOException e) { logger.error(e); } return outputStream; }