Example usage for org.apache.poi.hssf.usermodel HSSFCell setCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. setCellValue

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFCell setCellValue.

Prototype

@SuppressWarnings("fallthrough")
public void setCellValue(boolean value)

Source Link

Document

set a boolean value for the cell

Usage

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

public void generarExcelVentasCantadasDetalle() throws IOException {

    cargaResumenPorVendedor();//from ww w  . j  av 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

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);/*from   w w  w .j  av  a  2  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.arthyweb.ventas.web.controllers.VentasCantadasBean.java

private void generarTotal3(HSSFRow rowTotal3, int indexFinGrupo, CellStyle style, HSSFCell cellTerr,
        HSSFSheet sheet) {/*www  .j  a  va2s .  c  om*/
    HSSFCell cellTotal3Territorio = rowTotal3.createCell(indexCol);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol) + "$"
            + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 1) + "$" + indexRow));
    cellTotal3Territorio.setCellValue("Part. De Emb / " + cellTerr.getStringCellValue());
    cellTotal3Territorio.setCellStyle(styleTerritorioTotal3);

    HSSFCell cellTotal3Territorio1 = rowTotal3.createCell(indexCol + 1);
    cellTotal3Territorio1.setCellStyle(styleTerritorioTotal3);

    HSSFCell cellTotal3Boletas = rowTotal3.createCell(indexCol + 2);
    cellTotal3Boletas.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Red20g = rowTotal3.createCell(indexCol + 3);
    cellTotal3Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Red20g.setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Red20g.setCellStyle(style);

    HSSFCell cellTotal3Red20c = rowTotal3.createCell(indexCol + 4);
    cellTotal3Red20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Blue20g = rowTotal3.createCell(indexCol + 5);
    cellTotal3Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Blue20g.setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Blue20g.setCellStyle(style);

    HSSFCell cellTotal3Blue20c = rowTotal3.createCell(indexCol + 6);
    cellTotal3Blue20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Green20g = rowTotal3.createCell(indexCol + 7);
    cellTotal3Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Green20g.setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Green20g.setCellStyle(style);

    HSSFCell cellTotal3Green20c = rowTotal3.createCell(indexCol + 8);
    cellTotal3Green20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Tres20g = rowTotal3.createCell(indexCol + 9);
    cellTotal3Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Tres20g.setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Tres20g.setCellStyle(style);

    HSSFCell cellTotal3Tres20c = rowTotal3.createCell(indexCol + 10);
    cellTotal3Tres20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Duo20g = rowTotal3.createCell(indexCol + 11);
    cellTotal3Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Duo20g.setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Duo20g.setCellStyle(style);

    HSSFCell cellTotal3Duo20c = rowTotal3.createCell(indexCol + 12);
    cellTotal3Duo20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Red10g = rowTotal3.createCell(indexCol + 13);
    cellTotal3Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Red10g.setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Red10g.setCellStyle(style);

    HSSFCell cellTotal3Red10c = rowTotal3.createCell(indexCol + 14);
    cellTotal3Red10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Blue10g = rowTotal3.createCell(indexCol + 15);
    cellTotal3Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Blue10g.setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Blue10g.setCellStyle(style);

    HSSFCell cellTotal3Blue10c = rowTotal3.createCell(indexCol + 16);
    cellTotal3Blue10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Green10g = rowTotal3.createCell(indexCol + 17);
    cellTotal3Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Green10g.setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Green10g.setCellStyle(style);

    HSSFCell cellTotal3Green10c = rowTotal3.createCell(indexCol + 18);
    cellTotal3Green10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Tres10g = rowTotal3.createCell(indexCol + 19);
    cellTotal3Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Tres10g.setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Tres10g.setCellStyle(style);

    HSSFCell cellTotal3Tres10c = rowTotal3.createCell(indexCol + 20);
    cellTotal3Tres10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3Duo10g = rowTotal3.createCell(indexCol + 21);
    cellTotal3Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Duo10g.setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3Duo10g.setCellStyle(style);

    HSSFCell cellTotal3Duo10c = rowTotal3.createCell(indexCol + 22);
    cellTotal3Duo10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3SM20g = rowTotal3.createCell(indexCol + 23);
    cellTotal3SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3SM20g.setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3SM20g.setCellStyle(style);

    HSSFCell cellTotal3SM20c = rowTotal3.createCell(indexCol + 24);
    cellTotal3SM20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3SM10g = rowTotal3.createCell(indexCol + 25);
    cellTotal3SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3SM10g.setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3SM10g.setCellStyle(style);

    HSSFCell cellTotal3SM10c = rowTotal3.createCell(indexCol + 26);
    cellTotal3SM10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3SMSoftg = rowTotal3.createCell(indexCol + 27);
    cellTotal3SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3SMSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3SMSoftg.setCellStyle(style);

    HSSFCell cellTotal3SMSoftc = rowTotal3.createCell(indexCol + 28);
    cellTotal3SMSoftc.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3K20g = rowTotal3.createCell(indexCol + 29);
    cellTotal3K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3K20g.setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3K20g.setCellStyle(style);

    HSSFCell cellTotal3K20c = rowTotal3.createCell(indexCol + 30);
    cellTotal3K20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3K10g = rowTotal3.createCell(indexCol + 31);
    cellTotal3K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3K10g.setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3K10g.setCellStyle(style);

    HSSFCell cellTotal3K10c = rowTotal3.createCell(indexCol + 32);
    cellTotal3K10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3KSoftg = rowTotal3.createCell(indexCol + 33);
    cellTotal3KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3KSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexFinGrupo + 1)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3KSoftg.setCellStyle(style);

    HSSFCell cellTotal3KSoftc = rowTotal3.createCell(indexCol + 34);
    cellTotal3KSoftc.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal3g = rowTotal3.createCell(indexCol + 35);
    cellTotal3g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3g.setCellFormula(CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + "/$"
            + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1));
    cellTotal3g.setCellStyle(style);

    HSSFCell cellTotal3c = rowTotal3.createCell(indexCol + 36);
    cellTotal3c.setCellStyle(styleCantidadGris);

    HSSFCell cellTotal3caja = rowTotal3.createCell(indexCol + 37);
    cellTotal3caja.setCellStyle(styleCantidadNegrita);

    HSSFCell cellTotal3gr = rowTotal3.createCell(indexCol + 38);
    cellTotal3gr.setCellStyle(styleCantidadNegrita);

}

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

private void generarTotal2(HSSFRow rowTotal2, int indexFinGrupo, String[] aColsGrue, HSSFCell cellTerr) {
    HSSFCell cellTotal2Territorio = rowTotal2.createCell(indexCol);
    cellTotal2Territorio.setCellValue("Total " + cellTerr.getStringCellValue() + " - Cajas");
    cellTotal2Territorio.setCellStyle(styleTerritorioTotal1);

    HSSFCell cellTotal2Boletas = rowTotal2.createCell(indexCol + 2);
    cellTotal2Boletas.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Red20g = rowTotal2.createCell(indexCol + 3);
    cellTotal2Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Red20g//from  ww w  .  ja  v  a 2 s. co m
            .setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexFinGrupo + 1) + "/50");
    cellTotal2Red20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Red20c = rowTotal2.createCell(indexCol + 4);
    cellTotal2Red20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Blue20g = rowTotal2.createCell(indexCol + 5);
    cellTotal2Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Blue20g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexFinGrupo + 1) + "/50");
    cellTotal2Blue20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Blue20c = rowTotal2.createCell(indexCol + 6);
    cellTotal2Blue20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Green20g = rowTotal2.createCell(indexCol + 7);
    cellTotal2Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Green20g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexFinGrupo + 1) + "/50");
    cellTotal2Green20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Green20c = rowTotal2.createCell(indexCol + 8);
    cellTotal2Green20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Tres20g = rowTotal2.createCell(indexCol + 9);
    cellTotal2Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Tres20g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexFinGrupo + 1) + "/50");
    cellTotal2Tres20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Tres20c = rowTotal2.createCell(indexCol + 10);
    cellTotal2Tres20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Duo20g = rowTotal2.createCell(indexCol + 11);
    cellTotal2Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Duo20g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexFinGrupo + 1) + "/50");
    cellTotal2Duo20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Duo20c = rowTotal2.createCell(indexCol + 12);
    cellTotal2Duo20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Red10g = rowTotal2.createCell(indexCol + 13);
    cellTotal2Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Red10g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexFinGrupo + 1) + "/50");
    cellTotal2Red10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Red10c = rowTotal2.createCell(indexCol + 14);
    cellTotal2Red10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Blue10g = rowTotal2.createCell(indexCol + 15);
    cellTotal2Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Blue10g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexFinGrupo + 1) + "/50");
    cellTotal2Blue10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Blue10c = rowTotal2.createCell(indexCol + 16);
    cellTotal2Blue10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Green10g = rowTotal2.createCell(indexCol + 17);
    cellTotal2Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Green10g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexFinGrupo + 1) + "/50");
    cellTotal2Green10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Green10c = rowTotal2.createCell(indexCol + 18);
    cellTotal2Green10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Tres10g = rowTotal2.createCell(indexCol + 19);
    cellTotal2Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Tres10g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexFinGrupo + 1) + "/50");
    cellTotal2Tres10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Tres10c = rowTotal2.createCell(indexCol + 20);
    cellTotal2Tres10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Duo10g = rowTotal2.createCell(indexCol + 21);
    cellTotal2Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2Duo10g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexFinGrupo + 1) + "/50");
    cellTotal2Duo10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2Duo10c = rowTotal2.createCell(indexCol + 22);
    cellTotal2Duo10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2SM20g = rowTotal2.createCell(indexCol + 23);
    cellTotal2SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2SM20g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexFinGrupo + 1) + "/50");
    cellTotal2SM20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2SM20c = rowTotal2.createCell(indexCol + 24);
    cellTotal2SM20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2SM10g = rowTotal2.createCell(indexCol + 25);
    cellTotal2SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2SM10g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexFinGrupo + 1) + "/50");
    cellTotal2SM10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2SM10c = rowTotal2.createCell(indexCol + 26);
    cellTotal2SM10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2SMSoftg = rowTotal2.createCell(indexCol + 27);
    cellTotal2SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2SMSoftg
            .setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexFinGrupo + 1) + "/50");
    cellTotal2SMSoftg.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2SMSoftc = rowTotal2.createCell(indexCol + 28);
    cellTotal2SMSoftc.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2K20g = rowTotal2.createCell(indexCol + 29);
    cellTotal2K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2K20g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexFinGrupo + 1) + "/50");
    cellTotal2K20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2K20c = rowTotal2.createCell(indexCol + 30);
    cellTotal2K20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2K10g = rowTotal2.createCell(indexCol + 31);
    cellTotal2K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2K10g
            .setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexFinGrupo + 1) + "/50");
    cellTotal2K10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2K10c = rowTotal2.createCell(indexCol + 32);
    cellTotal2K10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2KSoftg = rowTotal2.createCell(indexCol + 33);
    cellTotal2KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2KSoftg
            .setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexFinGrupo + 1) + "/50");
    cellTotal2KSoftg.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal22KSoftc = rowTotal2.createCell(indexCol + 34);
    cellTotal22KSoftc.setCellStyle(styleCantidadAzul);

    String formTotal2Cajas = "";
    for (int i = 0; i < aColsGrue.length; i++) {
        formTotal2Cajas += aColsGrue[i] + (indexFinGrupo + 2) + "+";
    }
    formTotal2Cajas += "0";

    HSSFCell cellTotal2g = rowTotal2.createCell(indexCol + 35);
    cellTotal2g.setCellStyle(styleCantidadGris);

    HSSFCell cellTotal2c = rowTotal2.createCell(indexCol + 36);
    cellTotal2c.setCellStyle(styleCantidadGris);

    HSSFCell cellTotal2caja = rowTotal2.createCell(indexCol + 37);
    cellTotal2caja.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2caja.setCellFormula(formTotal2Cajas);
    cellTotal2caja.setCellStyle(styleCantidadNegrita);

    HSSFCell cellTotal2gr = rowTotal2.createCell(indexCol + 38);
    cellTotal2gr.setCellStyle(styleCantidadNegrita);

}

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

