List of usage examples for org.apache.poi.ss.usermodel ClientAnchor setRow1
public void setRow1(int row1);
From source file:controller.VisitasController.java
public void exportExcel() { FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("Open Resource File"); fileChooser.setInitialDirectory(new File(System.getProperty("user.home"))); fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("XLS", "*.xls")); File f = fileChooser.showSaveDialog(null); try {/*from www.j a v a2 s .c om*/ String filename = f.getAbsolutePath(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("FirstSheet"); int bool = 1; InputStream inputStream = getClass().getResourceAsStream("/images/excel-logo.jpg"); byte[] imageBytes = IOUtils.toByteArray(inputStream); int pictureureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG); inputStream.close(); CreationHelper helper = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(1); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureureIdx); HSSFRow rowhead = sheet.createRow(8); rowhead.createCell(0); rowhead.createCell(1).setCellValue("Cedula"); rowhead.createCell(2).setCellValue("Cliente"); rowhead.createCell(3).setCellValue("Contrato"); rowhead.createCell(4).setCellValue("Plan"); rowhead.createCell(5).setCellValue("Fecha"); rowhead.createCell(6).setCellValue("Hora"); rowhead.createCell(7).setCellValue("Invitados"); makeRowBold(workbook, rowhead); for (int i = 0; i < table.getItems().size(); i++) { HSSFRow row = sheet.createRow(i + 9); Asistencia a = (Asistencia) table.getItems().get(i); row.createCell(1).setCellValue(a.getCedula()); row.createCell(2).setCellValue(a.getNombre()); row.createCell(3).setCellValue(a.getContrato()); row.createCell(4).setCellValue(a.getPlan()); row.createCell(5).setCellValue(a.getFecha()); row.createCell(6).setCellValue(a.getHora()); row.createCell(7).setCellValue(Integer.parseInt(a.getInvitados())); centerRow(workbook, row); } autoSizeColumns(workbook); pict.resize(); FileOutputStream fileOut = new FileOutputStream(filename); workbook.write(fileOut); fileOut.close(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:de.enerko.reports2.engine.Report.java
License:Apache License
/** * This method adds a new cell to the sheet of a workbook. It could * (together with {@link #fill(Workbook, Cell, String, String, boolean)}) be moved to * the {@link CellDefinition} itself, but that would mean that the {@link CellDefinition} is * tied to a specific Excel API. Having those methods here allows the Report to become * an interface if a second engine (i.e. JXL) should be added in the future. * @param workbook//from w w w . j a v a 2s . c o m * @param sheet * @param cellDefinition */ private void addCell(final Workbook workbook, final Sheet sheet, final CellDefinition cellDefinition) { final int columnNum = cellDefinition.column, rowNum = cellDefinition.row; Row row = sheet.getRow(rowNum); if (row == null) row = sheet.createRow(rowNum); Cell cell = row.getCell(columnNum); // If the cell already exists and is no blank cell // it will be used including all formating if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { cell = fill(workbook, cell, cellDefinition, false); } // Otherwise a new cell will be created, the datatype set and // optionally a format will be created else { cell = fill(workbook, row.createCell(columnNum), cellDefinition, true); final Sheet referenceSheet; if (cellDefinition.getReferenceCell() != null && (referenceSheet = workbook.getSheet(cellDefinition.getReferenceCell().sheetname)) != null) { final Row referenceRow = referenceSheet.getRow(cellDefinition.getReferenceCell().row); final Cell referenceCell = referenceRow == null ? null : referenceRow.getCell(cellDefinition.getReferenceCell().column); if (referenceCell != null && referenceCell.getCellStyle() != null) cell.setCellStyle(referenceCell.getCellStyle()); } } // Add an optional comment if (cellDefinition.hasComment()) { final CreationHelper factory = workbook.getCreationHelper(); final Drawing drawing = sheet.createDrawingPatriarch(); final ClientAnchor commentAnchor = factory.createClientAnchor(); final int col1 = cellDefinition.comment.column == null ? cell.getColumnIndex() + 1 : cellDefinition.comment.column; final int row1 = cellDefinition.comment.row == null ? cell.getRowIndex() : cellDefinition.comment.row; commentAnchor.setCol1(col1); commentAnchor.setRow1(row1); commentAnchor.setCol2(col1 + Math.max(1, cellDefinition.comment.width)); commentAnchor.setRow2(row1 + Math.max(1, cellDefinition.comment.height)); final Comment comment = drawing.createCellComment(commentAnchor); comment.setString(factory.createRichTextString(cellDefinition.comment.text)); comment.setAuthor(cellDefinition.comment.author); comment.setVisible(cellDefinition.comment.visible); cell.setCellComment(comment); } }
From source file:de.fme.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. j av a 2s . c om*/ */ 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].*)"); // 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() != null) { // Use the friendly titles, which may even be localised! headings[i] = pd.getTitle(); descriptions[i] = pd.getDescription(); } 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, CSVStrategy.EXCEL_STRATEGY); 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); try { sheet.createFreezePane(0, 1); } catch (IndexOutOfBoundsException e) { //https://issues.apache.org/bugzilla/show_bug.cgi?id=51431 & http://stackoverflow.com/questions/6469693/apache-poi-clearing-freeze-split-panes } 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:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.AbstractIntroSheetGenerator.java
License:Open Source License
private void insertIteraplanLogo(int colIndex, int rowIndex) { CreationHelper helper = getWorkbook().getCreationHelper(); byte[] bytes; try {//from w w w .ja va 2 s . co m InputStream is = logoImage.getInputStream(); bytes = IOUtils.toByteArray(is); } catch (IOException e) { LOGGER.error("Could not read the excel template!", e); throw new IteraplanTechnicalException(IteraplanErrorMessages.INVALID_EXCEL_TEMPLATE); } int pictureIdx = getWorkbook().addPicture(bytes, Workbook.PICTURE_TYPE_PNG); Sheet sheet = getIntroductionSheet(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner of the picture anchor.setCol1(colIndex); anchor.setRow1(rowIndex); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.exporter.ExportWorkbook.java
License:Open Source License
/** * Adds headers stored in <code>headers</code> to the current sheet. If required, a special width * for the corresponding column can be set by providing a value in <code>headersWidth</code> using * header as key.<br/>/*from www .j a v a 2s .c o m*/ * <b>IMPORTANT</b>: Headers are added in the order provided in <code>headers</code>. * * @param headers * headers to be added */ public void addHeaders(int sheetId, List<ExcelSheet.Header> headers) { Sheet sheet = getSheetById(sheetId); Drawing drawing = sheet.createDrawingPatriarch(); CreationHelper factory = sheet.getWorkbook().getCreationHelper(); Row row = sheet.createRow(this.getCurrentRowOfSheet(sheet, 3)); int columnIndex = 0; for (ExcelSheet.Header header : headers) { int currColumnIndex = columnIndex; Cell cell = row.createCell(columnIndex); if (header.getDescription() != null) { ClientAnchor commentAnchor = factory.createClientAnchor(); //Sizing the comment 1x3 cells commentAnchor.setCol1(cell.getColumnIndex()); commentAnchor.setCol2(cell.getColumnIndex() + 1); commentAnchor.setRow1(row.getRowNum()); commentAnchor.setRow2(row.getRowNum() + 3); Comment comment = drawing.createCellComment(commentAnchor); RichTextString str = factory.createRichTextString(header.getDescription()); comment.setString(str); comment.setAuthor(""); cell.setCellComment(comment); } setCellValue(cell, header.getLabel(), getHeaderTableStyle()); Integer width = header.getWidth(); if (width != null) { sheet.setColumnWidth(currColumnIndex, width.intValue()); } columnIndex++; } LOGGER.debug("Added headers."); }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void setCellComment(Cell cell, String comment) { if (comment == null || comment.trim().isEmpty()) { cell.removeCellComment();// ww w . j av a 2 s. c o m } else { Comment c = cell.getCellComment(); if (c == null) { ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setRow1(cell.getRowIndex()); anchor.setRow2(cell.getRowIndex() + commentHeight); anchor.setCol1(cell.getColumnIndex() + 1); anchor.setCol2(cell.getColumnIndex() + commentWidth + 1); anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE); c = getDrawing().createCellComment(anchor); c.setVisible(false); if (commentAuthor != null) { c.setAuthor(commentAuthor); } cell.setCellComment(c); } RichTextString rts = creationHelper.createRichTextString(comment); c.setString(rts); } }
From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java
License:Apache License
private Comment comment(HSSFCell cell, String text) { CreationHelper factory = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); // 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(cell.getRow().getRowNum()); anchor.setRow2(cell.getRow().getRowNum() + 3); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(text); comment.setString(str);/* ww w.jav a2s .co m*/ cell.setCellComment(comment); return comment; }
From source file:demo.poi.AddDimensionedImage.java
License:Apache License
/** * Add an image to a worksheet./*from www .j a v a2 s. c o m*/ * * @param colNumber * A primitive int that contains the index number of a column on * the worksheet; POI column indices are zero based. Together * with the rowNumber parameter's value, this parameter * identifies a cell on the worksheet. The images top left hand * corner will be aligned with the top left hand corner of this * cell. * @param rowNumber * A primitive int that contains the index number of a row on the * worksheet; POI row indices are zero based. Together with the * rowNumber parameter's value, this parameter identifies a cell * on the worksheet. The images top left hand corner will be * aligned with the top left hand corner of this cell. * @param sheet * A reference to the sheet that contains the cell identified by * the two parameters above. * @param drawing * An instance of the DrawingPatriarch class. This is now passed * into the method where it was, previously, recovered from the * sheet in order to allow multiple pictures be inserted. If the * patriarch was not 'cached in this manner each time it was * created any previously positioned images would be simply * over-written. * @param imageFile * An instance of the URL class that encapsulates the name of and * path to the image that is to be 'inserted into' the sheet. * @param reqImageWidthMM * A primitive double that contains the required width of the * image in millimetres. * @param reqImageHeightMM * A primitive double that contains the required height of the * image in millimetres. * @param resizeBehaviour * A primitive int whose value will determine how the code should * react if the image is larger than the cell referenced by the * colNumber and rowNumber parameters. Four constants are * provided to determine what should happen; * AddDimensionedImage.EXPAND_ROW * AddDimensionedImage.EXPAND_COLUMN * AddDimensionedImage.EXPAND_ROW_AND_COLUMN * AddDimensionedImage.OVERLAY_ROW_AND_COLUMN * @throws java.io.FileNotFoundException * If the file containing the image cannot be located. * @throws java.io.IOException * If a problem occurs whilst reading the file of image data. * @throws java.lang.IllegalArgumentException * If an invalid value is passed to the resizeBehaviour * parameter or if the extension of the image file indicates * that it is of a type that cannot currently be added to the * worksheet. */ public void addImageToSheet(int colNumber, int rowNumber, Sheet sheet, Drawing drawing, URL imageFile, double reqImageWidthMM, double reqImageHeightMM, int resizeBehaviour) throws IOException, IllegalArgumentException { ClientAnchor anchor = null; ClientAnchorDetail rowClientAnchorDetail = null; ClientAnchorDetail colClientAnchorDetail = null; int imageType = 0; // Validate the resizeBehaviour parameter. if ((resizeBehaviour != AddDimensionedImage.EXPAND_COLUMN) && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW) && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW_AND_COLUMN) && (resizeBehaviour != AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)) { throw new IllegalArgumentException("Invalid value passed to the " + "resizeBehaviour parameter of AddDimensionedImage.addImageToSheet()"); } // Call methods to calculate how the image and sheet should be // manipulated to accomodate the image; columns and then rows. colClientAnchorDetail = this.fitImageToColumns(sheet, colNumber, reqImageWidthMM, resizeBehaviour); rowClientAnchorDetail = this.fitImageToRows(sheet, rowNumber, reqImageHeightMM, resizeBehaviour); // Having determined if and how to resize the rows, columns and/or the // image, create the ClientAnchor object to position the image on // the worksheet. Note how the two ClientAnchorDetail records are // interrogated to recover the row/column co-ordinates and any insets. // The first two parameters are not used currently but could be if the // need arose to extend the functionality of this code by adding the // ability to specify that a clear 'border' be placed around the image. anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor(); anchor.setDx1(0); anchor.setDy1(0); anchor.setDx2(colClientAnchorDetail.getInset()); anchor.setDy2(rowClientAnchorDetail.getInset()); anchor.setCol1(colClientAnchorDetail.getFromIndex()); anchor.setRow1(rowClientAnchorDetail.getFromIndex()); anchor.setCol2(colClientAnchorDetail.getToIndex()); anchor.setRow2(rowClientAnchorDetail.getToIndex()); // For now, set the anchor type to do not move or resize the // image as the size of the row/column is adjusted. This could easilly // become another parameter passed to the method. Please read the note // above regarding the behaviour of image resizing. anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE); // Now, add the picture to the workbook. Note that unlike the similar // method in the HSSF Examples section, the image type is checked. // First, // the image files location is identified by interrogating the URL // passed // to the method, the images type is identified before it is added to // the // sheet. String sURL = imageFile.toString().toLowerCase(); if (sURL.endsWith(".png")) { imageType = Workbook.PICTURE_TYPE_PNG; } else if (sURL.endsWith("jpg") || sURL.endsWith(".jpeg")) { imageType = Workbook.PICTURE_TYPE_JPEG; } else { throw new IllegalArgumentException("Invalid Image file : " + sURL); } int index = sheet.getWorkbook().addPicture(IOUtils.toByteArray(imageFile.openStream()), imageType); drawing.createPicture(anchor, index); }
From source file:domain.Excel.java
private void cargarLogo1(Integer fila, Integer columna, Double resize) { InputStream inputStream;/*from w w w . j a v a 2s.co m*/ try { inputStream = getClass().getResourceAsStream("/resources/logo tru-test.png");//Tru-Test.jpg"); byte[] bytes = IOUtils.toByteArray(inputStream); int pictureIdx = wb.addPicture(bytes, wb.PICTURE_TYPE_PNG); inputStream.close(); CreationHelper helper = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(columna); anchor.setRow1(fila); Picture pict = drawing.createPicture(anchor, pictureIdx); //Reset the image to the original size pict.resize(resize); } catch (FileNotFoundException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:domain.Excel.java
private void cargarLogo2(Integer fila, Integer columna, Double resize) { InputStream inputStream;//from w ww .j a v a 2s .co m try { inputStream = getClass().getResourceAsStream("/resources/LOGO.png");//Tru-Test.jpg"); byte[] bytes = IOUtils.toByteArray(inputStream); int pictureIdx = wb.addPicture(bytes, wb.PICTURE_TYPE_PNG); inputStream.close(); CreationHelper helper = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(columna); anchor.setRow1(fila); Picture pict = drawing.createPicture(anchor, pictureIdx); //Reset the image to the original size pict.resize(resize); } catch (FileNotFoundException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } }