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

Example 26 with CellStyle

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

the class TestPropertyTemplate method applyBorders.

@Test
public void applyBorders() throws IOException {
    CellRangeAddress a1c3 = new CellRangeAddress(0, 2, 0, 2);
    CellRangeAddress b2 = new CellRangeAddress(1, 1, 1, 1);
    PropertyTemplate pt = new PropertyTemplate();
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    pt.drawBorders(a1c3, BorderStyle.THIN, IndexedColors.RED.getIndex(), BorderExtent.ALL);
    pt.applyBorders(sheet);
    for (Row row : sheet) {
        for (Cell cell : row) {
            CellStyle cs = cell.getCellStyle();
            assertEquals(BorderStyle.THIN, cs.getBorderTopEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getTopBorderColor());
            assertEquals(BorderStyle.THIN, cs.getBorderBottomEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getBottomBorderColor());
            assertEquals(BorderStyle.THIN, cs.getBorderLeftEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getLeftBorderColor());
            assertEquals(BorderStyle.THIN, cs.getBorderRightEnum());
            assertEquals(IndexedColors.RED.getIndex(), cs.getRightBorderColor());
        }
    }
    pt.drawBorders(b2, BorderStyle.NONE, BorderExtent.ALL);
    pt.applyBorders(sheet);
    for (Row row : sheet) {
        for (Cell cell : row) {
            CellStyle cs = cell.getCellStyle();
            if (cell.getColumnIndex() != 1 || row.getRowNum() == 0) {
                assertEquals(BorderStyle.THIN, cs.getBorderTopEnum());
                assertEquals(IndexedColors.RED.getIndex(), cs.getTopBorderColor());
            } else {
                assertEquals(BorderStyle.NONE, cs.getBorderTopEnum());
            }
            if (cell.getColumnIndex() != 1 || row.getRowNum() == 2) {
                assertEquals(BorderStyle.THIN, cs.getBorderBottomEnum());
                assertEquals(IndexedColors.RED.getIndex(), cs.getBottomBorderColor());
            } else {
                assertEquals(BorderStyle.NONE, cs.getBorderBottomEnum());
            }
            if (cell.getColumnIndex() == 0 || row.getRowNum() != 1) {
                assertEquals(BorderStyle.THIN, cs.getBorderLeftEnum());
                assertEquals(IndexedColors.RED.getIndex(), cs.getLeftBorderColor());
            } else {
                assertEquals(BorderStyle.NONE, cs.getBorderLeftEnum());
            }
            if (cell.getColumnIndex() == 2 || row.getRowNum() != 1) {
                assertEquals(BorderStyle.THIN, cs.getBorderRightEnum());
                assertEquals(IndexedColors.RED.getIndex(), cs.getRightBorderColor());
            } else {
                assertEquals(BorderStyle.NONE, cs.getBorderRightEnum());
            }
        }
    }
    wb.close();
}

Example 27 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project Gargoyle by callakrsos.

the class AbstractExcelModel method work.

/**
	 * 2014. 11. 4. KYJ
	 * 
	 * @param
	 * @return
	 * @throws Exception
	 * @처리내용 : 입력된 엑셀파일로부터 ExcelSVO객체를 생성하여 반환한다.
	 */
