Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createDataFormat

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. createDataFormat

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createDataFormat.

Prototype

@Override
public XSSFDataFormat createDataFormat()

Source Link

Document

Returns the workbook's data format table (a factory for creating data format strings).

Usage

From source file:es.tena.foundation.util.POIUtil.java

/**
 * Crea un map con los estilos para cada tipo de datos
 *
 * @param wb//w  ww . j ava  2 s .c  o  m
 * @return un hashMap con las claves 'porcentaje', 'coeficiente', 'moneda',
 * 'fecha' y 'cabecera'
 */
public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat fmt = wb.createDataFormat();

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style1.setDataFormat(fmt.getFormat("0.0%"));
    styles.put("porcentaje", style1);

    XSSFCellStyle style2 = wb.createCellStyle();
    style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    style2.setDataFormat(fmt.getFormat("0.0X"));
    styles.put("coeficiente", style2);

    XSSFCellStyle style3 = wb.createCellStyle();
    style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style3.setDataFormat(fmt.getFormat("#,##0.00"));
    styles.put("moneda", style3);

    XSSFCellStyle style4 = wb.createCellStyle();
    style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style4.setDataFormat(fmt.getFormat("mmm dd"));
    styles.put("fecha", style4);

    XSSFCellStyle style5 = wb.createCellStyle();
    XSSFFont headerFont = wb.createFont();
    headerFont.setBold(true);
    style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    style5.setFont(headerFont);
    styles.put("cabecera", style5);

    return styles;
}

From source file:jp.ac.utokyo.rcast.karkinos.summary.SummaryDB.java

License:Apache License

private static void setRow(List sl, Row row, XSSFWorkbook wb) {

    XSSFCellStyle styleDouble = wb.createCellStyle();
    XSSFDataFormat fd = wb.createDataFormat();
    styleDouble.setDataFormat(fd.getFormat("0.00E+00"));

    int n = 0;/*from   w w  w .ja v a2 s . c om*/
    for (Object o : sl) {
        Cell c = row.createCell(n);
        if (o instanceof String) {
            c.setCellValue((String) o);
        } else if (o instanceof Double) {
            double d = (Double) o;
            if (d == Double.NEGATIVE_INFINITY) {
                c.setCellValue("n.v.");
            } else {
                c.setCellValue(d);
                if (d < 0.01) {
                    c.setCellStyle(styleDouble);
                }
            }

        } else if (o instanceof Integer) {

            c.setCellValue((Integer) o);

        } else {

            c.setCellValue(String.valueOf(o));
        }
        n++;
    }

}

From source file:net.openchrom.msd.converter.supplier.excel.io.ChromatogramWriter.java

License:Open Source License

private Map<String, XSSFCellStyle> createStyles(XSSFWorkbook excelWorkbook) {

    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat dataFormat = excelWorkbook.createDataFormat();
    /*//from   w  w w.  j  ava2 s  . c  o m
     * Abundance values
     */
    XSSFCellStyle style1 = excelWorkbook.createCellStyle();
    style1.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    style1.setDataFormat(dataFormat.getFormat("0.000"));
    styles.put(ABUNDANCE_STYLE, style1);
    /*
     * Styles to use.
     */
    return styles;
}

From source file:org.apache.fineract.accounting.closure.storeglaccountbalance.service.GLClosureJournalEntryBalanceReadPlatformServiceImpl.java

License:Apache License

/**
 * Create the excel file with the balance report data
 * //from w  w w  . j a v a  2 s .c o m
 * @param reportDataList
 * @return {@link File} object
 */
