List of usage examples for org.apache.poi.ss.usermodel CreationHelper createRichTextString
RichTextString createRichTextString(String text);
From source file:bloodbank.Simulation.java
/** * * @param args//from w w w .j a v a 2 s .c o m * @throws IOException */ public static void main(String[] args) throws IOException { //from 8am to 20pm Random rng = new Random(); Distribution plasmaInter = new DiscreteUniformDistribution(6, 6, rng); Distribution[] wholeInter = new Distribution[24]; Distribution[] procedures = new Distribution[10]; constructDistribution(wholeInter, procedures, rng); Simulation sim = new Simulation(plasmaInter, wholeInter, procedures); // Create the sheet Workbook wb = new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); int runs = 1; Row row = sheet.createRow((short) 0); Cell cell = row.createCell(0); cell.setCellValue(createHelper.createRichTextString("Total running time")); cell = row.createCell(1); cell.setCellValue(createHelper.createRichTextString("P pre-donation sojourn time")); cell = row.createCell(2); cell.setCellValue(createHelper.createRichTextString("W pre-donation sojourn time")); cell = row.createCell(3); cell.setCellValue(createHelper.createRichTextString("P total sojourn time")); cell = row.createCell(4); cell.setCellValue(createHelper.createRichTextString("W total sojourn time")); cell = row.createCell(5); cell.setCellValue(createHelper.createRichTextString("Qmean # P&W at registration")); cell = row.createCell(6); cell.setCellValue(createHelper.createRichTextString("Qmean # P&W at questionnaire")); cell = row.createCell(7); cell.setCellValue(createHelper.createRichTextString("Qmean # P at (pre-)interview")); cell = row.createCell(8); cell.setCellValue(createHelper.createRichTextString("Qmean # W at (pre-)interview")); cell = row.createCell(9); cell.setCellValue(createHelper.createRichTextString("# of available doctors")); cell = row.createCell(10); cell.setCellValue(createHelper.createRichTextString("Qmean # P at pre-donation room")); cell = row.createCell(11); cell.setCellValue(createHelper.createRichTextString("Qmean # W at pre-donation room")); cell = row.createCell(12); cell.setCellValue(createHelper.createRichTextString("Qmean # P at donation room")); cell = row.createCell(13); cell.setCellValue(createHelper.createRichTextString("Qmean # W at donation room")); for (int i = 0; i < 16; i++) { /*cell=row.createCell(13+i);cell.setCellValue(createHelper.createRichTextString("BedOcc.Pl " + "hr " + (8 + i)));//donor room Plasma cell=row.createCell(13+1*16+i);cell.setCellValue(createHelper.createRichTextString("BedOcc.Wh " + "hr" + (8 + i)));//donor room Whole cell=row.createCell(13+2*16+i);cell.setCellValue(createHelper.createRichTextString("Queue0" + "hr" + (8 + i)));//Queue lenght reception cell=row.createCell(13+3*16+i);cell.setCellValue(createHelper.createRichTextString("QueueDocPl" + "hr" + (8 + i)));//Queue length doctor plasma cell=row.createCell(13+4*16+i);cell.setCellValue(createHelper.createRichTextString("QueueDocWh" + "hr" + (8 + i)));//Queue length doctor whole cell=row.createCell(13+5*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Pl PreDon" + "hr" + (8 + i))); cell=row.createCell(13+6*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Pl TotDon" + "hr" + (8 + i))); cell=row.createCell(13+7*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Wh PreDon" + "hr" + (8 + i))); cell=row.createCell(13+8*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Wh TotDon" + "hr" + (8 + i))); cell=row.createCell(13+9*16+i);cell.setCellValue(createHelper.createRichTextString("QueuePreDonPl" + "hr" + (8 + i))); cell=row.createCell(13+10*16+i);cell.setCellValue(createHelper.createRichTextString("QueuePreDonWh" + "hr" + (8 + i))); cell=row.createCell(13+11*16+i);cell.setCellValue(createHelper.createRichTextString("QuestionNaire" + "hr" + (8 + i))); */ cell = row.createCell(13 + 1 * 16 + i); cell.setCellValue(createHelper.createRichTextString("AvailableNurse" + "hr" + (8 + i))); cell = row.createCell(13 + 2 * 16 + i); cell.setCellValue(createHelper.createRichTextString("P Wait for connect" + "hr" + (8 + i))); cell = row.createCell(13 + 3 * 16 + i); cell.setCellValue(createHelper.createRichTextString("W Wait for connect" + "hr" + (8 + i))); cell = row.createCell(13 + 4 * 16 + i); cell.setCellValue(createHelper.createRichTextString("Wait for disconnect" + "hr" + (8 + i))); } //other measures can be added, see all measures in line 364-379, as well as variance while (runs <= 10000) {//runs=10000 costs 9 seconds sim.simulate(sheet, runs); runs++; } FileOutputStream fileOut = new FileOutputStream("correct.xls");//name of the excel file wb.write(fileOut); fileOut.close(); }
From source file:com.alvexcore.repo.documents.generation.ExportDataListToXlsx.java
License:Open Source License
protected Workbook createXlsx(JSONArray rows, String XLS_SHEET_NAME) { Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(XLS_SHEET_NAME); for (int k = 0; k < rows.size(); k++) { Row row = sheet.createRow((short) k); JSONArray cells = (JSONArray) rows.get(k); for (int c = 0; c < cells.size(); c++) { String displayValue;/* ww w. ja v a2 s .com*/ Object item = cells.get(c); if (item == null) displayValue = I18NUtil.getMessage("label.empty"); else if (item instanceof Boolean) displayValue = (Boolean) item ? I18NUtil.getMessage("label.yes") : I18NUtil.getMessage("label.no"); else displayValue = item.toString(); row.createCell(c).setCellValue(createHelper.createRichTextString(displayValue)); } } return wb; }
From source file:com.alvexcore.repo.documents.generation.ExportReportToXlsx.java
License:Open Source License
protected Workbook createXlsx(JSONArray rows, String XLS_SHEET_NAME) { Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(XLS_SHEET_NAME); for (int k = 0; k < rows.size(); k++) { Row row = sheet.createRow((short) k); JSONArray cells = (JSONArray) rows.get(k); row.createCell(0).setCellValue(createHelper.createRichTextString((String) cells.get(0))); for (int c = 1; c < cells.size(); c++) row.createCell(c).setCellValue((String) cells.get(c)); }//from w w w. j a va2 s .co m return wb; }
From source file:com.cms.utils.ExportExcell.java
private Comment getcellComment(FormatExcell item, Cell cell) { ExcellHeaderComment headerCommand = item.getHeaderCommand(); Drawing sSFPatriarch = sh.createDrawingPatriarch(); CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = sSFPatriarch.createCellComment(anchor); anchor.setCol1(headerCommand.getRow1()); anchor.setCol2(headerCommand.getRow2()); anchor.setRow1(headerCommand.getColumn1()); anchor.setRow2(headerCommand.getColumn2()); anchor.setDx1(headerCommand.getDx1()); anchor.setDx2(headerCommand.getDx2()); anchor.setDy1(headerCommand.getDy1()); anchor.setDy2(headerCommand.getDy2()); RichTextString rtf1 = factory.createRichTextString(headerCommand.getValue()); Font font = wb.createFont();//from w w w . ja v a 2 s.c om font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); font.setBoldweight(Font.BOLDWEIGHT_NORMAL); font.setColor(IndexedColors.RED.getIndex()); rtf1.applyFont(font); comment1.setString(rtf1); comment1.setAuthor("Logistics"); // comment1.setColumn(cell.getColumnIndex()); // comment1.setRow(cell.getRowIndex()); return comment1; }
From source file:com.cms.utils.ExportExcell.java
private Comment getcellComment(Sheet sh, FormatExcell item, Cell cell) { ExcellHeaderComment headerCommand = item.getHeaderCommand(); Drawing sSFPatriarch = sh.createDrawingPatriarch(); CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = sSFPatriarch.createCellComment(anchor); anchor.setCol1(headerCommand.getRow1()); anchor.setCol2(headerCommand.getRow2()); anchor.setRow1(headerCommand.getColumn1()); anchor.setRow2(headerCommand.getColumn2()); anchor.setDx1(headerCommand.getDx1()); anchor.setDx2(headerCommand.getDx2()); anchor.setDy1(headerCommand.getDy1()); anchor.setDy2(headerCommand.getDy2()); RichTextString rtf1 = factory.createRichTextString(headerCommand.getValue()); Font font = wb.createFont();/*from ww w . java 2s.c om*/ font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); font.setBoldweight(Font.BOLDWEIGHT_NORMAL); font.setColor(IndexedColors.RED.getIndex()); rtf1.applyFont(font); comment1.setString(rtf1); comment1.setAuthor("Logistics"); // comment1.setColumn(cell.getColumnIndex()); // comment1.setRow(cell.getRowIndex()); return comment1; }
From source file:com.docdoku.server.export.ExcelGenerator.java
License:Open Source License
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) { File excelFile = new File("export_parts.xls"); //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Parts Data"); String header = StringUtils.join(queryResult.getQuery().getSelects(), ";"); String[] columns = header.split(";"); Map<Integer, String[]> data = new HashMap<>(); String[] headerFormatted = createXLSHeaderRow(header, columns, locale); data.put(1, headerFormatted);//from w ww. jav a2s. co m Map<Integer, String[]> commentsData = new HashMap<>(); String[] headerComments = createXLSHeaderRowComments(header, columns); commentsData.put(1, headerComments); List<String> selects = queryResult.getQuery().getSelects(); int i = 1; for (QueryResultRow row : queryResult.getRows()) { i++; data.put(i, createXLSRow(selects, row, baseURL)); commentsData.put(i, createXLSRowComments(selects, row)); } //Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = sheet.createRow(rownum++); String[] objArr = data.get(key); int cellnum = 0; for (String obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellValue(obj); } CreationHelper factory = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); String[] commentsObjArr = commentsData.get(key); cellnum = 0; for (String commentsObj : commentsObjArr) { if (commentsObj.length() > 0) { Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 1); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(commentsObj); comment.setString(str); // Assign the comment to the cell cell.setCellComment(comment); } cellnum++; } } // Define header style Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Courier New"); headerFont.setItalic(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // Set header style for (int j = 0; j < columns.length; j++) { Cell cell = sheet.getRow(0).getCell(j); cell.setCellStyle(headerStyle); if (cell.getCellComment() != null) { String comment = cell.getCellComment().getString().toString(); if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER) || comment.equals(QueryField.PART_MASTER_NUMBER)) { for (int k = 0; k < queryResult.getRows().size(); k++) { Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j) : sheet.getRow(k + 1).createCell(j); grayCell.setCellStyle(headerStyle); } } } } try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(excelFile); workbook.write(out); out.close(); } catch (Exception e) { LOGGER.log(Level.FINEST, null, e); } return excelFile; }
From source file:com.eurodyn.qlack2.fuse.lexicon.impl.LanguageServiceImpl.java
License:EUPL
@Override @Transactional(TxType.REQUIRED)//from w w w. j a v a 2 s . c om public byte[] downloadLanguage(String languageID) { byte[] retVal = null; // Check that the language exists and get its translations Language language = Language.find(languageID, em); // Create an Excel workbook. The workbook will contain a sheet for each // group. Workbook wb = new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); // Iterate over all existing groups and create a sheet for each one. // Creating a new list below and not using the one retrieved from // Group.getAllGroups since result lists are read only and // we need to add the empty group below to the list. List<Group> groups = new ArrayList<>(Group.getAllGroups(em)); // Add an dummy entry to the list to also check for translations without // a group. Group emptyGroup = new Group(); emptyGroup.setId(null); emptyGroup.setTitle("<No group>"); groups.add(0, emptyGroup); for (Group group : groups) { Map<String, String> translations; translations = keyService.getTranslationsForGroupAndLocale(group.getId(), language.getLocale()); if (!translations.isEmpty()) { Sheet sheet = wb.createSheet(group.getTitle()); // Add the header. Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue(createHelper.createRichTextString("Key")); headerRow.createCell(1).setCellValue(createHelper.createRichTextString("Translation")); // Add the data. int rowCounter = 1; for (String key : translations.keySet()) { Row row = sheet.createRow(rowCounter++); row.createCell(0).setCellValue(createHelper.createRichTextString(key)); row.createCell(1).setCellValue(createHelper.createRichTextString(translations.get(key))); } } } // Create the byte[] holding the Excel data. ByteArrayOutputStream bos = new ByteArrayOutputStream(); try { wb.write(bos); retVal = bos.toByteArray(); } catch (IOException ex) { // Convert to a runtime exception in order to roll back transaction LOGGER.log(Level.SEVERE, ex.getLocalizedMessage(), ex); throw new QLanguageProcessingException("Error creating Excel file for language " + languageID); } return retVal; }
From source file:com.github.camaral.sheeco.Sheeco.java
License:Apache License
private void createCell(final CreationHelper creationHelper, final Row row, Attribute attribute) { if (row.getCell(attribute.getColumnIndex()) == null) { final Cell cell = row.createCell(attribute.getColumnIndex()); RichTextString cellName = creationHelper.createRichTextString(attribute.getColumnName()); cell.setCellValue(cellName);/*from w w w . j a v a 2 s.com*/ } }
From source file:com.hp.amss.util.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);// ww w.j av a2 s.c o 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); //TODO cell.setCellValue(createHelper.createRichTextString("")); cell.setCellType(Cell.CELL_TYPE_STRING); //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("C:\\hyperinks.xlsx"); wb.write(out); out.close(); }
From source file:com.linus.excel.poi.AligningCells.java
License:Apache License
/** * Creates a cell and aligns it a certain way. * * @param wb the workbook//from ww w . j av a 2s . c o m * @param row the row to create the cell in * @param column the column number to create the cell in * @param halign the horizontal alignment for the cell. */ private static void createCell(Workbook wb, Row row, short column, short halign, short valign) { CreationHelper ch = wb.getCreationHelper(); Cell cell = row.createCell(column); cell.setCellValue(ch.createRichTextString("Align It")); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cell.setCellStyle(cellStyle); }