Example usage for org.apache.poi.ss.util CellRangeAddress valueOf

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress valueOf.

Prototype

public static CellRangeAddress valueOf(String ref) 

Source Link

Document

Creates a CellRangeAddress from a cell range reference string.

Usage

From source file:com.griffinslogistics.excel.BookLabelGenerator.java

private static void generateAddress(Sheet sheet, Map<String, CellStyle> styles, BookLabelModel bookLabelModel) {
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A2:A3"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A12:A13"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D2:D3"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D12:D13"));

    Row row2 = sheet.getRow(1);// w w w  . ja  va 2 s.c  o m
    row2.setHeightInPoints(25);

    Row row3 = sheet.getRow(2);
    row3.setHeightInPoints(25);

    Cell a2 = row2.createCell(0);
    a2.setCellValue(ADDRESS);
    a2.setCellStyle(styles.get(STYLE_LABEL));

    Cell a3 = row3.createCell(0);
    a3.setCellStyle(styles.get(STYLE_LABEL));

    Cell d2 = row2.createCell(3);
    d2.setCellValue(ADDRESS);
    d2.setCellStyle(styles.get(STYLE_LABEL));

    Cell d3 = row3.createCell(3);
    d3.setCellStyle(styles.get(STYLE_LABEL));

    Cell b2 = row2.createCell(1);
    b2.setCellValue(bookLabelModel.getAddress());
    b2.setCellStyle(styles.get(STYLE_CONTENT));

    Cell b3 = row3.createCell(1);
    b3.setCellValue(bookLabelModel.getPostalCode());
    b3.setCellStyle(styles.get(STYLE_CONTENT));

    Cell e2 = row2.createCell(4);
    e2.setCellValue(bookLabelModel.getAddress());
    e2.setCellStyle(styles.get(STYLE_CONTENT));

    Cell e3 = row3.createCell(4);
    e3.setCellValue(bookLabelModel.getPostalCode());
    e3.setCellStyle(styles.get(STYLE_CONTENT));

    Row row12 = sheet.getRow(11);
    row12.setHeightInPoints(25);

    Row row13 = sheet.getRow(12);
    row13.setHeightInPoints(25);

    Cell a12 = row12.createCell(0);
    a12.setCellValue(ADDRESS);
    a12.setCellStyle(styles.get(STYLE_LABEL));

    Cell a13 = row13.createCell(0);
    a13.setCellStyle(styles.get(STYLE_LABEL));

    Cell d12 = row12.createCell(3);
    d12.setCellValue(ADDRESS);
    d12.setCellStyle(styles.get(STYLE_LABEL));

    Cell d13 = row13.createCell(3);
    d13.setCellStyle(styles.get(STYLE_LABEL));

    Cell b12 = row12.createCell(1);
    b12.setCellValue(bookLabelModel.getAddress());
    b12.setCellStyle(styles.get(STYLE_CONTENT));

    Cell b13 = row13.createCell(1);
    b13.setCellValue(bookLabelModel.getPostalCode());
    b13.setCellStyle(styles.get(STYLE_CONTENT));

    Cell e12 = row12.createCell(4);
    e12.setCellValue(bookLabelModel.getAddress());
    e12.setCellStyle(styles.get(STYLE_CONTENT));

    Cell e13 = row13.createCell(4);
    e13.setCellValue(bookLabelModel.getPostalCode());
    e13.setCellStyle(styles.get(STYLE_CONTENT));
}

---

From source file:com.griffinslogistics.excel.BookLabelGenerator.java