public ExcelSVO work() throws Exception {
    // 결과반환용 SVO
    ExcelSVO excelSVO = new ExcelSVO();
    // 컬럼부
    /* 시작 엑셀관련 메타정보 처리객체 */
    FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
    DecimalFormat df = new DecimalFormat();
    /* 끝 엑셀관련 메타정보 처리객체 */
    for (int sheetIndex = 0; sheetIndex < excel.getNumberOfSheets(); sheetIndex++) {
        Sheet sheetAt = excel.getSheetAt(sheetIndex);
        String sheetName = sheetAt.getSheetName();
        List<ExcelColDVO> columnDVOList = new ArrayList<ExcelColDVO>();
        excelSVO.setColDvoList(sheetName, columnDVOList);
        int maxColumIndex = 0;
        // 시작 데이터부 처리
        List<ExcelDataDVO> arrayList = new ArrayList<ExcelDataDVO>();
        // 컬럼부에 정의되어야하는데 없음. 데이터부에는 존재할경우 컬럼부를 추가하기 위한 플래그
        boolean existsOutOfColumn = false;
        // while (rowIterator.hasNext())
        for (int row = 0; row < sheetAt.getLastRowNum(); row++) {
            Row next = sheetAt.getRow(row);
            if (next != null) {
                short lastCellNum = next.getLastCellNum();
                for (int col = 0; col < lastCellNum; col++) {
                    Cell cell = next.getCell(col);
                    if (cell != null) {
                        // 엑셀 셀
                        // Cell cell = cellIterator.next();
                        CellStyle cellStyle = cell.getCellStyle();
                        Color fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
                        String backColorRgb = "";
                        if (fillBackgroundColorColor instanceof HSSFColor) {
                            HSSFColor backColor = (HSSFColor) fillBackgroundColorColor;
                            backColorRgb = backColor.getHexString();
                        } else if (fillBackgroundColorColor instanceof XSSFColor) {
                            XSSFColor backColor = (XSSFColor) fillBackgroundColorColor;
                            backColorRgb = backColor.getARGBHex();
                        }
                        int cellType = cell.getCellType();
                        String stringCellValue = "";
                        switch(cellType) {
                            case Cell.CELL_TYPE_FORMULA:
                                if (!(cell.toString() == "")) {
                                    if (evaluator.evaluateFormulaCell(cell) == 0) {
                                        double fddata = cell.getNumericCellValue();
                                        stringCellValue = String.valueOf(fddata);
                                    } else if (evaluator.evaluateFormulaCell(cell) == 1) {
                                        stringCellValue = cell.getStringCellValue();
                                    } else if (evaluator.evaluateFormulaCell(cell) == 4) {
                                        boolean fbdata = cell.getBooleanCellValue();
                                        stringCellValue = String.valueOf(fbdata);
                                    }
                                    break;
                                }
                                stringCellValue = cell.getCellFormula();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                /* N/A */
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                // double numericCellValue =
                                // cell.getNumericCellValue();
                                String dateFormatString = cellStyle.getDataFormatString();
                                short dataFormat = cellStyle.getDataFormat();
                                boolean internalDateFormat = HSSFDateUtil.isInternalDateFormat(dataFormat);
                                double numericCellValue = cell.getNumericCellValue();
                                boolean validExcelDate = HSSFDateUtil.isValidExcelDate(numericCellValue);
                                boolean cellDateFormatted = HSSFDateUtil.isCellDateFormatted(cell);
                                boolean cellInternalDateFormatted = HSSFDateUtil.isCellInternalDateFormatted(cell);
                                LOGGER.debug("sheet : [ " + sheetName + " ] dateFormatString : [ " + dateFormatString + " ] dataFormat : [ " + dataFormat + " ] isInternalDateFormat : [ " + internalDateFormat + " ] validExcelDate : [ " + validExcelDate + " ] cellDateFormatted : [ " + cellDateFormatted + " ]  cellInternalDateFormatted : [" + cellInternalDateFormatted + " ] numericCellValue : [ " + numericCellValue + " ] rowIndex : [ " + row + " ] columnIndex : [ " + col + " ]");
                                if (cellDateFormatted || cellInternalDateFormatted || (!"GENERAL".equals(dateFormatString.toUpperCase()))) {
                                    Date date = cell.getDateCellValue();
                                    LOGGER.debug("dateFmt : %s", dateFormatString);
                                    stringCellValue = new CellDateFormatter(dateFormatString).format(date);
                                } else {
                                    double ddata = cell.getNumericCellValue();
                                    stringCellValue = df.format(ddata);
                                }
                                break;
                            case Cell.CELL_TYPE_STRING:
                                stringCellValue = cell.getStringCellValue();
                                break;
                            default:
                                /* N/A */
                                break;
                        }
                        // 시작 컬럼부 처리
                        if (row == 0) {
                            short alignment = cellStyle.getAlignment();
                            int columnWidth = sheetAt.getColumnWidth(col);
                            columnDVOList.add(new ExcelColDVO(col, stringCellValue, columnWidth, alignment));
                        }
                        // 끝 컬럼부 처리
                        ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, col, stringCellValue, backColorRgb);
                        arrayList.add(excelDataDVO);
                    } else {
                        ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, col, "");
                        arrayList.add(excelDataDVO);
                    }
                }
            // end for
            } else {
                ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, 0, "");
                arrayList.add(excelDataDVO);
            }
        }
        // end for
        // 끝 데이터부 처리
        excelSVO.addSheetExcelDVO(sheetName, arrayList);
    }
    return excelSVO;
}

Example 28 with CellStyle

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

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

use of org.apache.poi.ss.usermodel.CellStyle in project translationstudio8 by heartsome.

the class Excel2007Writer method getWrapedCell.

private CellStyle getWrapedCell() {
    if (cellStyle_Cache == null) {
        cellStyle_Cache = new ArrayList<CellStyle>();
    }
    if (cellStyle_Cache.isEmpty()) {
        final CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);
        /*	Display.getDefault().syncExec(new Runnable() {
				public void run() {
					org.eclipse.swt.graphics.Font font = JFaceResources.getFontRegistry().get(
							"net.heartsome.cat.te.ui.tmxeditor.font");
					if (null != font) {
						Font createFont = wb.createFont();
						createFont.setFontName(font.getFontData()[0].getName());
						createFont.setFontHeightInPoints((short) font.getFontData()[0].getHeight());
						cellStyle.setFont(createFont);
					}
				}
			});*/
        cellStyle_Cache.add(cellStyle);
    }
    return cellStyle_Cache.get(0);
}