Example usage for org.apache.poi.ss.usermodel CellStyle setBorderTop

List of usage examples for org.apache.poi.ss.usermodel CellStyle setBorderTop

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setBorderTop

Introduction

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

Prototype

void setBorderTop(BorderStyle border);

Source Link

Document

set the type of border to use for the top border of the cell

Usage

From source file:com.griffinslogistics.document.excel.BDLGenerator.java

private static Map<String, CellStyle> createStyles(Workbook workbook) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;
    Font titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 26);
    titleFont.setFontName("Calibri");
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = workbook.createCellStyle();/*w ww. j  a  v a  2  s  .co  m*/
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("title", style);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setFontName("Calibri");
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("pulsioName", style);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setFontName("Calibri");
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("contacts", style);

    CellStyle footerStyle = workbook.createCellStyle();
    Font footerFont = workbook.createFont();
    footerFont.setFontHeightInPoints((short) 14);
    footerFont.setFontName("Calibri");
    footerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    footerStyle.setFont(footerFont);
    footerStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("footer", footerStyle);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setFontName("Calibri");
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFont.setItalic(true);
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);

    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(CellStyle.BORDER_MEDIUM);
    style.setBorderTop(CellStyle.BORDER_MEDIUM);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderBottom(CellStyle.BORDER_THIN);

    styles.put("tableHeadersLeft", style);

    CellStyle headerRowMiddleCellStyle = workbook.createCellStyle();
    headerRowMiddleCellStyle.cloneStyleFrom(style);
    headerRowMiddleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

    styles.put("tableHeadersMiddle", headerRowMiddleCellStyle);

    CellStyle headerRowRightCellStyle = workbook.createCellStyle();
    headerRowRightCellStyle.cloneStyleFrom(style);
    headerRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);

    styles.put("tableHeadersRight", headerRowRightCellStyle);

    CellStyle footerRowRightCellStyle = workbook.createCellStyle();
    footerRowRightCellStyle.cloneStyleFrom(style);
    footerRowRightCellStyle.setFillPattern(CellStyle.NO_FILL);
    footerRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    footerRowRightCellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);

    styles.put("tableFooters", footerRowRightCellStyle);

    CellStyle bodyRowLeftCellStyle = workbook.createCellStyle();
    bodyRowLeftCellStyle.cloneStyleFrom(style);
    Font titleBodyFont = workbook.createFont();
    titleBodyFont.setFontHeightInPoints((short) 14);
    titleBodyFont.setFontName("Calibri");
    bodyRowLeftCellStyle.setFont(titleBodyFont);
    bodyRowLeftCellStyle.setBorderTop(CellStyle.BORDER_THIN);
    bodyRowLeftCellStyle.setFillPattern(CellStyle.NO_FILL);

    styles.put("tableBodyLeft", bodyRowLeftCellStyle);

    CellStyle bodyRowMiddleCellStyle = workbook.createCellStyle();
    bodyRowMiddleCellStyle.cloneStyleFrom(bodyRowLeftCellStyle);
    bodyRowMiddleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

    styles.put("tableBodyMiddle", bodyRowMiddleCellStyle);

    CellStyle bodyRowRightCellStyle = workbook.createCellStyle();
    bodyRowRightCellStyle.cloneStyleFrom(bodyRowMiddleCellStyle);
    bodyRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);

    styles.put("tableBodyRight", bodyRowRightCellStyle);

    return styles;
}

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

private static Map<String, CellStyle> createStyles(XSSFWorkbook workbook) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle smallStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    smallStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    smallStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    smallStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    smallStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
    smallStyle.setFont(font);/*from  w w w  .  j a  v a2  s . c om*/
    smallStyle.setAlignment(CellStyle.ALIGN_CENTER);
    smallStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(STYLE_SMALL_FONT, smallStyle);

    CellStyle labelStyle = workbook.createCellStyle();
    Font labelFont = workbook.createFont();
    labelFont.setFontHeightInPoints((short) 20);
    labelStyle.setBorderBottom(CellStyle.BORDER_THIN);
    labelStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    labelStyle.setBorderRight(CellStyle.BORDER_THIN);
    labelStyle.setBorderTop(CellStyle.BORDER_THIN);
    labelStyle.setFont(labelFont);
    labelStyle.setAlignment(CellStyle.ALIGN_CENTER);
    labelStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    labelStyle.setWrapText(true);
    styles.put(STYLE_LABEL, labelStyle);

    CellStyle contentStyle = workbook.createCellStyle();
    Font contentFont = workbook.createFont();
    contentFont.setFontHeightInPoints((short) 25);
    contentFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
    contentStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    contentStyle.setBorderTop(CellStyle.BORDER_THIN);
    contentStyle.setFont(contentFont);
    contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
    contentStyle.setWrapText(true);
    contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(STYLE_CONTENT, contentStyle);

    return styles;
}

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