private void generarTotal1(HSSFRow rowTotal1, int indexInicioGrupo, int indexFinGrupo, String[] aColsGrue,
        String[] aCols20, String[] aCols10, HSSFCell cellTerr) {
    HSSFCell cellTotal1Territorio = rowTotal1.createCell(indexCol);
    cellTotal1Territorio.setCellValue("Total " + cellTerr.getStringCellValue());
    cellTotal1Territorio.setCellStyle(styleTerritorioTotal1);

    HSSFCell cellTotal1Boleta = rowTotal1.createCell(indexCol + 2);
    cellTotal1Boleta.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Boleta.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 2)
            + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 2) + indexFinGrupo + ")");
    cellTotal1Boleta.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Red20g = rowTotal1.createCell(indexCol + 3);
    cellTotal1Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Red20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 3)
            + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 3) + indexFinGrupo + ")");
    cellTotal1Red20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Red20c = rowTotal1.createCell(indexCol + 4);
    cellTotal1Red20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Red20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 4)
            + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 4) + indexFinGrupo + ")");
    cellTotal1Red20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Blue20g = rowTotal1.createCell(indexCol + 5);
    cellTotal1Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Blue20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 5)
            + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 5) + indexFinGrupo + ")");
    cellTotal1Blue20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Blue20c = rowTotal1.createCell(indexCol + 6);
    cellTotal1Blue20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Blue20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 6)
            + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 6) + indexFinGrupo + ")");
    cellTotal1Blue20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Green20g = rowTotal1.createCell(indexCol + 7);
    cellTotal1Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Green20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 7)
            + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 7) + indexFinGrupo + ")");
    cellTotal1Green20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Green20c = rowTotal1.createCell(indexCol + 8);
    cellTotal1Green20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Green20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 8)
            + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 8) + indexFinGrupo + ")");
    cellTotal1Green20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Tres20g = rowTotal1.createCell(indexCol + 9);
    cellTotal1Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Tres20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 9)
            + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 9) + indexFinGrupo + ")");
    cellTotal1Tres20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Tres20c = rowTotal1.createCell(indexCol + 10);
    cellTotal1Tres20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Tres20c/* w  w  w  .  ja  v a2s . c o  m*/
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 10) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 10) + indexFinGrupo + ")");
    cellTotal1Tres20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Duo20g = rowTotal1.createCell(indexCol + 11);
    cellTotal1Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Duo20g
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 11) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 11) + indexFinGrupo + ")");
    cellTotal1Duo20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Duo20c = rowTotal1.createCell(indexCol + 12);
    cellTotal1Duo20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Duo20c
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 12) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 12) + indexFinGrupo + ")");
    cellTotal1Duo20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Red10g = rowTotal1.createCell(indexCol + 13);
    cellTotal1Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Red10g
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 13) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 13) + indexFinGrupo + ")");
    cellTotal1Red10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Red10c = rowTotal1.createCell(indexCol + 14);
    cellTotal1Red10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Red10c
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 14) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 14) + indexFinGrupo + ")");
    cellTotal1Red10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Blue10g = rowTotal1.createCell(indexCol + 15);
    cellTotal1Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Blue10g
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 15) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 15) + indexFinGrupo + ")");
    cellTotal1Blue10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Blue10c = rowTotal1.createCell(indexCol + 16);
    cellTotal1Blue10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Blue10c
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 16) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 16) + indexFinGrupo + ")");
    cellTotal1Blue10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Green10g = rowTotal1.createCell(indexCol + 17);
    cellTotal1Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Green10g
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 17) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 17) + indexFinGrupo + ")");
    cellTotal1Green10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Green10c = rowTotal1.createCell(indexCol + 18);
    cellTotal1Green10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Green10c
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 18) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 18) + indexFinGrupo + ")");
    cellTotal1Green10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Tres10g = rowTotal1.createCell(indexCol + 19);
    cellTotal1Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Tres10g
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 19) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 19) + indexFinGrupo + ")");
    cellTotal1Tres10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Tres10c = rowTotal1.createCell(indexCol + 20);
    cellTotal1Tres10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Tres10c
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 20) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 20) + indexFinGrupo + ")");
    cellTotal1Tres10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Duo10g = rowTotal1.createCell(indexCol + 21);
    cellTotal1Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Duo10g
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 21) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 21) + indexFinGrupo + ")");
    cellTotal1Duo10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Duo10c = rowTotal1.createCell(indexCol + 22);
    cellTotal1Duo10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Duo10c
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 22) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 22) + indexFinGrupo + ")");
    cellTotal1Duo10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SM20g = rowTotal1.createCell(indexCol + 23);
    cellTotal1SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SM20g
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 23) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 23) + indexFinGrupo + ")");
    cellTotal1SM20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SM20c = rowTotal1.createCell(indexCol + 24);
    cellTotal1SM20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SM20c
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 24) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 24) + indexFinGrupo + ")");
    cellTotal1SM20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SM10g = rowTotal1.createCell(indexCol + 25);
    cellTotal1SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SM10g
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 25) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 25) + indexFinGrupo + ")");
    cellTotal1SM10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SM10c = rowTotal1.createCell(indexCol + 26);
    cellTotal1SM10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SM10c
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 26) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 26) + indexFinGrupo + ")");
    cellTotal1SM10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SMSoftg = rowTotal1.createCell(indexCol + 27);
    cellTotal1SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SMSoftg
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 27) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 27) + indexFinGrupo + ")");
    cellTotal1SMSoftg.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SMSoftc = rowTotal1.createCell(indexCol + 28);
    cellTotal1SMSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SMSoftc
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 28) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 28) + indexFinGrupo + ")");
    cellTotal1SMSoftc.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1K20g = rowTotal1.createCell(indexCol + 29);
    cellTotal1K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1K20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 29) + indexInicioGrupo
            + ":" + CellReference.convertNumToColString(indexCol + 29) + indexFinGrupo + ")");
    cellTotal1K20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1K20c = rowTotal1.createCell(indexCol + 30);
    cellTotal1K20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1K20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 30) + indexInicioGrupo
            + ":" + CellReference.convertNumToColString(indexCol + 30) + indexFinGrupo + ")");
    cellTotal1K20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1K10g = rowTotal1.createCell(indexCol + 31);
    cellTotal1K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1K10g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 31) + indexInicioGrupo
            + ":" + CellReference.convertNumToColString(indexCol + 31) + indexFinGrupo + ")");
    cellTotal1K10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1K10c = rowTotal1.createCell(indexCol + 32);
    cellTotal1K10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1K10c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 32) + indexInicioGrupo
            + ":" + CellReference.convertNumToColString(indexCol + 32) + indexFinGrupo + ")");
    cellTotal1K10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1KSoftg = rowTotal1.createCell(indexCol + 33);
    cellTotal1KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1KSoftg
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 33) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 33) + indexFinGrupo + ")");
    cellTotal1KSoftg.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1KSoftc = rowTotal1.createCell(indexCol + 34);
    cellTotal1KSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1KSoftc
            .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 34) + indexInicioGrupo + ":"
                    + CellReference.convertNumToColString(indexCol + 34) + indexFinGrupo + ")");
    cellTotal1KSoftc.setCellStyle(styleCantidadAzul);

    String formTotal1Grue = "";
    for (int i = 0; i < aColsGrue.length; i++) {
        formTotal1Grue += aColsGrue[i] + (indexFinGrupo + 1) + "+";
    }
    formTotal1Grue += "0";

    String formTotal120 = "";
    for (int i = 0; i < aCols20.length; i++) {
        formTotal120 += aCols20[i] + (indexFinGrupo + 1) + "+";
    }

    formTotal120 += "0";
    String formTotal110 = "";
    for (int i = 0; i < aCols10.length; i++) {
        formTotal110 += aCols10[i] + (indexFinGrupo + 1) + "+";
    }
    formTotal110 += "0";

    HSSFCell cellTotal1g = rowTotal1.createCell(indexCol + 35);
    cellTotal1g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1g.setCellFormula(
            "(INT((" + formTotal120 + ")/10))+" + "(INT((" + formTotal110 + ")/20)) +(" + formTotal1Grue + ")");
    cellTotal1g.setCellStyle(styleCantidadGris);

    HSSFCell cellTotal1c = rowTotal1.createCell(indexCol + 36);
    cellTotal1c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1c.setCellFormula("MOD((" + formTotal110 + "),20)+ MOD((" + formTotal120 + "),10)");
    cellTotal1c.setCellStyle(styleCantidadGris);

    HSSFCell cellTotal1ca = rowTotal1.createCell(indexCol + 37);
    cellTotal1ca.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1ca.setCellFormula(
            "(INT(" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + "/50))");
    cellTotal1ca.setCellStyle(styleCantidadNegrita);

    HSSFCell cellTotal1gr = rowTotal1.createCell(indexCol + 38);
    cellTotal1gr.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1gr.setCellFormula(
            "(MOD(" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + ",50))");
    cellTotal1gr.setCellStyle(styleCantidadNegrita);

}

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

