Example usage for org.apache.poi.ss.usermodel DataFormat getFormat

List of usage examples for org.apache.poi.ss.usermodel DataFormat getFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DataFormat
  6. getFormat

Introduction

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

Prototype

String getFormat(short index);

Source Link

Document

get the format string that matches the given format index

Usage

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  w  w . j a  v a2s.  co  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.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

@Override
public void apply() {
    for (DataObject dataObject : data) {
        HSSFCell templateCell = dataObject.templateCell;
        HSSFCell resultCell = dataObject.resultCell;
        BandData bandData = dataObject.bandData;

        HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook();
        HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook();

        String templateCellValue = templateCell.getStringCellValue();

        Matcher matcher = pattern.matcher(templateCellValue);
        if (matcher.find()) {
            String paramName = matcher.group(1);
            String styleName = (String) bandData.getParameterValue(paramName);
            if (styleName == null)
                continue;

            HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName);
            if (cellStyle == null)
                continue;

            HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle);

            if (resultStyle == null) {
                HSSFCellStyle newStyle = resultWorkbook.createCellStyle();
                // color
                newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor());
                newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor());
                newStyle.setFillPattern(cellStyle.getFillPattern());

                // borders
                newStyle.setBorderLeft(cellStyle.getBorderLeft());
                newStyle.setBorderRight(cellStyle.getBorderRight());
                newStyle.setBorderTop(cellStyle.getBorderTop());
                newStyle.setBorderBottom(cellStyle.getBorderBottom());

                // border colors
                newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor());
                newStyle.setRightBorderColor(cellStyle.getRightBorderColor());
                newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor());
                newStyle.setTopBorderColor(cellStyle.getTopBorderColor());

                // alignment
                newStyle.setAlignment(cellStyle.getAlignment());
                newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment());
                // misc
                DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat();
                newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString()));
                newStyle.setHidden(cellStyle.getHidden());
                newStyle.setLocked(cellStyle.getLocked());
                newStyle.setIndention(cellStyle.getIndention());
                newStyle.setRotation(cellStyle.getRotation());
                newStyle.setWrapText(cellStyle.getWrapText());
                // font
                HSSFFont cellFont = cellStyle.getFont(templateWorkbook);
                HSSFFont newFont = fontCache.getFontByTemplate(cellFont);

                if (newFont == null) {
                    newFont = resultWorkbook.createFont();

                    newFont.setFontName(cellFont.getFontName());
                    newFont.setItalic(cellFont.getItalic());
                    newFont.setStrikeout(cellFont.getStrikeout());
                    newFont.setTypeOffset(cellFont.getTypeOffset());
                    newFont.setBoldweight(cellFont.getBoldweight());
                    newFont.setCharSet(cellFont.getCharSet());
                    newFont.setColor(cellFont.getColor());
                    newFont.setUnderline(cellFont.getUnderline());
                    newFont.setFontHeight(cellFont.getFontHeight());
                    newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints());
                    fontCache.addCachedFont(cellFont, newFont);
                }/*from ww w .  j  a va  2 s  .c  o  m*/
                newStyle.setFont(newFont);

                resultStyle = newStyle;
                styleCache.addCachedNamedStyle(cellStyle, resultStyle);
            }

            fixNeighbourCellBorders(cellStyle, resultCell);

            resultCell.setCellStyle(resultStyle);

            Sheet sheet = resultCell.getSheet();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {

                    int firstRow = mergedRegion.getFirstRow();
                    int lastRow = mergedRegion.getLastRow();
                    int firstCol = mergedRegion.getFirstColumn();
                    int lastCol = mergedRegion.getLastColumn();

                    for (int row = firstRow; row <= lastRow; row++)
                        for (int col = firstCol; col <= lastCol; col++)
                            sheet.getRow(row).getCell(col).setCellStyle(resultStyle);

                    // cell includes only in one merged region
                    break;
                }
            }
        }
    }
}

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

License:Open Source License

