Example usage for org.apache.poi.ss.usermodel CellStyle 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:com.globalsight.everest.webapp.pagehandler.administration.reports.VendorPOXlsReport.java

License:Apache License

private CellStyle getDateStyle(Workbook p_workbook) throws Exception {
    if (dateStyle == null) {
        Font dateFont = p_workbook.createFont();
        dateFont.setFontName("Arial");
        dateFont.setFontHeightInPoints((short) 10);

        DataFormat format = p_workbook.createDataFormat();
        CellStyle cs = p_workbook.createCellStyle();
        cs.setFont(dateFont);/*  w w w. ja  v  a  2  s .  c om*/
        cs.setDataFormat(format.getFormat("M/d/yy"));
        cs.setWrapText(false);

        dateStyle = cs;
    }
    return dateStyle;
}

---

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.VendorPOXlsReport.java

License:Apache License

private CellStyle getFailedDateStyle(Workbook p_workbook) throws Exception {
    if (failedDateStyle == null) {
        Font dateFont = p_workbook.createFont();
        dateFont.setFontName("Arial");
        dateFont.setFontHeightInPoints((short) 10);

        DataFormat format = p_workbook.createDataFormat();
        CellStyle cs = p_workbook.createCellStyle();
        cs.setFont(dateFont);// ww  w .j a  va  2  s  . c om
        cs.setDataFormat(format.getFormat("M/d/yy"));
        cs.setWrapText(false);
        cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cs.setFillForegroundColor(IndexedColors.RED.getIndex());

        failedDateStyle = cs;
    }
    return failedDateStyle;
}

---

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.VendorPOXlsReport.java

License:Apache License

private CellStyle getMoneyStyle(Workbook p_workbook) throws Exception {
    if (moneyStyle == null) {
        String euroJavaNumberFormat = getNumberFormatString();
        DataFormat euroNumberFormat = p_workbook.createDataFormat();

        CellStyle cs = p_workbook.createCellStyle();
        cs.setDataFormat(euroNumberFormat.getFormat(euroJavaNumberFormat));
        cs.setWrapText(false);//from  www  .  j  av  a 2  s .  co m

        moneyStyle = cs;
    }
    return moneyStyle;
}

---

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.VendorPOXlsReport.java

License:Apache License

private CellStyle getFailedMoneyStyle(Workbook p_workbook) throws Exception {
    if (failedMoneyStyle == null) {
        String euroJavaNumberFormat = getNumberFormatString();
        DataFormat euroNumberFormat = p_workbook.createDataFormat();

        CellStyle cs = p_workbook.createCellStyle();
        cs.setWrapText(false);//w ww.  ja  v  a  2 s.com
        cs.setDataFormat(euroNumberFormat.getFormat(euroJavaNumberFormat));
        cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cs.setFillForegroundColor(IndexedColors.RED.getIndex());

        failedMoneyStyle = cs;
    }
    return failedMoneyStyle;
}

---

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.VendorPOXlsReport.java

License:Apache License

private CellStyle getTotalMoneyStyle(Workbook p_workbook) throws Exception {
    if (totalMoneyStyle == null) {
        String euroJavaNumberFormat = getNumberFormatString();
        DataFormat euroNumberFormat = p_workbook.createDataFormat();
        CellStyle cs = p_workbook.createCellStyle();
        cs.setDataFormat(euroNumberFormat.getFormat(euroJavaNumberFormat));
        cs.setWrapText(false);//from  w  ww  .j  a  v a 2 s. c  o m
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cs.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        totalMoneyStyle = cs;
    }
    return totalMoneyStyle;
}

