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

Example 11 with CreationHelper

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

the class AccountExport method exportAccount.

public static void exportAccount(final Account account, final String[] columnNames, final LocalDate startDate, final LocalDate endDate, final File file) {
    Objects.requireNonNull(account);
    Objects.requireNonNull(startDate);
    Objects.requireNonNull(endDate);
    Objects.requireNonNull(file);
    Objects.requireNonNull(columnNames);
    final String extension = FileUtils.getFileExtension(file.getAbsolutePath());
    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(account.getName());
        // create 2 fonts objects
        final Font defaultFont = wb.createFont();
        final Font headerFont = wb.createFont();
        defaultFont.setFontHeightInPoints((short) 10);
        defaultFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setFontHeightInPoints((short) 11);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // create header cell styles
        final CellStyle headerStyle = wb.createCellStyle();
        // 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);
        final CellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yy"));
        dateStyle.setFont(defaultFont);
        final CellStyle timestampStyle = wb.createCellStyle();
        timestampStyle.setDataFormat(createHelper.createDataFormat().getFormat("YYYY-MM-DD HH:MM:SS"));
        timestampStyle.setFont(defaultFont);
        final CellStyle textStyle = wb.createCellStyle();
        textStyle.setFont(defaultFont);
        final CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(defaultFont);
        amountStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
        final String pattern = format.toLocalizedPattern().replace("¤", account.getCurrencyNode().getPrefix());
        final DataFormat df = wb.createDataFormat();
        amountStyle.setDataFormat(df.getFormat(pattern));
        // Create headers
        int row = 0;
        Row r = s.createRow(row);
        for (int i = 0; i < columnNames.length; i++) {
            Cell c = r.createCell(i);
            c.setCellValue(createHelper.createRichTextString(columnNames[i]));
            c.setCellStyle(headerStyle);
        }
        // Dump the transactions
        for (final Transaction transaction : account.getTransactions(startDate, endDate)) {
            r = s.createRow(++row);
            int col = 0;
            // date
            Cell c = r.createCell(col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(DateUtils.asDate(transaction.getLocalDate()));
            c.setCellStyle(dateStyle);
            // timestamp
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(DateUtils.asDate(transaction.getTimestamp()));
            c.setCellStyle(timestampStyle);
            // number
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getNumber());
            c.setCellStyle(textStyle);
            // payee
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getPayee());
            c.setCellStyle(textStyle);
            // memo
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getMemo());
            c.setCellStyle(textStyle);
            // account
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(getAccountColumnValue(transaction, account));
            c.setCellStyle(textStyle);
            // clr
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getReconciled(account).toString());
            c.setCellStyle(textStyle);
            final BigDecimal amount = transaction.getAmount(account);
            // increase
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            if (amount.signum() >= 0) {
                c.setCellValue(amount.doubleValue());
            }
            c.setCellStyle(amountStyle);
            // decrease
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            if (amount.signum() < 0) {
                c.setCellValue(amount.abs().doubleValue());
            }
            c.setCellStyle(amountStyle);
            // balance
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(account.getBalanceAt(transaction).doubleValue());
            c.setCellStyle(amountStyle);
        }
        // autosize the column widths
        final 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(AccountExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles());
        // Save
        final String filename;
        if (wb instanceof XSSFWorkbook) {
            filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xlsx";
        } else {
            filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xls";
        }
        try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) {
            wb.write(out);
        } catch (final Exception e) {
            Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
        }
    } catch (final IOException e) {
        Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

Example 12 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project data-prep by Talend.

the class XlsWriter method writeHeader.

/**
 * writing headers so first row.
 */
private void writeHeader(RowMetadata metadata) {
    CreationHelper createHelper = this.workbook.getCreationHelper();
    Row headerRow = this.sheet.createRow(rowIdx++);
    int cellIdx = 0;
    for (ColumnMetadata columnMetadata : metadata.getColumns()) {
        // TODO apply some formatting as it's an header cell?
        headerRow.createCell(cellIdx++).setCellValue(createHelper.createRichTextString(columnMetadata.getName()));
    }
}

Example 13 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project nebula.widgets.nattable by eclipse.

the class PoiExcelExporter method getExcelCellStyle.

private CellStyle getExcelCellStyle(Color fg, Color bg, FontData fontData, String dataFormat, int hAlign, int vAlign, boolean vertical, boolean wrap, boolean border) {
    CellStyle xlCellStyle = this.xlCellStyles.get(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical, wrap, border));
    if (xlCellStyle == null) {
        xlCellStyle = this.xlWorkbook.createCellStyle();
        if (this.applyBackgroundColor) {
            // Note: xl fill foreground = background
            setFillForegroundColor(xlCellStyle, bg);
            xlCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        Font xlFont = this.xlWorkbook.createFont();
        setFontColor(xlFont, fg);
        xlFont.setFontName(fontData.getName());
        xlFont.setFontHeightInPoints((short) fontData.getHeight());
        xlCellStyle.setFont(xlFont);
        if (vertical)
            xlCellStyle.setRotation((short) 90);
        if (wrap)
            xlCellStyle.setWrapText(wrap);
        if (border) {
            xlCellStyle.setBorderTop(CellStyle.BORDER_THIN);
            xlCellStyle.setBorderRight(CellStyle.BORDER_THIN);
            xlCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            xlCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        }
        switch(hAlign) {
            case SWT.CENTER:
                xlCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                break;
            case SWT.LEFT:
                xlCellStyle.setAlignment(CellStyle.ALIGN_LEFT);
                break;
            case SWT.RIGHT:
                xlCellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
                break;
        }
        switch(vAlign) {
            case SWT.TOP:
                xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
                break;
            case SWT.CENTER:
                xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                break;
            case SWT.BOTTOM:
                xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
                break;
        }
        if (dataFormat != null) {
            CreationHelper createHelper = this.xlWorkbook.getCreationHelper();
            xlCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(dataFormat));
        }
        this.xlCellStyles.put(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical, wrap, border), xlCellStyle);
    }
    return xlCellStyle;
}