/**
 * ?/*w w w . j  a  v  a2 s .co 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.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java

public void generarExcelVentasCantadas() throws IOException {

    cargaResumen();//from   w  w w  .  j  a v a  2s  .com

    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();//from  ww w.j a v  a 2s  . c  o m

    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

private void creaCabecera(HSSFWorkbook workbook, HSSFSheet sheet) {
    Font fontSubTitulo = workbook.createFont();
    fontSubTitulo.setFontHeightInPoints((short) 8);
    fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD);

    Font fontSubTituloAzul = workbook.createFont();
    fontSubTituloAzul.setFontHeightInPoints((short) 8);
    fontSubTituloAzul.setColor(HSSFColor.DARK_BLUE.index);
    fontSubTituloAzul.setBoldweight(Font.BOLDWEIGHT_BOLD);

    Font fontSubTituloCaje = workbook.createFont();
    fontSubTituloCaje.setFontHeightInPoints((short) 7);
    fontSubTituloCaje.setColor(HSSFColor.DARK_BLUE.index);
    fontSubTituloCaje.setBoldweight(Font.BOLDWEIGHT_BOLD);

    Font font7 = workbook.createFont();
    font7.setFontHeightInPoints((short) 6);

    Font fontTerritorio = workbook.createFont();
    fontTerritorio.setItalic(true);/* w  ww  . j  a v a2 s .c o m*/
    fontTerritorio.setFontHeightInPoints((short) 12);
    fontTerritorio.setBoldweight(Font.BOLDWEIGHT_BOLD);

    Font fontTerritorioTotal1 = workbook.createFont();
    fontTerritorioTotal1.setItalic(true);
    fontTerritorioTotal1.setFontHeightInPoints((short) 8);
    fontTerritorioTotal1.setBoldweight(Font.BOLDWEIGHT_BOLD);

    Font fontTitulo9 = workbook.createFont();
    fontTitulo9.setFontHeightInPoints((short) 8);

    Font fontTitulo = workbook.createFont();
    fontTitulo.setFontHeightInPoints((short) 12);
    fontTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD);

    styleTitulo = workbook.createCellStyle();
    styleTitulo.setFont(fontTitulo);
    styleTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex());

    styleTerritorio = workbook.createCellStyle();
    styleTerritorio.setFont(fontTerritorio);

    styleTerritorioTotal1 = workbook.createCellStyle();
    styleTerritorioTotal1.setFont(fontTerritorioTotal1);
    styleTerritorioTotal1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleTerritorioTotal1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleTerritorioTotal1.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    styleTitulo9 = workbook.createCellStyle();
    styleTitulo9.setFont(fontTitulo9);
    styleTitulo9.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleTitulo9.setBorderTop(HSSFCellStyle.BORDER_THIN);
    //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleTitulo9.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    styleSubTitulo = workbook.createCellStyle();
    styleSubTitulo.setFont(fontSubTitulo);
    styleSubTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styleSubTitulo.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    styleSubTitulo.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    styleSubTitulo.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    styleSubTitulo.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

    styleSubTituloCaje = workbook.createCellStyle();
    styleSubTituloCaje.setFont(fontSubTituloCaje);
    styleSubTituloCaje.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styleSubTituloCaje.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    styleSubTituloCaje.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    styleSubTituloCaje.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    styleSubTituloCaje.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

    styleMergeCentrado = workbook.createCellStyle();
    styleMergeCentrado.setFont(fontSubTituloAzul);
    styleMergeCentrado.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styleMergeCentrado.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    styleMergeCentrado.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    styleMergeCentrado.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    styleMergeCentrado.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    styleMergeCentrado.setWrapText(true);

    styleMergeTotal = workbook.createCellStyle();
    styleMergeTotal.setFont(fontSubTitulo);
    styleMergeTotal.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    styleMergeTotal.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleMergeTotal.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styleMergeTotal.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    styleMergeTotal.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    styleMergeTotal.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    styleMergeTotal.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    styleMergeTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    DataFormat format = workbook.createDataFormat();

    styleCantidadAzul = workbook.createCellStyle();
    styleCantidadAzul.setFont(fontSubTituloAzul);
    styleCantidadAzul.setDataFormat(format.getFormat("#,##0"));
    styleCantidadAzul.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadAzul.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadAzul.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadAzul.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadAzul.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    styleCantidadNegrita = workbook.createCellStyle();
    styleCantidadNegrita.setFont(fontSubTitulo);
    styleCantidadNegrita.setDataFormat(format.getFormat("#,##0"));
    styleCantidadNegrita.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadNegrita.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadNegrita.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadNegrita.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

    styleCantidadGris = workbook.createCellStyle();
    styleCantidadGris.setFont(fontSubTitulo);
    styleCantidadGris.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    styleCantidadGris.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleCantidadGris.setDataFormat(format.getFormat("#,##0"));
    styleCantidadGris.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadGris.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadGris.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    styleCantidadGris.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

    styleCantidad = workbook.createCellStyle();
    styleCantidad.setFont(fontTitulo9);
    styleCantidad.setDataFormat(format.getFormat("#,##0"));
    styleCantidad.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleCantidad.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleCantidad.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    styleCantidadGris1 = workbook.createCellStyle();
    styleCantidadGris1.setFont(fontTitulo9);
    styleCantidadGris1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    styleCantidadGris1.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleCantidadGris1.setDataFormat(format.getFormat("#,##0"));
    styleCantidadGris1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleCantidadGris1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleCantidadGris1.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleCantidadGris1.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    styleCantidadCaje = workbook.createCellStyle();
    styleCantidadCaje.setFont(font7);
    styleCantidadCaje.setDataFormat(format.getFormat("#,##0"));
    styleCantidadCaje.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleCantidadCaje.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleCantidadCaje.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleCantidadCaje.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    HSSFRow rowTitulo = sheet.createRow(0);
    HSSFCell cellTitulo = rowTitulo.createCell(18);
    cellTitulo.setCellValue("CONTROL DIARIO DE VENTAS");
    cellTitulo.setCellStyle(styleTitulo);

    HSSFRow rowFecha = sheet.createRow(++indexRow);
    HSSFCell cellFecha = rowFecha.createCell(18);

    // SimpleDateFormat sdf = new SimpleDateFormat("EEEE, dd 'de' MMMM 'de' yyyy", new Locale("es", "py"));
    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("es", "py"));

    cellFecha.setCellValue("De " + sdf.format(inicio) + " al " + sdf.format(fin));
    //Calendar calender = Calendar.getInstance();
    //cellFecha.setCellValue(calender.get(getInicio()));
    cellFecha.setCellStyle(styleTitulo);

    ++indexRow;
    HSSFRow rowCabeceraMarca = sheet.createRow((++indexRow));
    HSSFRow rowCabeceraProducto = sheet.createRow((++indexRow));
    HSSFRow rowCabecerauM = sheet.createRow((++indexRow));

    rowCabeceraProducto.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints()));

    HSSFCell cell0 = rowCabecerauM.createCell(indexCol);
    cell0.setCellValue("Zona");
    cell0.setCellStyle(styleSubTitulo);

    HSSFCell cell1 = rowCabecerauM.createCell(indexCol + 1);
    cell1.setCellValue("Vendedor");
    cell1.setCellStyle(styleSubTitulo);

    HSSFCell cell2 = rowCabecerauM.createCell(indexCol + 2);
    cell2.setCellValue("Boletas");
    cell2.setCellStyle(styleSubTitulo);

    HSSFCell cell3 = rowCabeceraProducto.createCell(indexCol + 3);
    HSSFCell cell4 = rowCabeceraProducto.createCell(indexCol + 4);
    cell3.setCellValue("Palermo Red Box 20");
    cell3.setCellStyle(styleMergeCentrado);
    cell4.setCellValue("");
    cell4.setCellStyle(styleMergeCentrado);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 3) + "$"
            + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 4) + "$" + indexRow));

    HSSFCell cell3g = rowCabecerauM.createCell(indexCol + 3);
    cell3g.setCellValue("Grue");
    cell3g.setCellStyle(styleMergeCentrado);

    HSSFCell cell3c = rowCabecerauM.createCell(indexCol + 4);
    cell3c.setCellValue("Caj");
    cell3c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell5 = (rowCabeceraProducto).createCell(indexCol + 5);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 5) + "$"
            + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 6) + "$" + indexRow));
    cell5.setCellValue("Palermo Blue Box 20");
    cell5.setCellStyle(styleMergeCentrado);

    HSSFCell cell5g = rowCabecerauM.createCell(indexCol + 5);
    cell5g.setCellValue("Grue");
    cell5g.setCellStyle(styleMergeCentrado);

    HSSFCell cell5c = rowCabecerauM.createCell(indexCol + 6);
    cell5c.setCellValue("Caj");
    cell5c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell7 = (rowCabeceraProducto).createCell(indexCol + 7);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 7) + "$"
            + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 8) + "$" + indexRow));
    cell7.setCellValue("Palermo Green Box 20");
    cell7.setCellStyle(styleMergeCentrado);

    HSSFCell cell7g = rowCabecerauM.createCell(indexCol + 7);
    cell7g.setCellValue("Grue");
    cell7g.setCellStyle(styleMergeCentrado);

    HSSFCell cell7c = rowCabecerauM.createCell(indexCol + 8);
    cell7c.setCellValue("Caj");
    cell7c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell9 = (rowCabeceraProducto).createCell(indexCol + 9);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 9) + "$"
            + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 10) + "$" + indexRow));
    cell9.setCellValue("Palermo Tres Box 20");
    cell9.setCellStyle(styleMergeCentrado);

    HSSFCell cell9g = rowCabecerauM.createCell(indexCol + 9);
    cell9g.setCellValue("Grue");
    cell9g.setCellStyle(styleMergeCentrado);

    HSSFCell cell9c = rowCabecerauM.createCell(indexCol + 10);
    cell9c.setCellValue("Caj");
    cell9c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell11 = (rowCabeceraProducto).createCell(indexCol + 11);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 11)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 12) + "$" + indexRow));
    cell11.setCellValue("Palermo Duo Box 20");
    cell11.setCellStyle(styleMergeCentrado);

    HSSFCell cell11g = rowCabecerauM.createCell(indexCol + 11);
    cell11g.setCellValue("Grue");
    cell11g.setCellStyle(styleMergeCentrado);

    HSSFCell cell11c = rowCabecerauM.createCell(indexCol + 12);
    cell11c.setCellValue("Caj");
    cell11c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell13 = rowCabeceraProducto.createCell(indexCol + 13);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 13)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 14) + "$" + indexRow));
    cell13.setCellValue("Palermo Red Box 10");
    cell13.setCellStyle(styleMergeCentrado);

    HSSFCell cell13g = rowCabecerauM.createCell(indexCol + 13);
    cell13g.setCellValue("Grue");
    cell13g.setCellStyle(styleMergeCentrado);

    HSSFCell cell13c = rowCabecerauM.createCell(indexCol + 14);
    cell13c.setCellValue("Caj");
    cell13c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell15 = (rowCabeceraProducto).createCell(indexCol + 15);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 15)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 16) + "$" + indexRow));
    cell15.setCellValue("Palermo Blue Box 10");
    cell15.setCellStyle(styleMergeCentrado);

    HSSFCell cell15g = rowCabecerauM.createCell(indexCol + 15);
    cell15g.setCellValue("Grue");
    cell15g.setCellStyle(styleMergeCentrado);

    HSSFCell cell15c = rowCabecerauM.createCell(indexCol + 16);
    cell15c.setCellValue("Caj");
    cell15c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell17 = (rowCabeceraProducto).createCell(indexCol + 17);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 17)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 18) + "$" + indexRow));
    cell17.setCellValue("Palermo Green Box 10");
    cell17.setCellStyle(styleMergeCentrado);

    HSSFCell cell17g = rowCabecerauM.createCell(indexCol + 17);
    cell17g.setCellValue("Grue");
    cell17g.setCellStyle(styleMergeCentrado);

    HSSFCell cell17c = rowCabecerauM.createCell(indexCol + 18);
    cell17c.setCellValue("Caj");
    cell17c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell19 = (rowCabeceraProducto).createCell(indexCol + 19);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 19)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 20) + "$" + indexRow));
    cell19.setCellValue("Palermo Tres Box 10");
    cell19.setCellStyle(styleMergeCentrado);

    HSSFCell cell19g = rowCabecerauM.createCell(indexCol + 19);
    cell19g.setCellValue("Grue");
    cell19g.setCellStyle(styleMergeCentrado);

    HSSFCell cell19c = rowCabecerauM.createCell(indexCol + 20);
    cell19c.setCellValue("Caj");
    cell19c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell21 = (rowCabeceraProducto).createCell(indexCol + 21);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 21)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 22) + "$" + indexRow));
    cell21.setCellValue("Palermo Duo Box 10");
    cell21.setCellStyle(styleMergeCentrado);

    HSSFCell cell21g = rowCabecerauM.createCell(indexCol + 21);
    cell21g.setCellValue("Grue");
    cell21g.setCellStyle(styleMergeCentrado);

    HSSFCell cell21c = rowCabecerauM.createCell(indexCol + 22);
    cell21c.setCellValue("Caj");
    cell21c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell23 = (rowCabeceraProducto).createCell(indexCol + 23);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 23)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 24) + "$" + indexRow));
    cell23.setCellValue("San Marino TYPE Box 20");
    cell23.setCellStyle(styleMergeCentrado);

    HSSFCell cell23g = rowCabecerauM.createCell(indexCol + 23);
    cell23g.setCellValue("Grue");
    cell23g.setCellStyle(styleMergeCentrado);

    HSSFCell cell23c = rowCabecerauM.createCell(indexCol + 24);
    cell23c.setCellValue("Caj");
    cell23c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell25 = (rowCabeceraProducto).createCell(indexCol + 25);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 25)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 26) + "$" + indexRow));
    cell25.setCellValue("San Marino TYPE Box 10");
    cell25.setCellStyle(styleMergeCentrado);

    HSSFCell cell25g = rowCabecerauM.createCell(indexCol + 25);
    cell25g.setCellValue("Grue");
    cell25g.setCellStyle(styleMergeCentrado);

    HSSFCell cell25c = rowCabecerauM.createCell(indexCol + 26);
    cell25c.setCellValue("Caj");
    cell25c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell27 = (rowCabeceraProducto).createCell(indexCol + 27);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 27)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 28) + "$" + indexRow));
    cell27.setCellValue("San Marino TYPE Soft");
    cell27.setCellStyle(styleMergeCentrado);

    HSSFCell cell27g = rowCabecerauM.createCell(indexCol + 27);
    cell27g.setCellValue("Grue");
    cell27g.setCellStyle(styleMergeCentrado);

    HSSFCell cell27c = rowCabecerauM.createCell(indexCol + 28);
    cell27c.setCellValue("Caj");
    cell27c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell29 = (rowCabeceraProducto).createCell(indexCol + 29);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 29)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 30) + "$" + indexRow));
    cell29.setCellValue("Kentucky Box 20");
    cell29.setCellStyle(styleMergeCentrado);

    HSSFCell cell29g = rowCabecerauM.createCell(indexCol + 29);
    cell29g.setCellValue("Grue");
    cell29g.setCellStyle(styleMergeCentrado);

    HSSFCell cell29c = rowCabecerauM.createCell(indexCol + 30);
    cell29c.setCellValue("Caj");
    cell29c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell31 = (rowCabeceraProducto).createCell(indexCol + 31);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 31)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 32) + "$" + indexRow));
    cell31.setCellValue("Kentucky Box 10");
    cell31.setCellStyle(styleMergeCentrado);

    HSSFCell cell31g = rowCabecerauM.createCell(indexCol + 31);
    cell31g.setCellValue("Grue");
    cell31g.setCellStyle(styleMergeCentrado);

    HSSFCell cell31c = rowCabecerauM.createCell(indexCol + 32);
    cell31c.setCellValue("Caj");
    cell31c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell33 = (rowCabeceraProducto).createCell(indexCol + 33);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 33)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 34) + "$" + indexRow));
    cell33.setCellValue("Kentucky Soft");
    cell33.setCellStyle(styleMergeCentrado);

    HSSFCell cell33g = rowCabecerauM.createCell(indexCol + 33);
    cell33g.setCellValue("Grue");
    cell33g.setCellStyle(styleMergeCentrado);

    HSSFCell cell33c = rowCabecerauM.createCell(indexCol + 34);
    cell33c.setCellValue("Caj");
    cell33c.setCellStyle(styleSubTituloCaje);

    HSSFCell cell35 = rowCabeceraProducto.createCell(indexCol + 35);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 35)
            + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 36) + "$" + indexRow));
    cell35.setCellValue("Total");
    cell35.setCellStyle(styleMergeTotal);
    HSSFCell cell38 = rowCabeceraProducto.createCell(indexCol + 36);
    cell38.setCellValue("");
    cell38.setCellStyle(styleMergeTotal);

    HSSFCell cell35g = rowCabecerauM.createCell(indexCol + 35);
    cell35g.setCellValue("Gruesas");
    cell35g.setCellStyle(styleSubTitulo);

    HSSFCell cell35c = rowCabecerauM.createCell(indexCol + 36);
    cell35c.setCellValue("Caje");
    cell35c.setCellStyle(styleSubTitulo);

    HSSFCell cell37c = rowCabecerauM.createCell(indexCol + 37);
    cell37c.setCellValue("Cajas");
    cell37c.setCellStyle(styleSubTitulo);

    HSSFCell cell38g = rowCabecerauM.createCell(indexCol + 38);
    cell38g.setCellValue("+gr");
    cell38g.setCellStyle(styleSubTitulo);

    sheet.createFreezePane(2, 7);

}

