Examples with CreationHelper org.apache.poi.ss.usermodel.CreationHelper used on opensource projects

Example 31 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project pentaho-kettle by pentaho.

the class ExcelWriterStep method writeField.

// VisibleForTesting
void writeField(Object v, ValueMetaInterface vMeta, ExcelWriterStepField excelField, Row xlsRow, int posX, Object[] row, int fieldNr, boolean isTitle) throws KettleException {
    try {
        boolean cellExisted = true;
        // get the cell
        Cell cell = xlsRow.getCell(posX);
        if (cell == null) {
            cellExisted = false;
            cell = xlsRow.createCell(posX);
        }
        // if cell existed and existing cell's styles should not be changed, don't
        if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {
            // if the style of this field is cached, reuse it
            if (!isTitle && data.getCachedStyle(fieldNr) != null) {
                cell.setCellStyle(data.getCachedStyle(fieldNr));
            } else {
                // apply style if requested
                if (excelField != null) {
                    // determine correct cell for title or data rows
                    String styleRef = null;
                    if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) {
                        styleRef = excelField.getStyleCell();
                    } else if (isTitle && !Utils.isEmpty(excelField.getTitleStyleCell())) {
                        styleRef = excelField.getTitleStyleCell();
                    }
                    if (styleRef != null) {
                        Cell styleCell = getCellFromReference(styleRef);
                        if (styleCell != null && cell != styleCell) {
                            cell.setCellStyle(styleCell.getCellStyle());
                        }
                    }
                }
                // set cell format as specified, specific format overrides cell specification
                if (!isTitle && excelField != null && !Utils.isEmpty(excelField.getFormat()) && !excelField.getFormat().startsWith("Image")) {
                    setDataFormat(excelField.getFormat(), cell);
                }
                // cache it for later runs
                if (!isTitle) {
                    data.cacheStyle(fieldNr, cell.getCellStyle());
                }
            }
        }
        // create link on cell if requested
        if (!isTitle && excelField != null && data.linkfieldnrs[fieldNr] >= 0) {
            String link = data.inputRowMeta.getValueMeta(data.linkfieldnrs[fieldNr]).getString(row[data.linkfieldnrs[fieldNr]]);
            if (!Utils.isEmpty(link)) {
                CreationHelper ch = data.wb.getCreationHelper();
                // set the link on the cell depending on link type
                Hyperlink hyperLink = null;
                if (link.startsWith("http:") || link.startsWith("https:") || link.startsWith("ftp:")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.URL);
                    hyperLink.setLabel("URL Link");
                } else if (link.startsWith("mailto:")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.EMAIL);
                    hyperLink.setLabel("Email Link");
                } else if (link.startsWith("'")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.DOCUMENT);
                    hyperLink.setLabel("Link within this document");
                } else {
                    hyperLink = ch.createHyperlink(HyperlinkType.FILE);
                    hyperLink.setLabel("Link to a file");
                }
                hyperLink.setAddress(link);
                cell.setHyperlink(hyperLink);
                // if cell existed and existing cell's styles should not be changed, don't
                if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {
                    if (data.getCachedLinkStyle(fieldNr) != null) {
                        cell.setCellStyle(data.getCachedLinkStyle(fieldNr));
                    } else {
                        // CellStyle style = cell.getCellStyle();
                        Font origFont = data.wb.getFontAt(cell.getCellStyle().getFontIndex());
                        Font hlink_font = data.wb.createFont();
                        // reporduce original font characteristics
                        hlink_font.setBold(origFont.getBold());
                        hlink_font.setCharSet(origFont.getCharSet());
                        hlink_font.setFontHeight(origFont.getFontHeight());
                        hlink_font.setFontName(origFont.getFontName());
                        hlink_font.setItalic(origFont.getItalic());
                        hlink_font.setStrikeout(origFont.getStrikeout());
                        hlink_font.setTypeOffset(origFont.getTypeOffset());
                        // make it blue and underlined
                        hlink_font.setUnderline(Font.U_SINGLE);
                        hlink_font.setColor(IndexedColors.BLUE.getIndex());
                        CellStyle style = cell.getCellStyle();
                        style.setFont(hlink_font);
                        cell.setCellStyle(style);
                        data.cacheLinkStyle(fieldNr, cell.getCellStyle());
                    }
                }
            }
        }
        // create comment on cell if requrested
        if (!isTitle && excelField != null && data.commentfieldnrs[fieldNr] >= 0 && data.wb instanceof XSSFWorkbook) {
            String comment = data.inputRowMeta.getValueMeta(data.commentfieldnrs[fieldNr]).getString(row[data.commentfieldnrs[fieldNr]]);
            if (!Utils.isEmpty(comment)) {
                String author = data.commentauthorfieldnrs[fieldNr] >= 0 ? data.inputRowMeta.getValueMeta(data.commentauthorfieldnrs[fieldNr]).getString(row[data.commentauthorfieldnrs[fieldNr]]) : "Kettle PDI";
                cell.setCellComment(createCellComment(author, comment));
            }
        }
        // cell is getting a formula value or static content
        if (!isTitle && excelField != null && excelField.isFormula()) {
            // formula case
            cell.setCellFormula(vMeta.getString(v));
        } else {
            // static content case
            switch(vMeta.getType()) {
                case ValueMetaInterface.TYPE_DATE:
                    if (v != null && vMeta.getDate(v) != null) {
                        cell.setCellValue(vMeta.getDate(v));
                    }
                    break;
                case ValueMetaInterface.TYPE_BOOLEAN:
                    if (v != null) {
                        cell.setCellValue(vMeta.getBoolean(v));
                    }
                    break;
                case ValueMetaInterface.TYPE_STRING:
                case ValueMetaInterface.TYPE_BINARY:
                    if (v != null) {
                        cell.setCellValue(vMeta.getString(v));
                    }
                    break;
                case ValueMetaInterface.TYPE_BIGNUMBER:
                case ValueMetaInterface.TYPE_NUMBER:
                case ValueMetaInterface.TYPE_INTEGER:
                    if (v != null) {
                        cell.setCellValue(vMeta.getNumber(v));
                    }
                    break;
                default:
                    break;
            }
        }
    } catch (Exception e) {
        logError("Error writing field (" + data.posX + "," + data.posY + ") : " + e.toString());
        logError(Const.getStackTracker(e));
        throw new KettleException(e);
    }
}