private static void generateTitle(Sheet sheet, Map<String, CellStyle> styles, BookLabelModel bookLabelModel) {
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A6:A7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D6:D7"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A16:A17"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D16:D17"));

    Row row6 = sheet.getRow(5);/*w  w w . j a  va 2 s .c om*/
    Row row7 = sheet.getRow(6);

    Cell a6 = row6.createCell(0);
    a6.setCellStyle(styles.get(STYLE_LABEL));
    a6.setCellValue(TITLE);

    Cell a7 = row7.createCell(0);
    a7.setCellStyle(styles.get(STYLE_LABEL));

    Cell b6 = row6.createCell(1);
    b6.setCellStyle(styles.get(STYLE_CONTENT));
    b6.setCellValue(bookLabelModel.getTitle());

    Cell b7 = row7.createCell(1);
    b7.setCellStyle(styles.get(STYLE_CONTENT));
    b7.setCellValue(bookLabelModel.getBookNumber());

    Cell d6 = row6.createCell(3);
    d6.setCellStyle(styles.get(STYLE_LABEL));
    d6.setCellValue(TITLE);

    Cell d7 = row7.createCell(3);
    d7.setCellStyle(styles.get(STYLE_LABEL));

    Cell e6 = row6.createCell(4);
    e6.setCellStyle(styles.get(STYLE_CONTENT));
    e6.setCellValue(bookLabelModel.getTitle());

    Cell e7 = row7.createCell(4);
    e7.setCellStyle(styles.get(STYLE_CONTENT));
    e7.setCellValue(bookLabelModel.getBookNumber());

    Row row16 = sheet.getRow(15);
    Row row17 = sheet.getRow(16);

    Cell a16 = row16.createCell(0);
    a16.setCellStyle(styles.get(STYLE_LABEL));
    a16.setCellValue(TITLE);

    Cell a17 = row17.createCell(0);
    a17.setCellStyle(styles.get(STYLE_LABEL));

    Cell b16 = row16.createCell(1);
    b16.setCellStyle(styles.get(STYLE_CONTENT));
    b16.setCellValue(bookLabelModel.getTitle());

    Cell b17 = row17.createCell(1);
    b17.setCellStyle(styles.get(STYLE_CONTENT));
    b17.setCellValue(bookLabelModel.getBookNumber());

    Cell d16 = row16.createCell(3);
    d16.setCellStyle(styles.get(STYLE_LABEL));
    d16.setCellValue(TITLE);

    Cell d17 = row17.createCell(3);
    d17.setCellStyle(styles.get(STYLE_LABEL));

    Cell e16 = row16.createCell(4);
    e16.setCellStyle(styles.get(STYLE_CONTENT));
    e16.setCellValue(bookLabelModel.getTitle());

    Cell e17 = row17.createCell(4);
    e17.setCellStyle(styles.get(STYLE_CONTENT));
    e17.setCellValue(bookLabelModel.getBookNumber());
}

---

From source file:com.griffinslogistics.excel.CMRGenerator.java