private static Map<String, CellStyle> createStyles(Workbook workbook) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    styles.put(DEFAULT_STYLE, style);/*from www  .j  a v a2 s.c o  m*/

    CellStyle labelStyle = workbook.createCellStyle();
    font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setColor(HSSFColor.DARK_RED.index);
    labelStyle.setFont(font);
    labelStyle.setWrapText(true);
    styles.put(LABEL_STYLE, labelStyle);

    CellStyle labelTopStyle = workbook.createCellStyle();
    labelTopStyle.cloneStyleFrom(labelStyle);
    labelTopStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    labelTopStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
    labelTopStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    labelTopStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put(LABEL_TOP_STYLE, labelTopStyle);

    CellStyle labelMiddleStyle = workbook.createCellStyle();
    labelMiddleStyle.cloneStyleFrom(labelStyle);
    labelMiddleStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    labelMiddleStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    labelMiddleStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put(LABEL_MIDDLE_STYLE, labelMiddleStyle);

    CellStyle labelLeftStyle = workbook.createCellStyle();
    labelLeftStyle.cloneStyleFrom(labelStyle);
    labelLeftStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    labelLeftStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put(LABEL_LEFT_STYLE, labelLeftStyle);

    CellStyle labelRightStyle = workbook.createCellStyle();
    labelRightStyle.cloneStyleFrom(labelStyle);
    labelRightStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    labelRightStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put(LABEL_RIGHT_STYLE, labelRightStyle);

    CellStyle labelBottomStyle = workbook.createCellStyle();
    labelBottomStyle.cloneStyleFrom(labelStyle);
    labelBottomStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    labelBottomStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    labelBottomStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    styles.put(LABEL_BOTTOM_STYLE, labelBottomStyle);

    CellStyle labelWholeStyle = workbook.createCellStyle();
    labelWholeStyle.cloneStyleFrom(labelStyle);
    labelWholeStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    labelWholeStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    labelWholeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    labelWholeStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
    labelWholeStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put(LABEL_WHOLE_STYLE, labelWholeStyle);

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

    CellStyle contentTopStyle = workbook.createCellStyle();
    contentTopStyle.cloneStyleFrom(labelTopStyle);
    contentTopStyle.setFont(contentFont);
    styles.put(CONTENT_TOP_STYLE, contentTopStyle);

    CellStyle contentMiddleStyle = workbook.createCellStyle();
    contentMiddleStyle.cloneStyleFrom(labelMiddleStyle);
    contentMiddleStyle.setFont(contentFont);
    styles.put(CONTENT_MIDDLE_STYLE, contentMiddleStyle);

    CellStyle contentMiddleAllignRightStyle = workbook.createCellStyle();
    contentMiddleAllignRightStyle.cloneStyleFrom(contentMiddleStyle);
    contentMiddleAllignRightStyle.setBorderRight(CellStyle.BORDER_NONE);
    contentMiddleAllignRightStyle.setFont(contentFont);
    contentMiddleAllignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put(CONTENT_MIDDLE_ALLIGN_RIGHT_STYLE, contentMiddleAllignRightStyle);

    CellStyle contentBottomAllignCenterStyle = workbook.createCellStyle();
    contentBottomAllignCenterStyle.cloneStyleFrom(contentMiddleStyle);
    contentBottomAllignCenterStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    contentBottomAllignCenterStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    contentBottomAllignCenterStyle.setFont(contentFont);
    contentBottomAllignCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE, contentBottomAllignCenterStyle);

    CellStyle contentMiddleNoBordersAllignRightStyle = workbook.createCellStyle();
    contentMiddleNoBordersAllignRightStyle.cloneStyleFrom(contentMiddleAllignRightStyle);
    contentMiddleNoBordersAllignRightStyle.setBorderLeft(CellStyle.BORDER_NONE);
    contentMiddleNoBordersAllignRightStyle.setAlignment(CellStyle.ALIGN_CENTER);
    contentMiddleNoBordersAllignRightStyle.setFont(contentFont);
    styles.put(CONTENT_MIDDLE_NO_BORDERS_STYLE, contentMiddleNoBordersAllignRightStyle);

    CellStyle contentMiddleAllignCenterStyle = workbook.createCellStyle();
    contentMiddleAllignCenterStyle.cloneStyleFrom(contentMiddleStyle);
    contentMiddleAllignCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
    contentMiddleAllignCenterStyle.setFont(contentFont);
    styles.put(CONTENT_MIDDLE_ALLIGN_CENTER_STYLE, contentMiddleAllignCenterStyle);

    CellStyle contentRightStyle = workbook.createCellStyle();
    contentRightStyle.cloneStyleFrom(labelRightStyle);
    contentRightStyle.setFont(contentFont);
    styles.put(CONTENT_RIGHT_STYLE, contentRightStyle);

    CellStyle contentBottomStyle = workbook.createCellStyle();
    contentBottomStyle.cloneStyleFrom(labelBottomStyle);
    contentBottomStyle.setFont(contentFont);
    styles.put(CONTENT_BOTTOM_STYLE, contentBottomStyle);

    CellStyle contentWholeStyle = workbook.createCellStyle();
    contentWholeStyle.cloneStyleFrom(labelWholeStyle);
    contentWholeStyle.setFont(contentFont);
    styles.put(CONTENT_WHOLE_STYLE, contentWholeStyle);

    return styles;
}

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

