List of usage examples for org.apache.poi.ss.usermodel Comment setAuthor
void setAuthor(String author);
From source file:excel.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { try (Workbook wb = new XSSFWorkbook()) { CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet();//from w w w . jav a 2 s .c o m Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); Drawing<?> drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1); comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //apply custom font to the text in the comment Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBold(true); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); comment2.setAddress(new CellAddress("C3")); try (FileOutputStream out = new FileOutputStream("comments.xlsx")) { wb.write(out); } } }
From source file:it.eng.spagobi.engines.worksheet.services.export.ExportWorksheetAction.java
License:Mozilla Public License
public void exportMetadataToXLS(Workbook wb, WorkSheetXLSExporter exporter, CreationHelper createHelper, JSONArray metadataPropertiesJSON, JSONArray parametersJSON) throws Exception { int FIRST_ROW = 0; int FIRST_COLUMN = 0; int rowCount = 0; JSONArray technicalMetadataProperty; JSONArray shortBusinessMetadataProperty; JSONArray longBusinessMetadataProperty; org.apache.poi.ss.usermodel.Sheet sheet = wb .createSheet(EngineMessageBundle.getMessage("worksheet.export.metadata.title", this.getLocale())); sheet.setColumnWidth(FIRST_COLUMN, 256 * 25); sheet.setColumnWidth(FIRST_COLUMN + 1, 256 * 90); CellStyle headerCellStyle = exporter.buildMetadataTitleCellStyle(sheet); CellStyle metaNameCellStyle = exporter.buildMetadataNameCellStyle(sheet); CellStyle metaValueCellStyle = exporter.buildMetadataValueCellStyle(sheet); Row row;/*from w w w . j ava 2 s. c om*/ Cell nameCell; Cell valueCell; Cell headerCell; String text; technicalMetadataProperty = new JSONArray(); shortBusinessMetadataProperty = new JSONArray(); longBusinessMetadataProperty = new JSONArray(); if (metadataPropertiesJSON != null) { for (int i = 0; i < metadataPropertiesJSON.length(); i++) { JSONObject metadataProperty = metadataPropertiesJSON.getJSONObject(i); String metadataPropertyType = metadataProperty.getString("meta_type"); if ("SHORT_TEXT".equalsIgnoreCase(metadataPropertyType)) { shortBusinessMetadataProperty.put(metadataProperty); continue; } else if ("LONG_TEXT".equalsIgnoreCase(metadataPropertyType)) { longBusinessMetadataProperty.put(metadataProperty); continue; } else { technicalMetadataProperty.put(metadataProperty); } } } if (technicalMetadataProperty.length() > 0) { row = sheet.createRow((FIRST_ROW) + rowCount); headerCell = row.createCell(FIRST_COLUMN + 1); headerCell = row.createCell(FIRST_COLUMN + 1); text = EngineMessageBundle.getMessage("worksheet.export.metadata.technicalMetadata", this.getLocale()); headerCell.setCellValue(createHelper.createRichTextString(text)); headerCell.setCellType(exporter.getCellTypeString()); headerCell.setCellStyle(headerCellStyle); rowCount++; for (int i = 0; i < technicalMetadataProperty.length(); i++) { JSONObject metadataProperty = technicalMetadataProperty.getJSONObject(i); String metadataPropertyName = metadataProperty.getString("meta_name"); String metadataPropertyValue = metadataProperty.getString("meta_content"); row = sheet.createRow((FIRST_ROW) + rowCount); nameCell = row.createCell(FIRST_COLUMN); nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName)); nameCell.setCellType(exporter.getCellTypeString()); nameCell.setCellStyle(metaNameCellStyle); valueCell = row.createCell(FIRST_COLUMN + 1); valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue)); valueCell.setCellType(exporter.getCellTypeString()); valueCell.setCellStyle(metaValueCellStyle); rowCount++; } rowCount = rowCount + 2; } if (shortBusinessMetadataProperty.length() + longBusinessMetadataProperty.length() > 0) { row = sheet.createRow((FIRST_ROW) + rowCount); headerCell = row.createCell(FIRST_COLUMN + 1); headerCell = row.createCell(FIRST_COLUMN + 1); text = EngineMessageBundle.getMessage("worksheet.export.metadata.businessMetadata", this.getLocale()); headerCell.setCellValue(createHelper.createRichTextString(text)); headerCell.setCellType(exporter.getCellTypeString()); headerCell.setCellStyle(headerCellStyle); rowCount++; for (int i = 0; i < shortBusinessMetadataProperty.length(); i++, rowCount++) { JSONObject metadataProperty = shortBusinessMetadataProperty.getJSONObject(i); String metadataPropertyName = metadataProperty.getString("meta_name"); String metadataPropertyValue = metadataProperty.getString("meta_content"); row = sheet.createRow((FIRST_ROW) + rowCount); nameCell = row.createCell(FIRST_COLUMN); nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName)); nameCell.setCellType(exporter.getCellTypeString()); nameCell.setCellStyle(metaNameCellStyle); valueCell = row.createCell(FIRST_COLUMN + 1); valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue)); valueCell.setCellType(exporter.getCellTypeString()); valueCell.setCellStyle(metaValueCellStyle); } for (int i = 0; i < longBusinessMetadataProperty.length(); i++, rowCount++) { JSONObject metadataProperty = longBusinessMetadataProperty.getJSONObject(i); String metadataPropertyName = metadataProperty.getString("meta_name"); String metadataPropertyValue = metadataProperty.getString("meta_content"); row = sheet.createRow((FIRST_ROW) + rowCount); nameCell = row.createCell(FIRST_COLUMN); nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName)); nameCell.setCellType(exporter.getCellTypeString()); nameCell.setCellStyle(metaNameCellStyle); valueCell = row.createCell(FIRST_COLUMN + 1); valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue)); valueCell.setCellType(exporter.getCellTypeString()); valueCell.setCellStyle(metaValueCellStyle); } rowCount = rowCount + 2; } if (parametersJSON.length() > 0) { row = sheet.createRow((FIRST_ROW) + rowCount); headerCell = row.createCell(FIRST_COLUMN + 1); headerCell = row.createCell(FIRST_COLUMN + 1); text = EngineMessageBundle.getMessage("worksheet.export.metadata.analyticalDrivers", this.getLocale()); headerCell.setCellValue(createHelper.createRichTextString(text)); headerCell.setCellType(exporter.getCellTypeString()); headerCell.setCellStyle(headerCellStyle); rowCount++; Drawing drawing = sheet.createDrawingPatriarch(); for (int i = 0; i < parametersJSON.length(); i++) { JSONObject parameterJSON = parametersJSON.getJSONObject(i); String name = parameterJSON.getString("name"); String value = parameterJSON.getString("value"); String description = parameterJSON.optString("description"); row = sheet.createRow((FIRST_ROW) + rowCount); nameCell = row.createCell(FIRST_COLUMN); nameCell.setCellValue(createHelper.createRichTextString(name)); nameCell.setCellType(exporter.getCellTypeString()); nameCell.setCellStyle(metaNameCellStyle); valueCell = row.createCell(FIRST_COLUMN + 1); if (StringUtilities.isNotEmpty(description)) { valueCell.setCellValue(createHelper.createRichTextString(description)); ClientAnchor anchor = createHelper.createClientAnchor(); anchor.setCol1(valueCell.getColumnIndex()); anchor.setCol2(valueCell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor); RichTextString str = createHelper.createRichTextString(value); comment.setString(str); comment.setAuthor("SpagoBI"); valueCell.setCellComment(comment); } else { valueCell.setCellValue(createHelper.createRichTextString(value)); } valueCell.setCellType(exporter.getCellTypeString()); valueCell.setCellStyle(metaValueCellStyle); rowCount++; } } }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetSetCellComment.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { if (parameters.get(2).getDataType() != cfData.CFSTRUCTDATA) throwException(_session, "parameter must be of type structure"); cfSpreadSheetData spreadsheet = null; cfStructData commentS = null;// w ww .ja va 2s.co m int rowNo, columnNo; /* * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(3); commentS = (cfStructData) parameters.get(2); rowNo = parameters.get(1).getInt() - 1; columnNo = parameters.get(0).getInt() - 1; if (rowNo < 0) throwException(_session, "row must be 1 or greater (" + rowNo + ")"); if (columnNo < 0) throwException(_session, "column must be 1 or greater (" + columnNo + ")"); /* * Perform the insertion */ Sheet sheet = spreadsheet.getActiveSheet(); Row row = sheet.getRow(rowNo); if (row == null) row = sheet.createRow(rowNo); Cell cell = row.getCell(columnNo); if (cell == null) cell = row.createCell(columnNo); // Create the anchor HSSFClientAnchor clientAnchor = new HSSFClientAnchor(); if (commentS.containsKey("anchor")) { String[] anchor = commentS.getData("anchor").getString().split(","); if (anchor.length != 4) throwException(_session, "Invalid 'anchor' attribute, should be 4 numbers"); clientAnchor.setRow1(Integer.valueOf(anchor[0]) - 1); clientAnchor.setCol1(Integer.valueOf(anchor[1]) - 1); clientAnchor.setRow2(Integer.valueOf(anchor[2]) - 1); clientAnchor.setCol2(Integer.valueOf(anchor[3]) - 1); } else { clientAnchor.setRow1(rowNo); clientAnchor.setCol1(columnNo); clientAnchor.setRow2(rowNo + 2); clientAnchor.setCol2(columnNo + 2); } // Create the comment Comment comment = spreadsheet.getActiveSheet().createDrawingPatriarch().createCellComment(clientAnchor); if (commentS.containsKey("author")) { comment.setAuthor(commentS.getData("author").getString()); } if (commentS.containsKey("visible")) { comment.setVisible(commentS.getData("visible").getBoolean()); } if (commentS.containsKey("comment")) { HSSFRichTextString richText = new HSSFRichTextString(commentS.getData("comment").getString()); try { richText.applyFont(SpreadSheetFormatOptions.createCommentFont(spreadsheet.getWorkBook(), commentS)); } catch (Exception e) { throwException(_session, e.getMessage()); } comment.setString(richText); } cell.setCellComment(comment); return cfBooleanData.TRUE; }
From source file:org.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java
License:Open Source License
/** * Generates the spreadsheet, based on the properties in the header * and a callback for the body.//from ww w . j a va 2 s. c o m */ public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status, Map<String, Object> model) throws IOException { Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)"); String delimiterParam = req.getParameter(PARAM_REQ_DELIMITER); CSVStrategy reqCSVstrategy = null; if (delimiterParam != null && !delimiterParam.isEmpty()) { reqCSVstrategy = new CSVStrategy(delimiterParam.charAt(0), '"', CSVStrategy.COMMENTS_DISABLED); } // Build up the details of the header List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req); String[] headings = new String[propertyDetails.size()]; String[] descriptions = new String[propertyDetails.size()]; boolean[] required = new boolean[propertyDetails.size()]; for (int i = 0; i < headings.length; i++) { Pair<QName, Boolean> property = propertyDetails.get(i); if (property == null || property.getFirst() == null) { headings[i] = ""; required[i] = false; } else { QName column = property.getFirst(); required[i] = property.getSecond(); // Ask the dictionary service nicely for the details PropertyDefinition pd = dictionaryService.getProperty(column); if (pd != null && pd.getTitle(dictionaryService) != null) { // Use the friendly titles, which may even be localised! headings[i] = pd.getTitle(dictionaryService); descriptions[i] = pd.getDescription(dictionaryService); } else { // Nothing friendly found, try to munge the raw qname into // something we can show to a user... String raw = column.getLocalName(); raw = raw.substring(0, 1).toUpperCase() + raw.substring(1); Matcher m = qnameMunger.matcher(raw); if (m.matches()) { headings[i] = m.group(1) + " " + m.group(2); } else { headings[i] = raw; } } } } // Build a list of just the properties List<QName> properties = new ArrayList<QName>(propertyDetails.size()); for (Pair<QName, Boolean> p : propertyDetails) { QName qn = null; if (p != null) { qn = p.getFirst(); } properties.add(qn); } // Output if ("csv".equals(format)) { StringWriter sw = new StringWriter(); CSVPrinter csv = new CSVPrinter(sw, reqCSVstrategy != null ? reqCSVstrategy : getCsvStrategy()); csv.println(headings); populateBody(resource, csv, properties); model.put(MODEL_CSV, sw.toString()); } else { Workbook wb; if ("xlsx".equals(format)) { wb = new XSSFWorkbook(); // TODO Properties } else { wb = new HSSFWorkbook(); // TODO Properties } // Add our header row Sheet sheet = wb.createSheet("Export"); Row hr = sheet.createRow(0); sheet.createFreezePane(0, 1); Font fb = wb.createFont(); fb.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fi = wb.createFont(); fi.setBoldweight(Font.BOLDWEIGHT_BOLD); fi.setItalic(true); CellStyle csReq = wb.createCellStyle(); csReq.setFont(fb); CellStyle csOpt = wb.createCellStyle(); csOpt.setFont(fi); // Populate the header Drawing draw = null; for (int i = 0; i < headings.length; i++) { Cell c = hr.createCell(i); c.setCellValue(headings[i]); if (required[i]) { c.setCellStyle(csReq); } else { c.setCellStyle(csOpt); } if (headings[i].length() == 0) { sheet.setColumnWidth(i, 3 * 250); } else { sheet.setColumnWidth(i, 18 * 250); } if (descriptions[i] != null && descriptions[i].length() > 0) { // Add a description for it too if (draw == null) { draw = sheet.createDrawingPatriarch(); } ClientAnchor ca = wb.getCreationHelper().createClientAnchor(); ca.setCol1(c.getColumnIndex()); ca.setCol2(c.getColumnIndex() + 1); ca.setRow1(hr.getRowNum()); ca.setRow2(hr.getRowNum() + 2); Comment cmt = draw.createCellComment(ca); cmt.setAuthor(""); cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i])); cmt.setVisible(false); c.setCellComment(cmt); } } // Have the contents populated populateBody(resource, wb, sheet, properties); // Save it for the template ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); model.put(MODEL_EXCEL, baos.toByteArray()); } }
From source file:org.eclipse.emfforms.internal.spreadsheet.core.renderer.EMFFormsSpreadsheetControlRenderer.java
License:Open Source License
private Comment createComment(Workbook workbook, Sheet sheet, VDomainModelReference domainModelReference, int row, int column) throws IOException { final CreationHelper factory = workbook.getCreationHelper(); // When the comment box is visible, have it show in a 1x3 space final ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(column);// w ww .j a va2s.c o m anchor.setCol2(column + 1); anchor.setRow1(row); anchor.setRow2(row + 1); final Drawing drawing = sheet.createDrawingPatriarch(); final Comment comment = drawing.createCellComment(anchor); comment.setAuthor("EMFForms Spreadsheet Renderer"); //$NON-NLS-1$ comment.setVisible(false); comment.setString(factory.createRichTextString(getSerializedDMR(domainModelReference))); return comment; }
From source file:org.eclipse.emfforms.spreadsheet.integrationtest.ImportErrors_ITest.java
License:Open Source License
private Comment createComment(Workbook workbook, Sheet sheet, int row, int column) throws IOException { final CreationHelper factory = workbook.getCreationHelper(); // When the comment box is visible, have it show in a 1x3 space final ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(column);// w w w .ja va 2 s . c o m anchor.setCol2(column + 1); anchor.setRow1(row); anchor.setRow2(row + 1); final Drawing drawing = sheet.createDrawingPatriarch(); final Comment comment = drawing.createCellComment(anchor); comment.setAuthor("EMFForms Spreadsheet Renderer"); //$NON-NLS-1$ comment.setVisible(false); comment.setString(factory.createRichTextString("Ignore Sheet")); //$NON-NLS-1$ return comment; }
From source file:org.nuxeo.ecm.platform.groups.audit.service.acl.excel.ExcelBuilder.java
License:Open Source License
/** * Return a Comment. Comments are supported only on XLS file (HSSF framework). * * @param row/*from w ww . j av a2s . c o m*/ * @param col * @param colWidth * @param rowHeight * @return */ public Comment buildComment(String text, int row, int col, int colWidth, int rowHeight) { ClientAnchor anchor = create.createClientAnchor(); anchor.setCol1(col); anchor.setCol2(col + colWidth); anchor.setRow1(row); anchor.setRow2(row + rowHeight); // Create the comment and set the text+author Comment comment = null; if (drawing instanceof HSSFPatriarch) { HSSFPatriarch p = (HSSFPatriarch) drawing; comment = p.createComment((HSSFAnchor) anchor); } else if (drawing instanceof XSSFDrawing) { log.error("comments not supported on XSSFDrawing, i.e. XLSX files"); // XSSFDrawing p = (XSSFDrawing)drawing; // comment = p.createComment((XSSFAnchor)anchor); } if (comment != null) { RichTextString str = create.createRichTextString(text); comment.setString(str); comment.setAuthor(""); // Assign the comment to the cell return comment; } else return null; }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
private Comment createCellComment(String author, String comment) { // comments only supported for XLSX if (data.sheet instanceof XSSFSheet) { CreationHelper factory = data.wb.getCreationHelper(); Drawing drawing = data.sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment cmt = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(comment); cmt.setString(str);/*from www.j a va 2 s . c o m*/ cmt.setAuthor(author); return cmt; } return null; }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * clone existing comments into new cell comment. * /*from ww w . j a v a 2 s .co m*/ * @param sourceCell * source cell. * @param newCell * target cell. */ public static void cloneComment(final Cell sourceCell, final Cell newCell) { XSSFSheet sheet = (XSSFSheet) newCell.getSheet(); CreationHelper factory = sheet.getWorkbook().getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); XSSFComment sourceComment = (XSSFComment) sourceCell.getCellComment(); // Below code are from POI busy manual. // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = createCommentAnchor(newCell, factory); // Create the comment and set the text+author Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(sourceComment.getString().toString()); comment.setString(str); comment.setAuthor(sourceComment.getAuthor()); // Assign the comment to the cell newCell.setCellComment(comment); comment.setColumn(newCell.getColumnIndex()); comment.setRow(newCell.getRowIndex()); // As POI doesn't has well support for comments, // So we have to use low level api to match the comments. matchCommentSettings(newCell, sourceCell); }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * Creates the or insert comment./*from w w w . j a v a 2 s .c o m*/ * * @param cell the cell * @param commentStr the comment str */ public static void createOrInsertComment(final Cell cell, final String commentStr) { XSSFSheet sheet = (XSSFSheet) cell.getSheet(); CreationHelper factory = sheet.getWorkbook().getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); Comment comment = cell.getCellComment(); String originStr = ""; if (comment == null) { // Below code are from POI busy manual. // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = createCommentAnchor(cell, factory); // Create the comment and set the text+author comment = drawing.createCellComment(anchor); } else { originStr = comment.getString().getString() + "\n"; } originStr += commentStr; RichTextString str = factory.createRichTextString(originStr); comment.setString(str); comment.setAuthor(""); // Assign the comment to the cell cell.setCellComment(comment); comment.setColumn(cell.getColumnIndex()); comment.setRow(cell.getRowIndex()); }