Example 32 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project CzechIdMng by bcvsolutions.

the class IdentityReportXlsxRenderer method render.

@Override
public InputStream render(RptReportDto report) {
    try {
        // read json stream
        JsonParser jParser = getMapper().getFactory().createParser(getReportData(report));
        XSSFWorkbook workbook = new XSSFWorkbook();
        CreationHelper createHelper = workbook.getCreationHelper();
        XSSFSheet sheet = workbook.createSheet("Report");
        // header
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Username");
        cell = row.createCell(1);
        cell.setCellValue("First name");
        cell = row.createCell(2);
        cell.setCellValue("Last name");
        cell = row.createCell(3);
        cell.setCellValue("Disabled");
        int rowNum = 1;
        // json is array of identities
        if (jParser.nextToken() == JsonToken.START_ARRAY) {
            // write single identity
            while (jParser.nextToken() == JsonToken.START_OBJECT) {
                IdmIdentityDto identity = getMapper().readValue(jParser, IdmIdentityDto.class);
                row = sheet.createRow(rowNum++);
                cell = row.createCell(0);
                cell.setCellValue(identity.getUsername());
                cell = row.createCell(1);
                cell.setCellValue(identity.getFirstName());
                cell = row.createCell(2);
                cell.setCellValue(identity.getLastName());
                cell = row.createCell(3);
                cell.setCellValue(identity.isDisabled());
            }
        }
        // close json stream
        jParser.close();
        // 
        // footer info about more available reports
        rowNum++;
        rowNum++;
        row = sheet.createRow(rowNum++);
        cell = row.createCell(0);
        cell.setCellValue("More reports are available in reports module:");
        row = sheet.createRow(rowNum++);
        cell = row.createCell(0);
        cell.setCellValue("https://wiki.czechidm.com/devel/documentation/modules_reports");
        Hyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_URL);
        link.setAddress("https://wiki.czechidm.com/devel/documentation/modules_reports");
        cell.setHyperlink(link);
        // close and return input stream
        return getInputStream(workbook);
    } catch (IOException ex) {
        throw new ReportRenderException(report.getName(), ex);
    }
}

Example 33 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project swift by luastar.

the class TestController method download.

@HttpService("/download")
public void download(HttpRequest request, HttpResponse response) {
    try {
        Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("sheet1");
        Row row = sheet.createRow((short) 0);
        row.createCell(0).setCellValue(createHelper.createRichTextString("aaa"));
        row.createCell(1).setCellValue(createHelper.createRichTextString("bbb"));
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        wb.write(outputStream);
        response.setResponseContentTypeStream("aaa.xlsx");
        response.setOutputStream(outputStream);
    } catch (IOException e) {
        logger.error(e.getMessage(), e);
    }
}

Example 34 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project Aspose.Cells-for-Java by aspose-cells.

the class ApacheAddCommentsToCell method main.

public static void main(String[] args) throws IOException {
    // The path to the documents directory.
    String dataDir = Utils.getDataDir(ApacheAddCommentsToCell.class);
    // or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    CreationHelper factory = wb.getCreationHelper();
    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(3);
    Cell cell = row.createCell(5);
    cell.setCellValue("F4");
    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(row.getRowNum());
    anchor.setRow2(row.getRowNum() + 3);
    // Create the comment and set the text+author
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString("Hello, World!");
    comment.setString(str);
    comment.setAuthor("Apache POI");
    // Assign the comment to the cell
    cell.setCellComment(comment);
    String fname = "AsposeComment-xssf.xls";
    if (wb instanceof XSSFWorkbook)
        fname += "x";
    FileOutputStream out = new FileOutputStream(dataDir + fname);
    wb.write(out);
    out.close();
    System.out.println("Done.");
}

Example 35 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project Aspose.Cells-for-Java by aspose-cells.

the class ApacheHyperlinks method main.

public static void main(String[] args) throws Exception {
    // The path to the documents directory.
    String dataDir = Utils.getDataDir(ApacheHyperlinks.class);
    // or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    // cell style for hyperlinks
    // by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    // URL
    cell = sheet.createRow(0).createCell((short) 0);
    cell.setCellValue("URL Link");
    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    // link to a file in the current directory
    cell = sheet.createRow(1).createCell((short) 0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    // e-mail link
    cell = sheet.createRow(2).createCell((short) 0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
    // note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    // link to a place in this workbook
    // create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell");
    cell = sheet.createRow(3).createCell((short) 0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);
    FileOutputStream out = new FileOutputStream(dataDir + "ApacheHyperlinks.xlsx");
    wb.write(out);
    out.close();
    System.out.println("Done..");
}