From source file:com.ideaspymes.proyecttemplate.stock.web.ProductoConsultaBean.java

@Override
public Workbook getWorkBook() {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("My Sample Excel");
    List<CatalogoProductos> lista = (List<CatalogoProductos>) getDetalles();

    sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * new Short("6")));

    org.apache.poi.ss.usermodel.Font fontTitulo = wb.createFont();
    fontTitulo.setFontHeightInPoints((short) 12);
    fontTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);

    org.apache.poi.ss.usermodel.Font fontTituloPricipal = wb.createFont();
    fontTituloPricipal.setFontHeightInPoints((short) 22);
    fontTituloPricipal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);

    DataFormat format = wb.createDataFormat();

    CellStyle styleTituloPrincipal = wb.createCellStyle();
    styleTituloPrincipal.setFont(fontTituloPricipal);
    styleTituloPrincipal.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleTituloPrincipal.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle styleTitulo = wb.createCellStyle();
    styleTitulo.setFont(fontTitulo);/*from w  w  w  . jav  a2s  .  co  m*/
    styleTitulo.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleTitulo.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
    styleTitulo.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleTitulo.setWrapText(true);

    CellStyle styleNumero = wb.createCellStyle();
    styleNumero.setDataFormat(format.getFormat("#,##0"));
    styleNumero.setWrapText(true);
    styleNumero.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleNumero.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle styleFecha = wb.createCellStyle();
    styleFecha.setDataFormat(format.getFormat("dd/MM/yyyy"));
    styleFecha.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleFecha.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true);

    CellStyle styleCenter = wb.createCellStyle();
    styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleCenter.setAlignment(CellStyle.ALIGN_CENTER);
    styleCenter.setWrapText(true);

    Row rowTitle = sheet.createRow(0);
    Cell cellTitle = rowTitle.createCell(1);
    cellTitle.setCellStyle(styleTituloPrincipal);

    sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based)
            1, //last row  (0-based)
            1, //first column (0-based)
            11 //last column  (0-based)
    ));

    cellTitle.setCellValue("Listado de Activos");

    int i = 2;

    Row row0 = sheet.createRow(i);
    row0.setHeight((short) 500);

    Cell cell1 = row0.createCell(1);
    cell1.setCellValue("Foto");
    cell1.setCellStyle(styleTitulo);

    Cell cellFecha = row0.createCell(3);
    cellFecha.setCellValue("Fecha Ingreso");
    cellFecha.setCellStyle(styleTitulo);

    Cell cellFechaCarga = row0.createCell(4);
    cellFechaCarga.setCellValue("Fecha Carga");
    cellFechaCarga.setCellStyle(styleTitulo);

    Cell cell3 = row0.createCell(5);
    cell3.setCellValue("Nombre");
    cell3.setCellStyle(styleTitulo);

    Cell cell4 = row0.createCell(6);
    cell4.setCellValue("Cdigo");
    cell4.setCellStyle(styleTitulo);

    Cell cell5 = row0.createCell(7);
    cell5.setCellValue("Descripcin");
    cell5.setCellStyle(styleTitulo);

    Cell cell6 = row0.createCell(8);
    cell6.setCellValue("Es Regalo?");
    cell6.setCellStyle(styleTitulo);

    Cell cell7 = row0.createCell(9);
    cell7.setCellValue("Familia");
    cell7.setCellStyle(styleTitulo);

    Cell cell8 = row0.createCell(10);
    cell8.setCellValue("Ubicaciones");
    cell8.setCellStyle(styleTitulo);

    Cell cell9 = row0.createCell(11);
    cell9.setCellValue("Stock");
    cell9.setCellStyle(styleTitulo);

    for (CatalogoProductos cp : lista) {

        int indexFila = i + 1;
        if (cp.getImagen() != null) {
            int pictureIdx = wb.addPicture(cp.getImagen(), Workbook.PICTURE_TYPE_PNG);
            CreationHelper helper = wb.getCreationHelper();

            //Creates the top-level drawing patriarch.
            Drawing drawing = sheet.createDrawingPatriarch();

            //Create an anchor that is attached to the worksheet
            ClientAnchor anchor = helper.createClientAnchor();
            //set top-left corner for the image
            anchor.setCol1(1);
            anchor.setRow1(indexFila);

            //Creates a picture
            Picture pict = drawing.createPicture(anchor, pictureIdx);
            //Reset the image to the original size
            pict.resize(0.4);
        }
        Row row1 = sheet.createRow(indexFila);
        row1.setHeightInPoints(80f);

        Cell cellColFecha = row1.createCell(3);

        if (cp.getFecha() != null) {
            cellColFecha.setCellValue(cp.getFecha());
            cellColFecha.setCellStyle(styleFecha);

        } else {
            cellColFecha.setCellValue("");
            cellColFecha.setCellStyle(styleFecha);
        }

        Cell cellColFechaCarga = row1.createCell(4);

        if (cp.getFechaCarga() != null) {
            cellColFechaCarga.setCellValue(cp.getFechaCarga());
            cellColFechaCarga.setCellStyle(styleFecha);

        } else {
            cellColFechaCarga.setCellValue("");
            cellColFechaCarga.setCellStyle(styleFecha);
        }

        Cell cellCol1 = row1.createCell(5);
        cellCol1.setCellValue(cp.getProducto());
        cellCol1.setCellStyle(style);

        Cell cellCol2 = row1.createCell(6);
        cellCol2.setCellValue(cp.getCodigo());
        cellCol2.setCellStyle(styleNumero);

        Cell cellCol3 = row1.createCell(7);
        cellCol3.setCellValue(cp.getDescripcion());
        cellCol3.setCellStyle(style);

        Cell cellCol4 = row1.createCell(8);
        cellCol4.setCellValue(cp.isEsRegalo() ? "SI" : "NO");
        cellCol4.setCellStyle(styleCenter);

        Cell cellCol5 = row1.createCell(9);
        cellCol5.setCellValue(cp.getFamilia());
        cellCol5.setCellStyle(style);

        Cell cellCol6 = row1.createCell(10);
        cellCol6.setCellValue(cp.getUbicaciones());
        cellCol6.setCellStyle(style);

        Cell cellCol7 = row1.createCell(11);
        cellCol7.setCellValue(cp.getStock());
        cellCol7.setCellStyle(styleNumero);

        i++;

    }

    sheet.setColumnWidth(1, 4000);
    sheet.setColumnWidth(2, 0);
    sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(4, 4000);
    sheet.setColumnWidth(5, 10000);
    sheet.setColumnWidth(6, 3000);
    sheet.setColumnWidth(7, 10000);
    sheet.setColumnWidth(8, 3500);
    sheet.setColumnWidth(9, 6000);
    sheet.setColumnWidth(10, 10000);
    sheet.setColumnWidth(11, 2000);

    return wb;
}