private static int generatePoint2Till9(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        String deliveryAddress) {
    for (int i = 11; i < 27; i++) {
        sheet.addMergedRegion(CellRangeAddress.valueOf("$B$" + i + ":$C$" + i));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + i + ":$I$" + i));
    }//w w  w. j a  va2 s . c o  m

    currentRow++;
    Row row11 = sheet.createRow(currentRow);
    Cell receiverLabelCell = row11.createCell(1);
    receiverLabelCell.setCellValue(LABEL_RECEIVER);
    receiverLabelCell.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row11.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE));

    currentRow++;
    Row row12 = sheet.createRow(currentRow);
    row12.createCell(1).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row12.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row row13 = sheet.createRow(currentRow);
    Cell receiverCell1 = row13.createCell(1);
    receiverCell1.setCellValue(RECEIVER_PART_1);
    receiverCell1.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row13.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row row14 = sheet.createRow(currentRow);
    Cell receiverCell2 = row14.createCell(1);
    receiverCell2.setCellValue(RECEIVER_PART_2);
    receiverCell2.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row14.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row14.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row15 = sheet.createRow(currentRow);
    Cell receiverCell3 = row15.createCell(1);
    receiverCell3.setCellValue(RECEIVER_PART_3);
    receiverCell3.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row15.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    Cell successiveCarrierCell1 = row11.createCell(3);
    successiveCarrierCell1.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_1);
    successiveCarrierCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row11.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell successiveCarrierCell2 = row12.createCell(3);
    successiveCarrierCell2.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_2);
    successiveCarrierCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row12.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell successiveCarrierCell3 = row13.createCell(3);
    successiveCarrierCell3.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_3);
    successiveCarrierCell3.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row13.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    Cell reservationsCell1 = row15.createCell(3);
    reservationsCell1.setCellValue(RESERVATIONS_PART_1);
    reservationsCell1.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row15.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row16 = sheet.createRow(currentRow);
    Cell reservationsCell2 = row16.createCell(3);
    reservationsCell2.setCellValue(RESERVATIONS_PART_2);
    reservationsCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row16.createCell(1).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row16.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row16.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row17 = sheet.createRow(currentRow);
    row17.createCell(1).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row17.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row17.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row17.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row18 = sheet.createRow(currentRow);
    Cell takingOverCellLabel = row18.createCell(1);
    takingOverCellLabel.setCellValue(LABEL_TAKING_OVER);
    takingOverCellLabel.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row18.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row18.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row18.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row19 = sheet.createRow(currentRow);
    Cell takingOverContentCell = row19.createCell(1);
    takingOverContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    takingOverContentCell.setCellValue(TAKING_OVER_CONTENT);

    row19.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row19.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row19.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row20 = sheet.createRow(currentRow);
    Cell countryLabelCell = row20.createCell(1);
    countryLabelCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    countryLabelCell.setCellValue(LABEL_COUNTRY);

    row20.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row20.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row20.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row21 = sheet.createRow(currentRow);
    Cell countryContentCell = row21.createCell(1);
    countryContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    countryContentCell.setCellValue(ADDRESS_PART_4.trim());

    row21.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row21.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row21.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row22 = sheet.createRow(currentRow);
    Cell dateCell = row22.createCell(1);
    dateCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    dateCell.setCellValue(LABEL_DATE);

    row22.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row22.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row22.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row23 = sheet.createRow(currentRow);
    Cell arrivalTime = row23.createCell(1);
    arrivalTime.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    arrivalTime.setCellValue(LABEL_ARRIVAL_TIME);

    row23.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    for (int i = 3; i < 9; i++) {
        row23.createCell(i).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    }

    currentRow++;
    Row row24 = sheet.createRow(currentRow);
    Cell goodsDeliveryLabelCell = row24.createCell(1);
    goodsDeliveryLabelCell.setCellValue(LABEL_DELIVERY_OF_GOODS);
    goodsDeliveryLabelCell.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row24.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE));

    currentRow++;
    Row row25 = sheet.createRow(currentRow);
    Cell placeLabelCell = row25.createCell(1);
    placeLabelCell.setCellValue(LABEL_PLACE);
    placeLabelCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
    row25.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));

    currentRow++;
    Row row26 = sheet.createRow(currentRow);
    Cell placeContentCell = row26.createCell(1);
    placeContentCell.setCellValue(deliveryAddress);
    placeContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    row26.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));

    currentRow++;
    Row row27 = sheet.createRow(currentRow);
    Cell deliveryCountryLabelCell = row27.createCell(1);
    deliveryCountryLabelCell.setCellValue(LABEL_COUNTRY);
    deliveryCountryLabelCell.setCellStyle(styles.get(LABEL_LEFT_STYLE));
    Cell deliveryCountryContentCell = row27.createCell(2);
    deliveryCountryContentCell.setCellValue(FRANCE);
    deliveryCountryContentCell.setCellStyle(styles.get(CONTENT_RIGHT_STYLE));

    currentRow++;
    Row row28 = sheet.createRow(currentRow);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$28:$C$28"));
    Cell openingHoursCell = row28.createCell(1);
    openingHoursCell.setCellValue(LABEL_OPENING_HOURS);
    openingHoursCell.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    row28.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    currentRow++;
    Row row29 = sheet.createRow(currentRow);
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$29:$C$29"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$B$30:$C$30"));
    Cell senderInstructionsCell1 = row29.createCell(1);
    senderInstructionsCell1.setCellValue(SENDER_INSTRUCTIONS_BULGARIAN);
    senderInstructionsCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));
    row29.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE));

    sheet.addMergedRegion(CellRangeAddress.valueOf("$D$29:$I$29"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$D$30:$I$30"));
    Cell carrierDocumentsCell1 = row29.createCell(3);
    carrierDocumentsCell1.setCellValue(CARRIER_DOCUMENTS_BULGARIAN);
    carrierDocumentsCell1.setCellStyle(styles.get(LABEL_TOP_STYLE));

    for (int i = 4; i < 9; i++) {
        row29.createCell(i).setCellStyle(styles.get(LABEL_TOP_STYLE));
    }

    currentRow++;
    Row row30 = sheet.createRow(currentRow);
    Cell senderInstructionsCell2 = row30.createCell(1);
    senderInstructionsCell2.setCellValue(SENDERS_INSTRUCTIONS_ENGLISH);
    senderInstructionsCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    row30.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    Cell carrierDocumentsCell2 = row30.createCell(3);
    carrierDocumentsCell2.setCellValue(CARRIER_DOCUMENTS_ENGLISH);
    carrierDocumentsCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE));

    for (int i = 4; i < 9; i++) {
        row30.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE));
    }

    for (int i = 23; i < 28; i++) {
        sheet.getRow(i).createCell(8).setCellStyle(styles.get(LABEL_RIGHT_STYLE));
    }

    return currentRow;
}