private static Map<String, CellStyle> createStyles(Workbook workbook) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;
    Font titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 26);
    titleFont.setFontName("Calibri");
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = workbook.createCellStyle();//from w  ww  .j  av  a2  s  . c  o m
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("title", style);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setFontName("Calibri");
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("pulsioName", style);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setFontName("Calibri");
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("contacts", style);

    CellStyle footerStyle = workbook.createCellStyle();
    Font footerFont = workbook.createFont();
    footerFont.setFontHeightInPoints((short) 14);
    footerFont.setFontName("Calibri");
    footerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    footerStyle.setFont(footerFont);
    footerStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("footer", footerStyle);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setFontName("Calibri");
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFont.setItalic(true);
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);

    // Override 25% grey to lighter grey
    HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
    HSSFPalette palette = hssfWorkbook.getCustomPalette();
    palette.setColorAtIndex(HSSFColor.GREY_25_PERCENT.index, (byte) 242, //RGB red (0-255)
            (byte) 242, //RGB green
            (byte) 242 //RGB blue
    );

    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(CellStyle.BORDER_MEDIUM);
    style.setBorderTop(CellStyle.BORDER_MEDIUM);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderBottom(CellStyle.BORDER_THIN);

    styles.put("tableHeadersLeft", style);

    CellStyle headerRowMiddleCellStyle = workbook.createCellStyle();
    headerRowMiddleCellStyle.cloneStyleFrom(style);
    headerRowMiddleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

    styles.put("tableHeadersMiddle", headerRowMiddleCellStyle);

    CellStyle headerRowRightCellStyle = workbook.createCellStyle();
    headerRowRightCellStyle.cloneStyleFrom(style);
    headerRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);

    styles.put("tableHeadersRight", headerRowRightCellStyle);

    CellStyle footerRowRightCellStyle = workbook.createCellStyle();
    footerRowRightCellStyle.cloneStyleFrom(style);
    footerRowRightCellStyle.setFillPattern(CellStyle.NO_FILL);
    footerRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    footerRowRightCellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);

    styles.put("tableFooters", footerRowRightCellStyle);

    CellStyle bodyRowLeftCellStyle = workbook.createCellStyle();
    bodyRowLeftCellStyle.cloneStyleFrom(style);
    Font titleBodyFont = workbook.createFont();
    titleBodyFont.setFontHeightInPoints((short) 14);
    titleBodyFont.setFontName("Calibri");
    bodyRowLeftCellStyle.setFont(titleBodyFont);
    bodyRowLeftCellStyle.setBorderTop(CellStyle.BORDER_THIN);
    bodyRowLeftCellStyle.setFillPattern(CellStyle.NO_FILL);

    styles.put("tableBodyLeft", bodyRowLeftCellStyle);

    CellStyle bodyRowMiddleCellStyle = workbook.createCellStyle();
    bodyRowMiddleCellStyle.cloneStyleFrom(bodyRowLeftCellStyle);
    bodyRowMiddleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

    styles.put("tableBodyMiddle", bodyRowMiddleCellStyle);

    CellStyle bodyRowRightCellStyle = workbook.createCellStyle();
    bodyRowRightCellStyle.cloneStyleFrom(bodyRowMiddleCellStyle);
    bodyRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);

    styles.put("tableBodyRight", bodyRowRightCellStyle);

    return styles;
}

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