From source file:com.jeans.iservlet.action.asset.AssetExportAction.java

private void appendRow(Sheet sheet, Asset asset, int rowNumber) {
    // /*from  w w  w  .  j a  v  a  2 s.  c  om*/
    DataFormat df = sheet.getWorkbook().createDataFormat();
    // ?10?
    Font font = sheet.getWorkbook().createFont();
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ?1???????
    CellStyle cellStyleString = sheet.getWorkbook().createCellStyle();
    cellStyleString.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleString.setFont(font);
    cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyleString.setWrapText(false);
    // ?2????(yyyyMM)???
    CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle();
    cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleDate.setFont(font);
    cellStyleDate.setDataFormat(df.getFormat("yyyyMM"));
    cellStyleDate.setWrapText(false);
    // ?3??????(#)???
    CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle();
    cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleQuantity.setFont(font);
    cellStyleQuantity.setDataFormat(df.getFormat("#"));
    cellStyleQuantity.setWrapText(false);
    // ?4?????(#,##0.00_ )???
    CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle();
    cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleCost.setFont(font);
    cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ "));
    cellStyleCost.setWrapText(false);
    // 20
    Row row = sheet.createRow(rowNumber);
    row.setHeightInPoints(20);
    Cell cell = null;
    if (asset instanceof Hardware) {
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getCode());

        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getFinancialCode());

        cell = row.createCell(2, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        Company company = asset.getCompany();
        if (null != company) {
            cell.setCellValue(company.getAlias());
        }

        cell = row.createCell(3, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog()));

        cell = row.createCell(4, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getName());

        cell = row.createCell(5, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getVendor());

        cell = row.createCell(6, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getModelOrVersion());

        cell = row.createCell(7, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getAssetUsage());

        cell = row.createCell(8, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getSn());

        cell = row.createCell(9, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getConfiguration());

        cell = row.createCell(10, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleDate);
        Date pt = asset.getPurchaseTime();
        if (null != pt) {
            cell.setCellValue(pt);
        }

        cell = row.createCell(11, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleQuantity);
        cell.setCellValue(asset.getQuantity());

        cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleCost);
        cell.setCellValue(asset.getCost().doubleValue());

        cell = row.createCell(13, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getAssetStateName(asset.getState()));

        cell = row.createCell(14, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getHardwareWarrantyName(((Hardware) asset).getWarranty()));

        cell = row.createCell(15, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getLocation());

        cell = row.createCell(16, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Hardware) asset).getIp());

        cell = row.createCell(17, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getHardwareImportanceName(((Hardware) asset).getImportance()));

        cell = row.createCell(18, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        Employee owner = ((Hardware) asset).getOwner();
        if (null != owner) {
            cell.setCellValue(owner.getName());
        }

        cell = row.createCell(19, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getComment());
    } else if (asset instanceof Software) {
        cell = row.createCell(0, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        Company company = asset.getCompany();
        if (null != company) {
            cell.setCellValue(company.getAlias());
        }

        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog()));

        cell = row.createCell(2, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getName());

        cell = row.createCell(3, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getVendor());

        cell = row.createCell(4, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getModelOrVersion());

        cell = row.createCell(5, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getAssetUsage());

        cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleDate);
        Date pt = asset.getPurchaseTime();
        if (null != pt) {
            cell.setCellValue(pt);
        }

        cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleQuantity);
        cell.setCellValue(asset.getQuantity());

        cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleCost);
        cell.setCellValue(asset.getCost().doubleValue());

        cell = row.createCell(9, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getAssetStateName(asset.getState()));

        cell = row.createCell(10, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(AssetConstants.getSoftwareTypeName(((Software) asset).getSoftwareType()));

        cell = row.createCell(11, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(((Software) asset).getLicense());

        cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(cellStyleDate);
        Date et = ((Software) asset).getExpiredTime();
        if (null != et) {
            cell.setCellValue(et);
        }

        cell = row.createCell(13, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyleString);
        cell.setCellValue(asset.getComment());
    }
}

