Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.ideaspymes.arthyweb.ventas.web.controllers; import com.ideaspymes.arthyweb.generico.JsfUtil; import com.ideaspymes.arthyweb.ventas.service.impl.ConsultasService; import com.ideaspymes.arthyweb.ventas.service.impl.ResumenVentas; import com.ideaspymes.arthyweb.ventas.service.impl.ResumenVentasCantadas; import java.io.IOException; import java.io.Serializable; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Collections; import java.util.Comparator; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Locale; import java.util.Map; import javax.annotation.PostConstruct; import javax.ejb.EJB; import javax.faces.context.FacesContext; import javax.faces.view.ViewScoped; import javax.inject.Named; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_RIGHT; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; /** * * @author gilda.perez */ @Named @ViewScoped public class VentasCantadasBean implements Serializable { @EJB private ConsultasService consultasService; private List<ResumenVentasCantadas> ventasCantadas; // private int vendedorid; private Date inicio = new Date(); private Date fin = new Date(); private Integer indexRow = 1; private Integer indexCol = 0; private CellStyle styleTitulo; private CellStyle styleTerritorio; private CellStyle styleTerritorioTotal1; private CellStyle styleTerritorioTotal3; private CellStyle styleTitulo9; private CellStyle styleSubTitulo; private CellStyle styleSubTituloCaje; private CellStyle styleCantidad; private CellStyle styleCantidadAzul; private CellStyle styleCantidadNegrita; private CellStyle styleCantidadGris; private CellStyle styleCantidadGris1; private CellStyle styleCantidadCaje; private CellStyle styleMergeCentrado; private CellStyle styleMergeTotal; private String cols20 = ""; private String cols10 = ""; private String colsGrue = ""; private Object calender; @PostConstruct public void init() { cargaResumen(); } public Date getInicio() { return inicio; } public void setInicio(Date inicio) { this.inicio = inicio; } public Date getFin() { return fin; } public void setFin(Date fin) { this.fin = fin; } public List<ResumenVentasCantadas> getVentasCantadas() { return ventasCantadas; } public void setVentasCantadas(List<ResumenVentasCantadas> ventasCantadas) { this.ventasCantadas = ventasCantadas; } public void cargaResumen() { //verificar que no sea nulo los parametros List<ResumenVentas> listaVentas = consultasService.getDetalleVentas(inicio, fin); //Agrupa por vendedor Map<String, List<ResumenVentas>> map = new HashMap<>(); for (ResumenVentas rv : listaVentas) { List<ResumenVentas> valueList = map.get(rv.getVendedor()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rv); map.put(rv.getVendedor(), valueList); } else { valueList.add(rv); } } ventasCantadas = new ArrayList<>(); for (Map.Entry<String, List<ResumenVentas>> entry : map.entrySet()) { ResumenVentasCantadas vc = new ResumenVentasCantadas(); for (ResumenVentas rv : entry.getValue()) { //vc.setCodVendedor(rv.getCodVendedor()); vc.setVendedor(rv.getVendedor()); vc.setTerritorio(rv.getTerritorio()); vc.setZona(rv.getZona()); vc.setCantboletas(rv.getCantboletas()); vc.setOrden(rv.getOrden()); cargaDatosProducto(rv.getProductoid(), rv, vc); } ventasCantadas.add(vc); } } public void cargaResumenPorVendedor() { //verificar que no sea nulo los parametros List<ResumenVentas> listaVentas = consultasService.getDetalleVentas(inicio, fin); //Agrupa por vendedor Map<String, List<ResumenVentas>> map = new HashMap<>(); for (ResumenVentas rv : listaVentas) { List<ResumenVentas> valueList = map.get(rv.getVendedorReal()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rv); map.put(rv.getVendedorReal(), valueList); } else { valueList.add(rv); } } ventasCantadas = new ArrayList<>(); for (Map.Entry<String, List<ResumenVentas>> entry : map.entrySet()) { ResumenVentasCantadas vc = new ResumenVentasCantadas(); for (ResumenVentas rv : entry.getValue()) { //vc.setCodVendedor(rv.getCodVendedor()); vc.setVendedor(rv.getVendedorReal()); vc.setTerritorio(rv.getTerritorio()); vc.setZona(rv.getZona()); vc.setCantboletas(rv.getCantboletas()); vc.setOrden(rv.getOrden()); cargaDatosProducto(rv.getProductoid(), rv, vc); } ventasCantadas.add(vc); } } private void cargaDatosProducto(Integer productoid, ResumenVentas rv, ResumenVentasCantadas vc) { // System.out.println("producto, gruesa, restocaje: " + productoid + " " + rv.getProducto() + " " + rv.getGruesas() + " " + rv.getRestocaje()); switch (productoid) { case 198: vc.setDes198(rv.getProducto()); vc.setGruesas198(rv.getGruesas()); vc.setRestocaje198(rv.getRestocaje()); break; case 204: vc.setDes204(rv.getProducto()); vc.setGruesas204(rv.getGruesas()); vc.setRestocaje204(rv.getRestocaje()); break; case 218: vc.setDes218(rv.getProducto()); vc.setGruesas218(rv.getGruesas()); vc.setRestocaje218(rv.getRestocaje()); break; case 411: vc.setDes411(rv.getProducto()); vc.setGruesas411(rv.getGruesas()); vc.setRestocaje411(rv.getRestocaje()); break; case 412: vc.setDes412(rv.getProducto()); vc.setGruesas412(rv.getGruesas()); vc.setRestocaje412(rv.getRestocaje()); break; case 413: vc.setDes413(rv.getProducto()); vc.setGruesas413(rv.getGruesas()); vc.setRestocaje413(rv.getRestocaje()); break; case 701: vc.setDes701(rv.getProducto()); vc.setGruesas701(rv.getGruesas()); vc.setRestocaje701(rv.getRestocaje()); break; case 702: vc.setDes702(rv.getProducto()); vc.setGruesas702(rv.getGruesas()); vc.setRestocaje702(rv.getRestocaje()); break; case 703: vc.setDes703(rv.getProducto()); vc.setGruesas703(rv.getGruesas()); vc.setRestocaje703(rv.getRestocaje()); break; case 704: vc.setDes704(rv.getProducto()); vc.setGruesas704(rv.getGruesas()); vc.setRestocaje704(rv.getRestocaje()); break; case 705: vc.setDes705(rv.getProducto()); vc.setGruesas705(rv.getGruesas()); vc.setRestocaje705(rv.getRestocaje()); break; case 706: vc.setDes706(rv.getProducto()); vc.setGruesas706(rv.getGruesas()); vc.setRestocaje706(rv.getRestocaje()); break; case 707: vc.setDes707(rv.getProducto()); vc.setGruesas707(rv.getGruesas()); vc.setRestocaje707(rv.getRestocaje()); break; case 708: vc.setDes708(rv.getProducto()); vc.setGruesas708(rv.getGruesas()); vc.setRestocaje708(rv.getRestocaje()); break; case 709: vc.setDes709(rv.getProducto()); vc.setGruesas709(rv.getGruesas()); vc.setRestocaje709(rv.getRestocaje()); break; case 710: vc.setDes710(rv.getProducto()); vc.setGruesas710(rv.getGruesas()); vc.setRestocaje710(rv.getRestocaje()); break; } } public void generarExcelVentasCantadas() throws IOException { cargaResumen(); if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } } public void generarExcelVentasCantadasDetalle() throws IOException { cargaResumenPorVendedor(); if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } } 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); 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); } 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); 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); } private void generarTotal2(HSSFRow rowTotal2, int indexFinGrupo, String[] aColsGrue, HSSFCell cellTerr) { HSSFCell cellTotal2Territorio = rowTotal2.createCell(indexCol); cellTotal2Territorio.setCellValue("Total " + cellTerr.getStringCellValue() + " - Cajas"); cellTotal2Territorio.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal2Boletas = rowTotal2.createCell(indexCol + 2); cellTotal2Boletas.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red20g = rowTotal2.createCell(indexCol + 3); cellTotal2Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Red20g .setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexFinGrupo + 1) + "/50"); cellTotal2Red20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red20c = rowTotal2.createCell(indexCol + 4); cellTotal2Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue20g = rowTotal2.createCell(indexCol + 5); cellTotal2Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Blue20g .setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexFinGrupo + 1) + "/50"); cellTotal2Blue20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue20c = rowTotal2.createCell(indexCol + 6); cellTotal2Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green20g = rowTotal2.createCell(indexCol + 7); cellTotal2Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Green20g .setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexFinGrupo + 1) + "/50"); cellTotal2Green20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green20c = rowTotal2.createCell(indexCol + 8); cellTotal2Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres20g = rowTotal2.createCell(indexCol + 9); cellTotal2Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Tres20g .setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexFinGrupo + 1) + "/50"); cellTotal2Tres20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres20c = rowTotal2.createCell(indexCol + 10); cellTotal2Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo20g = rowTotal2.createCell(indexCol + 11); cellTotal2Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Duo20g .setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexFinGrupo + 1) + "/50"); cellTotal2Duo20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo20c = rowTotal2.createCell(indexCol + 12); cellTotal2Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red10g = rowTotal2.createCell(indexCol + 13); cellTotal2Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Red10g .setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexFinGrupo + 1) + "/50"); cellTotal2Red10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red10c = rowTotal2.createCell(indexCol + 14); cellTotal2Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue10g = rowTotal2.createCell(indexCol + 15); cellTotal2Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Blue10g .setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexFinGrupo + 1) + "/50"); cellTotal2Blue10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue10c = rowTotal2.createCell(indexCol + 16); cellTotal2Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green10g = rowTotal2.createCell(indexCol + 17); cellTotal2Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Green10g .setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexFinGrupo + 1) + "/50"); cellTotal2Green10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green10c = rowTotal2.createCell(indexCol + 18); cellTotal2Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres10g = rowTotal2.createCell(indexCol + 19); cellTotal2Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Tres10g .setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexFinGrupo + 1) + "/50"); cellTotal2Tres10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres10c = rowTotal2.createCell(indexCol + 20); cellTotal2Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo10g = rowTotal2.createCell(indexCol + 21); cellTotal2Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Duo10g .setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexFinGrupo + 1) + "/50"); cellTotal2Duo10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo10c = rowTotal2.createCell(indexCol + 22); cellTotal2Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM20g = rowTotal2.createCell(indexCol + 23); cellTotal2SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SM20g .setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexFinGrupo + 1) + "/50"); cellTotal2SM20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM20c = rowTotal2.createCell(indexCol + 24); cellTotal2SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM10g = rowTotal2.createCell(indexCol + 25); cellTotal2SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SM10g .setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexFinGrupo + 1) + "/50"); cellTotal2SM10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM10c = rowTotal2.createCell(indexCol + 26); cellTotal2SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SMSoftg = rowTotal2.createCell(indexCol + 27); cellTotal2SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SMSoftg .setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexFinGrupo + 1) + "/50"); cellTotal2SMSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SMSoftc = rowTotal2.createCell(indexCol + 28); cellTotal2SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K20g = rowTotal2.createCell(indexCol + 29); cellTotal2K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2K20g .setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexFinGrupo + 1) + "/50"); cellTotal2K20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K20c = rowTotal2.createCell(indexCol + 30); cellTotal2K20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K10g = rowTotal2.createCell(indexCol + 31); cellTotal2K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2K10g .setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexFinGrupo + 1) + "/50"); cellTotal2K10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K10c = rowTotal2.createCell(indexCol + 32); cellTotal2K10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2KSoftg = rowTotal2.createCell(indexCol + 33); cellTotal2KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2KSoftg .setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexFinGrupo + 1) + "/50"); cellTotal2KSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal22KSoftc = rowTotal2.createCell(indexCol + 34); cellTotal22KSoftc.setCellStyle(styleCantidadAzul); String formTotal2Cajas = ""; for (int i = 0; i < aColsGrue.length; i++) { formTotal2Cajas += aColsGrue[i] + (indexFinGrupo + 2) + "+"; } formTotal2Cajas += "0"; HSSFCell cellTotal2g = rowTotal2.createCell(indexCol + 35); cellTotal2g.setCellStyle(styleCantidadGris); HSSFCell cellTotal2c = rowTotal2.createCell(indexCol + 36); cellTotal2c.setCellStyle(styleCantidadGris); HSSFCell cellTotal2caja = rowTotal2.createCell(indexCol + 37); cellTotal2caja.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2caja.setCellFormula(formTotal2Cajas); cellTotal2caja.setCellStyle(styleCantidadNegrita); HSSFCell cellTotal2gr = rowTotal2.createCell(indexCol + 38); cellTotal2gr.setCellStyle(styleCantidadNegrita); } private void generarTotal1(HSSFRow rowTotal1, int indexInicioGrupo, int indexFinGrupo, String[] aColsGrue, String[] aCols20, String[] aCols10, HSSFCell cellTerr) { HSSFCell cellTotal1Territorio = rowTotal1.createCell(indexCol); cellTotal1Territorio.setCellValue("Total " + cellTerr.getStringCellValue()); cellTotal1Territorio.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal1Boleta = rowTotal1.createCell(indexCol + 2); cellTotal1Boleta.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Boleta.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 2) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 2) + indexFinGrupo + ")"); cellTotal1Boleta.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red20g = rowTotal1.createCell(indexCol + 3); cellTotal1Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 3) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 3) + indexFinGrupo + ")"); cellTotal1Red20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red20c = rowTotal1.createCell(indexCol + 4); cellTotal1Red20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 4) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 4) + indexFinGrupo + ")"); cellTotal1Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue20g = rowTotal1.createCell(indexCol + 5); cellTotal1Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 5) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 5) + indexFinGrupo + ")"); cellTotal1Blue20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue20c = rowTotal1.createCell(indexCol + 6); cellTotal1Blue20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 6) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 6) + indexFinGrupo + ")"); cellTotal1Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green20g = rowTotal1.createCell(indexCol + 7); cellTotal1Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 7) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 7) + indexFinGrupo + ")"); cellTotal1Green20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green20c = rowTotal1.createCell(indexCol + 8); cellTotal1Green20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 8) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 8) + indexFinGrupo + ")"); cellTotal1Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres20g = rowTotal1.createCell(indexCol + 9); cellTotal1Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 9) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 9) + indexFinGrupo + ")"); cellTotal1Tres20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres20c = rowTotal1.createCell(indexCol + 10); cellTotal1Tres20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres20c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 10) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 10) + indexFinGrupo + ")"); cellTotal1Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo20g = rowTotal1.createCell(indexCol + 11); cellTotal1Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo20g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 11) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 11) + indexFinGrupo + ")"); cellTotal1Duo20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo20c = rowTotal1.createCell(indexCol + 12); cellTotal1Duo20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo20c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 12) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 12) + indexFinGrupo + ")"); cellTotal1Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red10g = rowTotal1.createCell(indexCol + 13); cellTotal1Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 13) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 13) + indexFinGrupo + ")"); cellTotal1Red10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red10c = rowTotal1.createCell(indexCol + 14); cellTotal1Red10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 14) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 14) + indexFinGrupo + ")"); cellTotal1Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue10g = rowTotal1.createCell(indexCol + 15); cellTotal1Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 15) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 15) + indexFinGrupo + ")"); cellTotal1Blue10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue10c = rowTotal1.createCell(indexCol + 16); cellTotal1Blue10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 16) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 16) + indexFinGrupo + ")"); cellTotal1Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green10g = rowTotal1.createCell(indexCol + 17); cellTotal1Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 17) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 17) + indexFinGrupo + ")"); cellTotal1Green10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green10c = rowTotal1.createCell(indexCol + 18); cellTotal1Green10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 18) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 18) + indexFinGrupo + ")"); cellTotal1Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres10g = rowTotal1.createCell(indexCol + 19); cellTotal1Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 19) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 19) + indexFinGrupo + ")"); cellTotal1Tres10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres10c = rowTotal1.createCell(indexCol + 20); cellTotal1Tres10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 20) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 20) + indexFinGrupo + ")"); cellTotal1Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo10g = rowTotal1.createCell(indexCol + 21); cellTotal1Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 21) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 21) + indexFinGrupo + ")"); cellTotal1Duo10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo10c = rowTotal1.createCell(indexCol + 22); cellTotal1Duo10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 22) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 22) + indexFinGrupo + ")"); cellTotal1Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM20g = rowTotal1.createCell(indexCol + 23); cellTotal1SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM20g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 23) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 23) + indexFinGrupo + ")"); cellTotal1SM20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM20c = rowTotal1.createCell(indexCol + 24); cellTotal1SM20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM20c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 24) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 24) + indexFinGrupo + ")"); cellTotal1SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM10g = rowTotal1.createCell(indexCol + 25); cellTotal1SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 25) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 25) + indexFinGrupo + ")"); cellTotal1SM10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM10c = rowTotal1.createCell(indexCol + 26); cellTotal1SM10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 26) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 26) + indexFinGrupo + ")"); cellTotal1SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SMSoftg = rowTotal1.createCell(indexCol + 27); cellTotal1SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SMSoftg .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 27) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 27) + indexFinGrupo + ")"); cellTotal1SMSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SMSoftc = rowTotal1.createCell(indexCol + 28); cellTotal1SMSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SMSoftc .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 28) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 28) + indexFinGrupo + ")"); cellTotal1SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K20g = rowTotal1.createCell(indexCol + 29); cellTotal1K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 29) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 29) + indexFinGrupo + ")"); cellTotal1K20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K20c = rowTotal1.createCell(indexCol + 30); cellTotal1K20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 30) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 30) + indexFinGrupo + ")"); cellTotal1K20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K10g = rowTotal1.createCell(indexCol + 31); cellTotal1K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K10g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 31) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 31) + indexFinGrupo + ")"); cellTotal1K10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K10c = rowTotal1.createCell(indexCol + 32); cellTotal1K10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K10c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 32) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 32) + indexFinGrupo + ")"); cellTotal1K10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KSoftg = rowTotal1.createCell(indexCol + 33); cellTotal1KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KSoftg .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 33) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 33) + indexFinGrupo + ")"); cellTotal1KSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KSoftc = rowTotal1.createCell(indexCol + 34); cellTotal1KSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KSoftc .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 34) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 34) + indexFinGrupo + ")"); cellTotal1KSoftc.setCellStyle(styleCantidadAzul); String formTotal1Grue = ""; for (int i = 0; i < aColsGrue.length; i++) { formTotal1Grue += aColsGrue[i] + (indexFinGrupo + 1) + "+"; } formTotal1Grue += "0"; String formTotal120 = ""; for (int i = 0; i < aCols20.length; i++) { formTotal120 += aCols20[i] + (indexFinGrupo + 1) + "+"; } formTotal120 += "0"; String formTotal110 = ""; for (int i = 0; i < aCols10.length; i++) { formTotal110 += aCols10[i] + (indexFinGrupo + 1) + "+"; } formTotal110 += "0"; HSSFCell cellTotal1g = rowTotal1.createCell(indexCol + 35); cellTotal1g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1g.setCellFormula( "(INT((" + formTotal120 + ")/10))+" + "(INT((" + formTotal110 + ")/20)) +(" + formTotal1Grue + ")"); cellTotal1g.setCellStyle(styleCantidadGris); HSSFCell cellTotal1c = rowTotal1.createCell(indexCol + 36); cellTotal1c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1c.setCellFormula("MOD((" + formTotal110 + "),20)+ MOD((" + formTotal120 + "),10)"); cellTotal1c.setCellStyle(styleCantidadGris); HSSFCell cellTotal1ca = rowTotal1.createCell(indexCol + 37); cellTotal1ca.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1ca.setCellFormula( "(INT(" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + "/50))"); cellTotal1ca.setCellStyle(styleCantidadNegrita); HSSFCell cellTotal1gr = rowTotal1.createCell(indexCol + 38); cellTotal1gr.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1gr.setCellFormula( "(MOD(" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + ",50))"); cellTotal1gr.setCellStyle(styleCantidadNegrita); } private void generarDetalles(HSSFRow row1, int indexFilaActual, ResumenVentasCantadas rv) { String formulaSumGruesa = "("; String formulaSumCaje10 = "((";//"(INT(("; String formulaSumCaje20 = "((";//"(INT(("; HSSFCell cellPRed20g = row1.createCell(indexCol + 3); cellPRed20g.setCellValue(rv.getGruesas706());//rv.getGruesas706() cellPRed20g.setCellStyle(styleCantidad); HSSFCell cellPRed20c = row1.createCell(indexCol + 4); cellPRed20c.setCellValue(rv.getRestocaje706());//rv.getRestocaje706() cellPRed20c.setCellStyle(styleCantidadCaje); indexFilaActual = indexFilaActual + 1; formulaSumGruesa += CellReference.convertNumToColString(indexCol + 3) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 4) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 3) + ","; cols20 += CellReference.convertNumToColString(indexCol + 4) + ","; HSSFCell cellPBlue20g = row1.createCell(indexCol + 5); cellPBlue20g.setCellValue(rv.getGruesas710());//rv.getGruesas710() cellPBlue20g.setCellStyle(styleCantidad); HSSFCell cellPBlue20c = row1.createCell(indexCol + 6); cellPBlue20c.setCellValue(rv.getRestocaje710());//rv.getRestocaje710() cellPBlue20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 5) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 6) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 5) + ","; cols20 += CellReference.convertNumToColString(indexCol + 6) + ","; HSSFCell cellPGreen20g = row1.createCell(indexCol + 7); cellPGreen20g.setCellValue(rv.getGruesas708());//rv.getGruesas708() cellPGreen20g.setCellStyle(styleCantidad); HSSFCell cellPGreen20c = row1.createCell(indexCol + 8); cellPGreen20c.setCellValue(rv.getRestocaje708()); //rv.getRestocaje708() cellPGreen20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 7) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 8) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 7) + ","; cols20 += CellReference.convertNumToColString(indexCol + 8) + ","; HSSFCell cellPTres20g = row1.createCell(indexCol + 9); cellPTres20g.setCellValue(rv.getGruesas704());//rv.getGruesas704() cellPTres20g.setCellStyle(styleCantidad); HSSFCell cellPTres20c = row1.createCell(indexCol + 10); cellPTres20c.setCellValue(rv.getRestocaje704());//rv.getRestocaje704() cellPTres20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 9) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 10) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 9) + ","; cols20 += CellReference.convertNumToColString(indexCol + 10) + ","; HSSFCell cellPDuo20g = row1.createCell(indexCol + 11); cellPDuo20g.setCellValue(rv.getGruesas702());//rv.getGruesas702() cellPDuo20g.setCellStyle(styleCantidad); HSSFCell cellPDuo20c = row1.createCell(indexCol + 12); cellPDuo20c.setCellValue(rv.getRestocaje702());//rv.getRestocaje702() cellPDuo20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 11) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 12) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 11) + ","; cols20 += CellReference.convertNumToColString(indexCol + 12) + ","; HSSFCell cellPRed10g = row1.createCell(indexCol + 13); cellPRed10g.setCellValue(rv.getGruesas705());//rv.getGruesas705() cellPRed10g.setCellStyle(styleCantidad); HSSFCell cellPRed10c = row1.createCell(indexCol + 14); cellPRed10c.setCellValue(rv.getRestocaje705());//rv.getRestocaje705() cellPRed10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 13) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 14) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 13) + ","; cols10 += CellReference.convertNumToColString(indexCol + 14) + ","; HSSFCell cellPBlue10g = row1.createCell(indexCol + 15); cellPBlue10g.setCellValue(rv.getGruesas709());//rv.getGruesas709() cellPBlue10g.setCellStyle(styleCantidad); HSSFCell cellPBlue10c = row1.createCell(indexCol + 16); cellPBlue10c.setCellValue(rv.getRestocaje709());//rv.getRestocaje709() cellPBlue10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 15) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 16) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 15) + ","; cols10 += CellReference.convertNumToColString(indexCol + 16) + ","; HSSFCell cellPGreen10g = row1.createCell(indexCol + 17); cellPGreen10g.setCellValue(rv.getGruesas707());//rv.getGruesas707() cellPGreen10g.setCellStyle(styleCantidad); HSSFCell cellPGreen10c = row1.createCell(indexCol + 18); cellPGreen10c.setCellValue(rv.getRestocaje707());//rv.getRestocaje707() cellPGreen10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 17) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 18) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 17) + ","; cols10 += CellReference.convertNumToColString(indexCol + 18) + ","; HSSFCell cellPTres10g = row1.createCell(indexCol + 19); cellPTres10g.setCellValue(rv.getGruesas703());//rv.getGruesas703() cellPTres10g.setCellStyle(styleCantidad); HSSFCell cellPTres10c = row1.createCell(indexCol + 20); cellPTres10c.setCellValue(rv.getRestocaje703());//rv.getRestocaje703() cellPTres10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 19) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 20) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 19) + ","; cols10 += CellReference.convertNumToColString(indexCol + 20) + ","; HSSFCell cellPDuo10g = row1.createCell(indexCol + 21); cellPDuo10g.setCellValue(rv.getGruesas701());//rv.getGruesas701() cellPDuo10g.setCellStyle(styleCantidad); HSSFCell cellPDuo10c = row1.createCell(indexCol + 22); cellPDuo10c.setCellValue(rv.getRestocaje701());//rv.getRestocaje701() cellPDuo10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 21) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 22) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 21) + ","; cols10 += CellReference.convertNumToColString(indexCol + 22) + ","; HSSFCell cellSM20g = row1.createCell(indexCol + 23); cellSM20g.setCellValue(rv.getGruesas411());//rv.getGruesas413() cellSM20g.setCellStyle(styleCantidad); HSSFCell cellSM20c = row1.createCell(indexCol + 24); cellSM20c.setCellValue(rv.getRestocaje411());//rv.getRestocaje413() cellSM20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 23) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 24) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 23) + ","; cols20 += CellReference.convertNumToColString(indexCol + 24) + ","; HSSFCell cellSM10g = row1.createCell(indexCol + 25); cellSM10g.setCellValue(rv.getGruesas412());//rv.getGruesas412() cellSM10g.setCellStyle(styleCantidad); HSSFCell cellSM10c = row1.createCell(indexCol + 26); cellSM10c.setCellValue(rv.getRestocaje412());//rv.getRestocaje412() cellSM10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 25) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 26) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 25) + ","; cols10 += CellReference.convertNumToColString(indexCol + 26) + ","; HSSFCell cellSMSoftg = row1.createCell(indexCol + 27); cellSMSoftg.setCellValue(rv.getGruesas413());//rv.getGruesas411() cellSMSoftg.setCellStyle(styleCantidad); HSSFCell cellSMSoftc = row1.createCell(indexCol + 28); cellSMSoftc.setCellValue(rv.getRestocaje413());//rv.getRestocaje411() cellSMSoftc.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 27) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 28) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 27) + ","; cols20 += CellReference.convertNumToColString(indexCol + 28) + ","; HSSFCell cellK20g = row1.createCell(indexCol + 29); cellK20g.setCellValue(rv.getGruesas198());//rv.getGruesas198() cellK20g.setCellStyle(styleCantidad); HSSFCell cellK20c = row1.createCell(indexCol + 30); cellK20c.setCellValue(rv.getRestocaje198());//rv.getRestocaje198() cellK20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 29) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 30) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 29) + ","; cols10 += CellReference.convertNumToColString(indexCol + 30) + ","; HSSFCell cellK10g = row1.createCell(indexCol + 31); cellK10g.setCellValue(rv.getGruesas218());//rv.getGruesas218() cellK10g.setCellStyle(styleCantidad); HSSFCell cellK10c = row1.createCell(indexCol + 32); cellK10c.setCellValue(rv.getRestocaje218());//rv.getRestocaje218() cellK10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 31) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 32) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 31) + ","; cols10 += CellReference.convertNumToColString(indexCol + 32) + ","; HSSFCell cellKSoftg = row1.createCell(indexCol + 33); cellKSoftg.setCellValue(rv.getGruesas204());//rv.getGruesas218() cellKSoftg.setCellStyle(styleCantidad); HSSFCell cellKSoftc = row1.createCell(indexCol + 34); cellKSoftc.setCellValue(rv.getRestocaje204());//rv.getRestocaje218() cellKSoftc.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 33) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 34) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 33) + ","; cols20 += CellReference.convertNumToColString(indexCol + 34) + ","; formulaSumGruesa += "0)"; String formulaTotalGruesas = "(INT" + formulaSumCaje10 + "0)/20))+(INT" + formulaSumCaje20 + "0)/10))+" + formulaSumGruesa; String formulaTotalCaje = "MOD" + formulaSumCaje10 + "0),20)+ MOD" + formulaSumCaje20 + "0),10)"; HSSFCell cellTotalg = row1.createCell(indexCol + 35); cellTotalg.setCellFormula(formulaTotalGruesas); cellTotalg.setCellStyle(styleCantidadGris1); HSSFCell cellTotalc = row1.createCell(indexCol + 36); cellTotalc.setCellFormula(formulaTotalCaje); cellTotalc.setCellStyle(styleCantidadGris1); HSSFCell cellTotalca = row1.createCell(indexCol + 37); cellTotalca.setCellFormula( "(INT(" + CellReference.convertNumToColString(indexCol + 35) + indexFilaActual + "/50))"); cellTotalca.setCellStyle(styleCantidad); HSSFCell cellTotalgr = row1.createCell(indexCol + 38); cellTotalgr.setCellFormula( "(MOD(" + CellReference.convertNumToColString(indexCol + 35) + indexFilaActual + ",50))"); cellTotalgr.setCellStyle(styleCantidad); } private void configAnchoCols(HSSFSheet sheet) { sheet.setColumnWidth(0, 4100); sheet.setColumnWidth(1, 3500); sheet.setColumnWidth(2, 2000); sheet.setColumnWidth(3, 1500); sheet.setColumnWidth(4, 1000); sheet.setColumnWidth(5, 1700); sheet.setColumnWidth(6, 1000); sheet.setColumnWidth(7, 2000); sheet.setColumnWidth(8, 1000); sheet.setColumnWidth(9, 1800); sheet.setColumnWidth(10, 1000); sheet.setColumnWidth(11, 1500); sheet.setColumnWidth(12, 1000); sheet.setColumnWidth(13, 1800); sheet.setColumnWidth(14, 1000); sheet.setColumnWidth(15, 1700); sheet.setColumnWidth(16, 1000); sheet.setColumnWidth(17, 2000); sheet.setColumnWidth(18, 1000); sheet.setColumnWidth(19, 1700); sheet.setColumnWidth(20, 1000); sheet.setColumnWidth(21, 1700); sheet.setColumnWidth(22, 1000); sheet.setColumnWidth(23, 1800); sheet.setColumnWidth(24, 1000); sheet.setColumnWidth(25, 1800); sheet.setColumnWidth(26, 1000); sheet.setColumnWidth(27, 1500); sheet.setColumnWidth(28, 1000); sheet.setColumnWidth(29, 1700); sheet.setColumnWidth(30, 1000); sheet.setColumnWidth(31, 1700); sheet.setColumnWidth(32, 1000); sheet.setColumnWidth(33, 1700); sheet.setColumnWidth(34, 1000); sheet.setColumnWidth(35, 2100); sheet.setColumnWidth(36, 1500); sheet.setColumnWidth(37, 1500); sheet.setColumnWidth(38, 1000); } 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] + "+"; } } 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); } public void buscar() { cargaResumenPorVendedor(); } }