private static Map<String, CellStyle> createStyles(XSSFWorkbook workbook) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle smallStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    smallStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    smallStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    smallStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    smallStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
    smallStyle.setFont(font);/*from www .j a va2s.  co m*/
    smallStyle.setAlignment(CellStyle.ALIGN_CENTER);
    smallStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(STYLE_SMALL_FONT, smallStyle);

    CellStyle labelStyle = workbook.createCellStyle();
    Font labelFont = workbook.createFont();
    labelFont.setFontHeightInPoints((short) 13);
    labelStyle.setBorderBottom(CellStyle.BORDER_THIN);
    labelStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    labelStyle.setBorderRight(CellStyle.BORDER_THIN);
    labelStyle.setBorderTop(CellStyle.BORDER_THIN);
    labelStyle.setFont(labelFont);
    labelStyle.setAlignment(CellStyle.ALIGN_CENTER);
    labelStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(STYLE_LABEL, labelStyle);

    CellStyle contentStyle = workbook.createCellStyle();
    Font contentFont = workbook.createFont();
    contentFont.setFontHeightInPoints((short) 15);
    contentFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
    contentStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    contentStyle.setBorderTop(CellStyle.BORDER_THIN);
    contentStyle.setFont(contentFont);
    contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
    contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(STYLE_CONTENT, contentStyle);

    return styles;
}

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/*from  w  w  w  .  j a v  a2 s.c o  m*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

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

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

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

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                calendar.set(Calendar.DAY_OF_MONTH, ++day);

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

    // Write the output to a file
    String file = RutaFile;
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();

}

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

License:Open Source License

/**
 * ?// www. j  a  va 2 s . co  m
 * 
 * @param wb
 *            
 * @return ?
 */