private void generarDetalles(HSSFRow row1, int indexFilaActual, ResumenVentasCantadas rv) {

    String formulaSumGruesa = "(";
    String formulaSumCaje10 = "((";//"(INT((";
    String formulaSumCaje20 = "((";//"(INT((";

    HSSFCell cellPRed20g = row1.createCell(indexCol + 3);
    cellPRed20g.setCellValue(rv.getGruesas706());//rv.getGruesas706()
    cellPRed20g.setCellStyle(styleCantidad);

    HSSFCell cellPRed20c = row1.createCell(indexCol + 4);
    cellPRed20c.setCellValue(rv.getRestocaje706());//rv.getRestocaje706()
    cellPRed20c.setCellStyle(styleCantidadCaje);

    indexFilaActual = indexFilaActual + 1;

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 3) + indexFilaActual + "+";
    formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 4) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 3) + ",";
    cols20 += CellReference.convertNumToColString(indexCol + 4) + ",";

    HSSFCell cellPBlue20g = row1.createCell(indexCol + 5);
    cellPBlue20g.setCellValue(rv.getGruesas710());//rv.getGruesas710()
    cellPBlue20g.setCellStyle(styleCantidad);

    HSSFCell cellPBlue20c = row1.createCell(indexCol + 6);
    cellPBlue20c.setCellValue(rv.getRestocaje710());//rv.getRestocaje710()
    cellPBlue20c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 5) + indexFilaActual + "+";
    formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 6) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 5) + ",";
    cols20 += CellReference.convertNumToColString(indexCol + 6) + ",";

    HSSFCell cellPGreen20g = row1.createCell(indexCol + 7);
    cellPGreen20g.setCellValue(rv.getGruesas708());//rv.getGruesas708()
    cellPGreen20g.setCellStyle(styleCantidad);
    HSSFCell cellPGreen20c = row1.createCell(indexCol + 8);
    cellPGreen20c.setCellValue(rv.getRestocaje708()); //rv.getRestocaje708()
    cellPGreen20c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 7) + indexFilaActual + "+";
    formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 8) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 7) + ",";
    cols20 += CellReference.convertNumToColString(indexCol + 8) + ",";

    HSSFCell cellPTres20g = row1.createCell(indexCol + 9);
    cellPTres20g.setCellValue(rv.getGruesas704());//rv.getGruesas704()
    cellPTres20g.setCellStyle(styleCantidad);
    HSSFCell cellPTres20c = row1.createCell(indexCol + 10);
    cellPTres20c.setCellValue(rv.getRestocaje704());//rv.getRestocaje704()
    cellPTres20c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 9) + indexFilaActual + "+";
    formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 10) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 9) + ",";
    cols20 += CellReference.convertNumToColString(indexCol + 10) + ",";

    HSSFCell cellPDuo20g = row1.createCell(indexCol + 11);
    cellPDuo20g.setCellValue(rv.getGruesas702());//rv.getGruesas702()
    cellPDuo20g.setCellStyle(styleCantidad);
    HSSFCell cellPDuo20c = row1.createCell(indexCol + 12);
    cellPDuo20c.setCellValue(rv.getRestocaje702());//rv.getRestocaje702()
    cellPDuo20c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 11) + indexFilaActual + "+";
    formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 12) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 11) + ",";
    cols20 += CellReference.convertNumToColString(indexCol + 12) + ",";

    HSSFCell cellPRed10g = row1.createCell(indexCol + 13);
    cellPRed10g.setCellValue(rv.getGruesas705());//rv.getGruesas705()
    cellPRed10g.setCellStyle(styleCantidad);
    HSSFCell cellPRed10c = row1.createCell(indexCol + 14);
    cellPRed10c.setCellValue(rv.getRestocaje705());//rv.getRestocaje705()
    cellPRed10c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 13) + indexFilaActual + "+";
    formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 14) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 13) + ",";
    cols10 += CellReference.convertNumToColString(indexCol + 14) + ",";

    HSSFCell cellPBlue10g = row1.createCell(indexCol + 15);
    cellPBlue10g.setCellValue(rv.getGruesas709());//rv.getGruesas709()
    cellPBlue10g.setCellStyle(styleCantidad);
    HSSFCell cellPBlue10c = row1.createCell(indexCol + 16);
    cellPBlue10c.setCellValue(rv.getRestocaje709());//rv.getRestocaje709()
    cellPBlue10c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 15) + indexFilaActual + "+";
    formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 16) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 15) + ",";
    cols10 += CellReference.convertNumToColString(indexCol + 16) + ",";

    HSSFCell cellPGreen10g = row1.createCell(indexCol + 17);
    cellPGreen10g.setCellValue(rv.getGruesas707());//rv.getGruesas707()
    cellPGreen10g.setCellStyle(styleCantidad);
    HSSFCell cellPGreen10c = row1.createCell(indexCol + 18);
    cellPGreen10c.setCellValue(rv.getRestocaje707());//rv.getRestocaje707()
    cellPGreen10c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 17) + indexFilaActual + "+";
    formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 18) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 17) + ",";
    cols10 += CellReference.convertNumToColString(indexCol + 18) + ",";

    HSSFCell cellPTres10g = row1.createCell(indexCol + 19);
    cellPTres10g.setCellValue(rv.getGruesas703());//rv.getGruesas703()
    cellPTres10g.setCellStyle(styleCantidad);
    HSSFCell cellPTres10c = row1.createCell(indexCol + 20);
    cellPTres10c.setCellValue(rv.getRestocaje703());//rv.getRestocaje703()
    cellPTres10c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 19) + indexFilaActual + "+";
    formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 20) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 19) + ",";
    cols10 += CellReference.convertNumToColString(indexCol + 20) + ",";

    HSSFCell cellPDuo10g = row1.createCell(indexCol + 21);
    cellPDuo10g.setCellValue(rv.getGruesas701());//rv.getGruesas701()
    cellPDuo10g.setCellStyle(styleCantidad);
    HSSFCell cellPDuo10c = row1.createCell(indexCol + 22);
    cellPDuo10c.setCellValue(rv.getRestocaje701());//rv.getRestocaje701()
    cellPDuo10c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 21) + indexFilaActual + "+";
    formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 22) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 21) + ",";
    cols10 += CellReference.convertNumToColString(indexCol + 22) + ",";

    HSSFCell cellSM20g = row1.createCell(indexCol + 23);
    cellSM20g.setCellValue(rv.getGruesas411());//rv.getGruesas413()
    cellSM20g.setCellStyle(styleCantidad);
    HSSFCell cellSM20c = row1.createCell(indexCol + 24);
    cellSM20c.setCellValue(rv.getRestocaje411());//rv.getRestocaje413()
    cellSM20c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 23) + indexFilaActual + "+";
    formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 24) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 23) + ",";
    cols20 += CellReference.convertNumToColString(indexCol + 24) + ",";

    HSSFCell cellSM10g = row1.createCell(indexCol + 25);
    cellSM10g.setCellValue(rv.getGruesas412());//rv.getGruesas412()
    cellSM10g.setCellStyle(styleCantidad);
    HSSFCell cellSM10c = row1.createCell(indexCol + 26);
    cellSM10c.setCellValue(rv.getRestocaje412());//rv.getRestocaje412()
    cellSM10c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 25) + indexFilaActual + "+";
    formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 26) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 25) + ",";
    cols10 += CellReference.convertNumToColString(indexCol + 26) + ",";

    HSSFCell cellSMSoftg = row1.createCell(indexCol + 27);
    cellSMSoftg.setCellValue(rv.getGruesas413());//rv.getGruesas411()
    cellSMSoftg.setCellStyle(styleCantidad);
    HSSFCell cellSMSoftc = row1.createCell(indexCol + 28);
    cellSMSoftc.setCellValue(rv.getRestocaje413());//rv.getRestocaje411()
    cellSMSoftc.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 27) + indexFilaActual + "+";
    formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 28) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 27) + ",";
    cols20 += CellReference.convertNumToColString(indexCol + 28) + ",";

    HSSFCell cellK20g = row1.createCell(indexCol + 29);
    cellK20g.setCellValue(rv.getGruesas198());//rv.getGruesas198()
    cellK20g.setCellStyle(styleCantidad);
    HSSFCell cellK20c = row1.createCell(indexCol + 30);
    cellK20c.setCellValue(rv.getRestocaje198());//rv.getRestocaje198()
    cellK20c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 29) + indexFilaActual + "+";
    formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 30) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 29) + ",";
    cols10 += CellReference.convertNumToColString(indexCol + 30) + ",";

    HSSFCell cellK10g = row1.createCell(indexCol + 31);
    cellK10g.setCellValue(rv.getGruesas218());//rv.getGruesas218()
    cellK10g.setCellStyle(styleCantidad);
    HSSFCell cellK10c = row1.createCell(indexCol + 32);
    cellK10c.setCellValue(rv.getRestocaje218());//rv.getRestocaje218()
    cellK10c.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 31) + indexFilaActual + "+";
    formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 32) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 31) + ",";
    cols10 += CellReference.convertNumToColString(indexCol + 32) + ",";

    HSSFCell cellKSoftg = row1.createCell(indexCol + 33);
    cellKSoftg.setCellValue(rv.getGruesas204());//rv.getGruesas218()
    cellKSoftg.setCellStyle(styleCantidad);
    HSSFCell cellKSoftc = row1.createCell(indexCol + 34);
    cellKSoftc.setCellValue(rv.getRestocaje204());//rv.getRestocaje218()
    cellKSoftc.setCellStyle(styleCantidadCaje);

    formulaSumGruesa += CellReference.convertNumToColString(indexCol + 33) + indexFilaActual + "+";
    formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 34) + indexFilaActual + "+";
    colsGrue += CellReference.convertNumToColString(indexCol + 33) + ",";
    cols20 += CellReference.convertNumToColString(indexCol + 34) + ",";

    formulaSumGruesa += "0)";
    String formulaTotalGruesas = "(INT" + formulaSumCaje10 + "0)/20))+(INT" + formulaSumCaje20 + "0)/10))+"
            + formulaSumGruesa;//from w w  w .ja  va 2s. c  om
    String formulaTotalCaje = "MOD" + formulaSumCaje10 + "0),20)+ MOD" + formulaSumCaje20 + "0),10)";

    HSSFCell cellTotalg = row1.createCell(indexCol + 35);
    cellTotalg.setCellFormula(formulaTotalGruesas);
    cellTotalg.setCellStyle(styleCantidadGris1);
    HSSFCell cellTotalc = row1.createCell(indexCol + 36);
    cellTotalc.setCellFormula(formulaTotalCaje);
    cellTotalc.setCellStyle(styleCantidadGris1);

    HSSFCell cellTotalca = row1.createCell(indexCol + 37);
    cellTotalca.setCellFormula(
            "(INT(" + CellReference.convertNumToColString(indexCol + 35) + indexFilaActual + "/50))");
    cellTotalca.setCellStyle(styleCantidad);

    HSSFCell cellTotalgr = row1.createCell(indexCol + 38);
    cellTotalgr.setCellFormula(
            "(MOD(" + CellReference.convertNumToColString(indexCol + 35) + indexFilaActual + ",50))");
    cellTotalgr.setCellStyle(styleCantidad);
}

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