---

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?// w ww .ja  v a  2 s.c  o  m
 * 
 * @param row
 *            
 * @param column
 *            ?
 * @param val
 *            
 * @param align
 *            ??1?23??
 * @return ?
 */
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType, String[] constraintValue) {
    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
    try {
        if (val == null) {
            cell.setCellValue("");
        } else if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer) val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long) val);
        } else if (val instanceof Double) {
            cell.setCellValue((Double) val);
        } else if (val instanceof Float) {
            cell.setCellValue((Float) val);
        } else if (val instanceof Date) {
            DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date) val);
        } else {
            if (fieldType != Class.class) {
                cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
            } else {
                cell.setCellValue((String) Class
                        .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                "fieldtype." + val.getClass().getSimpleName() + "Type"))
                        .getMethod("setValue", Object.class).invoke(null, val));
            }
        }

        if (constraintValue.length > 0) {
            // ?
            // ???
            CellRangeAddressList regions = new CellRangeAddressList(row.getRowNum(), row.getRowNum(), column,
                    column);
            // ?
            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(constraintValue);
            DataValidationHelper help = sheet.getDataValidationHelper();

            // 
            DataValidation validation = help.createValidation(constraint, regions);

            validation.createErrorBox("", "");
            validation.setShowErrorBox(true);

            // sheet
            sheet.addValidationData(validation);
        }
    } catch (Exception ex) {
        log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    cell.setCellStyle(style);
    return cell;
}

---

From source file:com.helger.poi.excel.style.ExcelStyle.java

License:Apache License

public void fillCellStyle(@Nonnull final Workbook aWB, @Nonnull final CellStyle aCS,
        @Nonnull final CreationHelper aCreationHelper) {
    if (m_eAlign != null)
        aCS.setAlignment(m_eAlign.getValue());
    if (m_eVAlign != null)
        aCS.setVerticalAlignment(m_eVAlign.getValue());
    aCS.setWrapText(m_bWrapText);//from  www .  jav  a  2s.c o m
    if (m_sDataFormat != null)
        aCS.setDataFormat(aCreationHelper.createDataFormat().getFormat(m_sDataFormat));
    if (m_eFillBackgroundColor != null)
        aCS.setFillBackgroundColor(m_eFillBackgroundColor.getIndex());
    if (m_eFillForegroundColor != null)
        aCS.setFillForegroundColor(m_eFillForegroundColor.getIndex());
    if (m_eFillPattern != null)
        aCS.setFillPattern(m_eFillPattern.getValue());
    if (m_eBorderTop != null)
        aCS.setBorderTop(m_eBorderTop.getValue());
    if (m_eBorderRight != null)
        aCS.setBorderRight(m_eBorderRight.getValue());
    if (m_eBorderBottom != null)
        aCS.setBorderBottom(m_eBorderBottom.getValue());
    if (m_eBorderLeft != null)
        aCS.setBorderLeft(m_eBorderLeft.getValue());
    if (m_nFontIndex >= 0)
        aCS.setFont(aWB.getFontAt(m_nFontIndex));
}

---

From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java

License:MIT License

/**
 * Utility function to write the date graph data as a Excel workbook; required since PowerPoint charts actually
 *   embed an Excel file with corresponding data. If invalid, it'll open in OpenOffice fine, but PowerPoint will
 *   complain that the presentation is corrupted.
 * @param data the date graph data./*w  w  w. j  a  v  a  2  s  . c o m*/
 * @return a new Excel workbook with specified data on a new sheet.
 */
private static XSSFWorkbook writeChart(final DategraphData data) {
    final XSSFWorkbook wb = new XSSFWorkbook();
    final XSSFSheet sheet = wb.createSheet("Sheet1");

    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat((short) 14);

    final List<DategraphData.Row> rows = data.getRows();
    final long[] timestamps = data.getTimestamps();

    final XSSFRow header = sheet.createRow(0);
    header.createCell(0).setCellValue("Timestamp");
    for (int ii = 0; ii < rows.size(); ++ii) {
        header.createCell(ii + 1).setCellValue(rows.get(ii).getLabel());
    }

    for (int rowIdx = 0; rowIdx < timestamps.length; ++rowIdx) {
        final XSSFRow row = sheet.createRow(rowIdx + 1);

        final XSSFCell cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(new Date(timestamps[rowIdx] * 1000));

        for (int ii = 0; ii < rows.size(); ++ii) {
            row.createCell(ii + 1).setCellValue(rows.get(ii).getValues()[rowIdx]);
        }
    }

    return wb;
}

---

From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java