private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont1 = wb.createFont();
    titleFont1.setFontName("Arial");
    titleFont1.setFontHeightInPoints((short) 10);
    titleFont1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFont1.setColor(Font.COLOR_RED);
    style.setFont(titleFont1);
    styles.put("title1", style);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

    style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_LEFT);
    styles.put("data1", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("data2", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put("data3", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    // style.setWrapText(true);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
}

From source file:com.helger.poi.excel.style.ExcelStyle.java

License:Apache License

public void fillCellStyle(@Nonnull final Workbook aWB, @Nonnull final CellStyle aCS,
        @Nonnull final CreationHelper aCreationHelper) {
    if (m_eAlign != null)
        aCS.setAlignment(m_eAlign.getValue());
    if (m_eVAlign != null)
        aCS.setVerticalAlignment(m_eVAlign.getValue());
    aCS.setWrapText(m_bWrapText);//from  w  ww .  ja v a 2 s .com
    if (m_sDataFormat != null)
        aCS.setDataFormat(aCreationHelper.createDataFormat().getFormat(m_sDataFormat));
    if (m_eFillBackgroundColor != null)
        aCS.setFillBackgroundColor(m_eFillBackgroundColor.getIndex());
    if (m_eFillForegroundColor != null)
        aCS.setFillForegroundColor(m_eFillForegroundColor.getIndex());
    if (m_eFillPattern != null)
        aCS.setFillPattern(m_eFillPattern.getValue());
    if (m_eBorderTop != null)
        aCS.setBorderTop(m_eBorderTop.getValue());
    if (m_eBorderRight != null)
        aCS.setBorderRight(m_eBorderRight.getValue());
    if (m_eBorderBottom != null)
        aCS.setBorderBottom(m_eBorderBottom.getValue());
    if (m_eBorderLeft != null)
        aCS.setBorderLeft(m_eBorderLeft.getValue());
    if (m_nFontIndex >= 0)
        aCS.setFont(aWB.getFontAt(m_nFontIndex));
}

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

public void generarExcelVentasCantadas() throws IOException {

    cargaResumen();//from  w  w w.j a v  a2 s.  c  o  m

    if (ventasCantadas == null || ventasCantadas.isEmpty()) {
        JsfUtil.addErrorMessage("No hay datos para generar");
    } else {
        Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>();

        for (ResumenVentasCantadas rc : ventasCantadas) {
            List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio());
            if (valueList == null) {
                valueList = new ArrayList<>();
                valueList.add(rc);
                map2.put(rc.getTerritorio(), valueList);
            } else {
                valueList.add(rc);
            }
        }

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Ventas Detalle");

        configAnchoCols(sheet);

        //Fonts
        Font fontSubTitulo = workbook.createFont();
        fontSubTitulo.setFontHeightInPoints((short) 8);
        fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Fonts
        Font fontTotal3 = workbook.createFont();
        fontTotal3.setFontHeightInPoints((short) 8);
        fontTotal3.setColor(HSSFColor.RED.index);
        fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Fonts
        Font fontTerritorioTotal3 = workbook.createFont();
        fontTerritorioTotal3.setFontHeightInPoints((short) 8);
        fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index);
        fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Estilos
        DataFormat format = workbook.createDataFormat();
        CellStyle styleTotal3 = workbook.createCellStyle();

        styleTotal3.setFont(fontTotal3);
        styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setDataFormat(format.getFormat("0.0%"));

        styleTerritorioTotal3 = workbook.createCellStyle();
        styleTerritorioTotal3.setFont(fontTerritorioTotal3);
        styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

        creaCabecera(workbook, sheet);

        String[] aCols20 = null;
        String[] aCols10 = null;
        String[] aColsGrue = null;
        String indicesTotales = "";

        for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) {
            ++indexRow;

            HSSFRow row = sheet.createRow((++indexRow));

            HSSFCell cellTerr = row.createCell(indexCol);
            cellTerr.setCellValue(entry.getKey().toUpperCase());
            cellTerr.setCellStyle(styleTerritorio);

            int indexInicioGrupo = indexRow + 2;

            List<ResumenVentasCantadas> detalles = entry.getValue();

            Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() {

                @Override
                public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) {
                    return o1.getOrden() > o2.getOrden() ? 1 : -1;
                }
            };

            Collections.sort(detalles, comp);

            for (ResumenVentasCantadas rv : detalles) {
                cols20 = "";
                cols10 = "";
                colsGrue = "";

                int indexFilaActual = ++indexRow;
                HSSFRow row1 = sheet.createRow((indexFilaActual));

                HSSFCell cellZona = row1.createCell(indexCol + 0);
                cellZona.setCellValue(rv.getZona());
                cellZona.setCellStyle(styleTitulo9);

                HSSFCell cellVendedor = row1.createCell(indexCol + 1);
                cellVendedor.setCellValue(rv.getVendedor());
                cellVendedor.setCellStyle(styleTitulo9);

                HSSFCell cellBoletas = row1.createCell(indexCol + 2);
                cellBoletas.setCellValue(rv.getCantboletas());
                cellBoletas.setCellStyle(styleCantidad);

                generarDetalles(row1, indexFilaActual, rv);

            }

            int indexFinGrupo = indexRow + 1;

            int indexTotal1 = ++indexRow;
            int indexTotal2 = ++indexRow;
            int indexTotal3 = ++indexRow;

            HSSFRow rowTotal1 = sheet.createRow(indexTotal1);
            HSSFRow rowTotal2 = sheet.createRow((indexTotal2));
            HSSFRow rowTotal3 = sheet.createRow((indexTotal3));

            aCols20 = cols20.split(",");
            aCols10 = cols10.split(",");
            aColsGrue = colsGrue.split(",");

            //TOTAL 1
            generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr);
            //TOTAL 2
            generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr);
            //TOTAL 3
            generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet);
            //++indexRow;

            indicesTotales += (indexFinGrupo + 1) + ",";
        }

        String[] aIndexTotales = indicesTotales.split(",");

        //TOTAL pais
        int indexTotalPais = ++indexRow;
        HSSFRow rowTotalPais = sheet.createRow((indexTotalPais));
        generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10);

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance()
                .getExternalContext().getResponse();

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls");
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
        FacesContext.getCurrentInstance().responseComplete();
        indexRow = 1;
    }

}

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