---

From source file:com.griffinslogistics.excel.CMRGenerator.java

private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow,
        Pulsiodetails pulsiodetails) {/*  www. j  av  a2s.com*/
    currentRow++;

    String mergeString;

    for (int i = currentRow; i < currentRow + 2; i++) {
        Row row = sheet.createRow(i);
        mergeString = String.format("$B$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

        for (int j = 1; j < 9; j++) {
            row.createCell(j)
                    .setCellStyle(styles.get(i == currentRow ? LABEL_MIDDLE_STYLE : LABEL_BOTTOM_STYLE));
        }
    }

    for (int i = currentRow + 2; i < currentRow + 16; i++) {
        Row row = sheet.createRow(i);
        mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1);
        sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));
        for (int j = 1; j < 9; j++) {
            row.createCell(j).setCellStyle(styles.get(LABEL_MIDDLE_STYLE));
        }
    }

    currentRow++;
    Row row45 = sheet.getRow(currentRow);
    row45.setHeightInPoints(30);
    row45.getCell(1).setCellValue(LABEL_POINT_20);

    currentRow++;
    Row row46 = sheet.getRow(currentRow);

    Cell establishedInCell = row46.getCell(1);
    establishedInCell.setCellValue(LABEL_ESTABLISHED_IN);

    Cell establishedOnCell = row46.getCell(2);
    establishedOnCell.setCellValue(LABEL_ESTABLISHED_ON);

    Cell goodsDeliveredCell = row46.getCell(3);
    goodsDeliveredCell.setCellValue(LABEL_GOODS_RECEIVED);

    currentRow++;
    Row row47 = sheet.getRow(currentRow);
    row47.getCell(3).setCellValue(LABEL_TIME_OF_ARRIVAL);

    currentRow++;
    Row row48 = sheet.getRow(currentRow);
    Cell cityCell = row48.getCell(1);
    cityCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    cityCell.setCellValue("Sofia, Bulgaria");

    Cell dateCell = row48.getCell(2);
    dateCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE));
    dateCell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(new Date()));

    currentRow += 2;
    sheet.getRow(currentRow).getCell(3).setCellValue(LABEL_PLACE_20);

    currentRow++;
    Row row51 = sheet.getRow(currentRow);

    currentRow++;

    row51.getCell(1).setCellValue(TWENTY_TWO);
    row51.getCell(2).setCellValue(TWENTY_THREE);

    // Insert signature picture
    Workbook workbook = sheet.getWorkbook();
    byte[] imageBytes = pulsiodetails.getSignature();
    int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
    CreationHelper helper = workbook.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();

    //set top-left corner for the image
    anchor.setCol1(1);
    anchor.setRow1(currentRow);

    Picture pict = drawing.createPicture(anchor, pictureIdx);
    pict.resize();

    currentRow += 4;
    Row row56 = sheet.getRow(currentRow);
    row56.getCell(3).setCellValue(LABEL_SIGNATURE_STAMP);

    currentRow += 2;
    Row row58 = sheet.getRow(currentRow);
    Cell signatureLabelCell1 = row58.getCell(1);
    signatureLabelCell1.setCellValue(LABEL_SENDER_SIGNATURE_BULGARIAN);

    Cell carrierSignatureCell = row58.getCell(2);
    carrierSignatureCell.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN);

    Cell receiverSignatureCell = row58.getCell(3);
    receiverSignatureCell.setCellValue(LABEL_RECEIVER_SIGNATURE_BULGARIAN);

    currentRow++;
    Row row59 = sheet.getRow(currentRow);
    Cell signatureLabelCell2 = row59.getCell(1);
    signatureLabelCell2.setCellValue(LABEL_SENDER_SIGNATURE_ENGLISH);

    Cell carrierSignatureCel2 = row59.getCell(2);
    carrierSignatureCel2.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN);

    Cell receiverSignatureCel2 = row59.getCell(3);
    receiverSignatureCel2.setCellValue(LABEL_RECEIVER_SIGNATURE_ENGLISH);

    currentRow++;
    Row row60 = sheet.createRow(currentRow);
    mergeString = String.format("$B$%s:$I$%s", currentRow + 1, currentRow + 1);
    sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString));

    for (int i = 1; i < 9; i++) {
        row60.createCell(i).setCellStyle(styles.get(LABEL_WHOLE_STYLE));
    }

    Cell additionalSpaceCell = row60.getCell(1);
    additionalSpaceCell.setCellValue(LABEL_ADDITIONAL_SPACE);

    return currentRow;
}