public void generarExcelVentasCantadas() throws IOException {

    cargaResumen();/*w w  w  . ja v  a  2 s. c  om*/

    if (ventasCantadas == null || ventasCantadas.isEmpty()) {
        JsfUtil.addErrorMessage("No hay datos para generar");
    } else {
        Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>();

        for (ResumenVentasCantadas rc : ventasCantadas) {
            List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio());
            if (valueList == null) {
                valueList = new ArrayList<>();
                valueList.add(rc);
                map2.put(rc.getTerritorio(), valueList);
            } else {
                valueList.add(rc);
            }
        }

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Ventas Detalle");

        configAnchoCols(sheet);

        //Fonts
        Font fontSubTitulo = workbook.createFont();
        fontSubTitulo.setFontHeightInPoints((short) 8);
        fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Fonts
        Font fontTotal3 = workbook.createFont();
        fontTotal3.setFontHeightInPoints((short) 8);
        fontTotal3.setColor(HSSFColor.RED.index);
        fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Fonts
        Font fontTerritorioTotal3 = workbook.createFont();
        fontTerritorioTotal3.setFontHeightInPoints((short) 8);
        fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index);
        fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Estilos
        DataFormat format = workbook.createDataFormat();
        CellStyle styleTotal3 = workbook.createCellStyle();

        styleTotal3.setFont(fontTotal3);
        styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setDataFormat(format.getFormat("0.0%"));

        styleTerritorioTotal3 = workbook.createCellStyle();
        styleTerritorioTotal3.setFont(fontTerritorioTotal3);
        styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

        creaCabecera(workbook, sheet);

        String[] aCols20 = null;
        String[] aCols10 = null;
        String[] aColsGrue = null;
        String indicesTotales = "";

        for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) {
            ++indexRow;

            HSSFRow row = sheet.createRow((++indexRow));

            HSSFCell cellTerr = row.createCell(indexCol);
            cellTerr.setCellValue(entry.getKey().toUpperCase());
            cellTerr.setCellStyle(styleTerritorio);

            int indexInicioGrupo = indexRow + 2;

            List<ResumenVentasCantadas> detalles = entry.getValue();

            Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() {

                @Override
                public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) {
                    return o1.getOrden() > o2.getOrden() ? 1 : -1;
                }
            };

            Collections.sort(detalles, comp);

            for (ResumenVentasCantadas rv : detalles) {
                cols20 = "";
                cols10 = "";
                colsGrue = "";

                int indexFilaActual = ++indexRow;
                HSSFRow row1 = sheet.createRow((indexFilaActual));

                HSSFCell cellZona = row1.createCell(indexCol + 0);
                cellZona.setCellValue(rv.getZona());
                cellZona.setCellStyle(styleTitulo9);

                HSSFCell cellVendedor = row1.createCell(indexCol + 1);
                cellVendedor.setCellValue(rv.getVendedor());
                cellVendedor.setCellStyle(styleTitulo9);

                HSSFCell cellBoletas = row1.createCell(indexCol + 2);
                cellBoletas.setCellValue(rv.getCantboletas());
                cellBoletas.setCellStyle(styleCantidad);

                generarDetalles(row1, indexFilaActual, rv);

            }

            int indexFinGrupo = indexRow + 1;

            int indexTotal1 = ++indexRow;
            int indexTotal2 = ++indexRow;
            int indexTotal3 = ++indexRow;

            HSSFRow rowTotal1 = sheet.createRow(indexTotal1);
            HSSFRow rowTotal2 = sheet.createRow((indexTotal2));
            HSSFRow rowTotal3 = sheet.createRow((indexTotal3));

            aCols20 = cols20.split(",");
            aCols10 = cols10.split(",");
            aColsGrue = colsGrue.split(",");

            //TOTAL 1
            generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr);
            //TOTAL 2
            generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr);
            //TOTAL 3
            generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet);
            //++indexRow;

            indicesTotales += (indexFinGrupo + 1) + ",";
        }

        String[] aIndexTotales = indicesTotales.split(",");

        //TOTAL pais
        int indexTotalPais = ++indexRow;
        HSSFRow rowTotalPais = sheet.createRow((indexTotalPais));
        generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10);

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance()
                .getExternalContext().getResponse();

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls");
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
        FacesContext.getCurrentInstance().responseComplete();
        indexRow = 1;
    }

}

---

From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java

