Example usage for org.apache.poi.ss.usermodel CreationHelper createClientAnchor

List of usage examples for org.apache.poi.ss.usermodel CreationHelper createClientAnchor

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CreationHelper
  6. createClientAnchor

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel CreationHelper createClientAnchor.

Prototype

ClientAnchor createClientAnchor();

Source Link

Document

Creates a ClientAnchor.

Usage

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;/*w  ww.  j  av  a 2s.  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: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);/*from w w w  . j  a  v a2  s.co  m*/
        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 av a2 s . co  m

    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: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);/*  ww w .j a v  a2s .co m*/
    anchor.setRow1(ligne);
    anchor.setCol2(11);
    anchor.setRow2(ligne + 1);
    drawing.createPicture(anchor, pictureIdx);
    construitLigneExportCatalogueGeneric(wb, row, null, 10, mouvementStock);
}

From source file:net.ceos.project.poi.annotated.core.CellStyleHandler.java

License:Apache License

/**
 * Apply the cell comment to a cell.//from  ww w  .  j  a v a 2s .  c  o  m
 * 
 * @param configCriteria
 *            the {@link XConfigCriteria} object
 * @param isAuthorizedComment
 *            the extension file
 * @param cell
 *            the {@link Cell}
 */
protected static void applyComment(final XConfigCriteria configCriteria, final Boolean isAuthorizedComment,
        final Cell cell) {
    if (StringUtils.isBlank(configCriteria.getElement().commentRules())
            || StringUtils.isNotBlank(configCriteria.getElement().commentRules()) && isAuthorizedComment) {
        if (ExtensionFileType.XLS.equals(configCriteria.getExtension())) {
            final Map<Sheet, HSSFPatriarch> drawingPatriarches = new HashMap<>();

            CreationHelper createHelper = cell.getSheet().getWorkbook().getCreationHelper();
            HSSFSheet sheet = (HSSFSheet) cell.getSheet();
            HSSFPatriarch drawingPatriarch = drawingPatriarches.get(sheet);
            if (drawingPatriarch == null) {
                drawingPatriarch = sheet.createDrawingPatriarch();
                drawingPatriarches.put(sheet, drawingPatriarch);
            }

            Comment comment = drawingPatriarch
                    .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
            comment.setString(createHelper.createRichTextString(configCriteria.getElement().comment()));

            cell.setCellComment(comment);

        } else if (ExtensionFileType.XLSX.equals(configCriteria.getExtension())) {
            CreationHelper factory = configCriteria.getWorkbook().getCreationHelper();

            Drawing drawing = cell.getSheet().createDrawingPatriarch();

            ClientAnchor anchor = factory.createClientAnchor();

            Comment comment = drawing.createCellComment(anchor);
            RichTextString str = factory.createRichTextString(configCriteria.getElement().comment());
            comment.setString(str);

            cell.setCellComment(comment);
        }
    }
}

From source file:nl.b3p.viewer.features.ExcelDownloader.java

License:Open Source License

@Override
public void init() throws IOException {
    wb = new XSSFWorkbook();

    styles = createStyles(wb);//  ww  w .  j a  va  2s  .  c om

    sheet = wb.createSheet(fs.getName().toString());

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    //the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(15f);
    int colNum = 0;
    Drawing drawing = sheet.createDrawingPatriarch();

    CreationHelper factory = wb.getCreationHelper();
    // When the comment box is visible, have it show in a 1x3 space
    ClientAnchor anchor = factory.createClientAnchor();
    for (ConfiguredAttribute configuredAttribute : attributes) {
        if (configuredAttribute.isVisible()) {
            Cell cell = headerRow.createCell(colNum);
            String alias = attributeAliases.get(configuredAttribute.getAttributeName());
            cell.setCellValue(alias);
            if (!alias.equals(configuredAttribute.getAttributeName())) {
                Comment comment = drawing.createCellComment(anchor);
                RichTextString str = factory.createRichTextString(configuredAttribute.getAttributeName());
                comment.setString(str);
                cell.setCellComment(comment);
            }
            cell.setCellStyle(styles.get("header"));
            sheet.autoSizeColumn(colNum);
            colNum++;
        }
    }

    //freeze the first row
    sheet.createFreezePane(0, 1);
    currentRow = 1;
}

From source file:org.bbreak.excella.reports.tag.ImageParamParser.java

License:Open Source License

/**
 * ??//  w  w w  .  j  a v  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);

}

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);// www . j av a 2 s . com
    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 .j  a  v a 2  s  .  co  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 *//*  ww  w.j  a  v a  2s.  co m*/
    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());
}