private void generarTotalPais(HSSFRow rowTotalPais, CellStyle styleTotal3, HSSFSheet sheet,
        String[] aIndexTotales, String[] aColsGrue, String[] aCols20, String[] aCols10) {

    //GeneraTotal1Pais
    HSSFCell cellTotal1Pais = rowTotalPais.createCell(indexCol);
    cellTotal1Pais.setCellValue("Total Pas");
    cellTotal1Pais.setCellStyle(styleTerritorioTotal1);

    HSSFCell cellTotal1Boleta = rowTotalPais.createCell(indexCol + 2);
    cellTotal1Boleta.setCellType(HSSFCell.CELL_TYPE_FORMULA);

    String formBoletaTotal1Pais = "";

    String formRed20Total1Paisg = "";
    String formRed20Total1Paisc = "";
    String formBlue20Total1Paisg = "";
    String formBlue20Total1Paisc = "";
    String formGreen20Total1Paisg = "";
    String formGreen20Total1Paisc = "";
    String formTres20Total1Paisg = "";
    String formTres20Total1Paisc = "";
    String formDuo20Total1Paisg = "";
    String formDuo20Total1Paisc = "";

    String formRed10Total1Paisg = "";
    String formRed10Total1Paisc = "";
    String formBlue10Total1Paisg = "";
    String formBlue10Total1Paisc = "";
    String formGreen10Total1Paisg = "";
    String formGreen10Total1Paisc = "";
    String formTres10Total1Paisg = "";
    String formTres10Total1Paisc = "";
    String formDuo10Total1Paisg = "";
    String formDuo10Total1Paisc = "";

    String formSM20Total1Paisg = "";
    String formSM20Total1Paisc = "";
    String formSM10Total1Paisg = "";
    String formSM10Total1Paisc = "";
    String formSMSoftTotal1Paisg = "";
    String formSMSoftTotal1Paisc = "";

    String formKY20Total1Paisg = "";
    String formKY20Total1Paisc = "";
    String formKY10Total1Paisg = "";
    String formKY10Total1Paisc = "";
    String formKYSoftTotal1Paisg = "";
    String formKYSoftTotal1Paisc = "";

    String formRed20Total2Paisg = "";
    String formBlue20Total2Paisg = "";
    String formGreen20Total2Paisg = "";
    String formTres20Total2Paisg = "";
    String formDuo20Total2Paisg = "";

    String formRed10Total2Paisg = "";
    String formBlue10Total2Paisg = "";
    String formGreen10Total2Paisg = "";
    String formTres10Total2Paisg = "";
    String formDuo10Total2Paisg = "";

    String formSM20Total2Paisg = "";
    String formSM10Total2Paisg = "";
    String formSMSoftTotal2Paisg = "";

    String formKY20Total2Paisg = "";
    String formKY10Total2Paisg = "";
    String formKYSoftTotal2Paisg = "";

    for (int i = 0; i < aIndexTotales.length; i++) {
        String indiceFila = aIndexTotales[i];
        if (indiceFila != null && indiceFila.length() > 0) {
            formBoletaTotal1Pais += CellReference.convertNumToColString(indexCol + 2) + aIndexTotales[i] + "+";

            formRed20Total1Paisg += CellReference.convertNumToColString(indexCol + 3) + aIndexTotales[i] + "+";
            formRed20Total1Paisc += CellReference.convertNumToColString(indexCol + 4) + aIndexTotales[i] + "+"; // "(INT("+CellReference.convertNumToColString(indexCol + 4)+aIndexTotales[i]+")/10)";
            formBlue20Total1Paisg += CellReference.convertNumToColString(indexCol + 5) + aIndexTotales[i] + "+";
            formBlue20Total1Paisc += CellReference.convertNumToColString(indexCol + 6) + aIndexTotales[i] + "+";
            formGreen20Total1Paisg += CellReference.convertNumToColString(indexCol + 7) + aIndexTotales[i]
                    + "+";
            formGreen20Total1Paisc += CellReference.convertNumToColString(indexCol + 8) + aIndexTotales[i]
                    + "+";
            formTres20Total1Paisg += CellReference.convertNumToColString(indexCol + 9) + aIndexTotales[i] + "+";
            formTres20Total1Paisc += CellReference.convertNumToColString(indexCol + 10) + aIndexTotales[i]
                    + "+";
            formDuo20Total1Paisg += CellReference.convertNumToColString(indexCol + 11) + aIndexTotales[i] + "+";
            formDuo20Total1Paisc += CellReference.convertNumToColString(indexCol + 12) + aIndexTotales[i] + "+";

            formRed10Total1Paisg += CellReference.convertNumToColString(indexCol + 13) + aIndexTotales[i] + "+";
            formRed10Total1Paisc += CellReference.convertNumToColString(indexCol + 14) + aIndexTotales[i] + "+"; // "(INT("+CellReference.convertNumToColString(indexCol + 4)+aIndexTotales[i]+")/10)";
            formBlue10Total1Paisg += CellReference.convertNumToColString(indexCol + 15) + aIndexTotales[i]
                    + "+";
            formBlue10Total1Paisc += CellReference.convertNumToColString(indexCol + 16) + aIndexTotales[i]
                    + "+";
            formGreen10Total1Paisg += CellReference.convertNumToColString(indexCol + 17) + aIndexTotales[i]
                    + "+";
            formGreen10Total1Paisc += CellReference.convertNumToColString(indexCol + 18) + aIndexTotales[i]
                    + "+";
            formTres10Total1Paisg += CellReference.convertNumToColString(indexCol + 19) + aIndexTotales[i]
                    + "+";
            formTres10Total1Paisc += CellReference.convertNumToColString(indexCol + 20) + aIndexTotales[i]
                    + "+";
            formDuo10Total1Paisg += CellReference.convertNumToColString(indexCol + 21) + aIndexTotales[i] + "+";
            formDuo10Total1Paisc += CellReference.convertNumToColString(indexCol + 22) + aIndexTotales[i] + "+";

            formSM20Total1Paisg += CellReference.convertNumToColString(indexCol + 23) + aIndexTotales[i] + "+";
            formSM20Total1Paisc += CellReference.convertNumToColString(indexCol + 24) + aIndexTotales[i] + "+";
            formSM10Total1Paisg += CellReference.convertNumToColString(indexCol + 25) + aIndexTotales[i] + "+";
            formSM10Total1Paisc += CellReference.convertNumToColString(indexCol + 26) + aIndexTotales[i] + "+";
            formSMSoftTotal1Paisg += CellReference.convertNumToColString(indexCol + 27) + aIndexTotales[i]
                    + "+";
            formSMSoftTotal1Paisc += CellReference.convertNumToColString(indexCol + 28) + aIndexTotales[i]
                    + "+";

            formKY20Total1Paisg += CellReference.convertNumToColString(indexCol + 29) + aIndexTotales[i] + "+";
            formKY20Total1Paisc += CellReference.convertNumToColString(indexCol + 30) + aIndexTotales[i] + "+";
            formKY10Total1Paisg += CellReference.convertNumToColString(indexCol + 31) + aIndexTotales[i] + "+";
            formKY10Total1Paisc += CellReference.convertNumToColString(indexCol + 32) + aIndexTotales[i] + "+";
            formKYSoftTotal1Paisg += CellReference.convertNumToColString(indexCol + 33) + aIndexTotales[i]
                    + "+";
            formKYSoftTotal1Paisc += CellReference.convertNumToColString(indexCol + 34) + aIndexTotales[i]
                    + "+";
        }//from   ww  w  . j  a  va2  s.c om

    }

    System.out.println("Formula Boletas 1 : " + formBoletaTotal1Pais);

    formRed20Total1Paisg += "0";
    formRed20Total1Paisc += "0";

    formRed20Total2Paisg = "(" + formRed20Total1Paisg + ")/50";

    formRed20Total1Paisg = formRed20Total1Paisg + "+(INT((" + formRed20Total1Paisc + ")/10))";
    formRed20Total1Paisc = "+(MOD((" + formRed20Total1Paisc + "),10))";

    formBlue20Total1Paisg += "0";
    formBlue20Total1Paisc += "0";

    formBlue20Total2Paisg = "(" + formBlue20Total1Paisg + ")/50";

    formBlue20Total1Paisg = formBlue20Total1Paisg + "+(INT((" + formBlue20Total1Paisc + ")/10))";
    formBlue20Total1Paisc = "+(MOD((" + formBlue20Total1Paisc + "),10))";

    formGreen20Total1Paisg += "0";
    formGreen20Total1Paisc += "0";

    formGreen20Total2Paisg = "(" + formGreen20Total1Paisg + ")/50";

    formGreen20Total1Paisg = formGreen20Total1Paisg + "+(INT((" + formGreen20Total1Paisc + ")/10))";
    formGreen20Total1Paisc = "+(MOD((" + formGreen20Total1Paisc + "),10))";

    formTres20Total1Paisg += "0";
    formTres20Total1Paisc += "0";

    formTres20Total2Paisg = "(" + formTres20Total1Paisg + ")/50";

    formTres20Total1Paisg = formTres20Total1Paisg + "+(INT((" + formTres20Total1Paisc + ")/10))";
    formTres20Total1Paisc = "+(MOD((" + formTres20Total1Paisc + "),10))";

    formDuo20Total1Paisg += "0";
    formDuo20Total1Paisc += "0";

    formDuo20Total2Paisg = "(" + formDuo20Total1Paisg + ")/50";

    formDuo20Total1Paisg = formDuo20Total1Paisg + "+(INT((" + formDuo20Total1Paisc + ")/10))";
    formDuo20Total1Paisc = "+(MOD((" + formDuo20Total1Paisc + "),10))";

    formRed10Total1Paisg += "0";
    formRed10Total1Paisc += "0";

    formRed10Total2Paisg = "(" + formRed10Total1Paisg + ")/50";

    formRed10Total1Paisg = formRed10Total1Paisg + "+(INT((" + formRed10Total1Paisc + ")/20))";
    formRed10Total1Paisc = "+(MOD((" + formRed10Total1Paisc + "),20))";

    formBlue10Total1Paisg += "0";
    formBlue10Total1Paisc += "0";

    formBlue10Total2Paisg = "(" + formBlue10Total1Paisg + ")/50";

    formBlue10Total1Paisg = formBlue10Total1Paisg + "+(INT((" + formBlue10Total1Paisc + ")/20))";
    formBlue10Total1Paisc = "+(MOD((" + formBlue10Total1Paisc + "),20))";

    formGreen10Total1Paisg += "0";
    formGreen10Total1Paisc += "0";

    formGreen10Total2Paisg = "(" + formGreen10Total1Paisg + ")/50";

    formGreen10Total1Paisg = formGreen10Total1Paisg + "+(INT((" + formGreen10Total1Paisc + ")/20))";
    formGreen10Total1Paisc = "+(MOD((" + formGreen10Total1Paisc + "),20))";

    formTres10Total1Paisg += "0";
    formTres10Total1Paisc += "0";

    formTres10Total2Paisg = "(" + formTres10Total1Paisg + ")/50";

    formTres10Total1Paisg = formTres10Total1Paisg + "+(INT((" + formTres10Total1Paisc + ")/20))";
    formTres10Total1Paisc = "+(MOD((" + formTres10Total1Paisc + "),20))";

    formDuo10Total1Paisg += "0";
    formDuo10Total1Paisc += "0";

    formDuo10Total2Paisg = "(" + formDuo10Total1Paisg + ")/50";

    formDuo10Total1Paisg = formDuo10Total1Paisg + "+(INT((" + formDuo10Total1Paisc + ")/20))";
    formDuo10Total1Paisc = "+(MOD((" + formDuo10Total1Paisc + "),20))";

    formSM20Total1Paisg += "0";
    formSM20Total1Paisc += "0";

    formSM20Total2Paisg = "(" + formSM20Total1Paisg + ")/50";

    formSM20Total1Paisg = formSM20Total1Paisg + "+(INT((" + formSM20Total1Paisc + ")/10))";
    formSM20Total1Paisc = "+(MOD((" + formSM20Total1Paisc + "),10))";

    formSM10Total1Paisg += "0";
    formSM10Total1Paisc += "0";

    formSM10Total2Paisg = "(" + formSM10Total1Paisg + ")/50";

    formSM10Total1Paisg = formSM10Total1Paisg + "+(INT((" + formSM10Total1Paisc + ")/20))";
    formSM10Total1Paisc = "+(MOD((" + formSM10Total1Paisc + "),20))";

    formSMSoftTotal1Paisg += "0";
    formSMSoftTotal1Paisc += "0";

    formSMSoftTotal2Paisg = "(" + formSMSoftTotal1Paisg + ")/50";

    formSMSoftTotal1Paisg = formSMSoftTotal1Paisg + "+(INT((" + formSMSoftTotal1Paisc + ")/10))";
    formSMSoftTotal1Paisc = "+(MOD((" + formSMSoftTotal1Paisc + "),10))";

    formKY20Total1Paisg += "0";
    formKY20Total1Paisc += "0";

    formKY20Total2Paisg = "(" + formKY20Total1Paisg + ")/50";

    formKY20Total1Paisg = formKY20Total1Paisg + "+(INT((" + formKY20Total1Paisc + ")/10))";
    formKY20Total1Paisc = "+(MOD((" + formKY20Total1Paisc + "),10))";

    formKY10Total1Paisg += "0";
    formKY10Total1Paisc += "0";

    formKY10Total2Paisg = "(" + formKY10Total1Paisg + ")/50";

    formKY10Total1Paisg = formKY10Total1Paisg + "+(INT((" + formKY10Total1Paisc + ")/20))";
    formKY10Total1Paisc = "+(MOD((" + formKY10Total1Paisc + "),20))";

    formKYSoftTotal1Paisg += "0";
    formKYSoftTotal1Paisc += "0";

    formKYSoftTotal2Paisg = "(" + formKYSoftTotal1Paisg + ")/50";

    formKYSoftTotal1Paisg = formKYSoftTotal1Paisg + "+(INT((" + formKYSoftTotal1Paisc + ")/10))";
    formKYSoftTotal1Paisc = "+(MOD((" + formKYSoftTotal1Paisc + "),10))";

    formBoletaTotal1Pais += "0";
    cellTotal1Boleta.setCellFormula(formBoletaTotal1Pais);
    cellTotal1Boleta.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Red20g = rowTotalPais.createCell(indexCol + 3);
    cellTotal1Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Red20c = rowTotalPais.createCell(indexCol + 4);
    cellTotal1Red20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Red20g.setCellFormula(formRed20Total1Paisg);
    cellTotal1Red20g.setCellStyle(styleCantidadAzul);
    cellTotal1Red20c.setCellFormula(formRed20Total1Paisc);
    cellTotal1Red20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Blue20g = rowTotalPais.createCell(indexCol + 5);
    cellTotal1Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Blue20c = rowTotalPais.createCell(indexCol + 6);
    cellTotal1Blue20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Blue20g.setCellFormula(formBlue20Total1Paisg);
    cellTotal1Blue20g.setCellStyle(styleCantidadAzul);
    cellTotal1Blue20c.setCellFormula(formBlue20Total1Paisc);
    cellTotal1Blue20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Green20g = rowTotalPais.createCell(indexCol + 7);
    cellTotal1Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Green20c = rowTotalPais.createCell(indexCol + 8);
    cellTotal1Green20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Green20g.setCellFormula(formGreen20Total1Paisg);
    cellTotal1Green20g.setCellStyle(styleCantidadAzul);
    cellTotal1Green20c.setCellFormula(formGreen20Total1Paisc);
    cellTotal1Green20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Tres20g = rowTotalPais.createCell(indexCol + 9);
    cellTotal1Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Tres20c = rowTotalPais.createCell(indexCol + 10);
    cellTotal1Tres20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Tres20g.setCellFormula(formTres20Total1Paisg);
    cellTotal1Tres20g.setCellStyle(styleCantidadAzul);
    cellTotal1Tres20c.setCellFormula(formTres20Total1Paisc);
    cellTotal1Tres20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Duo20g = rowTotalPais.createCell(indexCol + 11);
    cellTotal1Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Duo20c = rowTotalPais.createCell(indexCol + 12);
    cellTotal1Duo20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Duo20g.setCellFormula(formDuo20Total1Paisg);
    cellTotal1Duo20g.setCellStyle(styleCantidadAzul);
    cellTotal1Duo20c.setCellFormula(formDuo20Total1Paisc);
    cellTotal1Duo20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Red10g = rowTotalPais.createCell(indexCol + 13);
    cellTotal1Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Red10c = rowTotalPais.createCell(indexCol + 14);
    cellTotal1Red10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Red10g.setCellFormula(formRed10Total1Paisg);
    cellTotal1Red10g.setCellStyle(styleCantidadAzul);
    cellTotal1Red10c.setCellFormula(formRed10Total1Paisc);
    cellTotal1Red10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Blue10g = rowTotalPais.createCell(indexCol + 15);
    cellTotal1Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Blue10c = rowTotalPais.createCell(indexCol + 16);
    cellTotal1Blue10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Blue10g.setCellFormula(formBlue10Total1Paisg);
    cellTotal1Blue10g.setCellStyle(styleCantidadAzul);
    cellTotal1Blue10c.setCellFormula(formBlue10Total1Paisc);
    cellTotal1Blue10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Green10g = rowTotalPais.createCell(indexCol + 17);
    cellTotal1Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Green10c = rowTotalPais.createCell(indexCol + 18);
    cellTotal1Green10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Green10g.setCellFormula(formGreen10Total1Paisg);
    cellTotal1Green10g.setCellStyle(styleCantidadAzul);
    cellTotal1Green10c.setCellFormula(formGreen10Total1Paisc);
    cellTotal1Green10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Tres10g = rowTotalPais.createCell(indexCol + 19);
    cellTotal1Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Tres10c = rowTotalPais.createCell(indexCol + 20);
    cellTotal1Tres10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Tres10g.setCellFormula(formTres10Total1Paisg);
    cellTotal1Tres10g.setCellStyle(styleCantidadAzul);
    cellTotal1Tres10c.setCellFormula(formTres10Total1Paisc);
    cellTotal1Tres10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1Duo10g = rowTotalPais.createCell(indexCol + 21);
    cellTotal1Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1Duo10c = rowTotalPais.createCell(indexCol + 22);
    cellTotal1Duo10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1Duo10g.setCellFormula(formDuo10Total1Paisg);
    cellTotal1Duo10g.setCellStyle(styleCantidadAzul);
    cellTotal1Duo10c.setCellFormula(formDuo10Total1Paisc);
    cellTotal1Duo10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SM20g = rowTotalPais.createCell(indexCol + 23);
    cellTotal1SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1SM20c = rowTotalPais.createCell(indexCol + 24);
    cellTotal1SM20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SM20g.setCellFormula(formSM20Total1Paisg);
    cellTotal1SM20g.setCellStyle(styleCantidadAzul);
    cellTotal1SM20c.setCellFormula(formSM20Total1Paisc);
    cellTotal1SM20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SM10g = rowTotalPais.createCell(indexCol + 25);
    cellTotal1SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1SM10c = rowTotalPais.createCell(indexCol + 26);
    cellTotal1SM10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SM10g.setCellFormula(formSM10Total1Paisg);
    cellTotal1SM10g.setCellStyle(styleCantidadAzul);
    cellTotal1SM10c.setCellFormula(formSM10Total1Paisc);
    cellTotal1SM10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1SMSoftg = rowTotalPais.createCell(indexCol + 27);
    cellTotal1SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1SMSoftc = rowTotalPais.createCell(indexCol + 28);
    cellTotal1SMSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1SMSoftg.setCellFormula(formSMSoftTotal1Paisg);
    cellTotal1SMSoftg.setCellStyle(styleCantidadAzul);
    cellTotal1SMSoftc.setCellFormula(formSMSoftTotal1Paisc);
    cellTotal1SMSoftc.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1KY20g = rowTotalPais.createCell(indexCol + 29);
    cellTotal1KY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1KY20c = rowTotalPais.createCell(indexCol + 30);
    cellTotal1KY20c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1KY20g.setCellFormula(formKY20Total1Paisg);
    cellTotal1KY20g.setCellStyle(styleCantidadAzul);
    cellTotal1KY20c.setCellFormula(formKY20Total1Paisc);
    cellTotal1KY20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1KY10g = rowTotalPais.createCell(indexCol + 31);
    cellTotal1KY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1KY10c = rowTotalPais.createCell(indexCol + 32);
    cellTotal1KY10c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1KY10g.setCellFormula(formKY10Total1Paisg);
    cellTotal1KY10g.setCellStyle(styleCantidadAzul);
    cellTotal1KY10c.setCellFormula(formKY10Total1Paisc);
    cellTotal1KY10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal1KYSoftg = rowTotalPais.createCell(indexCol + 33);
    cellTotal1KYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    HSSFCell cellTotal1KYSoftc = rowTotalPais.createCell(indexCol + 34);
    cellTotal1KYSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal1KYSoftg.setCellFormula(formKYSoftTotal1Paisg);
    cellTotal1KYSoftg.setCellStyle(styleCantidadAzul);
    cellTotal1KYSoftc.setCellFormula(formKYSoftTotal1Paisc);
    cellTotal1KYSoftc.setCellStyle(styleCantidadAzul);

    String formTotalPaisGrue = "";
    if (aColsGrue != null) {
        for (int i = 0; i < aColsGrue.length; i++) {
            formTotalPaisGrue += aColsGrue[i] + (rowTotalPais.getRowNum() + 1) + "+";
            System.out.println("Row pais: " + rowTotalPais.getRowNum() + 1);
        }
        formTotalPaisGrue += "0";
    }

    String formTotalPais20 = "";
    if (aCols20 != null) {
        for (int i = 0; i < aCols20.length; i++) {
            formTotalPais20 += aCols20[i] + (rowTotalPais.getRowNum() + 1) + "+";
        }
        formTotalPais20 += "0";
    }

    String formTotalPais10 = "";
    if (aCols10 != null) {
        for (int i = 0; i < aCols10.length; i++) {
            formTotalPais10 += aCols10[i] + (rowTotalPais.getRowNum() + 1) + "+";
        }
        formTotalPais10 += "0";
    }

    System.out.println("form: " + formTotalPaisGrue);

    HSSFCell cellTotalPaisg = rowTotalPais.createCell(indexCol + 35);
    cellTotalPaisg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotalPaisg.setCellFormula("(INT((" + formTotalPais20 + ")/10))+" + "(INT((" + formTotalPais10
            + ")/20)) +(" + formTotalPaisGrue + ")");
    cellTotalPaisg.setCellStyle(styleCantidadGris);

    HSSFCell cellTotalPaisc = rowTotalPais.createCell(indexCol + 36);
    cellTotalPaisc.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotalPaisc.setCellFormula("MOD((" + formTotalPais10 + "),20)+ MOD((" + formTotalPais20 + "),10)");
    cellTotalPaisc.setCellStyle(styleCantidadGris);

    HSSFCell cellTotalPaisca = rowTotalPais.createCell(indexCol + 37);
    cellTotalPaisca.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotalPaisca.setCellFormula("(INT(" + CellReference.convertNumToColString(indexCol + 35)
            + (rowTotalPais.getRowNum() + 1) + "/50))");
    cellTotalPaisca.setCellStyle(styleCantidadNegrita);

    HSSFCell cellTotalPaisgr = rowTotalPais.createCell(indexCol + 38);
    cellTotalPaisgr.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotalPaisgr.setCellFormula("(MOD(" + CellReference.convertNumToColString(indexCol + 35)
            + (rowTotalPais.getRowNum() + 1) + ",50))");
    cellTotalPaisgr.setCellStyle(styleCantidadNegrita);

    //GeneraTotal2Pais
    int indexTotal2Pais = ++indexRow;
    HSSFRow rowTotal2Pais = sheet.createRow((indexTotal2Pais));
    HSSFCell cellTotal2Pais = rowTotal2Pais.createCell(indexCol);
    cellTotal2Pais.setCellValue("Total Pas - Cajas");
    cellTotal2Pais.setCellStyle(styleTerritorioTotal1);

    HSSFCell cellTotal2PaisBoletas = rowTotal2Pais.createCell(indexCol + 2);
    cellTotal2PaisBoletas.setCellValue("");
    cellTotal2PaisBoletas.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisRed20g = rowTotal2Pais.createCell(indexCol + 3);
    cellTotal2PaisRed20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisRed20g.setCellFormula(formRed20Total2Paisg);
    cellTotal2PaisRed20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisRed20c = rowTotal2Pais.createCell(indexCol + 4);
    cellTotal2PaisRed20c.setCellValue("");
    cellTotal2PaisRed20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisBlue20g = rowTotal2Pais.createCell(indexCol + 5);
    cellTotal2PaisBlue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisBlue20g.setCellFormula(formBlue20Total2Paisg);
    cellTotal2PaisBlue20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisBlue20c = rowTotal2Pais.createCell(indexCol + 6);
    cellTotal2PaisBlue20c.setCellValue("");
    cellTotal2PaisBlue20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisGreen20g = rowTotal2Pais.createCell(indexCol + 7);
    cellTotal2PaisGreen20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisGreen20g.setCellFormula(formGreen20Total2Paisg);
    cellTotal2PaisGreen20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisGreen20c = rowTotal2Pais.createCell(indexCol + 8);
    cellTotal2PaisGreen20c.setCellValue("");
    cellTotal2PaisGreen20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisTres20g = rowTotal2Pais.createCell(indexCol + 9);
    cellTotal2PaisTres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisTres20g.setCellFormula(formTres20Total2Paisg);
    cellTotal2PaisTres20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisTres20c = rowTotal2Pais.createCell(indexCol + 10);
    cellTotal2PaisTres20c.setCellValue("");
    cellTotal2PaisTres20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisDuo20g = rowTotal2Pais.createCell(indexCol + 11);
    cellTotal2PaisDuo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisDuo20g.setCellFormula(formDuo20Total2Paisg);
    cellTotal2PaisDuo20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisDuo20c = rowTotal2Pais.createCell(indexCol + 12);
    cellTotal2PaisDuo20c.setCellValue("");
    cellTotal2PaisDuo20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisRed10g = rowTotal2Pais.createCell(indexCol + 13);
    cellTotal2PaisRed10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisRed10g.setCellFormula(formRed10Total2Paisg);
    cellTotal2PaisRed10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisRed10c = rowTotal2Pais.createCell(indexCol + 14);
    cellTotal2PaisRed10c.setCellValue("");
    cellTotal2PaisRed10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisBlue10g = rowTotal2Pais.createCell(indexCol + 15);
    cellTotal2PaisBlue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisBlue10g.setCellFormula(formBlue10Total2Paisg);
    cellTotal2PaisBlue10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisBlue10c = rowTotal2Pais.createCell(indexCol + 16);
    cellTotal2PaisBlue10c.setCellValue("");
    cellTotal2PaisBlue10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisGreen10g = rowTotal2Pais.createCell(indexCol + 17);
    cellTotal2PaisGreen10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisGreen10g.setCellFormula(formGreen10Total2Paisg);
    cellTotal2PaisGreen10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisGreen10c = rowTotal2Pais.createCell(indexCol + 18);
    cellTotal2PaisGreen10c.setCellValue("");
    cellTotal2PaisGreen10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisTres10g = rowTotal2Pais.createCell(indexCol + 19);
    cellTotal2PaisTres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisTres10g.setCellFormula(formTres10Total2Paisg);
    cellTotal2PaisTres10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisTres10c = rowTotal2Pais.createCell(indexCol + 20);
    cellTotal2PaisTres10c.setCellValue("");
    cellTotal2PaisTres10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisDuo10g = rowTotal2Pais.createCell(indexCol + 21);
    cellTotal2PaisDuo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisDuo10g.setCellFormula(formDuo10Total2Paisg);
    cellTotal2PaisDuo10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisDuo10c = rowTotal2Pais.createCell(indexCol + 22);
    cellTotal2PaisDuo10c.setCellValue("");
    cellTotal2PaisDuo10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisSM20g = rowTotal2Pais.createCell(indexCol + 23);
    cellTotal2PaisSM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisSM20g.setCellFormula(formSM20Total2Paisg);
    cellTotal2PaisSM20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisSM20c = rowTotal2Pais.createCell(indexCol + 24);
    cellTotal2PaisSM20c.setCellValue("");
    cellTotal2PaisSM20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisSM10g = rowTotal2Pais.createCell(indexCol + 25);
    cellTotal2PaisSM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisSM10g.setCellFormula(formSM10Total2Paisg);
    cellTotal2PaisSM10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisSM10c = rowTotal2Pais.createCell(indexCol + 26);
    cellTotal2PaisSM10c.setCellValue("");
    cellTotal2PaisSM10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisSMSoftg = rowTotal2Pais.createCell(indexCol + 27);
    cellTotal2PaisSMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisSMSoftg.setCellFormula(formSMSoftTotal2Paisg);
    cellTotal2PaisSMSoftg.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisSMSoftc = rowTotal2Pais.createCell(indexCol + 28);
    cellTotal2PaisSMSoftc.setCellValue("");
    cellTotal2PaisSMSoftc.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisKY20g = rowTotal2Pais.createCell(indexCol + 29);
    cellTotal2PaisKY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisKY20g.setCellFormula(formKY20Total2Paisg);
    cellTotal2PaisKY20g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisKY20c = rowTotal2Pais.createCell(indexCol + 30);
    cellTotal2PaisKY20c.setCellValue("");
    cellTotal2PaisKY20c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisKY10g = rowTotal2Pais.createCell(indexCol + 31);
    cellTotal2PaisKY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisKY10g.setCellFormula(formKY10Total2Paisg);
    cellTotal2PaisKY10g.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisKY10c = rowTotal2Pais.createCell(indexCol + 32);
    cellTotal2PaisKY10c.setCellValue("");
    cellTotal2PaisKY10c.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisKYSoftg = rowTotal2Pais.createCell(indexCol + 33);
    cellTotal2PaisKYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal2PaisKYSoftg.setCellFormula(formKYSoftTotal2Paisg);
    cellTotal2PaisKYSoftg.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotal2PaisKYSoftc = rowTotal2Pais.createCell(indexCol + 34);
    cellTotal2PaisKYSoftc.setCellValue("");
    cellTotal2PaisKYSoftc.setCellStyle(styleCantidadAzul);

    HSSFCell cellTotalPais2caja = rowTotal2Pais.createCell(indexCol + 37);
    cellTotalPais2caja.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotalPais2caja.setCellFormula("(INT(" + CellReference.convertNumToColString(indexCol + 35)
            + (rowTotalPais.getRowNum() + 1) + "/50))");
    cellTotalPais2caja.setCellStyle(styleCantidadNegrita);

    //GeneraTotal3Pais
    int indexTotal3Pais = ++indexRow;
    HSSFRow rowTotal3Pais = sheet.createRow((indexTotal3Pais));
    HSSFCell cellTotal3Pais = rowTotal3Pais.createCell(indexCol);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol) + "$"
            + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 1) + "$" + indexRow));
    cellTotal3Pais.setCellValue("Part. De Emb / Pas");
    cellTotal3Pais.setCellStyle(styleTerritorioTotal3);
    HSSFCell cellTotal3Pais1 = rowTotal3Pais.createCell(indexCol + 1);
    cellTotal3Pais1.setCellStyle(styleTerritorioTotal3);

    HSSFCell cellTotal3PaisBoletas = rowTotal3Pais.createCell(indexCol + 2);
    cellTotal3PaisBoletas.setCellValue("");
    cellTotal3PaisBoletas.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Red20g = rowTotal3Pais.createCell(indexCol + 3);
    cellTotal3Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Red20g.setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexTotal2Pais) + "/$"
            + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Red20g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisRed20c = rowTotal3Pais.createCell(indexCol + 4);
    cellTotal3PaisRed20c.setCellValue("");
    cellTotal3PaisRed20c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Blue20g = rowTotal3Pais.createCell(indexCol + 5);
    cellTotal3Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Blue20g.setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Blue20g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisBlue20c = rowTotal3Pais.createCell(indexCol + 6);
    cellTotal3PaisBlue20c.setCellValue("");
    cellTotal3PaisBlue20c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Green20g = rowTotal3Pais.createCell(indexCol + 7);
    cellTotal3Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Green20g.setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Green20g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisGreen20c = rowTotal3Pais.createCell(indexCol + 8);
    cellTotal3PaisGreen20c.setCellValue("");
    cellTotal3PaisGreen20c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Tres20g = rowTotal3Pais.createCell(indexCol + 9);
    cellTotal3Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Tres20g.setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Tres20g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisTres20c = rowTotal3Pais.createCell(indexCol + 10);
    cellTotal3PaisTres20c.setCellValue("");
    cellTotal3PaisTres20c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Duo20g = rowTotal3Pais.createCell(indexCol + 11);
    cellTotal3Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Duo20g.setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Duo20g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisDuo20c = rowTotal3Pais.createCell(indexCol + 12);
    cellTotal3PaisDuo20c.setCellValue("");
    cellTotal3PaisDuo20c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Red10g = rowTotal3Pais.createCell(indexCol + 13);
    cellTotal3Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Red10g.setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Red10g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisRed10c = rowTotal3Pais.createCell(indexCol + 14);
    cellTotal3PaisRed10c.setCellValue("");
    cellTotal3PaisRed10c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Blue10g = rowTotal3Pais.createCell(indexCol + 15);
    cellTotal3Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Blue10g.setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Blue10g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisBlue10c = rowTotal3Pais.createCell(indexCol + 16);
    cellTotal3PaisBlue10c.setCellValue("");
    cellTotal3PaisBlue10c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Green10g = rowTotal3Pais.createCell(indexCol + 17);
    cellTotal3Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Green10g.setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Green10g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisGreen10c = rowTotal3Pais.createCell(indexCol + 18);
    cellTotal3PaisGreen10c.setCellValue("");
    cellTotal3PaisGreen10c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Tres10g = rowTotal3Pais.createCell(indexCol + 19);
    cellTotal3Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Tres10g.setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Tres10g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisTres10c = rowTotal3Pais.createCell(indexCol + 20);
    cellTotal3PaisTres10c.setCellValue("");
    cellTotal3PaisTres10c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3Duo10g = rowTotal3Pais.createCell(indexCol + 21);
    cellTotal3Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3Duo10g.setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3Duo10g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisDuo10c = rowTotal3Pais.createCell(indexCol + 22);
    cellTotal3PaisDuo10c.setCellValue("");
    cellTotal3PaisDuo10c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3SM20g = rowTotal3Pais.createCell(indexCol + 23);
    cellTotal3SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3SM20g.setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexTotal2Pais) + "/$"
            + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3SM20g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisSM20c = rowTotal3Pais.createCell(indexCol + 24);
    cellTotal3PaisSM20c.setCellValue("");
    cellTotal3PaisSM20c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3SM10g = rowTotal3Pais.createCell(indexCol + 25);
    cellTotal3SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3SM10g.setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexTotal2Pais) + "/$"
            + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3SM10g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisSM10c = rowTotal3Pais.createCell(indexCol + 26);
    cellTotal3PaisSM10c.setCellValue("");
    cellTotal3PaisSM10c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3SMSoftg = rowTotal3Pais.createCell(indexCol + 27);
    cellTotal3SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3SMSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3SMSoftg.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisSMSoftc = rowTotal3Pais.createCell(indexCol + 28);
    cellTotal3PaisSMSoftc.setCellValue("");
    cellTotal3PaisSMSoftc.setCellStyle(styleTotal3);

    HSSFCell cellTotal3KY20g = rowTotal3Pais.createCell(indexCol + 29);
    cellTotal3KY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3KY20g.setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexTotal2Pais) + "/$"
            + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3KY20g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisKY20c = rowTotal3Pais.createCell(indexCol + 30);
    cellTotal3PaisKY20c.setCellValue("");
    cellTotal3PaisKY20c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3KY10g = rowTotal3Pais.createCell(indexCol + 31);
    cellTotal3KY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3KY10g.setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexTotal2Pais) + "/$"
            + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3KY10g.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisKY10c = rowTotal3Pais.createCell(indexCol + 32);
    cellTotal3PaisKY10c.setCellValue("");
    cellTotal3PaisKY10c.setCellStyle(styleTotal3);

    HSSFCell cellTotal3KYSoftg = rowTotal3Pais.createCell(indexCol + 33);
    cellTotal3KYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cellTotal3KYSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexTotal2Pais)
            + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais));
    cellTotal3KYSoftg.setCellStyle(styleTotal3);

    HSSFCell cellTotal3PaisKYSoftc = rowTotal3Pais.createCell(indexCol + 32);
    cellTotal3PaisKYSoftc.setCellValue("");
    cellTotal3PaisKYSoftc.setCellStyle(styleTotal3);

}