---

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);//ww  w  .  j a va  2s. 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) {/*from w  ww . ja  v  a 2s.c o m*/
    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 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]
                    + "+";
        }/*  w  w  w  .java 2 s. c o  m*/

    }

    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.ipcglobal.fredimport.xls.DistinctCategoriesSpreadsheet.java

License:Apache License

/**
 * Creates the sheet.// ww w  . ja  v  a 2s . c om
 *
 * @param distinctCategoryItems the distinct category items
 * @throws Exception the exception
 */
public void createSheet(Collection<DistinctCategoryItem> distinctCategoryItems) throws Exception {
    List<XlsDefItem> xlsDefItems = initHhdrWidthItems();
    String sheetName = "DistinctCategoryItems";
    Sheet sheet = wb.createSheet(sheetName);
    processColumnWidths(sheet, xlsDefItems);
    sheet.createFreezePane(0, 1, 0, 1); // freeze top row
    sheet.setAutoFilter(CellRangeAddress.valueOf("A1:AB1")); // hack - i know the number of columns
    sheet.getPrintSetup().setLandscape(true);
    sheet.setAutobreaks(true);
    sheet.getPrintSetup().setFitWidth((short) 1);
    sheet.getPrintSetup().setFitHeight((short) 1);

    int rowCnt = 0;

    // Header
    int colCnt = 0;
    Row rowHdr = sheet.createRow(rowCnt);
    for (XlsDefItem xlsDefItem : xlsDefItems) {
        Cell cellHdr = rowHdr.createCell(colCnt, Cell.CELL_TYPE_STRING);
        CellStyle style = findCellStyle("Arial", HSSFColor.WHITE.index, (short) 11, XSSFFont.BOLDWEIGHT_BOLD,
                cellStyleFromHdrAlign(HdrAlign.Left), XSSFCellStyle.VERTICAL_TOP, HSSFColor.LIGHT_BLUE.index,
                CellBorder.All_Thin, formatGeneral);
        style.setWrapText(true);
        cellHdr.setCellStyle(style);
        cellHdr.setCellValue(xlsDefItem.getName());
        colCnt++;
    }
    rowCnt++;

    // Data
    for (DistinctCategoryItem distinctCategoryItem : distinctCategoryItems) {
        Row rowData = sheet.createRow(rowCnt);
        int colNum = 0;
        for (XlsDefItem xlsDefItem : xlsDefItems)
            populateCell(rowData, colNum++, xlsDefItem.getDataType(),
                    getByNameAsString(distinctCategoryItem, xlsDefItem.getName()));
        rowCnt++;
    }
}