@SuppressWarnings("unused")
private File createGLClosureAccountBalanceReportExcelFile(
        final Collection<GLClosureAccountBalanceReportData> reportDataList) {
    File file = null;

    try {
        if (reportDataList != null) {
            final String[] columnTitles = new String[13];

            columnTitles[0] = "AccountCostCentre";
            columnTitles[1] = "AccountDepartment";
            columnTitles[2] = "AccountNumber";
            columnTitles[3] = "TransactionType";
            columnTitles[4] = "TransactionDate";
            columnTitles[5] = "GoodsAmount";
            columnTitles[6] = "Reference";
            columnTitles[7] = "Narrative";
            columnTitles[8] = "UniqueReferenceNumber";
            columnTitles[9] = "UserNumber";
            columnTitles[10] = "Source";
            columnTitles[11] = "PostedDate";
            columnTitles[12] = "TransactionAnalysisCode";

            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet spreadsheet = workbook.createSheet(" nominaltransactions ");
            XSSFRow row;
            XSSFFont font;
            XSSFCellStyle style;
            XSSFDataFormat dataFormat;

            int rowId = 0;
            int cellId = 0;

            row = spreadsheet.createRow(rowId++);

            for (String columnTitle : columnTitles) {
                font = workbook.createFont();
                style = workbook.createCellStyle();

                font.setBold(true);
                font.setFontName("Arial");
                font.setFontHeightInPoints((short) 10);
                style.setFont(font);

                Cell cell = row.createCell(cellId++);

                cell.setCellValue(columnTitle);
                cell.setCellStyle(style);
            }

            for (GLClosureAccountBalanceReportData reportData : reportDataList) {
                row = spreadsheet.createRow(rowId++);
                font = workbook.createFont();
                dataFormat = workbook.createDataFormat();

                font.setFontName("Arial");
                font.setFontHeightInPoints((short) 10);
                font.setBold(false);

                // ====================================================
                Cell cell = row.createCell(2);
                style = workbook.createCellStyle();

                cell.setCellType(Cell.CELL_TYPE_STRING);
                style.setDataFormat(dataFormat.getFormat("@"));
                style.setFont(font);
                cell.setCellValue(reportData.getAccountNumber());
                cell.setCellStyle(style);
                // ====================================================

                // ====================================================
                if (reportData.getTransactionType() != null) {
                    cell = row.createCell(3);
                    style = workbook.createCellStyle();

                    style.setFont(font);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(reportData.getTransactionType().getValue());
                    cell.setCellStyle(style);
                }
                // ====================================================

                // ====================================================
                if (reportData.getTransactionDate() != null) {
                    cell = row.createCell(4);
                    style = workbook.createCellStyle();

                    Date transactionDate = reportData.getTransactionDate().toDate();

                    style.setDataFormat(dataFormat.getFormat("MM/DD/YY"));
                    style.setFont(font);
                    cell.setCellValue(transactionDate);
                    cell.setCellStyle(style);
                }
                // ====================================================

                // ====================================================
                if (reportData.getAmount() != null) {
                    cell = row.createCell(5);
                    style = workbook.createCellStyle();

                    Double amount = reportData.getAmount().doubleValue();

                    style.setDataFormat(dataFormat.getFormat("0.00"));
                    style.setFont(font);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(amount);
                    cell.setCellStyle(style);
                }
                // ====================================================

                // ====================================================
                if (reportData.getReference() != null) {
                    cell = row.createCell(6);
                    style = workbook.createCellStyle();

                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    style.setDataFormat(dataFormat.getFormat("@"));
                    style.setFont(font);
                    cell.setCellValue(reportData.getReference());
                    cell.setCellStyle(style);
                }
                // ====================================================

                // ====================================================
                if (reportData.getPostedDate() != null) {
                    cell = row.createCell(11);
                    style = workbook.createCellStyle();

                    Date postedDate = reportData.getPostedDate().toDate();

                    style.setDataFormat(dataFormat.getFormat("MM/DD/YY"));
                    style.setFont(font);
                    cell.setCellValue(postedDate);
                    cell.setCellStyle(style);
                }
                // ====================================================
            }

            final String fileDirectory = FileSystemContentRepository.MIFOSX_BASE_DIR + File.separator + "";

            if (!new File(fileDirectory).isDirectory()) {
                new File(fileDirectory).mkdirs();
            }

            file = new File(fileDirectory + "gl_closure_account_balance_report.xls");

            FileOutputStream fileOutputStream = new FileOutputStream(file);

            workbook.write(fileOutputStream);

            fileOutputStream.close();
        }
    }

    catch (Exception exception) {
        logger.error(exception.getMessage(), exception);
    }

    return file;
}

From source file:org.apache.fineract.infrastructure.dataexport.helper.XlsFileHelper.java

License:Apache License

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {//from   w w w  .j a  v a2  s . c om
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:
                        // TINYINT(1) is also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (mysqlDataType.equals(MysqlDataType.TINYINT)
                                && sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}

From source file:org.mifosplatform.infrastructure.dataexport.helper.XlsFileHelper.java

License:Mozilla Public License

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {// w w w  . ja  v a  2 s  . com
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:

                        // TINYINT(1), BIT(1), etc are also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

    } catch (Exception exception) {
        exception.printStackTrace();
    }
}

From source file:org.nekorp.workflow.desktop.servicio.reporte.global.GeneradorReporteGlobal.java

License:Apache License