From source file:com.idega.block.datareport.business.SimpleReportBusinessBean.java

License:Open Source License

public void writeSimpleExcelFile(JRDataSource reportData, String nameOfReport, String filePathAndName,
        ReportDescription description) throws IOException {
    if (nameOfReport == null || "".equals(nameOfReport)) {
        nameOfReport = NAME_OF_REPORT;/*from w  w  w.j  a va2s .  c  om*/
    }
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(TextSoap.encodeToValidExcelSheetName(nameOfReport));
    int rowIndex = 0;

    //-- Report Name --//
    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short) rowIndex++);
    // Create a cell and put a value in it.
    HSSFCell cell = row.createCell((short) 0);

    // Create a new font and alter it.
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 24);
    font.setFontName(REPORT_FONT);
    font.setItalic(true);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Fonts are set into a style so create a new one to use.
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);

    // Create a cell and put a value in it.
    cell.setCellValue(nameOfReport);
    cell.setCellStyle(style);

    //-- Report Parameters --//
    rowIndex++;
    HSSFRow row1 = null;
    String parameterString = "";
    List labels = description.getListOfHeaderParameterLabelKeys();
    List parameters = description.getListOfHeaderParameterKeys();
    Iterator labelIter = labels.iterator();
    Iterator parameterIter = parameters.iterator();
    boolean newLineForeEachParameter = description.doCreateNewLineForEachParameter();
    while (labelIter.hasNext() && parameterIter.hasNext()) {
        String label = description.getParameterOrLabelName((String) labelIter.next());
        String parameter = description.getParameterOrLabelName((String) parameterIter.next());
        if (newLineForeEachParameter) {
            row1 = sheet.createRow((short) rowIndex++);
            row1.createCell((short) 0).setCellValue(label + " " + parameter);
        } else {
            parameterString += label + " " + parameter + "      ";
        }
    }
    if (!newLineForeEachParameter) {
        row1 = sheet.createRow((short) rowIndex++);
        row1.createCell((short) 0).setCellValue(parameterString);
    }
    rowIndex++;

    //-- Report ColumnHeader --//
    List fields = description.getListOfFields();
    HSSFRow headerRow = sheet.createRow((short) rowIndex++);

    HSSFCellStyle headerCellStyle = wb.createCellStyle();

    headerCellStyle.setWrapText(true);
    headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

    HSSFFont headerCellFont = wb.createFont();
    //headerCellFont.setFontHeightInPoints((short)12);
    headerCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerCellStyle.setFont(headerCellFont);

    int colIndex = 0;
    int columnWithUnit = 256; // the unit is 1/256 of a character
    int numberOfCharactersPerLineInLongTextFields = 60;
    int numberOfCharactersPerLineInRatherLongTextFields = 35;
    int numberOfCharactersPerLineInUndifinedTextFields = 20;

    for (Iterator iter = fields.iterator(); iter.hasNext(); colIndex++) {
        ReportableField field = (ReportableField) iter.next();
        HSSFCell headerCell = headerRow.createCell((short) colIndex);
        headerCell.setCellValue(description.getColumnName(field));
        headerCell.setCellStyle(headerCellStyle);

        //column width
        int fieldsMaxChar = field.getMaxNumberOfCharacters();
        int colWith = numberOfCharactersPerLineInRatherLongTextFields * columnWithUnit; //default, can be rather long text
        if (fieldsMaxChar > 0 && fieldsMaxChar < numberOfCharactersPerLineInRatherLongTextFields) {
            colWith = (fieldsMaxChar + 1) * columnWithUnit; // short fields
        } else if (fieldsMaxChar > 500) { // when the field is set to be able to contain very long text
            colWith = numberOfCharactersPerLineInLongTextFields * columnWithUnit; //can be very long text
        } else if (fieldsMaxChar < 0) {
            colWith = numberOfCharactersPerLineInUndifinedTextFields * columnWithUnit;
        }
        sheet.setColumnWidth((short) colIndex, (short) colWith);

    }

    //-- Report ColumnDetail --//
    try {
        HSSFCellStyle dataCellStyle = wb.createCellStyle();
        dataCellStyle.setWrapText(true);
        dataCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        sheet.createFreezePane(0, rowIndex);

        while (reportData.next()) {
            HSSFRow dataRow = sheet.createRow((short) rowIndex++);
            colIndex = 0;
            for (Iterator iter = fields.iterator(); iter.hasNext(); colIndex++) {
                ReportableField field = (ReportableField) iter.next();
                HSSFCell dataCell = dataRow.createCell((short) colIndex);
                Object fieldValue = reportData.getFieldValue(field);
                if (fieldValue != null) {
                    dataCell.setCellValue(String.valueOf(fieldValue));
                }
                dataCell.setCellStyle(dataCellStyle);
            }
        }
    } catch (JRException e) {
        //-- Exception fetching data --//
        HSSFRow exceptionRow = sheet.createRow((short) rowIndex++);
        HSSFCell exceptionCell = exceptionRow.createCell((short) 0);

        // Create a new font and alter it.
        HSSFFont exceptionFont = wb.createFont();
        exceptionFont.setFontName(REPORT_FONT);
        exceptionFont.setItalic(true);

        // Fonts are set into a style so create a new one to use.
        HSSFCellStyle exceptionStyle = wb.createCellStyle();
        exceptionStyle.setFont(exceptionFont);

        // Create a cell and put a value in it.
        exceptionCell.setCellValue("Error occurred while getting data. Check log for more details.");
        exceptionCell.setCellStyle(exceptionStyle);

        e.printStackTrace();
    }

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(filePathAndName);
    wb.write(fileOut);
    fileOut.close();
}