public void generarExcelVentasCantadasDetalle() throws IOException {

    cargaResumenPorVendedor();/*from w  ww .j a v  a2s.c om*/

    if (ventasCantadas == null || ventasCantadas.isEmpty()) {
        JsfUtil.addErrorMessage("No hay datos para generar");
    } else {
        Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>();

        for (ResumenVentasCantadas rc : ventasCantadas) {
            List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio());
            if (valueList == null) {
                valueList = new ArrayList<>();
                valueList.add(rc);
                map2.put(rc.getTerritorio(), valueList);
            } else {
                valueList.add(rc);
            }
        }

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Ventas Detalle");

        configAnchoCols(sheet);

        //Fonts
        Font fontSubTitulo = workbook.createFont();
        fontSubTitulo.setFontHeightInPoints((short) 8);
        fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Fonts
        Font fontTotal3 = workbook.createFont();
        fontTotal3.setFontHeightInPoints((short) 8);
        fontTotal3.setColor(HSSFColor.RED.index);
        fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Fonts
        Font fontTerritorioTotal3 = workbook.createFont();
        fontTerritorioTotal3.setFontHeightInPoints((short) 8);
        fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index);
        fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //Estilos
        DataFormat format = workbook.createDataFormat();
        CellStyle styleTotal3 = workbook.createCellStyle();

        styleTotal3.setFont(fontTotal3);
        styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        styleTotal3.setDataFormat(format.getFormat("0.0%"));

        styleTerritorioTotal3 = workbook.createCellStyle();
        styleTerritorioTotal3.setFont(fontTerritorioTotal3);
        styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

        creaCabecera(workbook, sheet);

        String[] aCols20 = null;
        String[] aCols10 = null;
        String[] aColsGrue = null;
        String indicesTotales = "";

        for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) {
            ++indexRow;

            HSSFRow row = sheet.createRow((++indexRow));

            HSSFCell cellTerr = row.createCell(indexCol);
            cellTerr.setCellValue(entry.getKey().toUpperCase());
            cellTerr.setCellStyle(styleTerritorio);

            int indexInicioGrupo = indexRow + 2;

            List<ResumenVentasCantadas> detalles = entry.getValue();

            Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() {

                @Override
                public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) {
                    return o1.getOrden() > o2.getOrden() ? 1 : -1;
                }
            };

            Collections.sort(detalles, comp);

            for (ResumenVentasCantadas rv : detalles) {
                cols20 = "";
                cols10 = "";
                colsGrue = "";

                int indexFilaActual = ++indexRow;
                HSSFRow row1 = sheet.createRow((indexFilaActual));

                HSSFCell cellZona = row1.createCell(indexCol + 0);
                cellZona.setCellValue(rv.getZona());
                cellZona.setCellStyle(styleTitulo9);

                HSSFCell cellVendedor = row1.createCell(indexCol + 1);
                cellVendedor.setCellValue(rv.getVendedor());
                cellVendedor.setCellStyle(styleTitulo9);

                HSSFCell cellBoletas = row1.createCell(indexCol + 2);
                cellBoletas.setCellValue(rv.getCantboletas());
                cellBoletas.setCellStyle(styleCantidad);

                generarDetalles(row1, indexFilaActual, rv);

            }

            int indexFinGrupo = indexRow + 1;

            int indexTotal1 = ++indexRow;
            int indexTotal2 = ++indexRow;
            int indexTotal3 = ++indexRow;

            HSSFRow rowTotal1 = sheet.createRow(indexTotal1);
            HSSFRow rowTotal2 = sheet.createRow((indexTotal2));
            HSSFRow rowTotal3 = sheet.createRow((indexTotal3));

            aCols20 = cols20.split(",");
            aCols10 = cols10.split(",");
            aColsGrue = colsGrue.split(",");

            //TOTAL 1
            generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr);
            //TOTAL 2
            generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr);
            //TOTAL 3
            generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet);
            //++indexRow;

            indicesTotales += (indexFinGrupo + 1) + ",";
        }

        String[] aIndexTotales = indicesTotales.split(",");

        //TOTAL pais
        int indexTotalPais = ++indexRow;
        HSSFRow rowTotalPais = sheet.createRow((indexTotalPais));
        generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10);

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance()
                .getExternalContext().getResponse();

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls");
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
        FacesContext.getCurrentInstance().responseComplete();
        indexRow = 1;
    }

}