List of usage examples for org.apache.poi.ss.usermodel ClientAnchor setCol2
public void setCol2(int col2);
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 w w w . ja va2s . 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.bbreak.excella.reports.tag.ImageParamParser.java
License:Open Source License
/** * ??/*from ww w .j a va 2 s . c o m*/ * * @param sheet ? * @param cell * @param filePath ? * @param dx1 ?? * @param dy1 ??? * @param scale ??? * @throws ParseException */ public void replaceImageValue(Sheet sheet, Cell cell, String filePath, Integer dx1, Integer dy1, Double scale) throws ParseException { Workbook workbook = sheet.getWorkbook(); int format = -1; if (filePath.toLowerCase().endsWith(JPEG_SUFFIX) || filePath.toLowerCase().endsWith(JPG_SUFFIX)) { format = Workbook.PICTURE_TYPE_JPEG; } else if (filePath.toLowerCase().endsWith(PNG_SUFFIX)) { format = Workbook.PICTURE_TYPE_PNG; } if (format == -1) { throw new ParseException(cell, "????????" + filePath); } byte[] bytes = null; InputStream is = null; try { is = new FileInputStream(filePath); bytes = IOUtils.toByteArray(is); } catch (Exception e) { throw new ParseException(cell, e); } finally { try { is.close(); } catch (IOException e) { throw new ParseException(cell, e); } } int pictureIdx = workbook.addPicture(bytes, format); CreationHelper helper = workbook.getCreationHelper(); @SuppressWarnings("rawtypes") Drawing drawing = drawingCash.get(sheet); if (drawing == null) { drawing = sheet.createDrawingPatriarch(); drawingCash.put(sheet, drawing); } ClientAnchor anchor = helper.createClientAnchor(); anchor.setRow1(cell.getRowIndex()); anchor.setCol1(cell.getColumnIndex()); anchor.setRow2(cell.getRowIndex() + 1); anchor.setCol2(cell.getColumnIndex() + 1); if (dx1 != null) { anchor.setDx1(dx1); } if (dy1 != null) { anchor.setDy1(dy1); } Picture picture = drawing.createPicture(anchor, pictureIdx); picture.resize(scale); }
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. ja 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);//from w w w. ja v a 2s . 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.eclipse.emfforms.spreadsheet.integrationtest.ImportErrors_ITest.java
License:Open Source License
@Test public void testNoObjectIdColumn() throws IOException { /* setup *//* w w w . j av a 2s . c om*/ final Workbook workbook = new HSSFWorkbook(); final Sheet sheet = workbook.createSheet("root"); //$NON-NLS-1$ final Row rowLabel = sheet.createRow(0); rowLabel.createCell(0).setCellValue("My feature"); //$NON-NLS-1$ 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(0); anchor.setCol2(1); anchor.setRow1(0); anchor.setRow2(1); final Drawing drawing = sheet.createDrawingPatriarch(); final Comment comment = drawing.createCellComment(anchor); comment.setString(factory.createRichTextString( "<?xml version=\"1.0\" encoding=\"UTF-8\"?><org.eclipse.emf.ecp.view.model:FeaturePathDomainModelReference xmi:version=\"2.0\" xmlns:xmi=\"http://www.omg.org/XMI\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ecore=\"http://www.eclipse.org/emf/2002/Ecore\" xmlns:org.eclipse.emf.ecp.view.model=\"http://org/eclipse/emf/ecp/view/model/170\"><domainModelEFeature xsi:type=\"ecore:EAttribute\" href=\"http://eclipse/org/emf/ecp/makeithappen/model/task#//User/lastName\"/></org.eclipse.emf.ecp.view.model:FeaturePathDomainModelReference>")); //$NON-NLS-1$ final Row rowDescription = sheet.createRow(1); rowDescription.createCell(0).setCellValue("My feature description"); //$NON-NLS-1$ final Row rowMeta = sheet.createRow(2); rowMeta.createCell(0).setCellValue("Enter Numbers"); //$NON-NLS-1$ final Row rowData = sheet.createRow(3); rowData.createCell(0).setCellValue("My Feature Value"); //$NON-NLS-1$ /* act */ final SpreadsheetImportResult result = EMFFormsSpreadsheetImporter.INSTANCE.importSpreadsheet(workbook, eClass); assertEquals(1, result.getErrorReports().size()); }
From source file:org.isisaddons.module.excel.dom.CellMarshaller.java
License:Apache License
private static void setCellComment(final Cell cell, final String commentText) { Sheet sheet = cell.getSheet();/*www . j a va2s . c om*/ Row row = cell.getRow(); Workbook workbook = sheet.getWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 3); Drawing drawing = sheet.createDrawingPatriarch(); Comment comment1 = drawing.createCellComment(anchor); RichTextString commentRtf = creationHelper.createRichTextString(commentText); comment1.setString(commentRtf); Comment comment = comment1; cell.setCellComment(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 ww w .jav a2 s . 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.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelImageHandler.java
License:Open Source License
protected ClientAnchor computeExcel97ClientAnchor(final SlimSheetLayout currentLayout, final TableRectangle rectangle, final StrictBounds cb) { final int cell1x = rectangle.getX1(); final int cell1y = rectangle.getY1(); final int cell2x = Math.max(cell1x, rectangle.getX2() - 1); final int cell2y = Math.max(cell1y, rectangle.getY2() - 1); final long cell1width = currentLayout.getCellWidth(cell1x); final long cell1height = currentLayout.getRowHeight(cell1y); final long cell2width = currentLayout.getCellWidth(cell2x); final long cell2height = currentLayout.getRowHeight(cell2y); final long cell1xPos = currentLayout.getXPosition(cell1x); final long cell1yPos = currentLayout.getYPosition(cell1y); final long cell2xPos = currentLayout.getXPosition(cell2x); final long cell2yPos = currentLayout.getYPosition(cell2y); final int dx1 = (int) (1023 * ((cb.getX() - cell1xPos) / (double) cell1width)); final int dy1 = (int) (255 * ((cb.getY() - cell1yPos) / (double) cell1height)); final int dx2 = (int) (1023 * ((cb.getX() + cb.getWidth() - cell2xPos) / (double) cell2width)); final int dy2 = (int) (255 * ((cb.getY() + cb.getHeight() - cell2yPos) / (double) cell2height)); final ClientAnchor anchor = printerBase.getWorkbook().getCreationHelper().createClientAnchor(); anchor.setDx1(dx1);//w w w . ja va 2s . c om anchor.setDy1(dy1); anchor.setDx2(dx2); anchor.setDy2(dy2); anchor.setCol1(cell1x); anchor.setRow1(cell1y); anchor.setCol2(cell2x); anchor.setRow2(cell2y); anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); return anchor; }
From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelImageHandler.java
License:Open Source License
protected ClientAnchor computeExcel2003ClientAnchor(final SlimSheetLayout currentLayout, final TableRectangle rectangle, final StrictBounds cb) { final int cell1x = rectangle.getX1(); final int cell1y = rectangle.getY1(); final int cell2x = Math.max(cell1x, rectangle.getX2() - 1); final int cell2y = Math.max(cell1y, rectangle.getY2() - 1); final long cell1xPos = currentLayout.getXPosition(cell1x); final long cell1yPos = currentLayout.getYPosition(cell1y); final long cell2xPos = currentLayout.getXPosition(cell2x); final long cell2yPos = currentLayout.getYPosition(cell2y); final int dx1 = (int) StrictGeomUtility.toExternalValue((cb.getX() - cell1xPos) * XSSFShape.EMU_PER_POINT); final int dy1 = (int) StrictGeomUtility.toExternalValue((cb.getY() - cell1yPos) * XSSFShape.EMU_PER_POINT); final int dx2 = (int) Math.max(0, StrictGeomUtility .toExternalValue((cb.getX() + cb.getWidth() - cell2xPos) * XSSFShape.EMU_PER_POINT)); final int dy2 = (int) Math.max(0, StrictGeomUtility .toExternalValue((cb.getY() + cb.getHeight() - cell2yPos) * XSSFShape.EMU_PER_POINT)); final ClientAnchor anchor = printerBase.getWorkbook().getCreationHelper().createClientAnchor(); anchor.setDx1(dx1);//from w ww.j av a 2s . c o m anchor.setDy1(dy1); anchor.setDx2(dx2); anchor.setDy2(dy2); anchor.setCol1(cell1x); anchor.setRow1(cell1y); anchor.setCol2(cell2x); anchor.setRow2(cell2y); anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); return anchor; }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * Creates the comment anchor.//w w w . ja v a 2s . c om * * @param newCell the new cell * @param factory the factory * @return the client anchor */ private static ClientAnchor createCommentAnchor(final Cell newCell, CreationHelper factory) { ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(newCell.getColumnIndex()); anchor.setCol2(newCell.getColumnIndex() + 1); anchor.setRow1(newCell.getRowIndex()); anchor.setRow2(newCell.getRowIndex() + 3); return anchor; }