List of usage examples for org.apache.poi.ss.usermodel ClientAnchor setRow1
public void setRow1(int row1);
From source file:functions.excels.Excel.java
License:Apache License
/** * Colle le logo en haut gauche de la page donne. * @param page//from w ww. ja va 2 s .c o m * @throws IOException */ public void collerLogo(int page) throws IOException { InputStream is = new FileInputStream("public/images/banniere-aer.png"); byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); is.close(); CreationHelper helper = wb.getCreationHelper(); Sheet sheet = wb.getSheetAt(0); // Create the drawing patriarch. This is the top level container for all shapes. Drawing drawing = sheet.createDrawingPatriarch(); //add a picture shape ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.setCol1(0); anchor.setRow1(LIGNES * page); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.resize(); }
From source file:functions.excels.Excel.java
License:Apache License
/** * Insre la carte en paramtre la page donne * @param carte//from w w w . jav a 2s. co m * @param page * @throws IOException */ public void pasteMap(Carte carte, int page) throws IOException { ByteArrayOutputStream os = new ByteArrayOutputStream(); ImageIO.write(carte.getImage(), "png", os); byte[] bytes = os.toByteArray(); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); CreationHelper helper = wb.getCreationHelper(); Sheet sheet = wb.getSheetAt(0); // Create the drawing patriarch. This is the top level container for all shapes. Drawing drawing = sheet.createDrawingPatriarch(); //add a picture shape ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.setCol1(4); anchor.setRow1(LIGNES * page + 10); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.resize(); }
From source file:info.informationsea.tableio.excel.ExcelImageSheetWriter.java
License:Open Source License
public void addImage(ImageType type, byte[] data) { int pictureType; switch (type) { case TYPE_JPEG: pictureType = Workbook.PICTURE_TYPE_JPEG; break;// w w w. j a v a 2 s . c o m case TYPE_PNG: pictureType = Workbook.PICTURE_TYPE_PNG; break; default: throw new IllegalArgumentException("Image type should be jpeg or png"); } int pictureIndex = sheet.getWorkbook().addPicture(data, pictureType); CreationHelper creationHelper = sheet.getWorkbook().getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setCol1(1); anchor.setRow1(nextRow); Picture picture = drawing.createPicture(anchor, pictureIndex); picture.resize(); nextRow = picture.getPreferredSize().getRow2() + 1; }
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 ww .j a v a2 s. co m 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:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
private void createImage(Workbook wb, ReportModel model, jdbreport.model.Cell cell, RenderedImage image, int row, int column, CreationHelper createHelper) { int pictureIdx = createImage(wb, cell, image); if (pictureIdx > 0) { ClientAnchor anchor = createHelper.createClientAnchor(); anchor.setCol1(column);// w w w.java 2s.c om anchor.setRow1(row); anchor.setCol2(column + cell.getColSpan()); anchor.setRow2(row + cell.getRowSpan()); Picture pict = drawing.createPicture(anchor, pictureIdx); double scale = 1; if (cell.isScaleIcon()) { Dimension size = model.getCellSize(cell, row, column, false); double hscale = 1.0 * size.height / cell.getPicture().getHeight(); double wscale = 1.0 * size.width / cell.getPicture().getWidth(); scale = Math.min(hscale, wscale); } pict.resize(scale); } }
From source file:module.siadap.domain.SiadapRootModule.java
License:Open Source License
private void populateSheet(HSSFSheet sheetToWriteTo, boolean considerQuotas, UnitSiadapWrapper unitToSearchIn, HSSFWorkbook wb, boolean shouldIncludeEndOfRole, boolean includeHarmonizationResponsibles, boolean shouldIncludeUniverse) { CreationHelper creationHelper = wb.getCreationHelper(); // make the sheet fit the page PrintSetup ps = sheetToWriteTo.getPrintSetup(); sheetToWriteTo.setAutobreaks(true);/*from w ww . j a v a 2 s . c om*/ ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); /* ** styles ** */ // CostCenter style HSSFFont costCenterFont = wb.createFont(); costCenterFont.setColor(HSSFColor.DARK_BLUE.index); costCenterFont.setFontHeightInPoints((short) 12); costCenterFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle costCenterStyle = wb.createCellStyle(); costCenterStyle.setFont(costCenterFont); // make the Unit header style CellStyle unitHeaderStyle = wb.createCellStyle(); unitHeaderStyle.setBorderBottom(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderTop(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderLeft(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderRight(CellStyle.BORDER_THIN); unitHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); unitHeaderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setItalic(true); unitHeaderStyle.setFont(headerFont); // make the default name style CellStyle defaultTextNameStyle = wb.createCellStyle(); defaultTextNameStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextNameStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont defaultFont = wb.createFont(); defaultFont.setFontHeightInPoints((short) 11); defaultTextNameStyle.setFont(defaultFont); // make the last line name style CellStyle defaultTextNameLastStyle = wb.createCellStyle(); defaultTextNameLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextNameLastStyle.setFont(defaultFont); // make the default IST-ID last line style CellStyle defaultTextIstIdLastStyle = wb.createCellStyle(); defaultTextIstIdLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdLastStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdLastStyle.setFont(defaultFont); // make the default IST-ID style CellStyle defaultTextIstIdStyle = wb.createCellStyle(); defaultTextIstIdStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdStyle.setFont(defaultFont); // header style // CellStyle headerStyle = wb.createCellStyle(); // HSSFFont headerFont = wb.createFont(); // headerFont.setFontName(HSSFFont.FONT_ARIAL); // headerFont.setFontHeightInPoints((short) 10); // headerStyle.setFont(headerFont); // // first line style CellStyle firstLineStyle = wb.createCellStyle(); HSSFFont firstLineFont = wb.createFont(); firstLineFont.setColor(HSSFColor.DARK_BLUE.index); firstLineFont.setFontHeightInPoints((short) 14); firstLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); firstLineStyle.setFont(firstLineFont); firstLineStyle.setAlignment(CellStyle.ALIGN_CENTER); firstLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // second line style CellStyle secondLineStyle = wb.createCellStyle(); HSSFFont secondLineFont = wb.createFont(); secondLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); secondLineFont.setFontHeightInPoints((short) 14); secondLineStyle.setFont(secondLineFont); secondLineStyle.setAlignment(CellStyle.ALIGN_CENTER); secondLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // the style for Unit Harmonization responsibles - title CellStyle unitHarmonizationTitleStyle = wb.createCellStyle(); // the BLUE title font - is equal to 'firstLineFont' unitHarmonizationTitleStyle.setFont(firstLineFont); // now we just have to shade it unitHarmonizationTitleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationTitleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationTitleStyle.setAlignment(CellStyle.ALIGN_CENTER); // the style for Unit Harmonization responsibles - normal // let's create the BLUE Arial 14 font for the responsibles of // harmonization HSSFFont harmonizationResponsibleFont = wb.createFont(); harmonizationResponsibleFont.setColor(HSSFColor.DARK_BLUE.index); harmonizationResponsibleFont.setFontHeightInPoints((short) 14); CellStyle unitHarmonizationResponsibleStyle = wb.createCellStyle(); unitHarmonizationResponsibleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationResponsibleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationResponsibleStyle.setFont(harmonizationResponsibleFont); unitHarmonizationResponsibleStyle.setAlignment(CellStyle.ALIGN_CENTER); /* ** END of styles ** */ /* ** Immutable IST header ** */ HSSFHeader header = sheetToWriteTo.getHeader(); header.setCenter(HSSFHeader.font("Arial", "Normal") + HSSFHeader.fontSize((short) 10)); header.setCenter("Instituto Superior Tcnico"); int rowIndex = START_ROW_INDEX; int cellIndex = START_CELL_INDEX; int firstLineIndex = rowIndex++; int secondLineIndex = rowIndex++; /* ** Write the first lines with the dates ** */ HSSFRow row = sheetToWriteTo.createRow(firstLineIndex); HSSFCell cell = row.createCell(cellIndex); cell.setCellValue("SIADAP - LISTA DE AVALIADORES " + unitToSearchIn.getYear()); cell.setCellStyle(firstLineStyle); sheetToWriteTo .addMergedRegion(new CellRangeAddress(firstLineIndex, firstLineIndex, cellIndex, cellIndex + 3)); // second line if (!considerQuotas) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(secondLineIndex); cell = row.createCell(cellIndex); cell.setCellValue("PESSOAL CONTRATADO PELA ADIST"); cell.setCellStyle(secondLineStyle); } /* ** write the IST logo ** */ int pictureIdx = wb.addPicture(istLogoBytes, Workbook.PICTURE_TYPE_PNG); HSSFPatriarch drawingPatriarch = sheetToWriteTo.createDrawingPatriarch(); ClientAnchor clientAnchor = creationHelper.createClientAnchor(); clientAnchor.setCol1(cellIndex); clientAnchor.setRow1(rowIndex); HSSFPicture picture = drawingPatriarch.createPicture(clientAnchor, pictureIdx); // let's give the next item some space rowIndex += 6; /* ** Dynamic IST footer ** */ HSSFFooter footer = sheetToWriteTo.getFooter(); footer.setLeft("Lista gerada em: " + HSSFFooter.date() + " " + HSSFFooter.time()); footer.setCenter(HSSFFooter.page()); footer.setRight("SIADAP - Lista de avaliadores " + unitToSearchIn.getYear()); for (UnitSiadapWrapper eachUnit : unitToSearchIn .getAllChildUnits(unitToSearchIn.getConfiguration().getUnitRelations())) { Collection<Person> harmonizationResponsibles = eachUnit.getHarmonizationResponsibles(); if (includeHarmonizationResponsibles && !harmonizationResponsibles.isEmpty()) { // let's add the section stating the responsible for // Harmonization cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // let's merge the row sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationTitleStyle); cell.setCellValue("UNIDADE DE HARMONIZAO: " + eachUnit.getName()); // a 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // each responsible has one of the following lines for (Person harmonizationResponsible : harmonizationResponsibles) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationResponsibleStyle); cell.setCellValue("RESPONS?VEL PELA HARMONIZAO: " + harmonizationResponsible.getName()); } // and let's add an extra 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // and a regular one! (skip one in the index) ++rowIndex; } if (eachUnit.getQuotaAwareTotalPeopleWorkingInUnit(false, considerQuotas) > 0) { row = sheetToWriteTo.createRow(++rowIndex); cellIndex = START_CELL_INDEX; // write the unit name and cost center String unitNameWithCC = eachUnit.getUnit().getPartyName().getContent(); if (eachUnit.getUnit().getPartyTypesSet().contains(PartyType.readBy("CostCenter"))) { unitNameWithCC += " - " + eachUnit.getUnit().getAcronym(); } cell = row.createCell(cellIndex++); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, --cellIndex, ++cellIndex)); cell.setCellValue(unitNameWithCC); cell.setCellStyle(costCenterStyle); /* **** write the Unit header ***** */ // restart the cell's index cellIndex = START_CELL_INDEX; // IST id avaliado int firstLineAfterUnitNameIndex = ++rowIndex; int secondLineAfterUnitNameIndex = ++rowIndex; row = sheetToWriteTo.createRow(firstLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.createRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge the IST id sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // Nome avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Nome"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); if (shouldIncludeUniverse) { // SIADAP do avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("SIADAP"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); } // Ist id do avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Avaliador"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); List<PersonSiadapWrapper> listToUse = (considerQuotas) ? eachUnit.getUnitEmployeesWithQuotas(false) : eachUnit.getUnitEmployeesWithoutQuotas(true); // now let's take care of exporting the persons for (PersonSiadapWrapper personWrapper : listToUse) { row = sheetToWriteTo.createRow(++rowIndex); // restart the cell's index cellIndex = START_CELL_INDEX; String istIdEvaluated = personWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluated); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatedPerson = personWrapper.getPerson().getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatedPerson); cell.setCellStyle(defaultTextNameStyle); if (shouldIncludeUniverse) { Siadap siadap = personWrapper.getSiadap(); String siadapUniverseToBeWritten = (siadap == null || siadap.getDefaultSiadapUniverse() == null) ? "No definido" : siadap.getDefaultSiadapUniverse().getLocalizedName(); cell = row.createCell(cellIndex++); cell.setCellValue(siadapUniverseToBeWritten); cell.setCellStyle(defaultTextNameStyle); } PersonSiadapWrapper evaluatorWrapper = personWrapper.getEvaluator(); String istIdEvaluator = evaluatorWrapper == null ? "-" : evaluatorWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluator); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatorWrapper = evaluatorWrapper == null ? "-" : evaluatorWrapper.getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatorWrapper); cell.setCellStyle(defaultTextNameStyle); } // let's make a bottom border on the last four cells for (int i = START_CELL_INDEX; i < START_CELL_INDEX + 4; i++) { cell = row.getCell(i); // let's diferentaitate between the IST-id and the name if (i == START_CELL_INDEX || i == START_CELL_INDEX + 2) // first // cell, // IST-ID // then. // or // third // cell // the // other // IST-ID { cell.setCellStyle(defaultTextIstIdLastStyle); } else { cell.setCellStyle(defaultTextNameLastStyle); } } row = sheetToWriteTo.createRow(++rowIndex); row = sheetToWriteTo.createRow(++rowIndex); } } sheetToWriteTo.autoSizeColumn(START_CELL_INDEX); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 1); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 2); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 3); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 4); // now let's resize the logo picture.resize(); }
From source file:mx.avanti.siract.ui.FiltrosBeanUI.java
public HSSFSheet cabezeraGeneralExcel(HSSFSheet sheet, int logouabc, HSSFCellStyle style) { /* Create the drawing container */ HSSFPatriarch drawing = sheet.createDrawingPatriarch(); /* Create an anchor point */ ClientAnchor my_anchor = new HSSFClientAnchor(); /* Define top left corner, and we can resize picture suitable from there */ my_anchor.setCol1(1);//from w ww. j av a 2s .c om my_anchor.setRow1(1); /* Invoke createPicture and pass the anchor point and ID */ HSSFPicture my_picture = drawing.createPicture(my_anchor, logouabc); /* Call resize method, which resizes the image */ double escalaRes = 1; my_picture.resize(escalaRes); //definiremos el estilo para estas Celdas //Definiremos el nombre de la escuela HSSFRow row = sheet.createRow(2); row.setHeight((short) 600); HSSFCell cell = row.createCell(3); cell.setCellValue("Universidad Autnoma de Baja California"); cell.setCellStyle(style); row = sheet.createRow(3); row.setHeight((short) 600); cell = row.createCell(3); cell.setCellValue("Facultad de Ingeniera"); cell.setCellStyle(style); row = sheet.createRow(4); row.setHeight((short) 600); cell = row.createCell(3); cell.setCellValue("Mexicali"); cell.setCellStyle(style); return sheet; }
From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java
License:Open Source License
private void insertPhotoArticleCatalogueInCell(Workbook wb, Sheet sheet, ArticleCatalogue articleCatalogue, int ligne, Row row, boolean mouvementStock) throws IOException { File fichierPhotoArticleCatalogue = catalogueService .getFilePieceJointe(articleCatalogue.getPhotoArticleCatalogue()); byte[] bytes = Files.readAllBytes(fichierPhotoArticleCatalogue.toPath()); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); Drawing drawing = sheet.createDrawingPatriarch(); CreationHelper helper = wb.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(10);/*from ww w .j a v a2s .c o m*/ anchor.setRow1(ligne); anchor.setCol2(11); anchor.setRow2(ligne + 1); drawing.createPicture(anchor, pictureIdx); construitLigneExportCatalogueGeneric(wb, row, null, 10, mouvementStock); }
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.//ww w. j ava 2 s .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].*)"); 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 w w w . jav a 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); }