From source file:com.jeans.iservlet.controller.impl.ExportController.java

private void generateSheet(Sheet sheet, byte type, Company company) {
    // sheet//from  w ww.j  ava  2s.c o  m
    if (type == AssetConstants.HARDWARE_ASSET) {
        // 
        generateSheetHeader(sheet, true);
    } else if (type == AssetConstants.SOFTWARE_ASSET) {
        // 
        generateSheetHeader(sheet, false);
    } else if (type >= AssetConstants.NETWORK_EQUIPMENT && type <= AssetConstants.OTHER_EQUIPMENT) {
        // ?
        generateSheetHeader(sheet, true);
    } else if (type >= AssetConstants.OPERATING_SYSTEM_SOFTWARE && type <= AssetConstants.OTHER_SOFTWARE) {
        // ?
        generateSheetHeader(sheet, false);
    }
    List<Asset> assets = astService.listAssets(company, type);
    DataFormat df = sheet.getWorkbook().createDataFormat();
    // ?10?
    Font font = sheet.getWorkbook().createFont();
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ?1???????
    CellStyle cellStyleString = sheet.getWorkbook().createCellStyle();
    cellStyleString.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleString.setFont(font);
    cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyleString.setWrapText(false);
    // ?2????(yyyyMM)???
    CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle();
    cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleDate.setFont(font);
    cellStyleDate.setDataFormat(df.getFormat("yyyyMM"));
    cellStyleDate.setWrapText(false);
    // ?3??????(#)???
    CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle();
    cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleQuantity.setFont(font);
    cellStyleQuantity.setDataFormat(df.getFormat("#"));
    cellStyleQuantity.setWrapText(false);
    // ?4?????(#,##0.00_ )???
    CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle();
    cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleCost.setFont(font);
    cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ "));
    cellStyleCost.setWrapText(false);
    int rowNumber = 1;
    for (Asset asset : assets) {
        appendRow(sheet, asset, rowNumber++, cellStyleString, cellStyleDate, cellStyleQuantity, cellStyleCost);
    }
}

