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

Example 1 with DataFormat

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

the class CreateUserDefinedDataFormats method main.

public static void main(String[] args) throws IOException {
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("format sheet");
    CellStyle style;
    DataFormat format = wb.createDataFormat();
    Row row;
    Cell cell;
    short rowNum = 0;
    short colNum = 0;
    row = sheet.createRow(rowNum);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("0.0"));
    cell.setCellStyle(style);
    row = sheet.createRow(++rowNum);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("#,##0.0000"));
    cell.setCellStyle(style);
    FileOutputStream fileOut = new FileOutputStream("ooxml_dataFormat.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 2 with DataFormat

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

the class TestDateFormatConverter method outputLocaleDataFormats.

private void outputLocaleDataFormats(Date date, boolean dates, boolean times, int style, String styleName) throws Exception {
    Workbook workbook = new HSSFWorkbook();
    try {
        String sheetName;
        if (dates) {
            if (times) {
                sheetName = "DateTimes";
            } else {
                sheetName = "Dates";
            }
        } else {
            sheetName = "Times";
        }
        Sheet sheet = workbook.createSheet(sheetName);
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("locale");
        header.createCell(1).setCellValue("DisplayName");
        header.createCell(2).setCellValue("Excel " + styleName);
        header.createCell(3).setCellValue("java.text.DateFormat");
        header.createCell(4).setCellValue("Equals");
        header.createCell(5).setCellValue("Java pattern");
        header.createCell(6).setCellValue("Excel pattern");
        int rowNum = 1;
        for (Locale locale : DateFormat.getAvailableLocales()) {
            try {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(locale.toString());
                row.createCell(1).setCellValue(locale.getDisplayName(Locale.ROOT));
                DateFormat dateFormat;
                if (dates) {
                    if (times) {
                        dateFormat = DateFormat.getDateTimeInstance(style, style, locale);
                    } else {
                        dateFormat = DateFormat.getDateInstance(style, locale);
                    }
                } else {
                    dateFormat = DateFormat.getTimeInstance(style, locale);
                }
                Cell cell = row.createCell(2);
                cell.setCellValue(date);
                CellStyle cellStyle = row.getSheet().getWorkbook().createCellStyle();
                String javaDateFormatPattern = ((SimpleDateFormat) dateFormat).toPattern();
                String excelFormatPattern = DateFormatConverter.convert(locale, javaDateFormatPattern);
                DataFormat poiFormat = row.getSheet().getWorkbook().createDataFormat();
                cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
                row.createCell(3).setCellValue(dateFormat.format(date));
                cell.setCellStyle(cellStyle);
                // the formula returns TRUE is the formatted date in column C equals to the string in column D
                row.createCell(4).setCellFormula("TEXT(C" + rowNum + ",G" + rowNum + ")=D" + rowNum);
                row.createCell(5).setCellValue(javaDateFormatPattern);
                row.createCell(6).setCellValue(excelFormatPattern);
            } catch (Exception e) {
                throw new RuntimeException("Failed for locale: " + locale + ", having locales: " + Arrays.toString(DateFormat.getAvailableLocales()), e);
            }
        }
        File outputFile = TempFile.createTempFile("Locale" + sheetName + styleName, ".xlsx");
        FileOutputStream outputStream = new FileOutputStream(outputFile);
        try {
            workbook.write(outputStream);
        } finally {
            outputStream.close();
        }
        System.out.println("Open " + outputFile.getAbsolutePath() + " in Excel");
    } finally {
        workbook.close();
    }
}

Example 3 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat 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;
}

Example 4 with DataFormat

use of org.apache.poi.ss.usermodel.DataFormat 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 5 with DataFormat

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

the class ExcelWriterStep_StyleFormatTest method testStyleFormat.

/**
 * Test applying Format and Style from cell (from a template) when writing fields
 *
 * @param fileType
 * @throws Exception
 */
private void testStyleFormat(String fileType) throws Exception {
    setupStepMock(fileType);
    createStepMeta(fileType);
    createStepData(fileType);
    step.init(stepMeta, stepData);
    // We do not run transformation or executing the whole step
    // instead we just execute ExcelWriterStepData.writeNextLine() to write to Excel workbook object
    // Values are written in A2:D2 and A3:D3 rows
    List<Object[]> rows = createRowData();
    for (int i = 0; i < rows.size(); i++) {
        step.writeNextLine(rows.get(i));
    }
    // Custom styles are loaded from G1 cell
    Row xlsRow = stepData.sheet.getRow(0);
    Cell baseCell = xlsRow.getCell(6);
    CellStyle baseCellStyle = baseCell.getCellStyle();
    DataFormat format = stepData.wb.createDataFormat();
    // Check style of the exported values in A3:D3
    xlsRow = stepData.sheet.getRow(2);
    for (int i = 0; i < stepData.inputRowMeta.size(); i++) {
        Cell cell = xlsRow.getCell(i);
        CellStyle cellStyle = cell.getCellStyle();
        if (i > 0) {
            assertEquals(cellStyle.getBorderRight(), baseCellStyle.getBorderRight());
            assertEquals(cellStyle.getFillPattern(), baseCellStyle.getFillPattern());
        } else {
            // cell A2/A3 has no custom style
            assertFalse(cellStyle.getBorderRight() == baseCellStyle.getBorderRight());
            assertFalse(cellStyle.getFillPattern() == baseCellStyle.getFillPattern());
        }
        if (i != 1) {
            assertEquals(format.getFormat(cellStyle.getDataFormat()), "0.00000");
        } else {
            // cell B2/B3 use different format from the custom style
            assertEquals(format.getFormat(cellStyle.getDataFormat()), "##0,000.0");
        }
    }
}