Example usage for org.apache.poi.ss.usermodel CellStyle setDataFormat

List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setDataFormat

Introduction

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

Prototype

void setDataFormat(short fmt);

Source Link

Document

set the data format (must be a valid format).

Usage

From source file:javacommon.excel.ExcelWriter.java

/**
 * ?//  w w  w .j a  va 2 s  .c o m
 * @param row
 * @param value
 * @param index 
 */
public void writeCell(Row row, Object value, int index) {
    if (value != null) {
        Cell cell = row.createCell(index);
        if (value instanceof Date) {
            CellStyle style = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellStyle(style);
            cell.setCellValue((Date) value);
        } else if (value instanceof Calendar) {
            cell.setCellValue((Calendar) value);
            CellStyle style = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellStyle(style);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Short) {
            cell.setCellValue((Short) value);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Float) {
            cell.setCellValue((Float) value);
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof StringBuilder) {
            cell.setCellValue(value.toString());
        } else if (value instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) value).doubleValue());
        }
    }
}

From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java

License:Apache License

private CellStyle getStyle(Object styleId, Type cellType, Workbook wb, CreationHelper createHelper) {
    if (cellType == Type.DATE || cellType == Type.FLOAT || cellType == Type.CURRENCY) {
        String key = String.valueOf(styleId) + cellType;
        CellStyle style = styleMap.get(key);
        if (style == null) {
            style = wb.createCellStyle();
            CellStyle parentStyle = styleMap.get(styleId);
            if (parentStyle != null) {
                style.cloneStyleFrom(parentStyle);
            }/*from  ww w.  j  a v a  2  s . c o  m*/
            if (cellType == Type.DATE) {
                style.setDataFormat(createHelper.createDataFormat().getFormat("dd.mm.yyyy"));
            } else {
                style.setDataFormat(createHelper.createDataFormat().getFormat("General"));
            }
            styleMap.put(key, style);
        }
        return style;
    }
    return styleMap.get(styleId);
}

From source file:jgnash.convert.exportantur.ssf.AccountExport.java

License:Open Source License

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.setBold(true);/*  ww  w .j a va  2  s  .  c o  m*/

        // create header cell styles
        final CellStyle headerStyle = wb.createCellStyle();

        // Set the other cell style and formatting
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        DataFormat df_header = wb.createDataFormat();

        headerStyle.setDataFormat(df_header.getFormat("text"));
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.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(HorizontalAlignment.RIGHT);

        final DecimalFormat format = (DecimalFormat) NumericFormats
                .getFullCommodityFormat(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, CellType.STRING);
            c.setCellValue(DateUtils.asDate(transaction.getLocalDate()));
            c.setCellStyle(dateStyle);

            // timestamp
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(DateUtils.asDate(transaction.getTimestamp()));
            c.setCellStyle(timestampStyle);

            // number
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(transaction.getNumber());
            c.setCellStyle(textStyle);

            // payee
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(transaction.getPayee());
            c.setCellStyle(textStyle);

            // memo
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(transaction.getMemo());
            c.setCellStyle(textStyle);

            // account
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(getAccountColumnValue(transaction, account));
            c.setCellStyle(textStyle);

            // clr, strip any zero width spaces
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(transaction.getReconciled(account).toString().replaceAll(ZERO_WIDTH_SPACE, ""));
            c.setCellStyle(textStyle);

            final BigDecimal amount = transaction.getAmount(account);

            // increase
            c = r.createCell(++col, CellType.NUMERIC);
            if (amount.signum() >= 0) {
                c.setCellValue(amount.doubleValue());
            }
            c.setCellStyle(amountStyle);

            // decrease
            c = r.createCell(++col, CellType.NUMERIC);
            if (amount.signum() < 0) {
                c.setCellValue(amount.abs().doubleValue());
            }
            c.setCellStyle(amountStyle);

            // balance
            c = r.createCell(++col, CellType.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);
    }
}

From source file:jgnash.convert.exports.ssf.AccountExport.java

License:Open Source License

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);/*w  w  w  . j  a  va  2s . c o m*/

        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);

            // 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 FileOutputStream out = new FileOutputStream(filename)) {
            wb.write(out);
        } catch (final Exception e) {
            Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
        }

    } catch (IOException e) {
        Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

From source file:jgnash.engine.budget.BudgetResultsExport.java

License:Open Source License

public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) {

    Resource rb = Resource.get();

    Workbook wb;//from  www. j  a v  a  2 s  . co m

    String extension = FileUtils.getFileExtension(file.getAbsolutePath());

    if (extension.equals("xlsx")) {
        wb = new XSSFWorkbook();
    } else {
        wb = new HSSFWorkbook();
    }

    CreationHelper createHelper = wb.getCreationHelper();

    // create a new sheet
    Sheet s = wb.createSheet(model.getBudget().getName());

    // create header cell styles
    CellStyle headerStyle = wb.createCellStyle();

    // create 2 fonts objects
    Font amountFont = wb.createFont();
    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);

    DataFormat df = wb.createDataFormat();

    // 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);

    headerStyle.setDataFormat(df.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.Change")));
            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());
    Collections.sort(accounts);

    // create account rows
    for (Account account : accounts) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
        String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().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);
        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 (AccountGroup group : model.getAccountGroupList()) {

        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);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
        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);
    }

    // Save
    String filename = file.getAbsolutePath();

    if (wb instanceof XSSFWorkbook) {
        filename = FileUtils.stripFileExtension(filename) + ".xlsx";
    } else {
        filename = FileUtils.stripFileExtension(filename) + ".xls";
    }

    try (FileOutputStream out = new FileOutputStream(filename)) {
        wb.write(out);
    } catch (Exception e) {
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

From source file:joinery.impl.Serialization.java

License:Open Source License

private static final void writeCell(final Cell cell, final Object value) {
    if (value instanceof Number) {
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        cell.setCellValue(Number.class.cast(value).doubleValue());
    } else if (value instanceof Date) {
        final CellStyle style = cell.getSheet().getWorkbook().createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell.setCellStyle(style);/*from   w w w. j a va 2  s  .com*/
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        cell.setCellValue(Date.class.cast(value));
    } else if (value instanceof Boolean) {
        cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
    } else {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(value != null ? String.valueOf(value) : "");
    }
}

From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java

private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();
    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();/*from   ww w. j a  va2s .  c  o  m*/
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
}