@Override
public void generaReporte(ParametrosReporteGlobal param) {
    FileOutputStream fileOut = null;
    try {//from  www  .j  ava  2s.  c  om
        //List<Servicio> datos = servicioDAO.getByDate(new DateTime("2013-05-1T00:00:00.000-00:00"), new DateTime("2013-05-1T23:59:59.999-00:00"));
        List<Servicio> datos = servicioDAO.getByDate(param.getFechaInicial(), param.getFechaFinal());
        GeneradorReporteGlobal.LOGGER.debug("file:" + param.getDestination());
        GeneradorReporteGlobal.LOGGER.debug("fecha Inicial:" + param.getFechaInicial());
        GeneradorReporteGlobal.LOGGER.debug("fecha Final:" + param.getFechaFinal());
        XSSFWorkbook wb = new XSSFWorkbook();
        //estilo para las fechas
        XSSFCellStyle dateCellStyle = wb.createCellStyle();
        CreationHelper createHelper = wb.getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        //estilos para las cantidades
        XSSFCellStyle moneyCellStyle = wb.createCellStyle();
        XSSFDataFormat mf = wb.createDataFormat();
        moneyCellStyle.setDataFormat(mf.getFormat("$#,##0.00"));
        Sheet sheet = wb.createSheet("Hoja1");
        int rowCount = 1;
        Cell actual;
        Row r;
        for (Servicio x : datos) {
            RenglonRG datRen = renglonFactory.build(x);
            r = sheet.createRow(rowCount);
            rowCount = rowCount + 1;
            for (int i = 0; i < valueMap.length; i++) {
                actual = r.createCell(i);
                if (valueMap[i].getType() == CellValueType.TEXT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    actual.setCellValue(obj.toString());
                }
                if (valueMap[i].getType() == CellValueType.DATE) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    if (obj != null) {
                        Date val = (Date) obj;
                        actual.setCellValue(val);
                        actual.setCellStyle(dateCellStyle);
                    }
                }
                if (valueMap[i].getType() == CellValueType.AMOUNT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    double val = (double) obj;
                    actual.setCellValue(val);
                    actual.setCellStyle(moneyCellStyle);
                }
                if (valueMap[i].getType() == CellValueType.FORMULA) {
                    String formulaRaw = valueMap[i].getValue();
                    ST formula = new ST(formulaRaw);
                    formula.add("row", rowCount + "");
                    actual.setCellFormula(formula.render());
                    actual.setCellStyle(moneyCellStyle);
                }
            }
        }
        llenarEncabezado(sheet, 0, 0);
        fileOut = new FileOutputStream(param.getDestination());
        wb.write(fileOut);
    } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException | FormulaParseException
            | IOException ex) {
        GeneradorReporteGlobal.LOGGER.error("error al generar reporte", ex);
    } finally {
        try {
            fileOut.close();
        } catch (IOException | NullPointerException ex) {
            GeneradorReporteGlobal.LOGGER.error("error al cerrar archivo de reporte", ex);
        }
    }
}

From source file:preprocessing.EnemyInfoGenerator.java

private static void createCellStyles(XSSFWorkbook book) {
    XSSFDataFormat format = book.createDataFormat();
    XSSFFont font = book.createFont();/*w  ww.j  av  a 2s  .c om*/
    font.setFontHeightInPoints((short) 9);
    font.setFontName("");
    XSSFFont headerFont = book.createFont();
    headerFont.setFontHeightInPoints((short) 9);
    headerFont.setBold(true);
    headerFont.setColor(new XSSFColor(new Color(0, 176, 80)));
    headerFont.setFontName("");

    headerStyle = book.createCellStyle();
    headerStyle.setAlignment(HorizontalAlignment.CENTER);
    headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    //        headerStyle.setBorderRight(CellStyle.BORDER_THIN);
    //        headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
    //        headerStyle.setBorderTop(CellStyle.BORDER_THIN);
    //        headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
    headerStyle.setFont(headerFont);
    headerStyle.setWrapText(true);

    contentStyle = book.createCellStyle();
    contentStyle.setAlignment(HorizontalAlignment.CENTER);
    contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    //        contentStyle.setBorderRight(CellStyle.BORDER_THIN);
    //        contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
    //        contentStyle.setBorderTop(CellStyle.BORDER_THIN);
    //        contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contentStyle.setFont(font);

    moneyStyle = book.createCellStyle();
    moneyStyle.setAlignment(HorizontalAlignment.CENTER);
    moneyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    moneyStyle.setBorderRight(CellStyle.BORDER_THIN);
    moneyStyle.setBorderLeft(CellStyle.BORDER_THIN);
    moneyStyle.setBorderTop(CellStyle.BORDER_THIN);
    moneyStyle.setBorderBottom(CellStyle.BORDER_THIN);
    moneyStyle.setDataFormat(format.getFormat("$#,##0.00_);[Red]($#,##0.00)"));
    moneyStyle.setFont(font);

}