From source file:com.jeans.iservlet.controller.impl.ExportController.java

/**
 * ?????//from   ww  w  .j a  v a  2s.com
 * 
 * @param storedOnly
 *            ???
 * @return
 * @throws IOException
 */
@RequestMapping(method = RequestMethod.POST, value = "/accessories")
public ResponseEntity<byte[]> exportAccessories(@RequestParam boolean storedOnly) throws IOException {
    StringBuilder fn = new StringBuilder(getCurrentCompany().getName());
    Date n = new Date();
    String today = (new SimpleDateFormat("yyyyMMdd")).format(n);
    String now = (new SimpleDateFormat("yyyy-MM-dd HHmmss")).format(n);
    Workbook wb = new XSSFWorkbook();
    fn.append(" - ???(").append(today).append(").xlsx");
    Sheet sheet = wb.createSheet(now);
    // 
    // ?10??
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ?????????
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(font);
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyle.setWrapText(false);
    // 20
    Row row = sheet.createRow(0);
    row.setHeightInPoints(20);
    Cell cell = null;
    for (int i = 0; i < 7; i++) {
        cell = row.createCell(i, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(ACS_HEADERS[i]);
        sheet.setColumnWidth(i, ACS_HEADERS_WIDTH[i] * 256);
    }
    List<Accessory> acs = acsService.listAccessories(getCurrentCompany(), storedOnly);
    Collections.sort(acs, new Comparator<Accessory>() {

        @Override
        public int compare(Accessory o1, Accessory o2) {
            int ret = o1.getType().compareTo(o2.getType());
            if (ret == 0) {
                ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName());
                if (ret == 0) {
                    ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getBrand(), o2.getBrand());
                    if (ret == 0) {
                        ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getModel(),
                                o2.getModel());
                    }
                }
            }
            return ret;
        }

    });
    // 
    DataFormat df = wb.createDataFormat();
    // ?10?
    Font dFont = wb.createFont();
    dFont.setFontName("");
    dFont.setFontHeightInPoints((short) 10);
    // ?1???????
    CellStyle cellStyleString = wb.createCellStyle();
    cellStyleString.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleString.setFont(dFont);
    cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    cellStyleString.setWrapText(false);
    // ?2??????(#)???
    CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle();
    cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyleQuantity.setFont(dFont);
    cellStyleQuantity.setDataFormat(df.getFormat("#"));
    cellStyleQuantity.setWrapText(false);
    int rowNumber = 1;
    for (Accessory ac : acs) {
        // 20
        Row dRow = sheet.createRow(rowNumber);
        dRow.setHeightInPoints(20);
        Cell dCell = null;
        dCell = dRow.createCell(0, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getType().getTitle());

        dCell = dRow.createCell(1, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getName());

        dCell = dRow.createCell(2, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getBrand());

        dCell = dRow.createCell(3, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getModel());

        dCell = dRow.createCell(4, Cell.CELL_TYPE_NUMERIC);
        dCell.setCellStyle(cellStyleQuantity);
        dCell.setCellValue(null == ac.getStorage() ? 0 : ac.getStorage().getQuantity());

        dCell = dRow.createCell(5, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getUnit());

        dCell = dRow.createCell(6, Cell.CELL_TYPE_STRING);
        dCell.setCellStyle(cellStyleString);
        dCell.setCellValue(ac.getDescription());

        rowNumber++;
    }
    String filename = null;
    if (isIE()) {
        filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20");
    } else {
        filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1");
    }
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-disposition", "attachment; filename=" + filename);
    BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096);
    wb.write(out);
    wb.close();
    out.close();

    return null;
}