Example usage for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion

Introduction

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

Prototype

@Override
public int addMergedRegion(CellRangeAddress region) 

Source Link

Document

Adds a merged region of cells on a sheet.

Usage

From source file:com.hp.action.ReportSaleWithProductsAction.java

public String exportSaleOrderList() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize/*from  ww w. ja  va2s .  c o  m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    reportSaleWithProductList = (List<ReportSaleWithProduct>) session.getAttribute("reportSaleWithProductList");

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Sale Order with product");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //SaleOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                8 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co bn hng theo sn phm");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                8 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write SaleOrder
    for (int i = 0; i < reportSaleWithProductList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(reportSaleWithProductList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

        //SUM ROW
        if (i == (reportSaleWithProductList.size() - 1)) {
            Row rowEnd = sheet.createRow(i + 7);
            for (int j = (titleArray().length - 3); j < (titleArray().length); j++) {
                Cell cell = rowEnd.createCell(j);
                cell.setCellValue((Double) sumArray()[j - (titleArray().length - 3)]);
            }
        }
    }

    outputFile = "BaoCaoBanHangTheoSanPham" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

---

From source file:com.hp.action.ReportTakeOrderAction.java

public String exportTakeOrderList() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize/*from   ww  w  . j  av a 2  s  .  com*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    takeOrdersList = (List<TakeOrder>) session.getAttribute("takeOrdersList");
    takeOrderDetailList = (List<List<TakeOrderDetail>>) session.getAttribute("takeOrderDetailList");

    if (takeOrdersList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Take Order");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                8 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co ha n t hng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                8 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < takeOrdersList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(takeOrdersList.get(i), takeOrderDetailList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

        //SUM ROW
        if (i == (takeOrdersList.size() - 1)) {
            Row rowEnd = sheet.createRow(i + 7);
            for (int j = (titleArray().length - 5); j < (titleArray().length - 1); j++) {
                Cell cell = rowEnd.createCell(j);
                cell.setCellValue((Double) sumArray()[j - (titleArray().length - 5)]);
            }
        }
    }

    outputFile = "BaoCaoHoaDonDatHang" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

---

From source file:com.hp.action.SetLunchAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize// w  w w .j  ava2  s. c om
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    setLunchList = (List<SetLunch>) session.getAttribute("setLunchList");

    if (setLunchList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Bo cm");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo cm vn phng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < setLunchList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(setLunchList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "BaoComVanPhong" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

---

From source file:com.hp.action.StaffHistoryAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize/*from www . j  a  v a2  s. c o m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    listStaffHistory = (List<StaffHistory>) session.getAttribute("listStaffHistory");

    if (listStaffHistory == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Chm sc khch hng");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co chm sc khch hng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < listStaffHistory.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(listStaffHistory.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "BaoCaoChamSocKhachHang" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

---

From source file:com.hp.action.StaffsAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*from  w w  w  .jav  a 2s  .co m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    staffsList = (List<Staff>) session.getAttribute("staffsList");

    if (staffsList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Nhn vin");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                10 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Danh sch nhn vin");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                10 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        cell1.setCellValue("");

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < staffsList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(staffsList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "DanhSachNhanVien.xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

---

From source file:com.hp.action.TimeKeeperAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize/* w  w w .ja  v a  2 s. c  o m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    timeKeeperList = (List<TimeKeeper>) session.getAttribute("timeKeeperList");

    if (timeKeeperList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Chm cng");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co chm cng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < timeKeeperList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(timeKeeperList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "BaoCaoChamCong" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

---

From source file:com.huateng.struts.query.action.T50202Action.java

License:Open Source License

public String download() {

    String brhBelow = InformationUtil.getBrhGroupString(brhId);
    System.out.println(brhBelow);

    String thisMonStart = date + "01";
    String thisMonEnd = date + "31";
    String lastYear = String.valueOf(Integer.parseInt(date.substring(0, 4)) - 1) + "1231";
    String lastMon = thisMonStart;//start?
    String thisYearStart = date.substring(0, 4) + "0101";
    String thisYearEnd = date.substring(0, 4) + "1231";

    try {//from   w  ww. j av  a 2s  .  c om
        //?
        String[][] data = new String[40][9];
        //?data
        for (int i = 0; i < data.length; i++) {
            for (int j = 0; j < data[i].length; j++) {
                if (j < 4) {
                    data[i][j] = "";
                } else {
                    data[i][j] = "0";
                }
            }
        }
        Iterator it = null;
        //???
        //?data
        data[0][0] = "1";
        data[0][1] = "?";
        data[0][3] = "";
        data[1][1] = "";
        data[1][2] = "?";
        data[1][3] = "";
        data[2][2] = "?";
        data[2][3] = "";
        String sql = "select CONN_TYPE, " + "sum(case when apply_date <= '" + lastYear
                + "' then 1 else 0 end) as c1," + "sum(case when apply_date < '" + lastMon
                + "' then 1 else 0 end) as c2," + "sum(case when (apply_date >= '" + thisMonStart
                + "' and apply_date <= '" + thisMonEnd + "') then 1 else 0 end) as c3,"
                + "sum(case when (apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd
                + "') then 1 else 0 end) as c4 " + "from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow
                + " group by CONN_TYPE";
        List list = commQueryDAO.findBySQLQuery(sql);
        if (null != list && !list.isEmpty()) {
            it = list.iterator();
            while (it.hasNext()) {
                Object[] obj = (Object[]) it.next();
                int index = 0;
                if ("J".equals(obj[0])) {
                    index = 1;
                } else {
                    index = 2;
                }
                data[index][4] = obj[1].toString();
                data[index][5] = obj[2].toString();
                data[index][6] = obj[3].toString();
                data[index][7] = obj[4].toString();
                data[index][8] = String
                        .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString()));
            }
            data[0][4] = String.valueOf(Integer.valueOf(data[1][4]) + Integer.valueOf(data[2][4]));
            data[0][5] = String.valueOf(Integer.valueOf(data[1][5]) + Integer.valueOf(data[2][5]));
            data[0][6] = String.valueOf(Integer.valueOf(data[1][6]) + Integer.valueOf(data[2][6]));
            data[0][7] = String.valueOf(Integer.valueOf(data[1][7]) + Integer.valueOf(data[2][7]));
            data[0][8] = String.valueOf(Integer.valueOf(data[1][8]) + Integer.valueOf(data[2][8]));
        }
        //???
        //?data
        data[3][0] = "2";
        data[3][1] = "?POS";
        data[3][3] = "?";
        data[4][1] = "";
        data[4][2] = "?";
        data[4][3] = "?";
        data[5][2] = "?";
        data[5][3] = "?";
        sql = "select CONN_TYPE ," + "sum(case when t.REC_CRT_TS <= '" + lastYear
                + "' then 1 else 0 end) as c1," + "sum(case when t.REC_CRT_TS < '" + lastMon
                + "' then 1 else 0 end) as c2," + "sum(case when (t.REC_CRT_TS >= '" + thisMonStart
                + "' and t.REC_CRT_TS <= '" + thisMonEnd + "') then 1 else 0 end) as c3,"
                + "sum(case when (t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd
                + "') then 1 else 0 end) as c4 "
                + "from TBL_TERM_INF t,TBL_MCHT_BASE_INF m where t.MCHT_CD = m.MCHT_NO "
                + "and m.ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE";
        list = commQueryDAO.findBySQLQuery(sql);
        if (null != list && !list.isEmpty()) {
            it = list.iterator();
            while (it.hasNext()) {
                Object[] obj = (Object[]) it.next();
                int index = 0;
                if ("J".equals(obj[0])) {
                    index = 4;
                } else {
                    index = 5;
                }
                data[index][4] = obj[1].toString();
                data[index][5] = obj[2].toString();
                data[index][6] = obj[3].toString();
                data[index][7] = obj[4].toString();
                data[index][8] = String
                        .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString()));
            }
            data[3][4] = String.valueOf(Integer.valueOf(data[4][4]) + Integer.valueOf(data[5][4]));
            data[3][5] = String.valueOf(Integer.valueOf(data[4][5]) + Integer.valueOf(data[5][5]));
            data[3][6] = String.valueOf(Integer.valueOf(data[4][6]) + Integer.valueOf(data[5][6]));
            data[3][7] = String.valueOf(Integer.valueOf(data[4][7]) + Integer.valueOf(data[5][7]));
            data[3][8] = String.valueOf(Integer.valueOf(data[4][8]) + Integer.valueOf(data[5][8]));
        }
        //???
        //?data
        data[6][0] = "3";
        data[6][1] = "?";
        sql = "SELECT DESCR,nvl(mchnt1.c,0),nvl(mchnt2.c,0),nvl(mchnt3.c,0),nvl(mchnt4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp "
                + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in "
                + brhBelow + " and apply_date <= '" + lastYear
                + "' group by MCHT_GRP) mchnt1 on (grp.MCHNT_TP_GRP = mchnt1.MCHT_GRP) "
                + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in "
                + brhBelow + " and apply_date < '" + lastMon
                + "' group by MCHT_GRP) mchnt2 on (grp.MCHNT_TP_GRP = mchnt2.MCHT_GRP)"
                + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in "
                + brhBelow + " and apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd
                + "' group by MCHT_GRP) mchnt3 on (grp.MCHNT_TP_GRP = mchnt3.MCHT_GRP)"
                + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in "
                + brhBelow + " and apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd
                + "' group by MCHT_GRP) mchnt4 on (grp.MCHNT_TP_GRP = mchnt4.MCHT_GRP) "
                + "order by MCHNT_TP_GRP ";
        list = commQueryDAO.findBySQLQuery(sql);
        int index = 6;
        int len = 0;
        if (null != list && !list.isEmpty()) {
            it = list.iterator();
            while (it.hasNext()) {
                Object[] obj = (Object[]) it.next();
                if (!StringUtil.isNull(obj[0])
                        && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) {
                    int a = obj[0].toString().indexOf("(");
                    int b = obj[0].toString().indexOf("");
                    if (a > 0 && b > 0) {
                        data[index][2] = obj[0].toString().substring(0, a < b ? a : b);
                    } else {
                        data[index][2] = obj[0].toString().substring(0, a + b + 1);
                    }
                } else {
                    data[index][2] = obj[0].toString();
                }
                data[index][3] = "";
                data[index][4] = obj[1].toString();
                data[index][5] = obj[2].toString();
                data[index][6] = obj[3].toString();
                data[index][7] = obj[4].toString();
                data[index][8] = String
                        .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString()));
                index++;
                len++;
            }
        }
        data[index][0] = "4";
        data[index][1] = "?POS";
        sql = "SELECT DESCR,nvl(term1.c,0),nvl(term2.c,0),nvl(term3.c,0),nvl(term4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp "
                + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in "
                + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS <= '" + lastYear
                + "' group by MCHT_GRP) term1 on (grp.MCHNT_TP_GRP = term1.MCHT_GRP) "
                + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in "
                + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS < '" + lastMon
                + "' group by MCHT_GRP) term2 on (grp.MCHNT_TP_GRP = term2.MCHT_GRP)"
                + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in "
                + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisMonStart
                + "' and t.REC_CRT_TS <= '" + thisMonEnd
                + "' group by MCHT_GRP) term3 on (grp.MCHNT_TP_GRP = term3.MCHT_GRP)"
                + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in "
                + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisYearStart
                + "' and t.REC_CRT_TS <= '" + thisYearEnd
                + "' group by MCHT_GRP) term4 on (grp.MCHNT_TP_GRP = term4.MCHT_GRP) "
                + "order by MCHNT_TP_GRP ";
        list = commQueryDAO.findBySQLQuery(sql);
        if (null != list && !list.isEmpty()) {
            it = list.iterator();
            while (it.hasNext()) {
                Object[] obj = (Object[]) it.next();
                if (!StringUtil.isNull(obj[0])
                        && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) {
                    int a = obj[0].toString().indexOf("(");
                    int b = obj[0].toString().indexOf("");
                    if (a > 0 && b > 0) {
                        data[index][2] = obj[0].toString().substring(0, a < b ? a : b);
                    } else {
                        data[index][2] = obj[0].toString().substring(0, a + b + 1);
                    }
                } else {
                    data[index][2] = obj[0].toString();
                }
                data[index][3] = "?";
                data[index][4] = obj[1].toString();
                data[index][5] = obj[2].toString();
                data[index][6] = obj[3].toString();
                data[index][7] = obj[4].toString();
                data[index][8] = String
                        .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString()));
                index++;
            }
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        sheet.setDefaultColumnWidth(10);
        sheet.setColumnWidth(1, 3000);
        sheet.setColumnWidth(2, 6000);
        //?
        HSSFCellStyle styleTitle = ExcelUtil.createStyleTitle(workbook);
        HSSFCellStyle styleBold = ExcelUtil.createStyleBold(workbook);
        HSSFCellStyle styleCenter = ExcelUtil.createStyleCenter(workbook);
        HSSFCellStyle styleRight = ExcelUtil.createStyleRight(workbook);
        HSSFCellStyle styleThin = ExcelUtil.createStyleThinCenter(workbook);

        short rowIndex = 0;// 
        // 
        row = sheet.createRow(rowIndex++);
        row.setHeight((short) 800);
        cell = row.createCell(0);
        cell.setCellStyle(styleTitle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("?");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));// ??
        rowIndex++;
        row = sheet.createRow(rowIndex++);
        cell = row.createCell(0);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(styleThin);
        cell.setCellValue("");
        cell = row.createCell(1);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(styleThin);
        cell.setCellValue(date.substring(0, 4) + "" + date.substring(4) + "");
        cell = row.createCell(3);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(styleThin);
        cell.setCellValue("");
        cell = row.createCell(4);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(styleThin);
        cell.setCellValue(InformationUtil.getBrhName(brhId));

        String[] titles = { "??", "        ", "", "??", "", "", "",
                "", "" };
        row = sheet.createRow(rowIndex++);
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(styleBold);
        }
        for (int i = 0; i < index; i++) {
            row = sheet.createRow(rowIndex++);
            for (int j = 0; j < data[i].length; j++) {
                cell = row.createCell(j);
                if (j < 4) {
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle(styleCenter);
                    cell.setCellValue(data[i][j]);
                } else {
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(styleRight);
                    cell.setCellValue(Double.valueOf(data[i][j]));
                }
            }
        }

        int start = 1;
        sheet.addMergedRegion(new CellRangeAddress(start + 1, start + 1, 4, 8));

        sheet.addMergedRegion(new CellRangeAddress(start + 2, start + 2, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 3, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 5, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(start + 4, start + 5, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 6, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 8, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(start + 7, start + 8, 1, 1));

        sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 1, 1));

        String path = ExcelUtil.writeFiles(workbook,
                "RN50202RN_" + brhId + "_" + CommonFunction.getCurrentDateTime() + ".xls");

        return returnService(Constants.SUCCESS_CODE_CUSTOMIZE + path);

    } catch (Exception e) {
        e.printStackTrace();
        return returnService("?,?", e);
    }
}

---

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

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

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

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

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

    Font fontTerritorio = workbook.createFont();
    fontTerritorio.setItalic(true);/*from   w w w.j av  a 2  s . co 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) {
    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);//from   w  w w  .  ja v  a2  s.co m

    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]
                    + "+";
        }//from   ww w.  j a  v a2 s  . c  om

    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

}

---

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