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

Example 6 with CreationHelper

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

the class TestXSSFHyperlink method testLoadSave.

@Test
public void testLoadSave() {
    XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook("WithMoreVariousData.xlsx");
    CreationHelper createHelper = workbook.getCreationHelper();
    assertEquals(3, workbook.getNumberOfSheets());
    XSSFSheet sheet = workbook.getSheetAt(0);
    // Check hyperlinks
    assertEquals(4, sheet.getNumHyperlinks());
    doTestHyperlinkContents(sheet);
    // Write out, and check
    // Load up again, check all links still there
    XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(workbook);
    assertEquals(3, wb2.getNumberOfSheets());
    assertNotNull(wb2.getSheetAt(0));
    assertNotNull(wb2.getSheetAt(1));
    assertNotNull(wb2.getSheetAt(2));
    sheet = wb2.getSheetAt(0);
    // Check hyperlinks again
    assertEquals(4, sheet.getNumHyperlinks());
    doTestHyperlinkContents(sheet);
    // Add one more, and re-check
    Row r17 = sheet.createRow(17);
    Cell r17c = r17.createCell(2);
    Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
    hyperlink.setAddress("http://poi.apache.org/spreadsheet/");
    hyperlink.setLabel("POI SS Link");
    r17c.setHyperlink(hyperlink);
    assertEquals(5, sheet.getNumHyperlinks());
    doTestHyperlinkContents(sheet);
    assertEquals(HyperlinkType.URL, sheet.getRow(17).getCell(2).getHyperlink().getTypeEnum());
    assertEquals("POI SS Link", sheet.getRow(17).getCell(2).getHyperlink().getLabel());
    assertEquals("http://poi.apache.org/spreadsheet/", sheet.getRow(17).getCell(2).getHyperlink().getAddress());
    // Save and re-load once more
    XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2);
    assertEquals(3, wb3.getNumberOfSheets());
    assertNotNull(wb3.getSheetAt(0));
    assertNotNull(wb3.getSheetAt(1));
    assertNotNull(wb3.getSheetAt(2));
    sheet = wb3.getSheetAt(0);
    assertEquals(5, sheet.getNumHyperlinks());
    doTestHyperlinkContents(sheet);
    assertEquals(HyperlinkType.URL, sheet.getRow(17).getCell(2).getHyperlink().getTypeEnum());
    assertEquals("POI SS Link", sheet.getRow(17).getCell(2).getHyperlink().getLabel());
    assertEquals("http://poi.apache.org/spreadsheet/", sheet.getRow(17).getCell(2).getHyperlink().getAddress());
}

Example 7 with CreationHelper

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

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_mergeHyperlink.

@Test
public final void testCopyCellFrom_CellCopyPolicy_mergeHyperlink() throws IOException {
    setUp_testCopyCellFrom_CellCopyPolicy();
    final Workbook wb = srcCell.getSheet().getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    srcCell.setCellValue("URL LINK");
    Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    destCell.setHyperlink(link);
    // Set link cell style (optional)
    CellStyle hlinkStyle = wb.createCellStyle();
    Font hlinkFont = wb.createFont();
    hlinkFont.setUnderline(Font.U_SINGLE);
    hlinkFont.setColor(IndexedColors.BLUE.getIndex());
    hlinkStyle.setFont(hlinkFont);
    destCell.setCellStyle(hlinkStyle);
    // Pre-condition assumptions. This test is broken if either of these fail.
    assertSame("unit test assumes srcCell and destCell are on the same sheet", srcCell.getSheet(), destCell.getSheet());
    assertNull(srcCell.getHyperlink());
    // Merge hyperlink - since srcCell doesn't have a hyperlink, destCell's hyperlink is not overwritten (cleared).
    final CellCopyPolicy policy = new CellCopyPolicy.Builder().mergeHyperlink(true).copyHyperlink(false).build();
    destCell.copyCellFrom(srcCell, policy);
    assertNull(srcCell.getHyperlink());
    assertNotNull(destCell.getHyperlink());
    assertSame(link, destCell.getHyperlink());
    List<XSSFHyperlink> links;
    links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 1, links.size());
    assertEquals("source hyperlink", new CellReference(destCell).formatAsString(), links.get(0).getCellRef());
    // Merge destCell's hyperlink to srcCell. Since destCell does have a hyperlink, this should copy destCell's hyperlink to srcCell.
    srcCell.copyCellFrom(destCell, policy);
    assertNotNull(srcCell.getHyperlink());
    assertNotNull(destCell.getHyperlink());
    links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 2, links.size());
    assertEquals("dest hyperlink", new CellReference(destCell).formatAsString(), links.get(0).getCellRef());
    assertEquals("source hyperlink", new CellReference(srcCell).formatAsString(), links.get(1).getCellRef());
    wb.close();
}