From source file:logic.Export.java

public boolean convertXls()
        throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
    FileInputStream tamplateFile = new FileInputStream(templatePath);
    XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
    double hours = 0.0;
    NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
    Number number;/*w w  w . ja v  a 2  s.co m*/
    XSSFSheet sheet;
    XSSFSheet sheet2;
    Cell cell = null;
    ConvertData cd = new ConvertData();
    for (int i = 0; i < cd.getSheetnames().size(); i++) {
        sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
        sheet = workbook.getSheetAt(i + 1);
        //formate sheets
        sheet.getPrintSetup().setLandscape(true);
        sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

        cell = sheet.getRow(0).getCell(1);
        cell.setCellValue(cd.sheetnames.get(i));
        ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
        //setPrintArea 
        workbook.setPrintArea(i + 1, //sheet index
                0, //start column Spalte
                6, //end column
                0, //start row zeile
                convert.size() + 8 //end row
        );
        for (int Row = 0; Row < convert.size(); Row++) {
            for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                cell = sheet.getRow(9 + Row).getCell(Cell);
                if (Cell == 3) {
                    if ("true".equals(convert.get(Row)[Cell])) {
                        XSSFCellStyle style1 = workbook.createCellStyle();
                        style1 = (XSSFCellStyle) cell.getCellStyle();
                        style1 = (XSSFCellStyle) style1.clone();
                        style1.setFillBackgroundColor(HSSFColor.RED.index);
                        style1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                        XSSFColor myColor = new XSSFColor(Color.RED);
                        style1.setFillForegroundColor(myColor);
                        sheet.getRow(9 + Row).getCell(6).setCellStyle(style1);
                    }
                } else {
                    cell.setCellValue(convert.get(Row)[Cell]);
                }
            }
        }
    }

    workbook.removeSheetAt(0);
    tamplateFile.close();
    File exportFile = newPath.getSelectedFile();
    if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

    } else {
        exportFile = new File(exportFile.getParentFile(),
                FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
    }

    FileOutputStream outFile = new FileOutputStream(exportFile);
    workbook.write(outFile);
    outFile.close();
    tamplateFile.close();
    return true;

}

From source file:logic.Export.java

public boolean convertXls2()
        throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
    FileInputStream tamplateFile = new FileInputStream(templatePath);
    XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
    double hours = 0.0;
    NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
    Number number;/*from   ww w.  j  a v  a 2  s .  c  om*/
    XSSFSheet sheet;
    XSSFSheet sheet2;
    Cell cell = null;
    ConvertData cd = new ConvertData();
    for (int i = 0; i < cd.getSheetnames().size(); i++) {
        sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
        sheet = workbook.getSheetAt(i + 1);
        cell = sheet.getRow(0).getCell(1);
        cell.setCellValue(cd.sheetnames.get(i));
        ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
        for (int Row = 0; Row < convert.size(); Row++) {
            for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                cell = sheet.getRow(9 + Row).getCell(Cell + 1);
                String name;
                switch (Cell) {
                case 3:
                    name = convert.get(Row)[Cell];
                    int parseInt = Integer.parseInt(name);
                    cell.setCellValue(parseInt);
                    cell.setCellType(CellType.NUMERIC);
                    break;
                case 4:
                    number = format.parse(convert.get(Row)[Cell]);
                    cell.setCellValue(number.doubleValue());
                    //  cell.setCellStyle(cellStyle);
                    cell.setCellType(CellType.NUMERIC);

                    break;
                default:
                    cell.setCellValue(convert.get(Row)[Cell]);
                    break;
                }
            }
        }

        for (String[] sa : convert) {
            number = format.parse(sa[4]);
            hours = hours + number.doubleValue();
        }
        cell = sheet.getRow(6).getCell(5);
        cell.setCellValue(hours);
        cell = sheet.getRow(2).getCell(8);
        XSSFCell cellHourlyRate = sheet.getRow(1).getCell(8);
        double numericCellValue = cellHourlyRate.getNumericCellValue();
        cell.setCellValue(hours * numericCellValue);
    }
    workbook.removeSheetAt(0);
    tamplateFile.close();
    File exportFile = newPath.getSelectedFile();
    if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

    } else {
        exportFile = new File(exportFile.getParentFile(),
                FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
    }

    FileOutputStream outFile = new FileOutputStream(exportFile);
    workbook.write(outFile);
    outFile.close();
    tamplateFile.close();
    return true;

}

From source file:main.ExcelUtils.java

/**
 * Devuelve el CellStyle adecuado para mostrar una fecha en un formato legible
 * @return /*from   w w w. j a v a2 s.  c  o  m*/
 */
public CellStyle getDateStyle() {
    CellStyle style = wb.createCellStyle();
    CreationHelper ch = wb.getCreationHelper();
    style.setDataFormat(ch.createDataFormat().getFormat("dd/mm/yyyy"));

    return style;
}