---

From source file:com.lufs.java.apache.poi.example.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (String arg : args) {
        if (arg.charAt(0) == '-') {
            xlsx = arg.equals("-xlsx");
        } else {/*  w  ww.  j a  v  a  2 s . c o  m*/
            calendar.set(Calendar.YEAR, Integer.parseInt(arg));
        }
    }
    int year = calendar.get(Calendar.YEAR);

    try (Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook()) {

        Map<String, CellStyle> styles = createStyles(wb);

        for (int month = 0; month < 12; month++) {
            calendar.set(Calendar.MONTH, month);
            calendar.set(Calendar.DAY_OF_MONTH, 1);
            //create a sheet for each month
            Sheet sheet = wb.createSheet(months[month]);

            //turn off gridlines
            sheet.setDisplayGridlines(false);
            sheet.setPrintGridlines(false);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);
            PrintSetup printSetup = sheet.getPrintSetup();
            printSetup.setLandscape(true);

            //the following three statements are required only for HSSF
            sheet.setAutobreaks(true);
            printSetup.setFitHeight((short) 1);
            printSetup.setFitWidth((short) 1);

            //the header row: centered text in 48pt font
            Row headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(80);
            Cell titleCell = headerRow.createCell(0);
            titleCell.setCellValue(months[month] + " " + year);
            titleCell.setCellStyle(styles.get("title"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

            //header with month titles
            Row monthRow = sheet.createRow(1);
            for (int i = 0; i < days.length; i++) {
                //set column widths, the width is measured in units of 1/256th of a character width
                sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
                sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
                sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
                Cell monthCell = monthRow.createCell(i * 2);
                monthCell.setCellValue(days[i]);
                monthCell.setCellStyle(styles.get("month"));
            }

            int cnt = 1, day = 1;
            int rownum = 2;
            for (int j = 0; j < 6; j++) {
                Row row = sheet.createRow(rownum++);
                row.setHeightInPoints(100);
                for (int i = 0; i < days.length; i++) {
                    Cell dayCell_1 = row.createCell(i * 2);
                    Cell dayCell_2 = row.createCell(i * 2 + 1);

                    int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                    if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                        dayCell_1.setCellValue(day);
                        calendar.set(Calendar.DAY_OF_MONTH, ++day);

                        if (i == 0 || i == days.length - 1) {
                            dayCell_1.setCellStyle(styles.get("weekend_left"));
                            dayCell_2.setCellStyle(styles.get("weekend_right"));
                        } else {
                            dayCell_1.setCellStyle(styles.get("workday_left"));
                            dayCell_2.setCellStyle(styles.get("workday_right"));
                        }
                    } else {
                        dayCell_1.setCellStyle(styles.get("grey_left"));
                        dayCell_2.setCellStyle(styles.get("grey_right"));
                    }
                    cnt++;
                }
                if (calendar.get(Calendar.MONTH) > month)
                    break;
            }
        }

        // Write the output to a file
        String file = "calendar.xls";
        if (wb instanceof XSSFWorkbook)
            file += "x";

        try (FileOutputStream out = new FileOutputStream(file)) {
            wb.write(out);
        }
    }
}

---

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void mergeCells(int sheetIndex, String reference) {
    getSheet(sheetIndex).addMergedRegion(CellRangeAddress.valueOf(reference));
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• »