From source file:com.insoul.ti.controller.ContestProjectEntryController.java

@RequestMapping("/download")
public ModelAndView download(HttpServletResponse response) throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook();// Excel
    HSSFSheet sheet = workbook.createSheet();// ExcelSheet
    sheet.createFreezePane(1, 2);// 
    // //from   w  w w  . j  a  v  a  2s . c  o m
    sheet.setColumnWidth(0, 1000);
    sheet.setColumnWidth(1, 3500);
    sheet.setColumnWidth(2, 3500);
    sheet.setColumnWidth(3, 6500);
    sheet.setColumnWidth(4, 6500);
    sheet.setColumnWidth(5, 6500);
    sheet.setColumnWidth(6, 6500);
    sheet.setColumnWidth(7, 2500);
    // Sheet?
    //        HSSFCellStyle sheetStyle = workbook.createCellStyle();
    // 
    //        sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    // ?
    //        sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    // ?
    //        sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
    // ?
    //        for (int i = 0; i <= 14; i++) {
    //            sheet.setDefaultColumnStyle((short) i, sheetStyle);
    //        }
    // 
    HSSFFont headfont = workbook.createFont();
    headfont.setFontName("");
    headfont.setFontHeightInPoints((short) 22);// ?
    headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 
    // ??
    HSSFCellStyle headstyle = workbook.createCellStyle();
    headstyle.setFont(headfont);
    headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ?
    headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    headstyle.setLocked(true);
    headstyle.setWrapText(true);// ?
    // ??
    HSSFFont columnHeadFont = workbook.createFont();
    columnHeadFont.setFontName("");
    columnHeadFont.setFontHeightInPoints((short) 10);
    columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // ?
    HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
    columnHeadStyle.setFont(columnHeadFont);
    columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ?
    columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    columnHeadStyle.setLocked(true);
    columnHeadStyle.setWrapText(true);
    columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 
    columnHeadStyle.setBorderLeft((short) 1);// ?
    columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// ?
    columnHeadStyle.setBorderRight((short) 1);// ?
    columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ?
    columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // ?
    // ????
    columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    HSSFFont font = workbook.createFont();
    font.setFontName("");
    font.setFontHeightInPoints((short) 10);
    // ??
    HSSFCellStyle style = workbook.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// ?
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 
    style.setWrapText(true);
    style.setLeftBorderColor(HSSFColor.BLACK.index);
    style.setBorderLeft((short) 1);
    style.setRightBorderColor(HSSFColor.BLACK.index);
    style.setBorderRight((short) 1);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ?
    style.setBottomBorderColor(HSSFColor.BLACK.index); // ?
    style.setFillForegroundColor(HSSFColor.WHITE.index);// ?
    // ??
    HSSFCellStyle centerstyle = workbook.createCellStyle();
    centerstyle.setFont(font);
    centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ?
    centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    centerstyle.setWrapText(true);
    centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);
    centerstyle.setBorderLeft((short) 1);
    centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
    centerstyle.setBorderRight((short) 1);
    centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ?
    centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // ?
    centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// ?
    try {
        // 
        HSSFRow row0 = sheet.createRow(0);
        // 
        row0.setHeight((short) 900);
        // 
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue(new HSSFRichTextString("???"));
        cell0.setCellStyle(headstyle);
        /**
         * ?? ??0 ??0 ??0 ??0
         */
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, 14);
        sheet.addMergedRegion(range);
        // 
        HSSFRow row1 = sheet.createRow(1);
        row1.setHeight((short) 750);
        HSSFCell cell = row1.createCell(0);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(1);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(2);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(3);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(4);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(5);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(6);
        cell.setCellValue(new HSSFRichTextString("?"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(7);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(8);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(9);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(10);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(11);
        cell.setCellValue(new HSSFRichTextString(""));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(12);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(13);
        cell.setCellValue(new HSSFRichTextString("?"));
        cell.setCellStyle(columnHeadStyle);
        cell = row1.createCell(14);
        cell.setCellValue(new HSSFRichTextString("??"));
        cell.setCellStyle(columnHeadStyle);
        List<ContestEntry> projectList = contestEntryDAO.findAll();
        int m = 2;
        int len = projectList.size();
        for (int i = 0; i < len; i++) {
            ContestEntry c = projectList.get(i);
            HSSFRow row = sheet.createRow(m + i);
            cell = row.createCell(0);
            cell.setCellValue(new HSSFRichTextString(c.getId() + ""));
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(new HSSFRichTextString(c.getLocation()));
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue(new HSSFRichTextString(c.getInstance()));
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue(new HSSFRichTextString(c.getIndustry()));
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue(new HSSFRichTextString(c.getLegalFormation()));
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue(new HSSFRichTextString(c.getRegtime()));
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue(new HSSFRichTextString(c.getEmployqty() + ""));
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue(new HSSFRichTextString(c.getLegalPerson()));
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue(new HSSFRichTextString(c.getUserCategory()));
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue(new HSSFRichTextString(c.getContact()));
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue(new HSSFRichTextString(c.getIdNumber()));
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue(new HSSFRichTextString(c.getBankName()));
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue(new HSSFRichTextString(c.getBankUserName()));
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue(new HSSFRichTextString(c.getBankAccount()));
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue(new HSSFRichTextString(c.getSupportMoney()));
            cell.setCellStyle(style);
        }
        String filename = System.nanoTime() + ".xls";// Excel??
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        OutputStream ouputStream = response.getOutputStream();
        workbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    } catch (Exception e) {
        log.error("download excel Error.", e);
    }
    return null;
}