From source file:se.minstrel.tools.xssfbuilder.impl.Support.java

License:Open Source License

public Support(XSSFWorkbook workbook, MarkerManager markerManager) {
    this.markerManager = markerManager;
    this.workbook = workbook;
    this.styleMap = new HashMap<Style, XSSFCellStyle>();
    this.dataFormat = workbook.createDataFormat();
    this.formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
}

From source file:uk.gov.ofwat.fountain.api.table.POITableRenderer.java

License:Open Source License

public Workbook renderTable(XSSFWorkbook workBook, TableStructure tableStructure, DataTable table) {
    this.workBook = workBook;
    creationHelper = workBook.getCreationHelper();

    sheet = workBook.createSheet(table.getCompany().getCode() + " Table " + tableStructure.getTableName());
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);

    inputDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();
    copyCellDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();
    calcDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();

    yellow = new XSSFColor(new java.awt.Color(255, 255, 0));
    lightYellow = new XSSFColor(new java.awt.Color(255, 255, 224));
    lightBlue = new XSSFColor(new java.awt.Color(224, 255, 255));
    pink = new XSSFColor(new java.awt.Color(255, 204, 204));

    // Styles/*from  w w w  . jav  a 2s .  c o m*/
    // Row header style
    rowHeaderStyle = workBook.createCellStyle();
    // Col header style
    colHeaderStyle = workBook.createCellStyle();
    colHeaderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    colHeaderStyle.setFillForegroundColor(yellow);
    Font colHeaderFont = workBook.createFont();
    colHeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    colHeaderStyle.setFont(colHeaderFont);
    // Copycell text data cell style
    copyCellTextStyle = workBook.createCellStyle();
    copyCellTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    copyCellTextStyle.setFillForegroundColor(pink);
    // Input text data cell style
    inputDataTextStyle = workBook.createCellStyle();
    inputDataTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    inputDataTextStyle.setFillForegroundColor(lightYellow);
    // Calc text data cell style
    calcDataTextStyle = workBook.createCellStyle();
    calcDataTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    calcDataTextStyle.setFillForegroundColor(lightBlue);
    // Input CG style
    inputCGStyle = workBook.createCellStyle();
    inputCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    inputCGStyle.setFillForegroundColor(lightYellow);
    // Input CG style
    copyCellCGStyle = workBook.createCellStyle();
    copyCellCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    copyCellCGStyle.setFillForegroundColor(pink);
    // Calc CG style
    calcCGStyle = workBook.createCellStyle();
    calcCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    calcCGStyle.setFillForegroundColor(lightBlue);

    // data format
    DataFormat format = workBook.createDataFormat();

    int rownum = 1; // starting point
    Row infoRow1 = sheet.createRow(rownum);
    CellStyle style = workBook.createCellStyle();
    Font font = workBook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(font);

    Cell titleCell1 = infoRow1.createCell(0);
    titleCell1.setCellType(Cell.CELL_TYPE_STRING);
    String DATE_FORMAT = "dd MMM yyyy h:mm";
    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
    Calendar c1 = Calendar.getInstance(); // today
    String today = sdf.format(c1.getTime());
    RichTextString dateRts = creationHelper.createRichTextString(today + ": "
            + tableStructure.getModelPage().getModel().getCode() + " for " + table.getCompany().getName());
    titleCell1.setCellValue(dateRts);
    titleCell1.setCellStyle(style);
    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 9));

    rownum++;
    Row infoRow2 = sheet.createRow(rownum);
    Cell titleCell = infoRow2.createCell(0);
    titleCell.setCellType(Cell.CELL_TYPE_STRING);
    RichTextString rts = creationHelper.createRichTextString(tableStructure.getModelPage().getTable().getName()
            + " - " + tableStructure.getModelPage().getTableDescription());
    titleCell.setCellValue(rts);
    titleCell.setCellStyle(style);
    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 4));

    rownum++;
    rownum++;

    if (tableStructure.getModelPage().isGroupSelect()) {
        // group dropdown
        groupSelectTable(tableStructure, table, workBook, sheet, format, rownum);
    } else {
        tableWithoutGroupSelect(tableStructure, table, workBook, sheet, format, rownum);
    }
    return workBook;
}