Example 14 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project cerberus-source by cerberustesting.

the class ExportServiceFactory method createRow.

private int createRow(String test, HashMap<String, List<TestCaseExecution>> executionsPerTestCase, Sheet sheet, int currentIndex, List<String> mapCountries) {
    int lastRow = currentIndex + executionsPerTestCase.size();
    int current = currentIndex;
    TreeMap<String, List<TestCaseExecution>> sortedKeys = new TreeMap<String, List<TestCaseExecution>>(executionsPerTestCase);
    // Create new style
    CellStyle wrapStyle = sheet.getColumnStyle(0);
    // Set wordwrap
    wrapStyle.setWrapText(true);
    for (String testCaseKey : sortedKeys.keySet()) {
        List<String> browserEnvironment = new LinkedList<String>();
        String application;
        String description;
        Row r = sheet.createRow(current);
        List<TestCaseExecution> executionList = executionsPerTestCase.get(testCaseKey);
        Cell testCell = r.createCell(0);
        testCell.setCellValue(test);
        testCell.setCellStyle(wrapStyle);
        r.createCell(1).setCellValue(testCaseKey);
        // gets the first object to retrieve the application - at least exists one test case execution
        if (executionList.isEmpty()) {
            application = "N/D";
            description = "N/D";
        } else {
            application = executionList.get(0).getApplication();
            description = executionList.get(0).getTestCaseObj().getBehaviorOrValueExpected();
        }
        // Sets the application and description
        r.createCell(2).setCellValue(application);
        r.createCell(3).setCellValue(description);
        int rowStartedTestCaseInfo = current;
        for (TestCaseExecution exec : executionList) {
            if (browserEnvironment.isEmpty()) {
                browserEnvironment.add(exec.getEnvironment() + "_" + exec.getBrowser());
                r.createCell(4).setCellValue(exec.getEnvironment());
                r.createCell(5).setCellValue(exec.getBrowser());
            } else {
                int index = browserEnvironment.indexOf(exec.getEnvironment() + "_" + exec.getBrowser());
                // Does not exist any information about browser and environment
                if (browserEnvironment.indexOf(exec.getEnvironment() + "_" + exec.getBrowser()) == -1) {
                    // need to add another row with the same characteristics
                    r = sheet.createRow(++current);
                    r.createCell(0).setCellValue(test);
                    r.createCell(1).setCellValue(testCaseKey);
                    r.createCell(2).setCellValue(application);
                    r.createCell(3).setCellValue(description);
                    r.createCell(4).setCellValue(exec.getEnvironment());
                    r.createCell(5).setCellValue(exec.getBrowser());
                    browserEnvironment.add(exec.getEnvironment() + "_" + exec.getBrowser());
                } else {
                    // there is information about the browser and environment
                    Row rowExisting = sheet.getRow(rowStartedTestCaseInfo + index);
                    r = rowExisting;
                }
            }
            // TODO:FN tirar daqui estes valores
            int indexOfCountry = mapCountries.indexOf(exec.getCountry()) + 6;
            Cell executionResult = r.createCell(indexOfCountry);
            executionResult.setCellValue(exec.getControlStatus());
            // Create hyperling
            CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
            CellStyle hlinkstyle = sheet.getWorkbook().createCellStyle();
            Font hlinkfont = sheet.getWorkbook().createFont();
            hlinkfont.setUnderline(XSSFFont.U_SINGLE);
            hlinkfont.setColor(HSSFColor.BLUE.index);
            hlinkstyle.setFont(hlinkfont);
            Hyperlink link = (Hyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
            link.setAddress("http://www.tutorialspoint.com/");
            executionResult.setHyperlink((Hyperlink) link);
            executionResult.setCellStyle(hlinkstyle);
        }
        current++;
    }
    /*CellRangeAddress range = new CellRangeAddress(currentIndex, lastRow, 0, 0);
         sheet.addMergedRegion(range);*/
    return lastRow;
}

Example 15 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project acs-aem-commons by Adobe-Consulting-Services.

the class ProcessErrorReportExcelServlet method createSpreadsheet.

@SuppressWarnings("squid:S3776")
protected Workbook createSpreadsheet(ManagedProcess report) {
    Workbook wb = new XSSFWorkbook();
    String name = report.getName();
    for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) {
        name = StringUtils.remove(name, ch);
    }
    Sheet sheet = wb.createSheet(name);
    sheet.createFreezePane(0, 1, 0, 1);
    Row headerRow = sheet.createRow(0);
    CellStyle headerStyle = createHeaderStyle(wb);
    CellStyle dateStyle = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss"));
    for (String columnName : Arrays.asList("Time", "Path", "Error", "Stack trace")) {
        Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells());
        headerCell.setCellValue(columnName);
        headerCell.setCellStyle(headerStyle);
    }
    Collection<ArchivedProcessFailure> rows = report.getReportedErrors();
    // make rows, don't forget the header row
    for (ArchivedProcessFailure error : rows) {
        Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
        Cell c;
        c = row.createCell(0);
        c.setCellValue(error.time);
        c.setCellStyle(dateStyle);
        c = row.createCell(1);
        c.setCellValue(error.nodePath);
        c = row.createCell(2);
        c.setCellValue(error.error);
        c = row.createCell(3);
        c.setCellValue(error.stackTrace);
    }
    autosize(sheet, 4);
    sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3));
    return wb;
}