From source file:com.isoftstone.web.dao.Car_dao.java

/**
 * ?excel,?excel/*  w  ww . j av a  2  s  .  co m*/
 * @param carlist
 * @param path
 * @return ?excel
 */
public boolean addToExcel(List<Car_inf> carlist, String path) {

    System.out.println("excel\n");
    // webbookExcel  
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet  
    HSSFSheet sheet = wb.createSheet("");
    // sheet0,poiExcelshort  
    HSSFRow row = sheet.createRow((int) 0);
    //    
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //   

    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("id");
    cell.setCellStyle(style);
    cell = row.createCell((short) 1);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 2);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 4);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 5);
    cell.setCellValue("");
    cell.setCellStyle(style);

    for (int i = 0; i < carlist.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car_inf car = (Car_inf) carlist.get(i);
        //   
        String logon = car.getLogon().toString();
        String dated = car.getDated().toString();
        row.createCell((short) 0).setCellValue(car.getId());
        row.createCell((short) 1).setCellValue(car.getBrand());
        row.createCell((short) 2).setCellValue(car.getSeat());
        row.createCell((short) 3).setCellValue(logon);
        row.createCell((short) 4).setCellValue(dated);
        row.createCell((short) 5).setCellValue(car.getD_license());
        row.createCell((short) 6).setCellValue(car.getC_license());
    }
    //   
    try {
        System.out.println("true\n");
        String name = path + "?.xls";
        FileOutputStream fout = new FileOutputStream(name);
        wb.write(fout);
        fout.close();
        System.out.println("\n");
        return true;
    } catch (Exception e) {
        e.printStackTrace();
    }

    return false;
}