Example 8 with CreationHelper

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

the class ExcelWriterStep method createCellComment.

private Comment createCellComment(String author, String comment) {
    // comments only supported for XLSX
    if (data.sheet instanceof XSSFSheet) {
        CreationHelper factory = data.wb.getCreationHelper();
        Drawing drawing = data.sheet.createDrawingPatriarch();
        ClientAnchor anchor = factory.createClientAnchor();
        Comment cmt = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(comment);
        cmt.setString(str);
        cmt.setAuthor(author);
        return cmt;
    }
    return null;
}

Example 9 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project tcom-poi by ai-coders.

the class AbstractWriter method writeComment.

@Override
public void writeComment(String commentStr) {
    if (AiStringUtil.isEmpty(commentStr)) {
        return;
    }
    CreationHelper factory = workbook.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = factory.createClientAnchor();
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString(commentStr);
    comment.setString(str);
    // comment.setAuthor("Ai Coder");
    cell.setCellComment(comment);
}

Example 10 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project jgnash by ccavanaugh.

the class BudgetResultsExport method exportBudgetResultsModel.

/**
     * Exports a {@code BudgetResultsModel} to a spreadsheet.
     * 
     * @param file File to save to
     * @param model Results model to export
     * @return Error message
     */
public static String exportBudgetResultsModel(final Path file, final BudgetResultsModel model) {
    String message = null;
    final ResourceBundle rb = ResourceUtils.getBundle();
    final String extension = FileUtils.getFileExtension(file.toString());
    try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) {
        final CreationHelper createHelper = wb.getCreationHelper();
        // create a new sheet
        final Sheet s = wb.createSheet(model.getBudget().getName());
        // create header cell styles
        final CellStyle headerStyle = wb.createCellStyle();
        // create 2 fonts objects
        final Font amountFont = wb.createFont();
        final Font headerFont = wb.createFont();
        amountFont.setFontHeightInPoints((short) 10);
        amountFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setFontHeightInPoints((short) 11);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // Set the other cell style and formatting
        headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headerStyle.setBorderTop(CellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headerStyle.setBorderRight(CellStyle.BORDER_THIN);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        DataFormat df_header = wb.createDataFormat();
        headerStyle.setDataFormat(df_header.getFormat("text"));
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        int row = 0;
        Row r = s.createRow(row);
        // create period headers
        for (int i = 0; i < model.getDescriptorList().size(); i++) {
            Cell c = r.createCell(i * 3 + 1);
            c.setCellValue(createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription()));
            c.setCellStyle(headerStyle);
            s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3));
        }
        {
            int col = model.getDescriptorList().size() * 3 + 1;
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary")));
            c.setCellStyle(headerStyle);
            s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2));
        }
        // create results header columns
        row++;
        r = s.createRow(row);
        {
            Cell c = r.createCell(0);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account")));
            c.setCellStyle(headerStyle);
            for (int i = 0; i <= model.getDescriptorList().size(); i++) {
                c = r.createCell(i * 3 + 1);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted")));
                c.setCellStyle(headerStyle);
                c = r.createCell(i * 3 + 2);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Actual")));
                c.setCellStyle(headerStyle);
                c = r.createCell(i * 3 + 3);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining")));
                c.setCellStyle(headerStyle);
            }
        }
        // must sort the accounts, otherwise child structure is not correct
        List<Account> accounts = new ArrayList<>(model.getAccounts());
        accounts.sort(Comparators.getAccountByTreePosition(Comparators.getAccountByCode()));
        // create account rows
        for (final Account account : accounts) {
            final DataFormat df = wb.createDataFormat();
            final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
            final String pattern = format.toLocalizedPattern().replace("¤", account.getCurrencyNode().getPrefix());
            final CellStyle amountStyle = wb.createCellStyle();
            amountStyle.setFont(amountFont);
            amountStyle.setDataFormat(df.getFormat(pattern));
            // Sets cell indentation, only impacts display if users changes the cell formatting to be left aligned.
            amountStyle.setIndention((short) (model.getDepth(account) * 2));
            row++;
            int col = 0;
            r = s.createRow(row);
            CellStyle cs = wb.createCellStyle();
            cs.cloneStyleFrom(headerStyle);
            cs.setAlignment(CellStyle.ALIGN_LEFT);
            cs.setIndention((short) (model.getDepth(account) * 2));
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(account.getName()));
            c.setCellStyle(cs);
            List<CellReference> budgetedRefList = new ArrayList<>();
            List<CellReference> changeRefList = new ArrayList<>();
            List<CellReference> remainingRefList = new ArrayList<>();
            for (int i = 0; i < model.getDescriptorList().size(); i++) {
                BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getBudgeted().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference budgetedRef = new CellReference(row, col);
                budgetedRefList.add(budgetedRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getChange().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference changeRef = new CellReference(row, col);
                changeRefList.add(changeRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_FORMULA);
                c.setCellStyle(amountStyle);
                c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());
                CellReference remainingRef = new CellReference(row, col);
                remainingRefList.add(remainingRef);
            }
            // add summary columns                               
            addSummaryCell(r, ++col, budgetedRefList, amountStyle);
            addSummaryCell(r, ++col, changeRefList, amountStyle);
            addSummaryCell(r, ++col, remainingRefList, amountStyle);
        }
        // add group summary rows
        for (final AccountGroup group : model.getAccountGroupList()) {
            final DataFormat df = wb.createDataFormat();
            final CellStyle amountStyle = wb.createCellStyle();
            amountStyle.setFont(amountFont);
            amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            amountStyle.setBorderBottom(CellStyle.BORDER_THIN);
            amountStyle.setBorderTop(CellStyle.BORDER_THIN);
            amountStyle.setBorderLeft(CellStyle.BORDER_THIN);
            amountStyle.setBorderRight(CellStyle.BORDER_THIN);
            final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
            final String pattern = format.toLocalizedPattern().replace("¤", model.getBaseCurrency().getPrefix());
            amountStyle.setDataFormat(df.getFormat(pattern));
            row++;
            int col = 0;
            r = s.createRow(row);
            CellStyle cs = wb.createCellStyle();
            cs.cloneStyleFrom(headerStyle);
            cs.setAlignment(CellStyle.ALIGN_LEFT);
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(group.toString()));
            c.setCellStyle(cs);
            List<CellReference> budgetedRefList = new ArrayList<>();
            List<CellReference> changeRefList = new ArrayList<>();
            List<CellReference> remainingRefList = new ArrayList<>();
            for (int i = 0; i < model.getDescriptorList().size(); i++) {
                BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getBudgeted().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference budgetedRef = new CellReference(row, col);
                budgetedRefList.add(budgetedRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getChange().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference changeRef = new CellReference(row, col);
                changeRefList.add(changeRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_FORMULA);
                c.setCellStyle(amountStyle);
                c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());
                CellReference remainingRef = new CellReference(row, col);
                remainingRefList.add(remainingRef);
            }
            // add summary columns                               
            addSummaryCell(r, ++col, budgetedRefList, amountStyle);
            addSummaryCell(r, ++col, changeRefList, amountStyle);
            addSummaryCell(r, ++col, remainingRefList, amountStyle);
        }
        // force evaluation
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();
        short columnCount = s.getRow(1).getLastCellNum();
        // autosize all of the columns + 10 pixels
        for (int i = 0; i <= columnCount; i++) {
            s.autoSizeColumn(i);
            s.setColumnWidth(i, s.getColumnWidth(i) + 10);
        }
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles());
        // Save
        String filename = file.toString();
        if (wb instanceof XSSFWorkbook) {
            filename = FileUtils.stripFileExtension(filename) + ".xlsx";
        } else {
            filename = FileUtils.stripFileExtension(filename) + ".xls";
        }
        try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) {
            wb.write(out);
        } catch (final Exception e) {
            Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
            message = e.getLocalizedMessage();
        }
    } catch (IOException e) {
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
    return message;
}