public void generarExcelVentasCantadasDetalle() throws IOException {

    cargaResumenPorVendedor();/*  w  w  w .  j  a  v a 2s  .  c om*/

    if (ventasCantadas == null || ventasCantadas.isEmpty()) {
        JsfUtil.addErrorMessage("No hay datos para generar");
    } else {
        Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>();

        for (ResumenVentasCantadas rc : ventasCantadas) {
            List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio());
            if (valueList == null) {
                valueList = new ArrayList<>();
                valueList.add(rc);
                map2.put(rc.getTerritorio(), valueList);
            } else {
                valueList.add(rc);
            }
        }

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Ventas Detalle");

        configAnchoCols(sheet);

        //Fonts
        Font fontSubTitulo = workbook.createFont();
        fontSubTitulo.setFontHeightInPoints((short) 8);
        fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Fonts
        Font fontTotal3 = workbook.createFont();
        fontTotal3.setFontHeightInPoints((short) 8);
        fontTotal3.setColor(HSSFColor.RED.index);
        fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Fonts
        Font fontTerritorioTotal3 = workbook.createFont();
        fontTerritorioTotal3.setFontHeightInPoints((short) 8);
        fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index);
        fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Estilos
        DataFormat format = workbook.createDataFormat();
        CellStyle styleTotal3 = workbook.createCellStyle();

        styleTotal3.setFont(fontTotal3);
        styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setDataFormat(format.getFormat("0.0%"));

        styleTerritorioTotal3 = workbook.createCellStyle();
        styleTerritorioTotal3.setFont(fontTerritorioTotal3);
        styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

        creaCabecera(workbook, sheet);

        String[] aCols20 = null;
        String[] aCols10 = null;
        String[] aColsGrue = null;
        String indicesTotales = "";

        for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) {
            ++indexRow;

            HSSFRow row = sheet.createRow((++indexRow));

            HSSFCell cellTerr = row.createCell(indexCol);
            cellTerr.setCellValue(entry.getKey().toUpperCase());
            cellTerr.setCellStyle(styleTerritorio);

            int indexInicioGrupo = indexRow + 2;

            List<ResumenVentasCantadas> detalles = entry.getValue();

            Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() {

                @Override
                public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) {
                    return o1.getOrden() > o2.getOrden() ? 1 : -1;
                }
            };

            Collections.sort(detalles, comp);

            for (ResumenVentasCantadas rv : detalles) {
                cols20 = "";
                cols10 = "";
                colsGrue = "";

                int indexFilaActual = ++indexRow;
                HSSFRow row1 = sheet.createRow((indexFilaActual));

                HSSFCell cellZona = row1.createCell(indexCol + 0);
                cellZona.setCellValue(rv.getZona());
                cellZona.setCellStyle(styleTitulo9);

                HSSFCell cellVendedor = row1.createCell(indexCol + 1);
                cellVendedor.setCellValue(rv.getVendedor());
                cellVendedor.setCellStyle(styleTitulo9);

                HSSFCell cellBoletas = row1.createCell(indexCol + 2);
                cellBoletas.setCellValue(rv.getCantboletas());
                cellBoletas.setCellStyle(styleCantidad);

                generarDetalles(row1, indexFilaActual, rv);

            }

            int indexFinGrupo = indexRow + 1;

            int indexTotal1 = ++indexRow;
            int indexTotal2 = ++indexRow;
            int indexTotal3 = ++indexRow;

            HSSFRow rowTotal1 = sheet.createRow(indexTotal1);
            HSSFRow rowTotal2 = sheet.createRow((indexTotal2));
            HSSFRow rowTotal3 = sheet.createRow((indexTotal3));

            aCols20 = cols20.split(",");
            aCols10 = cols10.split(",");
            aColsGrue = colsGrue.split(",");

            //TOTAL 1
            generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr);
            //TOTAL 2
            generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr);
            //TOTAL 3
            generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet);
            //++indexRow;

            indicesTotales += (indexFinGrupo + 1) + ",";
        }

        String[] aIndexTotales = indicesTotales.split(",");

        //TOTAL pais
        int indexTotalPais = ++indexRow;
        HSSFRow rowTotalPais = sheet.createRow((indexTotalPais));
        generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10);

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance()
                .getExternalContext().getResponse();

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls");
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
        FacesContext.getCurrentInstance().responseComplete();
        indexRow = 1;
    }

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• »