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

List of usage examples for org.apache.poi.ss.usermodel CellStyle 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:Export.GenericExcel.java

/**
 *
 * @param user// w  ww .  ja v a2  s . c  o m
 * @param nomeDoc
 * @param titleDoc
 * @param rs
 * @param paramFilter
 * @return
 */
public static String createDoc(String user, String nomeDoc, String titleDoc, DataTableControl rs,
        int paramFilter) {

    OutputStream outputStraem = null;
    try {
        i = 0;
        SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy hh'.'mm'.'ss");
        File ff = new File(ConfigDoc.Fontes.getDiretorio() + "/" + user + "/Relatorio");
        ff.mkdirs();
        String Ddata = sdf1.format(new Date());
        ff = new File(ff.getAbsoluteFile() + "/" + nomeDoc + " " + Ddata + ".xls");
        outputStraem = new FileOutputStream(ff);
        reString = "../Documentos/" + user + "/Relatorio/" + nomeDoc + " " + Ddata + ".xls";

        getMap(rs, paramFilter);
        float[] colun = createPerncetage(list, paramFilter);

        Workbook wb = new HSSFWorkbook();

        Font fTitulo = wb.createFont();
        fTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fTitulo.setFontHeightInPoints((short) 14);

        Font fTituloP = wb.createFont();
        fTituloP.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fTituloP.setFontHeightInPoints((short) 12);
        fTituloP.setUnderline(Font.U_SINGLE);

        Font fTituloTabela = wb.createFont();
        fTituloTabela.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fTituloTabela.setFontHeightInPoints((short) 8);

        Font fCorpoTabela = wb.createFont();
        fCorpoTabela.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        fCorpoTabela.setFontHeightInPoints((short) 8.5);

        Font fRodapeTabela = wb.createFont();
        fRodapeTabela.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fRodapeTabela.setFontHeightInPoints((short) 8.5);

        Font fNormal = wb.createFont();
        fNormal.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fNormal.setFontHeightInPoints((short) 8.5);

        CellStyle csTitulo = wb.createCellStyle();
        csTitulo.setFont(fTitulo);
        csTitulo.setAlignment((short) 1);
        csTitulo.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        csTitulo.setBorderBottom((short) 0);
        csTitulo.setBorderTop((short) 0);
        csTitulo.setBorderRight((short) 0);
        csTitulo.setBorderLeft((short) 0);
        csTitulo.setWrapText(true);

        CellStyle csTituloP = wb.createCellStyle();
        csTituloP.setFont(fTituloP);
        csTituloP.setAlignment((short) 1);
        csTituloP.setVerticalAlignment((short) 1);
        csTituloP.setBorderBottom((short) 0);
        csTituloP.setBorderTop((short) 0);
        csTituloP.setBorderRight((short) 0);
        csTituloP.setBorderLeft((short) 0);
        csTituloP.setWrapText(true);

        CellStyle csTituloT = wb.createCellStyle();
        csTituloT.setFont(fTituloP);
        csTituloT.setAlignment((short) 1);
        csTituloT.setVerticalAlignment((short) 1);
        csTituloT.setBorderBottom((short) 0);
        csTituloT.setBorderTop((short) 0);
        csTituloT.setBorderRight((short) 0);
        csTituloT.setBorderLeft((short) 0);
        csTituloT.setWrapText(true);

        CellStyle csTituloTabela = wb.createCellStyle();
        csTituloTabela.setFont(fTituloTabela);
        csTituloTabela.setAlignment(CellStyle.ALIGN_CENTER);
        csTituloTabela.setVerticalAlignment((short) 2);
        csTituloTabela.setBorderBottom((short) 2);
        csTituloTabela.setBorderTop((short) 2);
        csTituloTabela.setBorderRight((short) 2);
        csTituloTabela.setBorderLeft((short) 2);
        csTituloTabela.setWrapText(true);

        CellStyle csTituloTabelaNBorder = wb.createCellStyle();
        csTituloTabelaNBorder.setFont(fTituloTabela);
        csTituloTabelaNBorder.setAlignment(CellStyle.ALIGN_CENTER);
        csTituloTabelaNBorder.setVerticalAlignment((short) 2);
        csTituloTabelaNBorder.setBorderBottom((short) 2);
        csTituloTabelaNBorder.setBorderTop((short) 2);
        csTituloTabelaNBorder.setBorderRight((short) 2);
        csTituloTabelaNBorder.setBorderLeft((short) 2);
        csTituloTabelaNBorder.setWrapText(true);

        CellStyle csCorpoTabela = wb.createCellStyle();
        csCorpoTabela.setFont(fCorpoTabela);
        csCorpoTabela.setAlignment((short) 2);
        csCorpoTabela.setVerticalAlignment((short) 1);
        csCorpoTabela.setBorderBottom((short) 1);
        csCorpoTabela.setBorderTop((short) 1);
        csCorpoTabela.setBorderRight((short) 1);
        csCorpoTabela.setBorderLeft((short) 1);
        csCorpoTabela.setWrapText(true);

        CellStyle csCorpoTabelaR = wb.createCellStyle();
        csCorpoTabelaR.setFont(fCorpoTabela);
        csCorpoTabelaR.setAlignment(CellStyle.ALIGN_RIGHT);
        csCorpoTabelaR.setVerticalAlignment((short) 1);
        csCorpoTabelaR.setBorderBottom((short) 1);
        csCorpoTabelaR.setBorderTop((short) 1);
        csCorpoTabelaR.setBorderRight((short) 1);
        csCorpoTabelaR.setBorderLeft((short) 1);
        csCorpoTabelaR.setWrapText(true);

        CellStyle csCorpoTabelaL = wb.createCellStyle();
        csCorpoTabelaL.setFont(fCorpoTabela);
        csCorpoTabelaL.setAlignment(CellStyle.ALIGN_LEFT);
        csCorpoTabelaL.setVerticalAlignment((short) 1);
        csCorpoTabelaL.setBorderBottom((short) 1);
        csCorpoTabelaL.setBorderTop((short) 1);
        csCorpoTabelaL.setBorderRight((short) 1);
        csCorpoTabelaL.setBorderLeft((short) 1);
        csCorpoTabelaL.setWrapText(true);

        CellStyle csRodapeTabelaL = wb.createCellStyle();
        csRodapeTabelaL.setFont(fRodapeTabela);
        csRodapeTabelaL.setAlignment(CellStyle.ALIGN_CENTER);
        csRodapeTabelaL.setVerticalAlignment((short) 2);
        csRodapeTabelaL.setBorderBottom((short) 2);
        csRodapeTabelaL.setBorderTop((short) 2);
        csRodapeTabelaL.setBorderRight((short) 2);
        csRodapeTabelaL.setBorderLeft((short) 2);
        csRodapeTabelaL.setWrapText(true);

        CellStyle csRodapeTabela = wb.createCellStyle();
        csRodapeTabela.setFont(fRodapeTabela);
        csRodapeTabela.setAlignment(CellStyle.ALIGN_CENTER);
        csRodapeTabela.setVerticalAlignment((short) 2);
        csRodapeTabela.setBorderBottom((short) 2);
        csRodapeTabela.setBorderTop((short) 2);
        csRodapeTabela.setBorderRight((short) 2);
        csRodapeTabela.setBorderLeft((short) 2);
        csRodapeTabela.setWrapText(true);

        CellStyle csRodapeTabelaR = wb.createCellStyle();
        csRodapeTabelaR.setFont(fRodapeTabela);
        csRodapeTabelaR.setAlignment(CellStyle.ALIGN_RIGHT);
        csRodapeTabelaR.setVerticalAlignment((short) 2);
        csRodapeTabelaR.setBorderBottom((short) 2);
        csRodapeTabelaR.setBorderTop((short) 2);
        csRodapeTabelaR.setBorderRight((short) 2);
        csRodapeTabelaR.setBorderLeft((short) 2);
        csRodapeTabelaR.setWrapText(true);

        CellStyle csNomal = wb.createCellStyle();
        csNomal.setFont(fCorpoTabela);
        csNomal.setAlignment((short) 1);
        csNomal.setVerticalAlignment((short) 1);
        csNomal.setBorderBottom((short) 0);
        csNomal.setBorderTop((short) 0);
        csNomal.setBorderRight((short) 0);
        csNomal.setBorderLeft((short) 0);
        csNomal.setWrapText(true);

        Sheet s = wb.createSheet(titleDoc);

        linha = 0;

        Row r = s.createRow(linha);
        Cell c = r.createCell(2);
        createCellM(c, r, s, csTitulo, linha, linha + 3, ConfigDoc.Empresa.NOME, 1, 22);
        linha += 4;

        r = s.createRow(linha);
        createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.ENDERECO, 1, 22);
        linha++;

        r = s.createRow(linha);
        createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.CAIXAPOSTAL, 1, 22);
        linha++;

        r = s.createRow(linha);
        createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.TELEFAX + " " + ConfigDoc.Empresa.EMAIL,
                1, 22);
        linha++;

        r = s.createRow(linha);
        createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.SOCIEDADE, 1, 22);
        linha += 3;

        r = s.createRow(linha);
        SimpleDateFormat format = new SimpleDateFormat("dd 'de' MMMM 'de' yyyy", new Locale("pt", "BR"));
        createCellM(c, r, s, csTituloT, linha, linha,
                titleDoc.toUpperCase() + ((dF != null && dI != null)
                        ? (" de " + format.format(dI) + "  " + format.format(dF)).toUpperCase()
                        : ""),
                1, 22);
        linha += 2;
        if (paramFilter < 0) {
            for (Object[] emap : list) {
                k = 0;
                r = s.createRow(linha);
                for (int j = 0; j < emap.length; j++) {
                    if (j != paramFilterOculta) {
                        if (i == 0) {
                            lista_titulo_table = emap;
                            csCorpoTabela.setFillBackgroundColor(HSSFColor.BLUE.index);
                            createCell(c, r, s, csTituloTabela, linha, linha, toString(emap[j]), k + 1,
                                    toInt(colun[k]));
                            k++;
                        } else {
                            csCorpoTabelaL.setFillBackgroundColor(
                                    ((i % 2) == 0) ? HSSFColor.WHITE.index : HSSFColor.GREY_25_PERCENT.index);
                            createCell(c, r, s,
                                    ((alignment.containsKey(j))
                                            ? ((alignment.get(j) == Alignment.RIGHT) ? csCorpoTabelaR
                                                    : ((alignment.get(j) == Alignment.CENTER) ? csCorpoTabela
                                                            : csCorpoTabelaL))
                                            : csCorpoTabelaL),
                                    linha, linha, toString(emap[j]), k + 1, toInt(colun[k]));
                            k++;
                        }
                    }
                }
                i++;
                linha++;
            }
            for (Map.Entry<String, ArrayList<Object[]>> entrySet : mapTotal.entrySet()) {
                for (Object[] emapT : entrySet.getValue()) {
                    k = 0;
                    r = s.createRow(linha);
                    for (int j = 0; j < emapT.length; j++) {
                        if (j != paramFilterOculta) {
                            csCorpoTabelaL.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
                            createCell(c, r, s,
                                    ((alignment.containsKey(j))
                                            ? ((alignment.get(j) == Alignment.RIGHT) ? csRodapeTabelaR
                                                    : ((alignment.get(j) == Alignment.CENTER) ? csRodapeTabelaR
                                                            : csRodapeTabelaL))
                                            : csRodapeTabelaL),
                                    linha, linha, ((j == 0) ? "TOTAL" : toString(emapT[j])), k + 1,
                                    toInt(colun[k]));
                            k++;
                        }
                    }
                }
            }
        } else {
            int t = 0;
            for (Map.Entry<String, ArrayList<Object[]>> lista : map.entrySet()) {
                r = s.createRow(linha);
                csTituloTabela.setFillBackgroundColor(HSSFColor.WHITE.index);
                createCellM(c, r, s, csTituloTabelaNBorder, linha, linha, toString(lista.getKey()), 1, 7);
                linha += 2;
                for (Object[] emap : lista.getValue()) {
                    k = 0;
                    r = s.createRow(linha);
                    for (int j = 0; j < emap.length; j++) {
                        if (j != paramFilterOculta) {
                            if (i == 0) {
                                lista_titulo_table = emap;
                                csTituloTabela.setFillBackgroundColor(HSSFColor.BLUE.index);
                                createCell(c, r, s, csTituloTabela, linha, linha, toString(emap[j]), k + 1,
                                        toInt(colun[k]));
                                k++;
                            } else {
                                csCorpoTabelaL.setFillBackgroundColor(((i % 2) == 0) ? HSSFColor.WHITE.index
                                        : HSSFColor.GREY_25_PERCENT.index);
                                createCell(c, r, s,
                                        ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT)
                                                ? csCorpoTabelaR
                                                : ((alignment.get(j) == Alignment.CENTER) ? csCorpoTabela
                                                        : csCorpoTabelaL))
                                                : csCorpoTabelaL),
                                        linha, linha, toString(emap[j]), k + 1, toInt(colun[k]));
                                k++;
                            }
                        }

                    }
                    i++;
                    linha++;
                }

                if (mapTotal.containsKey(lista.getKey())) {
                    for (Object[] emapT : mapTotal.get(lista.getKey())) {
                        k = 0;
                        r = s.createRow(linha);
                        for (int j = 0; j < emapT.length; j++) {
                            if (j != paramFilterOculta) {
                                createCell(c, r, s,
                                        ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT)
                                                ? csRodapeTabelaR
                                                : ((alignment.get(j) == Alignment.CENTER) ? csRodapeTabela
                                                        : csRodapeTabelaL))
                                                : csRodapeTabelaL),
                                        linha, linha, ((j == 0) ? "TOTAL" : toString(emapT[j])), k + 1,
                                        toInt(colun[k]));
                                k++;
                            }
                        }
                    }
                }
                t++;
                i = 0;
                linha += 3;
                if (t == map.size() && paramFilter > -1) {
                    k = 0;
                    r = s.createRow(linha);
                    for (int j = 0; j < lista_titulo_table.length; j++) {
                        if (j != paramFilterOculta) {
                            csTituloTabela.setFillBackgroundColor(HSSFColor.BLUE.index);
                            createCell(c, r, s, csTituloTabela, linha, linha, toString(lista_titulo_table[j]),
                                    k + 1, toInt(colun[k]));
                            k++;
                        }
                    }
                    linha++;
                    Double[] total_total = new Double[lista_titulo_table.length];
                    for (Map.Entry<String, ArrayList<Object[]>> lista_for_total : map.entrySet()) {
                        String key = lista_for_total.getKey();
                        for (Object[] emapT : mapTotal.get(key)) {
                            k = 0;
                            r = s.createRow(linha);
                            for (int j = 0; j < emapT.length; j++) {
                                if (j != paramFilterOculta) {
                                    if (Moeda.unFormat(toString(emapT[j]).replaceAll(" ", "")
                                            .replaceAll(",", ".").replaceAll("STD", "")) != -1) {
                                        Double v = ((total_total[j] == null) ? 0.0 : total_total[j]);
                                        total_total[j] = Moeda.unFormat(toString(emapT[j]).replaceAll(" ", "")
                                                .replaceAll(",", ".").replaceAll("STD", "")) + v;
                                    }
                                    createCell(c, r, s,
                                            ((alignment.containsKey(j)) ? ((alignment.get(j) == Alignment.RIGHT)
                                                    ? csRodapeTabelaR
                                                    : ((alignment.get(j) == Alignment.CENTER) ? csRodapeTabela
                                                            : csRodapeTabelaL))
                                                    : csRodapeTabelaL),
                                            linha, linha,
                                            ((j == 0) ? "TOTAL " + key.toUpperCase() : toString(emapT[j])),
                                            k + 1, toInt(colun[k]));
                                    k++;
                                }
                            }
                        }
                        linha++;
                    }
                    k = 0;
                    r = s.createRow(linha);
                    for (int j = 0; j < total_total.length; j++) {
                        if (j != paramFilterOculta) {
                            csTituloTabela.setFillBackgroundColor(HSSFColor.BLUE.index);
                            createCell(c, r, s,
                                    ((alignment.containsKey(j))
                                            ? ((alignment.get(j) == Alignment.RIGHT) ? csRodapeTabelaR
                                                    : ((alignment.get(j) == Alignment.CENTER) ? csRodapeTabela
                                                            : csRodapeTabelaL))
                                            : csRodapeTabelaL),
                                    linha, linha,
                                    (total_total[j] != null) ? Moeda.format_are(total_total[j]) : " ", k + 1,
                                    toInt(colun[k]));
                            k++;
                        }
                    }
                }
            }

        }

        try (FileOutputStream out = new FileOutputStream(ff)) {
            wb.write(out);
        } catch (IOException ex) {
            Logger.getLogger(GenericExcel.class.getName()).log(Level.SEVERE, null, ex);
        }

        RequestContext.getCurrentInstance().execute("openAllDocument('" + reString + "')");
        no = false;
        nomeNo = "";
        dI = null;
        dF = null;
        paramFilterOculta = -1;
        removeItem = new int[] {};
        renameItem = new HashMap<>();
        alignment = new HashMap<>();
        valoresTotal = new String[] {};
        arrValoresTotal = new int[] {};
        return reString;
    } catch (FileNotFoundException ex) {
        Logger.getLogger(GenericExcel.class.getName()).log(Level.SEVERE, null, ex);
        return reString;
    }
}

From source file:Export.ListaVeiculo.java

public static void criarDocExcel(List<Veiculo> ls, String user) {
    try {/* w  w  w .  j  a va  2 s  .c o  m*/

        SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy hh'.'mm'.'ss");
        File ff = new File(ConfigDoc.Fontes.getDiretorio() + "/" + user + "/Relatorio");
        ff.mkdirs();
        String Ddata = sdf1.format(new Date());
        ff = new File(ff.getAbsoluteFile() + "/" + "Lista de Veiculos" + " " + Ddata + ".xls");
        FileOutputStream outputStraem = new FileOutputStream(ff);
        String reString = "../Documentos/" + user + "/Relatorio/" + "Lista de Veiculos" + " " + Ddata + ".xls";

        Workbook wb = new HSSFWorkbook();

        org.apache.poi.ss.usermodel.Font fTitulo = wb.createFont();
        fTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        fTitulo.setFontHeightInPoints((short) 22);

        org.apache.poi.ss.usermodel.Font fTituloP = wb.createFont();
        fTituloP.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        fTituloP.setFontHeightInPoints((short) 15);
        //            fTituloP.setStrikeout(true);
        fTituloP.setUnderline(org.apache.poi.ss.usermodel.Font.U_SINGLE);

        org.apache.poi.ss.usermodel.Font fTituloTabela = wb.createFont();
        fTituloTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        fTituloTabela.setFontHeightInPoints((short) 11);

        org.apache.poi.ss.usermodel.Font fCorpoTabela = wb.createFont();
        fCorpoTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_NORMAL);
        fCorpoTabela.setFontHeightInPoints((short) 11.5);

        org.apache.poi.ss.usermodel.Font fRodapeTabela = wb.createFont();
        fRodapeTabela.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        fRodapeTabela.setFontHeightInPoints((short) 11.5);

        org.apache.poi.ss.usermodel.Font fNormal = wb.createFont();
        fNormal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        fNormal.setFontHeightInPoints((short) 11);

        CellStyle csTitulo = wb.createCellStyle();
        csTitulo.setFont(fTitulo);
        csTitulo.setAlignment((short) 1);
        csTitulo.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        //            csTitulo.setWrapText(true);
        csTitulo.setBorderBottom((short) 0);
        csTitulo.setBorderTop((short) 0);
        csTitulo.setBorderRight((short) 0);
        csTitulo.setBorderLeft((short) 0);
        //            csTitulo.setWrapText(true);

        CellStyle csTituloP = wb.createCellStyle();
        csTituloP.setFont(fTituloP);
        csTituloP.setAlignment((short) 1);
        csTituloP.setVerticalAlignment((short) 1);
        //            csTituloP.setWrapText(true);
        csTituloP.setBorderBottom((short) 0);
        csTituloP.setBorderTop((short) 0);
        csTituloP.setBorderRight((short) 0);
        csTituloP.setBorderLeft((short) 0);
        //            csTituloP.setWrapText(true);

        CellStyle csTituloT = wb.createCellStyle();
        csTituloT.setFont(fTituloP);
        csTituloT.setAlignment((short) 1);
        csTituloT.setVerticalAlignment((short) 1);
        //            csTituloT.setWrapText(true);
        csTituloT.setBorderBottom((short) 0);
        csTituloT.setBorderTop((short) 0);
        csTituloT.setBorderRight((short) 0);
        csTituloT.setBorderLeft((short) 0);
        //            csTituloT.setWrapText(true);

        CellStyle csTituloTabela = wb.createCellStyle();
        csTituloTabela.setFont(fTituloTabela);
        csTituloTabela.setAlignment(CellStyle.ALIGN_CENTER);
        csTituloTabela.setVerticalAlignment((short) 2);
        csTituloTabela.setBorderBottom((short) 2);
        csTituloTabela.setBorderTop((short) 2);
        csTituloTabela.setBorderRight((short) 2);
        csTituloTabela.setBorderLeft((short) 2);
        //            csTituloTabela.setWrapText(true);

        CellStyle csCorpoTabela = wb.createCellStyle();
        csCorpoTabela.setFont(fCorpoTabela);
        csCorpoTabela.setAlignment((short) 2);
        csCorpoTabela.setVerticalAlignment((short) 1);
        csCorpoTabela.setBorderBottom((short) 1);
        csCorpoTabela.setBorderTop((short) 1);
        csCorpoTabela.setBorderRight((short) 1);
        csCorpoTabela.setBorderLeft((short) 1);
        //            csCorpoTabela.setWrapText(true);

        CellStyle csCorpoTabelaR = wb.createCellStyle();
        csCorpoTabelaR.setFont(fCorpoTabela);
        csCorpoTabelaR.setAlignment(CellStyle.ALIGN_RIGHT);
        csCorpoTabelaR.setVerticalAlignment((short) 1);
        csCorpoTabelaR.setBorderBottom((short) 1);
        csCorpoTabelaR.setBorderTop((short) 1);
        csCorpoTabelaR.setBorderRight((short) 1);
        csCorpoTabelaR.setBorderLeft((short) 1);
        //            csCorpoTabelaR.setWrapText(true);

        CellStyle csCorpoTabelaL = wb.createCellStyle();
        csCorpoTabelaL.setFont(fCorpoTabela);
        csCorpoTabelaL.setAlignment(CellStyle.ALIGN_LEFT);
        csCorpoTabelaL.setVerticalAlignment((short) 1);
        csCorpoTabelaL.setBorderBottom((short) 1);
        csCorpoTabelaL.setBorderTop((short) 1);
        csCorpoTabelaL.setBorderRight((short) 1);
        csCorpoTabelaL.setBorderLeft((short) 1);
        //            csCorpoTabelaL.setWrapText(true);

        CellStyle csRodapeTabela = wb.createCellStyle();
        csRodapeTabela.setFont(fRodapeTabela);
        csRodapeTabela.setAlignment((short) 1);
        csRodapeTabela.setVerticalAlignment((short) 2);
        csRodapeTabela.setBorderBottom((short) 2);
        csRodapeTabela.setBorderTop((short) 2);
        csRodapeTabela.setBorderRight((short) 2);
        csRodapeTabela.setBorderLeft((short) 2);
        //            csRodapeTabela.setWrapText(true);

        CellStyle csRodapeTabelaR = wb.createCellStyle();
        csRodapeTabelaR.setFont(fRodapeTabela);
        csRodapeTabelaR.setAlignment(CellStyle.ALIGN_RIGHT);
        csRodapeTabelaR.setVerticalAlignment((short) 2);
        csRodapeTabelaR.setBorderBottom((short) 2);
        csRodapeTabelaR.setBorderTop((short) 2);
        csRodapeTabelaR.setBorderRight((short) 2);
        csRodapeTabelaR.setBorderLeft((short) 2);
        //            csRodapeTabelaR.setWrapText(true);

        CellStyle csNomal = wb.createCellStyle();
        csNomal.setFont(fCorpoTabela);
        csNomal.setAlignment((short) 1);
        csNomal.setVerticalAlignment((short) 1);
        csNomal.setBorderBottom((short) 0);
        csNomal.setBorderTop((short) 0);
        csNomal.setBorderRight((short) 0);
        csNomal.setBorderLeft((short) 0);
        //            csNomal.setWrapText(true);

        Sheet s = wb.createSheet("Lista de Veiculos");

        short linha = 0;

        Row r = s.createRow(linha);
        Cell c = r.createCell(2);
        CreateCellM(c, r, s, csTitulo, linha, linha + 3, ConfigDoc.Empresa.NOME, 1, 22);
        linha += 4;

        r = s.createRow(linha);
        CreateCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.ENDERECO, 1, 22);
        linha++;

        r = s.createRow(linha);
        CreateCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.CAIXAPOSTAL, 1, 22);
        linha++;

        r = s.createRow(linha);
        CreateCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.TELEFAX + " " + ConfigDoc.Empresa.EMAIL,
                1, 22);
        linha++;

        r = s.createRow(linha);
        CreateCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.SOCIEDADE, 1, 22);
        linha += 3;

        r = s.createRow(linha);

        CreateCellM(c, r, s, csTituloT, linha, linha, "Lista de Veiculos".toUpperCase(), 1, 22);
        linha += 2;

        csCorpoTabela.setFillBackgroundColor(HSSFColor.BLUE.index);
        r = s.createRow(linha);
        CreateCell(c, r, s, csTituloTabela, linha, linha, getList(0), 1, 8);
        CreateCell(c, r, s, csTituloTabela, linha, linha, getList(1), 2, 18);
        CreateCell(c, r, s, csTituloTabela, linha, linha, getList(2), 3, 18);
        CreateCell(c, r, s, csTituloTabela, linha, linha, getList(3), 4, 18);
        CreateCell(c, r, s, csTituloTabela, linha, linha, getList(4), 5, 18);
        CreateCell(c, r, s, csTituloTabela, linha, linha, getList(5), 6, 10);
        CreateCell(c, r, s, csTituloTabela, linha, linha, getList(6), 7, 10);
        CreateCell(c, r, s, csTituloTabela, linha, linha, getList(7), 8, 8);
        linha++;

        for (int i = 0; i < ls.size(); i++) {
            r = s.createRow(linha);
            csCorpoTabelaL.setFillBackgroundColor(
                    ((i % 2) == 0) ? HSSFColor.WHITE.index : HSSFColor.GREY_25_PERCENT.index);
            CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getNumeroMatricula(), 1, 8);
            CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getMarca(), 2, 18);
            CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getModelo(), 3, 18);
            CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getNumMotor(), 4, 18);
            CreateCell(c, r, s, csCorpoTabelaL, linha, linha, ls.get(i).getChassi(), 5, 18);
            CreateCell(c, r, s, csCorpoTabelaL, linha, linha,
                    (ls.get(i).getAnoFabrico() == null || ls.get(i).getAnoFabrico().equals("")) ? ""
                            : Integer.valueOf(ls.get(i).getAnoFabrico()),
                    6, 10);
            CreateCell(c, r, s, csCorpoTabelaL, linha, linha,
                    (ls.get(i).getAnoCompra() == null || ls.get(i).getAnoCompra().equals("")) ? ""
                            : Integer.valueOf(ls.get(i).getAnoCompra()),
                    7, 10);
            CreateCell(c, r, s, csCorpoTabelaL, linha, linha, Integer.valueOf(ls.get(i).getCapacidade()), 8, 8);

            linha++;
        }

        try (FileOutputStream out = new FileOutputStream(ff)) {
            wb.write(out);
        } catch (IOException ex) {
            Logger.getLogger(GenericExcel.class.getName()).log(Level.SEVERE, null, ex);
        }

        RequestContext.getCurrentInstance().execute("openAllDocument('" + reString + "')");

    } catch (IOException ex) {
        Logger.getLogger(GenericExcel.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:exportex.ExportEx.java

public static void cellBorderBlack(CellStyle style) {

    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

}

From source file:FILING.cboreport.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from w ww.  j  a  va2s  . co  m*/
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    response.setContentType("text/html;charset=UTF-8");
    //        PrintWriter out = response.getWriter();
    try {
        dbConn conn = new dbConn("1");
        String District[];
        String Year = "";
        District = request.getParameterValues("District");
        Year = request.getParameter("Year");
        String FirstName = "";
        String MiddleName = "";
        String Surname = "";
        String ovcid1 = "";
        String ovcid2 = "";
        int value0 = 0;
        int value1 = 0;
        String Districtid = "";
        String cboid = "";
        String doc = "";
        String docname = "";
        String cboname = "";
        String districtname = "";
        String distval = "";
        int activeOVC = 0;
        int activeHH = 0;
        float activeovc = 0;
        float activehh = 0;
        float percent = 0;
        //             ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet shet1 = wb.createSheet();

        //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

        // style header with font color yello 

        HSSFFont font_header = wb.createFont();
        font_header.setFontHeightInPoints((short) 10);
        font_header.setFontName("Arial Black");
        font_header.setItalic(true);
        font_header.setBoldweight((short) 05);
        font_header.setColor(HSSFColor.BLACK.index);
        CellStyle style_header = wb.createCellStyle();
        style_header.setFont(font_header);
        style_header.setWrapText(true);
        style_header.setFillForegroundColor(HSSFColor.YELLOW.index);
        style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style_header.setAlignment(CellStyle.ALIGN_CENTER);
        style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // stylex with font color blue  and backgound grey
        HSSFCellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        HSSFFont fontx = wb.createFont();
        fontx.setColor(HSSFColor.DARK_BLUE.index);
        stylex.setFont(fontx);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex.setAlignment(CellStyle.ALIGN_CENTER);

        // gold bg color -style
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("Cambria");
        font.setItalic(true);
        font.setBoldweight((short) 02);
        font.setColor(HSSFColor.BLACK.index);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setWrapText(true);
        style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        // for border with no font color
        CellStyle style_border = wb.createCellStyle();
        style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        HSSFFont font1 = wb.createFont();
        font1.setFontHeightInPoints((short) 18);
        font1.setFontName("Cambria");
        font1.setBoldweight((short) 7);
        font1.setColor(HSSFColor.BLACK.index);

        CellStyle style_border1 = wb.createCellStyle();
        style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style_border1.setFont(font);
        style_border1.setAlignment(CellStyle.ALIGN_CENTER);
        style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT);

        HSSFRow rw1 = shet1.createRow(1);
        rw1.setHeightInPoints(30);
        for (int y = 0; y < 11; ++y) {
            HSSFCell cell = rw1.createCell(y);
            cell.setCellStyle(stylex);

            if (y == 0) {
                cell.setCellValue("CBO FILING TRACKER REPORT" + "( " + Year + ")");

            }
        }
        shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));

        shet1.setColumnWidth(0, 4500);
        shet1.setColumnWidth(1, 8500);
        shet1.setColumnWidth(2, 5000);
        shet1.setColumnWidth(3, 5000);
        shet1.setColumnWidth(4, 5000);
        shet1.setColumnWidth(5, 5000);
        shet1.setColumnWidth(6, 5000);
        shet1.setColumnWidth(7, 5000);
        shet1.setColumnWidth(8, 5000);
        shet1.setColumnWidth(9, 5000);
        shet1.setColumnWidth(10, 5000);
        shet1.setColumnWidth(11, 5000);
        shet1.setColumnWidth(12, 5000);
        shet1.setColumnWidth(13, 5000);
        shet1.setColumnWidth(14, 5000);
        shet1.setColumnWidth(15, 5000);
        shet1.setColumnWidth(16, 5000);
        shet1.setColumnWidth(17, 4000);
        shet1.setColumnWidth(18, 4000);
        shet1.setColumnWidth(19, 4200);
        shet1.setColumnWidth(20, 4200);
        shet1.setColumnWidth(21, 4200);
        shet1.setColumnWidth(22, 4200);

        //  Merge the cells
        //  shet1.addMergedRegion(new CellRangeAddress(1,1,1,3));

        HSSFRow rw4 = shet1.createRow(2);
        rw4.setHeightInPoints(40);
        HSSFRow rw6 = shet1.createRow(3);
        rw6.setHeightInPoints(25);
        //    rw4.setRowStyle(style);
        //    
        //  
        //    rw6.setRowStyle(style);
        // 

        // rw4.createCell(1).setCellValue("Number");
        HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13,
                cell14, cell15, cell16;
        // cells fo row 2 
        cell1 = rw4.createCell(0);
        cell2 = rw4.createCell(1);

        cell4 = rw4.createCell(3);

        cell1.setCellValue("DISTRICT");
        cell1.setCellStyle(style);
        cell2.setCellValue("CBO");
        cell2.setCellStyle(style);

        cell6 = rw6.createCell(0);
        cell6.setCellValue("Status");
        cell6.setCellStyle(style);
        cell7 = rw6.createCell(1);
        cell7.setCellValue("");
        cell7.setCellStyle(style);
        shet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
        int rowcount = 3;
        int doccounter = 4;
        int doccounter1 = 4;
        int columcounter = 3;
        String cboids = "";
        int mergecounter = 2;

        cell3 = rw4.createCell(2);
        cell3.setCellValue("ACTIVE OVC");
        cell3.setCellStyle(style);
        cell3 = rw4.createCell(3);
        cell3.setCellValue("ACTIVE HH");
        cell3.setCellStyle(style);

        cell6 = rw6.createCell(2);
        cell6.setCellValue("");
        cell6.setCellStyle(stylex);
        cell6 = rw6.createCell(3);
        cell6.setCellValue("");
        cell6.setCellStyle(stylex);
        ArrayList docidarray = new ArrayList();
        String getdocname = "select * from ovcdocuments WHERE DocumentName!=''";
        conn.rs3 = conn.state3.executeQuery(getdocname);
        while (conn.rs3.next()) {
            docidarray.add(conn.rs3.getString(1));
            System.out.println(conn.rs3.getString(2));
            docname = conn.rs3.getString(2);
            cell3 = rw4.createCell(doccounter1);
            cell3.setCellValue(docname);
            cell3.setCellStyle(style);

            cell6 = rw6.createCell(doccounter1);
            cell6.setCellValue("Available");
            cell6.setCellStyle(stylex);

            //           cell5=rw6.createCell(doccounter1);
            //          cell5.setCellValue("Not Available"); 
            //          cell5.setCellStyle(stylex);
            doccounter1++;

            System.out.println("mergecounter b4" + mergecounter);
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,2,3));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,4,5));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,6,7));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,8,9));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,10,11));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,12,13));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,14,15));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,16,17));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,18,19));
            //      shet1.addMergedRegion(new CellRangeAddress(2,2,20,21));

            System.out.println("mergecounter after" + mergecounter);
            // mergecounter++;

            //shet1.addMergedRegion(new CellRangeAddress(2,2,doccounter1,doccounter1++));
        }

        System.out.println("lll " + doccounter1);
        int rowcounter = 4;
        int counter = 0;
        int countercopy = 4;
        HSSFRow rw5 = null;
        for (int j = 0; j < District.length; j++) {
            String getcboCOUNT = "select * from CBO where DistrictID='" + District[j]
                    + "' Order by DistrictID ";
            conn.rs2 = conn.state2.executeQuery(getcboCOUNT);
            while (conn.rs2.next()) {

                counter++;
                rowcount++;
                rw5 = shet1.createRow(rowcount);
                for (int i = 2; i < doccounter1; i++) {
                    System.out.println("mm " + i);
                    cell5 = rw5.createCell(i);
                    cell5.setCellValue("");
                    cell5.setCellStyle(style_border1);

                }

                cell2 = rw5.createCell(1);
                cell2.setCellValue(conn.rs2.getString(2));
                cell2.setCellStyle(style_border);

                //            cell5=rw5.createCell(rowcount);
                //        cell6=rw5.createCell(rowcounter++);
                //        cell5.setCellValue("x"); 
                //        cell6.setCellValue("y");
                System.out.println("rowcount " + rowcount + " lll   " + counter + " rowcounter " + rowcounter);

                rw5.setHeightInPoints(25);

                //   cell3=rw5.createCell(2);
                String getDistrict = "select * from District where DistrictID='" + District[j]
                        + "' Order by DistrictID ";
                conn.rs4 = conn.state4.executeQuery(getDistrict);
                while (conn.rs4.next()) {
                    districtname = conn.rs4.getString("District");
                    cell1 = rw5.createCell(0);
                    cell1.setCellValue(districtname);
                    cell1.setCellStyle(style_border1);

                }
                doccounter = 4;
                System.out.println("doccount " + doccounter);

                String getdocname1 = "select * from ovcdocuments";
                //          conn.rs3 = conn.state.executeQuery(getdocname1);
                //          while(conn.rs3.next()){
                String getData = "select " + "SUM(CASE WHEN ovcfiling.value='1' THEN 1 ELSE 0 END) AS COUNT1, "
                        + "SUM(CASE WHEN ovcfiling.value='0' THEN 1 ELSE 0 END) AS COUNT0,"
                        + "Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District,"
                        + "count(Clientdetails.OVCID)," + "count(DISTINCT(Clientdetails.HouseHoldheadID))"
                        + " from ovcfiling,Clientdetails  " + "WHERE Clientdetails.District ='" + District[j]
                        + "' and " + "Clientdetails.Cbo='" + conn.rs2.getString(1)
                        + "' and Clientdetails.Exited='1' and  " + " (Year='" + Year
                        + "' OR Year='') AND  Clientdetails.OVCID = ovcfiling.ovcid "
                        + "group by ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District  Order by  ovcfiling.ovcdocid,District ";
                System.out.println("aaaaa  " + getData);
                conn.rs = conn.state.executeQuery(getData);
                while (conn.rs.next()) {

                    value1 = conn.rs.getInt(1);
                    value0 = conn.rs.getInt(2);
                    cboid = conn.rs.getString(3);
                    doc = conn.rs.getString(4);
                    Districtid = conn.rs.getString(5);
                    activeOVC = conn.rs.getInt(6);
                    activeHH = conn.rs.getInt(7);

                    int a = 0;
                    for (int i = 0; i < docidarray.size(); i++) {
                        System.out.println("hh " + docidarray.get(i) + "  " + doc);

                        if (docidarray.get(i).equals(doc)) {
                            System.out.println(doc + "lll" + docidarray.get(i));
                            int cellcount = i + 2;
                            activeovc = activeOVC;
                            activehh = activeHH;

                            if (doc.equals("8")) {
                                percent = value1 / activehh * 100;
                            } else {
                                percent = value1 / activeovc * 100;
                            }
                            cell7 = rw5.createCell(2);
                            cell8 = rw5.createCell(3);

                            cell5 = rw5.createCell(doccounter);
                            //                             cell6=rw5.createCell(doccounter);
                            cell5.setCellValue(Math.round(percent) + "%");
                            //                              cell6.setCellValue(value0); 

                            //FOR ACTICE OVCs
                            cell7.setCellValue(activeOVC);
                            cell7.setCellStyle(style_border);
                            //FOR ACTICE hhs
                            cell8.setCellValue(activeHH);
                            cell8.setCellStyle(style_border);

                            cell5.setCellStyle(style_border);

                            cell6.setCellStyle(style_border);
                            System.out.println("****a  " + doc + " " + doccounter);

                            if (doc.equals("2")) {

                                // System.out.println("****i  "+doc +" "+doccounter);
                                doccounter++;
                                //                                 doccounter++; 
                                System.out.println("am in2");
                                System.out.println("****b  " + doc + " " + doccounter);
                            }
                            if (doc.equals("3")) {

                                doccounter--;
                                //  doccounter--; 
                                //                                doccounter--; 
                                System.out.println("****f  " + doc + " " + doccounter);
                                cell5 = rw5.createCell(doccounter++);
                                cell5.setCellStyle(style_border);
                                cell5.setCellValue(Math.round(percent) + "%");
                                cell6 = rw5.createCell(doccounter);
                                //                                   cell6.setCellStyle(style_border); 
                                //                                cell6.setCellValue(value0);
                                System.out.println("****b  " + doc + " " + doccounter);

                                doccounter--;
                            }
                            //                           else if(!doc.equals("4") && !docidarray.get(i).equals("4")){
                            //                                doccounter++; 
                            //                                 doccounter++; 
                            //                            System.out.println("****c  "+doc +" "+doccounter);
                            //                           }

                            doccounter++;
                        }

                    }

                }
                doccounter = 2;
                //               String getcbo= "select * from CBO where cboid='"+cboid+"'";
                //                      conn.rs2 = conn.state2.executeQuery(getcbo);
                //                      while(conn.rs2.next()){
                //                    
                //                            cell2=rw5.createCell(1);
                //                             cell2.setCellValue(conn.rs2.getString(2));
                //                          
                //                                   System.out.println("rowcount "+rowcount );
                //                                     
                //                      }

                //}

                System.out.println("aaaaaa   " + districtname + "__" + cboname + "____" + docname + "___"
                        + value1 + "__" + value0);

            }
            //}
            // end of while loop
            if (distval.equals("")) {
                //     totalvalue= countercopy+counter;
                System.out.println(countercopy + " counter " + counter + "  " + rowcount);
                distval = districtname;
                System.out.println(countercopy + " nnnn " + counter + " " + rowcount);

                if (counter > countercopy) {

                    shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount, 0, 0));
                    countercopy = rowcount;
                    //cell1.setCellValue(districtname);
                }

                System.out.println(countercopy + " nnn " + counter + " " + rowcount + "  " + distval);
            }
            // cell1.setCellValue(districtname);

            if (!distval.equals(districtname) && !distval.equals("")) {
                distval = districtname;
                //  cell1.setCellValue(districtname);    
                shet1.addMergedRegion(new CellRangeAddress(countercopy + 1, rowcount, 0, 0));
                countercopy = rowcount;

                //

                System.out.println(counter + "@@@@1 " + rowcount + "__" + countercopy);
                System.out.println(distval + "@@@@1 " + districtname);
            }
            System.out.println(counter + "@@@@ " + rowcount);
            //shet1.addMergedRegion(new CellRangeAddress(counter,rowcount,0,0));
            System.out.println(distval + "@@@@ " + districtname);

        } // end of for loop 

        //                      int totalvalue=countercopy+counter;
        // System.out.println(counter+" hhhh "+countercopy);
        //                      if(counter>countercopy){
        //                 shet1.addMergedRegion(new CellRangeAddress(countercopy,totalvalue-1,0,0));
        //               
        //                 countercopy=counter;
        //             }
        // System.out.println("aaaaaannnn   "+districtname+"__"+ cboname +"____"+ doc +"___"+value1 +"__"+value0 ); 

        // write it as an excel attachment
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=CBO_FILING_TRACKER_REPORT_FOR_" + Year + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
    } finally {
        //            out.close();
    }
}

From source file:FILING.childdetailsreport.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from   w ww. j  av  a  2  s .c o m*/
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    response.setContentType("text/html;charset=UTF-8");
    //        PrintWriter out = response.getWriter();
    try {
        dbConn conn = new dbConn("1");
        String District = "";
        District = request.getParameter("District");
        String Year = "";
        Year = request.getParameter("Year");
        String chw = "";
        String FirstName = "";
        String MiddleName = "";
        String Surname = "";
        String ovcid1 = "";
        String ovcid2 = "";
        int value0 = 0;
        int value1 = 0;
        String Districtid = "";
        String cboid = "";
        String doc = "";
        String docname = "";
        String cboname = "";
        String districtname = "";
        String chwval = "";
        String docid = "";
        //             ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet shet1 = wb.createSheet();
        int cbocount = 3;
        //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

        // style header with font color yello 

        HSSFFont font_header = wb.createFont();
        font_header.setFontHeightInPoints((short) 10);
        font_header.setFontName("Arial Black");
        font_header.setItalic(true);
        font_header.setBoldweight((short) 05);
        font_header.setColor(HSSFColor.BLACK.index);
        CellStyle style_header = wb.createCellStyle();
        style_header.setFont(font_header);
        style_header.setWrapText(true);
        style_header.setFillForegroundColor(HSSFColor.YELLOW.index);
        style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style_header.setAlignment(CellStyle.ALIGN_CENTER);
        style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // stylex with font color blue  and backgound grey
        HSSFCellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        HSSFFont fontx = wb.createFont();
        fontx.setColor(HSSFColor.DARK_BLUE.index);
        stylex.setFont(fontx);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex.setAlignment(CellStyle.ALIGN_CENTER);

        // gold bg color -style
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("Cambria");
        font.setItalic(true);
        font.setBoldweight((short) 02);
        font.setColor(HSSFColor.BLACK.index);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setWrapText(true);
        style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        // for border with no font color
        CellStyle style_border = wb.createCellStyle();
        style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        HSSFFont font1 = wb.createFont();
        font1.setFontHeightInPoints((short) 18);
        font1.setFontName("Cambria");
        font1.setBoldweight((short) 7);
        font1.setColor(HSSFColor.BLACK.index);

        CellStyle style_border1 = wb.createCellStyle();
        style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style_border1.setFont(font);
        style_border1.setAlignment(CellStyle.ALIGN_CENTER);
        style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT);

        HSSFRow rw1 = shet1.createRow(1);
        rw1.setHeightInPoints(30);
        for (int y = 0; y < 11; ++y) {
            HSSFCell cell = rw1.createCell(y);
            cell.setCellStyle(stylex);

            if (y == 0) {
                cell.setCellValue("OVC DOCUMENT DETAILS REPORT");

            }
        }
        shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));

        shet1.setColumnWidth(0, 9000);
        shet1.setColumnWidth(1, 9000);
        shet1.setColumnWidth(2, 9000);
        shet1.setColumnWidth(3, 9000);
        shet1.setColumnWidth(4, 5000);
        shet1.setColumnWidth(5, 5000);
        shet1.setColumnWidth(6, 5000);
        shet1.setColumnWidth(7, 5000);
        shet1.setColumnWidth(8, 5000);
        shet1.setColumnWidth(9, 5000);
        shet1.setColumnWidth(10, 5000);
        shet1.setColumnWidth(11, 5000);
        shet1.setColumnWidth(12, 5000);
        shet1.setColumnWidth(13, 5000);

        //  Merge the cells
        //  shet1.addMergedRegion(new CellRangeAddress(1,1,1,3));

        HSSFRow rw4 = shet1.createRow(2);
        rw4.setHeightInPoints(50);
        HSSFRow rw6 = shet1.createRow(3);
        rw6.setHeightInPoints(25);
        //    rw4.setRowStyle(style);
        //    
        //  
        //    rw6.setRowStyle(style);
        // 

        // rw4.createCell(1).setCellValue("Number");
        HSSFCell cell1, cell0, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12,
                cell13, cell14, cell15, cell16;
        // cells fo row 2 
        cell0 = rw4.createCell(0);
        cell1 = rw4.createCell(1);
        cell2 = rw4.createCell(2);

        cell4 = rw4.createCell(3);

        cell0.setCellValue("CBO");
        cell0.setCellStyle(style);

        cell1.setCellValue("CHW");
        cell1.setCellStyle(style);

        cell2.setCellValue("OVCID");
        cell2.setCellStyle(style);

        cell4.setCellValue("FULLNAME");
        cell4.setCellStyle(style);

        ArrayList docidarray = new ArrayList();
        int rowcount = 3;
        int doccounter = 3;
        int doccounter1 = 4;
        int columcounter = 3;
        String cboids = "";
        int mergecounter = 2;
        String getdocname = "select * from ovcdocuments WHERE DocumentName!=''";
        conn.rs3 = conn.state3.executeQuery(getdocname);
        while (conn.rs3.next()) {

            System.out.println(conn.rs3.getString(2));
            docname = conn.rs3.getString(2);
            cell3 = rw4.createCell(doccounter1);
            cell3.setCellValue(docname);
            cell3.setCellStyle(style);
            docidarray.add(conn.rs3.getString(1));
            doccounter1++;
        }

        System.out.println("lll " + doccounter1);
        int rowcounter = 4;
        int counter = 0;
        int countercopy = 3;
        int countercopy1 = 3;
        HSSFRow rw5 = null;

        String getcboCOUNT = "select * from CBO where DistrictID='" + District + "' Order by DistrictID ";
        conn.rs2 = conn.state2.executeQuery(getcboCOUNT);
        while (conn.rs2.next()) {
            counter++;
            System.out.println("bb b " + conn.rs2.getString(2));
            System.out.println("rowcount " + rowcount + " lll   " + counter + " rowcounter " + rowcounter);

            System.out.println("doccount " + doccounter);

            String getData = "select "
                    + "ovcfiling.value,Clientdetails.FirstName, Middlename,SurName,ovcfiling.OVCID"
                    + " , Clientdetails.Cbo,"
                    + "ovcfiling.ovcdocid,Clientdetails.District,Clientdetails.Volunteerid "
                    + " from ovcfiling,Clientdetails  " + "WHERE Clientdetails.District ='" + District
                    + "' and Clientdetails.Cbo='" + conn.rs2.getString(1) + "' "
                    + "and Clientdetails.OVCID = ovcfiling.ovcid and (Year='" + Year + "' OR Year='') "
                    + "group by Clientdetails.FirstName, Middlename,Surname,ovcfiling.OVCID,Clientdetails.Volunteerid,ovcfiling.value,ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District  "
                    + "Order by  Clientdetails.Volunteerid,ovcfiling.OVCID,ovcfiling.ovcdocid";
            System.out.println("aaaaa  " + getData);
            conn.rs = conn.state.executeQuery(getData);
            while (conn.rs.next()) {

                value1 = conn.rs.getInt(1);
                FirstName = conn.rs.getString(2);
                MiddleName = conn.rs.getString(3);
                Surname = conn.rs.getString(4);
                ovcid1 = conn.rs.getString(5);
                //                String getcbo= "select * from CBO where CBOID='"+cboid+"' ";
                //                System.out.println("aaa "+getcbo);
                //                conn.rs_1= conn.state4.executeQuery(getcbo);
                //                while(conn.rs_1.next()){
                ////                   
                //                    cbocount++;
                //                cboname=conn.rs_1.getString("CBO");
                //                System.out.println("aaab  "+cboname);
                //                }
                cboname = conn.rs2.getString(2);
                cboid = conn.rs.getString(6);
                docid = conn.rs.getString(7);
                String getchw = "select * from CHW where CHWID='" + conn.rs.getString(9) + "' ";
                conn.rs3 = conn.state3.executeQuery(getchw);
                while (conn.rs3.next()) {
                    chw = conn.rs3.getString("FirstName") + " " + conn.rs3.getString("MiddleName") + " "
                            + conn.rs3.getString("Surname") + " " + conn.rs3.getString("CBOID");
                }

                // fro holding ovc id 

                // to create rows         
                if (docid.equals("1")) {
                    rw5 = shet1.createRow(rowcount);
                    rw5.setHeightInPoints(25);

                    for (int i = 2; i < doccounter1; i++) {
                        System.out.println("mm " + i);
                        cell5 = rw5.createCell(i);
                        cell5.setCellValue("");
                        cell5.setCellStyle(style_border1);

                    }
                    rowcount++;
                    cbocount++;
                }

                cell1 = rw5.createCell(0);
                cell1.setCellValue(cboname);
                cell1.setCellStyle(style_border1);
                cell1 = rw5.createCell(1);
                cell1.setCellValue(chw);
                cell1.setCellStyle(style_border1);
                cell1 = rw5.createCell(2);
                cell1.setCellValue(ovcid1);
                cell1.setCellStyle(style_border1);
                cell1 = rw5.createCell(3);
                cell1.setCellValue(FirstName + " " + MiddleName + " " + Surname);
                cell1.setCellStyle(style_border1);

                for (int i = 0; i < docidarray.size(); i++) {
                    System.out.println("hh " + docidarray.get(i));
                    if (rw5 == null) {
                        rw5 = shet1.createRow(rowcount);
                    }
                    if (docidarray.get(i).equals(docid)) {
                        int cellcount = i + 4;
                        cell2 = rw5.createCell(cellcount);
                        cell2.setCellValue(value1);
                        cell2.setCellStyle(style_border1);

                        //                    if( docid.equals("3") ) {
                        //                           System.out.println("am in2");
                        //                           System.out.println("****a  "+docid +" "+cellcount);
                        //                           cellcount++; 
                        //                           } 
                        System.out.println("****b  " + docid + " " + cellcount);
                    }
                }
                //                      cell3=rw5.createCell(2);
                //                      cell3.setCellValue(docid);
                //                      cell3.setCellStyle(style_border1);

                System.out.println(
                        FirstName + " " + ovcid1 + "  " + value1 + "___" + doccounter + "_____" + rowcount);
                if (docid.equals("10")) {
                    //     rowcount++;
                    doccounter = 2;
                }
                if (chwval.equals("")) {
                    chwval = chw;

                    System.out.println(countercopy + " nnnna " + rowcount);
                    //                                        shet1.addMergedRegion(new CellRangeAddress(countercopy,rowcount-1,0,0));
                    //                                        countercopy=rowcount; 
                    System.out.println(countercopy + " nnnnb " + rowcount + "  " + chwval);
                }

                if (!chwval.equals(chw) && !chwval.equals("")) {
                    chwval = chw;

                    System.out.println(countercopy + " nnna" + rowcount + "  " + chwval);

                    shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount - 2, 1, 1));
                    countercopy = rowcount - 1;
                    System.out.println(countercopy + " nnnb  " + rowcount + "  " + chwval);

                }
                String cboval = "";
                if (cboval.equals("")) {
                    cboval = cboname;

                    //                shet1.addMergedRegion(new CellRangeAddress(countercopy1,cbocount-1,0,0));
                    //                countercopy1=cbocount;

                }
                if (!cboval.equals(cboname) && !cboval.equals("")) {
                    cboval = cboname;

                    System.out.println(countercopy1 + " nnna" + cbocount + "  " + chwval);

                    shet1.addMergedRegion(new CellRangeAddress(countercopy1, cbocount - 2, 0, 0));
                    countercopy1 = cbocount - 1;
                    System.out.println(countercopy1 + " nnnb  " + rowcount + "  " + cboval);

                }
                //             
                //              if(monthval.equals("")){
                //              monthval= months;
                //              System.out.println("88"+monthval +"___"+months);
                //                System.out.println("88"+monthcopy1);
                //                System.out.println("88"+counter1);
                //                cell31.setCellValue(""+months+ " ("+conn.rs3.getInt(5)+")");
                //                shet2.addMergedRegion(new CellRangeAddress(monthcopy_1,counter1-1,1,1));
                //                monthcopy1=counter1;
                //            
                //            }
                //          if(!monthval.equals("") && !monthval.equals(months)){
                //                 monthval= months;
                //                System.out.println("!!!"+monthval +"___"+months);
                //                System.out.println("!!!"+monthcopy_1);
                //                System.out.println("!!!!"+counter1);
                ////                cell31.setCellValue(months);
                ////              shet1.addMergedRegion(new CellRangeAddress(monthcopy,counter-1,1,1));
                //                monthcopy_1=counter1;
                //               
                //            }

                //       if(rowcount>countercopy)  {      
                //       
                //         shet1.addMergedRegion(new CellRangeAddress(countercopy,rowcount-1,0,0));
                //                              countercopy=rowcount;
                //       }  
            }
            if (rowcount > countercopy) {
                shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount - 1, 1, 1));
                countercopy = rowcount;
            }
            if (cbocount > countercopy1) {
                shet1.addMergedRegion(new CellRangeAddress(countercopy1, cbocount - 1, 0, 0));
                countercopy1 = cbocount;
            }
            //}

            System.out.println("aaaaaa   " + districtname + "__" + cboname + "____" + docname + "___" + value1
                    + "__" + value0);

        }
        //   // end of while loop

        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition", "attachment; filename=CBO_Details_Report_" + Year + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
    } finally {
        //            out.close();
    }
}

From source file:FILING.countyreport.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from  www  . j  a  v  a 2 s  .  c o  m
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    response.setContentType("text/html;charset=UTF-8");
    //        PrintWriter out = response.getWriter();
    try {
        dbConn conn = new dbConn("1");
        String County[];
        County = request.getParameterValues("County");
        String Year = "";
        Year = request.getParameter("Year");
        float percent = 0;
        ArrayList docidarray = new ArrayList();
        System.out.println("countyv " + County);
        String FirstName = "";
        String MiddleName = "";
        String Surname = "";
        String ovcid1 = "";
        String ovcid2 = "";
        int value0 = 0;
        int value1 = 0;
        String Districtid = "";
        String cboid = "";
        String doc = "";
        String docname = "";
        String cboname = "";
        String districtname = "";
        String countyval = "";
        int countercopy = 4;
        int activeOVC = 0;
        int activeHH = 0;
        //             ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet shet1 = wb.createSheet();

        //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

        // style header with font color yello 

        HSSFFont font_header = wb.createFont();
        font_header.setFontHeightInPoints((short) 10);
        font_header.setFontName("Arial Black");
        font_header.setItalic(true);
        font_header.setBoldweight((short) 05);
        font_header.setColor(HSSFColor.BLACK.index);
        CellStyle style_header = wb.createCellStyle();
        style_header.setFont(font_header);
        style_header.setWrapText(true);
        style_header.setFillForegroundColor(HSSFColor.YELLOW.index);
        style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style_header.setAlignment(CellStyle.ALIGN_CENTER);
        style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // stylex with font color blue  and backgound grey
        HSSFCellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        HSSFFont fontx = wb.createFont();
        fontx.setColor(HSSFColor.DARK_BLUE.index);
        stylex.setFont(fontx);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex.setAlignment(CellStyle.ALIGN_CENTER);

        // gold bg color -style
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("Cambria");
        font.setItalic(true);
        font.setBoldweight((short) 02);
        font.setColor(HSSFColor.BLACK.index);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setWrapText(true);
        style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        // for border with no font color
        CellStyle style_border = wb.createCellStyle();
        style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        HSSFFont font1 = wb.createFont();
        font1.setFontHeightInPoints((short) 18);
        font1.setFontName("Cambria");
        font1.setBoldweight((short) 7);
        font1.setColor(HSSFColor.BLACK.index);

        CellStyle style_border1 = wb.createCellStyle();
        style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style_border1.setFont(font);
        style_border1.setAlignment(CellStyle.ALIGN_JUSTIFY);
        style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT);

        HSSFRow rw1 = shet1.createRow(1);
        //cell;

        rw1.setHeightInPoints(30);
        for (int y = 0; y < 11; ++y) {
            HSSFCell cell = rw1.createCell(y);
            cell.setCellStyle(stylex);

            if (y == 0) {
                cell.setCellValue("COUNTY FILING TRACKER REPORT " + Year);

            }
        }
        shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));

        shet1.setColumnWidth(0, 4500);
        shet1.setColumnWidth(1, 8500);
        shet1.setColumnWidth(2, 5000);
        shet1.setColumnWidth(3, 5000);
        shet1.setColumnWidth(4, 5000);
        shet1.setColumnWidth(5, 5000);
        shet1.setColumnWidth(6, 5000);
        shet1.setColumnWidth(7, 5000);
        shet1.setColumnWidth(8, 5000);
        shet1.setColumnWidth(9, 5000);
        shet1.setColumnWidth(10, 5000);
        shet1.setColumnWidth(11, 5000);
        shet1.setColumnWidth(12, 5000);
        shet1.setColumnWidth(13, 5000);
        shet1.setColumnWidth(14, 5000);
        shet1.setColumnWidth(15, 5000);
        shet1.setColumnWidth(16, 5000);
        shet1.setColumnWidth(17, 5000);
        shet1.setColumnWidth(18, 5000);
        shet1.setColumnWidth(19, 5000);
        shet1.setColumnWidth(20, 4500);
        shet1.setColumnWidth(21, 4500);
        shet1.setColumnWidth(22, 3500);

        //  Merge the cells
        //  shet1.addMergedRegion(new CellRangeAddress(1,1,1,3));

        HSSFRow rw4 = shet1.createRow(2);
        rw4.setHeightInPoints(40);
        HSSFRow rw6 = shet1.createRow(3);
        rw6.setHeightInPoints(25);
        //    rw4.setRowStyle(style);
        //    
        //  
        //    rw6.setRowStyle(style);
        // 

        // rw4.createCell(1).setCellValue("Number");
        HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13,
                cell14, cell15, cell16;
        // cells fo row 2 
        cell1 = rw4.createCell(0);
        cell2 = rw4.createCell(1);

        cell4 = rw4.createCell(3);

        cell1.setCellValue("COUNTY");
        cell1.setCellStyle(style);
        cell2.setCellValue("DISTRICT");
        cell2.setCellStyle(style);

        cell6 = rw6.createCell(0);
        cell6.setCellValue("Status");
        cell6.setCellStyle(style);
        cell7 = rw6.createCell(1);
        cell7.setCellValue("");
        cell7.setCellStyle(style_header);
        cell3 = rw4.createCell(2);
        cell3.setCellValue("ACTIVE OVC");
        cell3.setCellStyle(style);
        cell3 = rw4.createCell(3);
        cell3.setCellValue("ACTIVE HH");
        cell3.setCellStyle(style);
        shet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
        int rowcount = 3;
        int doccounter = 4;
        int doccounter1 = 4;
        int columcounter = 3;
        String cboids = "";
        int mergecounter = 2;

        cell6 = rw6.createCell(2);
        cell6.setCellValue("");
        cell6.setCellStyle(stylex);
        cell6 = rw6.createCell(3);
        cell6.setCellValue("");
        cell6.setCellStyle(stylex);
        String getdocname = "select * from ovcdocuments where DocumentName!=''";
        conn.rs3 = conn.state3.executeQuery(getdocname);
        while (conn.rs3.next()) {
            System.out.println("mmm  " + doccounter1);
            System.out.println(conn.rs3.getString(2));
            docname = conn.rs3.getString(2);
            cell3 = rw4.createCell(doccounter1);
            cell3.setCellValue(docname);
            cell3.setCellStyle(style);
            docidarray.add(conn.rs3.getString(1));

            cell6 = rw6.createCell(doccounter1++);
            cell6.setCellValue("Available");
            cell6.setCellStyle(stylex);

            //           cell5=rw6.createCell(doccounter1);
            //          cell5.setCellValue("Not Available"); 
            //          cell5.setCellStyle(stylex);
            //        doccounter1++;

            //         System.out.println("mergecounter b4"+mergecounter);

            // System.out.println("mergecounter after"+mergecounter);  
            // mergecounter++;

            //shet1.addMergedRegion(new CellRangeAddress(2,2,doccounter1,doccounter1++));
        }

        System.out.println("lll " + doccounter1);
        int rowcounter = 4;
        int counter = 0;

        HSSFRow rw5 = null;
        String DistrictID = "";

        for (int j = 0; j < County.length; j++) {
            String getDistrictCounts = "select * from District where Countyid='" + County[j]
                    + "' order by District";
            System.out.println("districtID " + getDistrictCounts);
            conn.rs2 = conn.state2.executeQuery(getDistrictCounts);
            while (conn.rs2.next()) {
                System.out.println("district1" + conn.rs2.getString("District"));
                counter++;
                rowcount++;
                rw5 = shet1.createRow(rowcount);
                for (int i = 2; i < doccounter1; i++) {
                    System.out.println("mm " + i);
                    cell5 = rw5.createCell(i);
                    cell5.setCellValue("");
                    cell5.setCellStyle(style_border1);

                }

                cell2 = rw5.createCell(1);
                cell2.setCellValue(conn.rs2.getString(2));
                cell2.setCellStyle(style_border);

                //            cell5=rw5.createCell(rowcount);
                //        cell6=rw5.createCell(rowcounter++);
                //        cell5.setCellValue("x"); 
                //        cell6.setCellValue("y");
                System.out.println("rowcount " + rowcount + " lll   " + counter + " rowcounter " + rowcounter);

                rw5.setHeightInPoints(25);

                //   cell3=rw5.createCell(2);
                String getDistrict = "select * from County where CountyID='" + County[j] + "' order by County";
                System.out.println("dname " + getDistrict);
                conn.rs4 = conn.state4.executeQuery(getDistrict);
                while (conn.rs4.next()) {
                    districtname = conn.rs4.getString("County");
                    System.out.println("district2" + districtname);
                    cell1 = rw5.createCell(0);
                    cell1.setCellValue(districtname);
                    cell1.setCellStyle(style_border1);

                    // to marge these values 

                }

                System.out.println("doccount " + doccounter);

                doccounter = 4;
                String getData = "select " + "SUM(CASE WHEN ovcfiling.value='1' THEN 1 ELSE 0 END) AS COUNT1, "
                        + "SUM(CASE WHEN ovcfiling.value='0' THEN 1 ELSE 0 END) AS COUNT0,"
                        + "Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District, "
                        + "count(Clientdetails.OVCID)," + "count(DISTINCT(Clientdetails.HouseHoldheadID))"
                        + " from ovcfiling,Clientdetails  " + "WHERE Clientdetails.District ='"
                        + conn.rs2.getString("DistrictID")
                        + "' and Clientdetails.OVCID = ovcfiling.ovcid and Clientdetails.Exited='1' and (Year='"
                        + Year + "' OR Year='') "
                        + "group by ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District order by Clientdetails.District,ovcfiling.ovcdocid";
                System.out.println("aaaaa  " + getData);
                conn.rs = conn.state.executeQuery(getData);
                while (conn.rs.next()) {

                    value1 = conn.rs.getInt(1);
                    value0 = conn.rs.getInt(2);
                    cboid = conn.rs.getString(3);
                    doc = conn.rs.getString(4);
                    Districtid = conn.rs.getString(5);
                    activeOVC = conn.rs.getInt(6);
                    activeHH = conn.rs.getInt(7);
                    System.out.println("district3 " + Districtid);
                    float activeovc = 0;
                    float activehh = 0;
                    for (int i = 0; i < docidarray.size(); i++) {
                        System.out.println("hh " + docidarray.get(i) + "  " + doc);
                        //                      if(rw5==null){
                        //                    rw5=shet1.createRow(rowcount);
                        //                                    }
                        if (docidarray.get(i).equals(doc)) {
                            System.out.println(doc + "lll" + docidarray.get(i));
                            int cellcount = i + 2;
                            //                      cell2=rw5.createCell(cellcount++);
                            //                      cell2.setCellValue(value1);
                            //                      cell2.setCellStyle(style_border1);
                            //                        String getdocname1="select * from ovcdocuments";
                            //              conn.rs3= conn.state3.executeQuery(getdocname1);
                            //              if(conn.rs3.next()){

                            cell7 = rw5.createCell(2);
                            cell8 = rw5.createCell(3);

                            cell5 = rw5.createCell(doccounter);
                            //                             cell6=rw5.createCell(doccounter);
                            activeovc = activeOVC;
                            activehh = activeHH;
                            if (doc.equals("8")) {
                                percent = value1 / activehh * 100;
                            } else {
                                percent = value1 / activeovc * 100;
                            }
                            System.out.println("percenta " + percent + " " + value1 + "  " + activeOVC + " act "
                                    + activeovc);
                            cell5.setCellValue(Math.round(percent) + "%");
                            //                              cell6.setCellValue(value0); 

                            //FOR ACTICE OVCs
                            cell7.setCellValue(activeOVC);
                            cell7.setCellStyle(style_border);
                            //FOR ACTICE hhs
                            cell8.setCellValue(activeHH);
                            cell8.setCellStyle(style_border);

                            cell5.setCellStyle(style_border);

                            cell6.setCellStyle(style_border);
                            System.out.println("****a  " + doc + " " + doccounter);

                            if (doc.equals("2")) {

                                // System.out.println("****i  "+doc +" "+doccounter);
                                doccounter++;
                                //                                 doccounter++; 
                                System.out.println("am in2");
                                System.out.println("****b  " + doc + " " + doccounter);
                            }
                            if (doc.equals("3")) {
                                percent = value1 / activeovc * 100;
                                doccounter--;
                                //  doccounter--; 
                                //                                doccounter--; 
                                System.out.println("****f  " + doc + " " + doccounter);
                                cell5 = rw5.createCell(doccounter++);
                                cell5.setCellStyle(style_border);
                                cell5.setCellValue(percent);
                                cell6 = rw5.createCell(doccounter);
                                //                                   cell6.setCellStyle(style_border); 
                                //                                cell6.setCellValue(value0);
                                System.out.println("****b  " + doc + " " + doccounter);

                                doccounter--;
                            }
                            //                           else if(!doc.equals("4") && !docidarray.get(i).equals("4")){
                            //                                doccounter++; 
                            //                                 doccounter++; 
                            //                            System.out.println("****c  "+doc +" "+doccounter);
                            //                           }

                            doccounter++;
                        }
                    }

                }
                doccounter = 2;
                //               String getcbo= "select * from CBO where cboid='"+cboid+"'";
                //                      conn.rs2 = conn.state2.executeQuery(getcbo);
                //                      while(conn.rs2.next()){
                //                    
                //                            cell2=rw5.createCell(1);
                //                             cell2.setCellValue(conn.rs2.getString(2));
                //                          
                //                                   System.out.println("rowcount "+rowcount );
                //                                     
                //                      }

                //}

                System.out.println("aaaaaa   " + districtname + "__" + cboname + "____" + docname + "___"
                        + value1 + "__" + value0);

                //   shet1.addMergedRegion(new CellRangeAddress(countercopy,counter,0,0));
                //    countercopy=counter;
                //   cell1.setCellValue(districtname);                 

            }

            if (countyval.equals("")) {
                //     totalvalue= countercopy+counter;
                System.out.println(countercopy + " counter " + counter + "  " + rowcount);
                countyval = districtname;
                System.out.println(countercopy + " nnnn " + counter + " " + rowcount);

                //                            if(counter>countercopy){

                shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount, 0, 0));
                countercopy = rowcount;
                //cell1.setCellValue(districtname);
                //                            }

                System.out.println(countercopy + " nnn " + counter + " " + rowcount + "  " + countyval);
            }
            // cell1.setCellValue(districtname);

            if (!countyval.equals(districtname) && !countyval.equals("")) {
                countyval = districtname;
                //  cell1.setCellValue(districtname);    
                shet1.addMergedRegion(new CellRangeAddress(countercopy + 1, rowcount, 0, 0));
                countercopy = rowcount;

                //

                System.out.println(counter + "@@@@1 " + rowcount + "__" + countercopy);
                System.out.println(countyval + "@@@@1 " + districtname);
            }
            System.out.println(counter + "@@@@ " + rowcount);
            //shet1.addMergedRegion(new CellRangeAddress(counter,rowcount,0,0));
            System.out.println(countyval + "@@@@ " + districtname);
        }
        System.out.println(counter + "@@@@2 " + rowcount + " copy ");

        // System.out.println("aaaaaannnn   "+districtname+"__"+ cboname +"____"+ doc +"___"+value1 +"__"+value0 ); 

        // write it as an excel attachment
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=County_Filing_Tracker_Report_" + Year + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
    } finally {
        //            out.close();
    }
}

From source file:FormatStatics.BorderedStyle.java

/**
 * This method alters the workbook object to create a completely bordered
 * cell for use in tables./*from   w  w w.j  a  va 2s  .  c om*/
 * @param wb The current workbook object that will use the bordered style
 * @return A bordered cell style.
 */
public static CellStyle createBorderedStyle(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    return style;
}

From source file:fr.amapj.service.engine.generator.excel.ExcelGeneratorTool.java

License:Open Source License

private void addBorderedStyle(CellStyle style) {
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
}

From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java

License:Apache License

/**
 * Initialisation des styles de cellule/* ww  w  .j  a  va  2 s  .c  o m*/
 */
protected void initStyles() {
    CellStyle defaultStyle = workbook.createCellStyle();
    defaultStyle.setFont(getFont(FONT_NORMAL_NAME));
    setStyleFillForegroundColor(defaultStyle, colorRegistry, HSSFColor.WHITE.index);
    defaultStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    defaultStyle.setBorderBottom(CellStyle.BORDER_THIN);
    setStyleBottomBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX);
    defaultStyle.setBorderLeft(CellStyle.BORDER_THIN);
    setStyleLeftBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX);
    defaultStyle.setBorderRight(CellStyle.BORDER_THIN);
    setStyleRightBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX);
    defaultStyle.setBorderTop(CellStyle.BORDER_THIN);
    setStyleTopBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX);
    defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    defaultStyle.setWrapText(true);
    registerStyle(STYLE_DEFAULT_NAME, defaultStyle);

    CellStyle styleHeader = workbook.createCellStyle();
    styleHeader.setAlignment(CellStyle.ALIGN_CENTER);
    styleHeader.setFont(getFont(FONT_HEADER_NAME));
    setStyleFillForegroundColor(styleHeader, colorRegistry, HEADER_BACKGROUND_COLOR_INDEX);
    styleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleHeader.setBorderBottom(CellStyle.BORDER_THIN);
    setStyleBottomBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX);
    styleHeader.setBorderLeft(CellStyle.BORDER_THIN);
    setStyleLeftBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX);
    styleHeader.setBorderRight(CellStyle.BORDER_THIN);
    setStyleRightBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX);
    styleHeader.setBorderTop(CellStyle.BORDER_THIN);
    setStyleTopBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX);
    styleHeader.setDataFormat((short) 0);
    styleHeader.setWrapText(true);
    registerStyle(STYLE_HEADER_NAME, styleHeader);

    CellStyle styleOdd = cloneStyle(defaultStyle);
    registerStyle(STYLE_STANDARD_NAME + ROW_ODD_NAME, styleOdd);

    CellStyle styleEven = cloneStyle(styleOdd);
    setStyleFillForegroundColor(styleEven, colorRegistry, EVEN_ROW_BACKGROUND_COLOR_INDEX);
    registerStyle(STYLE_STANDARD_NAME + ROW_EVEN_NAME, styleEven);

    // styles pour les nombres entiers
    short integerFormatIndex = dataFormat.getFormat(integerDataFormat);

    CellStyle styleOddInteger = cloneStyle(styleOdd);
    styleOddInteger.setAlignment(CellStyle.ALIGN_RIGHT);
    styleOddInteger.setDataFormat(integerFormatIndex);
    registerStyle(STYLE_INTEGER_NAME + ROW_ODD_NAME, styleOddInteger);

    CellStyle styleEvenInteger = cloneStyle(styleEven);
    styleEvenInteger.setAlignment(CellStyle.ALIGN_RIGHT);
    styleEvenInteger.setDataFormat(integerFormatIndex);
    registerStyle(STYLE_INTEGER_NAME + ROW_EVEN_NAME, styleEvenInteger);

    // styles pour les nombres dcimaux
    short decimalFormatIndex = dataFormat.getFormat(decimalDataFormat);

    CellStyle styleOddDecimal = cloneStyle(styleOdd);
    styleOddDecimal.setAlignment(CellStyle.ALIGN_RIGHT);
    styleOddDecimal.setDataFormat(decimalFormatIndex);
    registerStyle(STYLE_DECIMAL_NAME + ROW_ODD_NAME, styleOddDecimal);

    CellStyle styleEvenDecimal = cloneStyle(styleEven);
    styleEvenDecimal.setAlignment(CellStyle.ALIGN_RIGHT);
    styleEvenDecimal.setDataFormat(decimalFormatIndex);
    registerStyle(STYLE_DECIMAL_NAME + ROW_EVEN_NAME, styleEvenDecimal);

    // styles pour les dates
    short dateFormatIndex = dataFormat.getFormat(dateDataFormat);

    CellStyle styleOddDate = cloneStyle(styleOdd);
    styleOddDate.setDataFormat(dateFormatIndex);
    registerStyle(STYLE_DATE_NAME + ROW_ODD_NAME, styleOddDate);

    CellStyle styleEvenDate = cloneStyle(styleEven);
    styleEvenDate.setDataFormat(dateFormatIndex);
    registerStyle(STYLE_DATE_NAME + ROW_EVEN_NAME, styleEvenDate);

    // styles pour les dates avec heure
    short dateTimeFormatIndex = dataFormat.getFormat(dateTimeDataFormat);

    CellStyle styleOddDateTime = cloneStyle(styleOdd);
    styleOddDateTime.setDataFormat(dateTimeFormatIndex);
    registerStyle(STYLE_DATE_TIME_NAME + ROW_ODD_NAME, styleOddDateTime);

    CellStyle styleEvenDateTime = cloneStyle(styleEven);
    styleEvenDateTime.setDataFormat(dateTimeFormatIndex);
    registerStyle(STYLE_DATE_TIME_NAME + ROW_EVEN_NAME, styleEvenDateTime);

    // styles pour les pourcentages
    short percentFormatIndex = dataFormat.getFormat(percentDataFormat);

    CellStyle styleOddPercent = cloneStyle(styleOdd);
    styleOddPercent.setDataFormat(percentFormatIndex);
    registerStyle(STYLE_PERCENT_NAME + ROW_ODD_NAME, styleOddPercent);

    CellStyle styleEvenPercent = cloneStyle(styleEven);
    styleEvenPercent.setDataFormat(percentFormatIndex);
    registerStyle(STYLE_PERCENT_NAME + ROW_EVEN_NAME, styleEvenPercent);

    short percentRelativeFormatIndex = dataFormat.getFormat(percentRelativeDataFormat);

    CellStyle styleOddPercentRelative = cloneStyle(styleOdd);
    styleOddPercentRelative.setDataFormat(percentRelativeFormatIndex);
    registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_ODD_NAME, styleOddPercentRelative);

    CellStyle styleEvenPercentRelative = cloneStyle(styleEven);
    styleEvenPercentRelative.setDataFormat(percentRelativeFormatIndex);
    registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_EVEN_NAME, styleEvenPercentRelative);

    // styles pour les liens
    CellStyle styleOddLink = cloneStyle(styleOdd);
    styleOddLink.setFont(getFont(FONT_LINK_NAME));
    registerStyle(STYLE_LINK_NAME + ROW_ODD_NAME, styleOddLink);

    CellStyle styleEvenLink = cloneStyle(styleEven);
    styleEvenLink.setFont(getFont(FONT_LINK_NAME));
    registerStyle(STYLE_LINK_NAME + ROW_EVEN_NAME, styleEvenLink);

    // styles pour les tailles de fichiers
    short fileSizeFormatIndex = dataFormat.getFormat(fileSizeDataFormat);

    CellStyle styleOddFileSize = cloneStyle(styleOdd);
    styleOddFileSize.setDataFormat(fileSizeFormatIndex);
    registerStyle(STYLE_FILE_SIZE_NAME + ROW_ODD_NAME, styleOddFileSize);

    CellStyle styleEvenFileSize = cloneStyle(styleEven);
    styleEvenFileSize.setDataFormat(fileSizeFormatIndex);
    registerStyle(STYLE_FILE_SIZE_NAME + ROW_EVEN_NAME, styleEvenFileSize);
}

From source file:GapAnalysis.gapAnalysis.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    //PrintWriter out = response.getWriter();
    try {/*www.j a  va2  s  .  com*/

        ArrayList keyal = new ArrayList();
        ArrayList countyal = new ArrayList();
        ArrayList scountyal = new ArrayList();
        ArrayList facilal = new ArrayList();
        ArrayList yearmonthal = new ArrayList();
        ArrayList monthal = new ArrayList();
        ArrayList sectional = new ArrayList();

        XSSFWorkbook wb;

        String periodname = "";
        String allpath = getServletContext().getRealPath("/Gapanalysis.xlsm");

        System.out.println(allpath);

        XSSFWorkbook workbook;
        String mydrive = allpath.substring(0, 1);
        // wb = new XSSFWorkbook( OPCPackage.open(allpath) );

        Date da = new Date();
        String dat2 = da.toString().replace(" ", "_");
        dat2 = dat2.toString().replace(":", "_");

        String np = mydrive + ":\\APHIAPLUS\\InternalSystem\\Gapanalysis" + dat2 + ".xlsm";
        System.out.println("path:: " + np);
        // String desteepath1 = getServletContext().getRealPath("/Females 15to24.xlsm");
        String sr = getServletContext().getRealPath("/Gapanalysis.xlsm");
        //check if file exists

        //first time , it should create those folders that host the macro file
        File f = new File(np);
        if (!f.exists() && !f.isDirectory()) { /* do something */
            copytemplates ct = new copytemplates();
            ct.transfermacros(sr, np);
            //rem np is the destination file name  

            System.out.println("Copying macro template first time ..");

        } else
        //copy the file alone  
        {
            copytemplates ct = new copytemplates();
            //copy the agebased file only
            ct.copymacros(sr, np);

        }
        String filepth = np;

        File allpathfile = new File(filepth);

        OPCPackage pkg = OPCPackage.open(allpathfile);

        pathtodelete = filepth;
        wb = new XSSFWorkbook(pkg);

        dbConn conn = new dbConn();
        HashMap<String, String> rawdatahashmap = new HashMap<String, String>();

        int year = 0;
        String yearval = "";
        int prevyear = 0;

        String quarter = "";

        String yearmonth = "";
        String startyearmonth = "";
        String endyearmonth = "";

        yearval = request.getParameter("year").toString();

        System.out.println("YEARVAL" + yearval);
        year = Integer.parseInt(yearval);
        prevyear = year - 1;
        quarter = request.getParameter("quarter");
        periodname += yearval + "_";
        if (quarter.equals("1")) {
            startyearmonth = prevyear + "10";
            endyearmonth = prevyear + "12";
            periodname = prevyear + "_(Oct_Dec)";
        } else if (quarter.equals("2")) {
            startyearmonth = year + "01";
            endyearmonth = year + "03";
            periodname = yearval + "_(Jan-Mar)";
        } else if (quarter.equals("3")) {
            startyearmonth = year + "04";
            endyearmonth = year + "06";
            periodname = yearval + "_(Apr_Jun)";
        } else if (quarter.equals("4")) {
            startyearmonth = year + "07";
            endyearmonth = year + "09";
            periodname = yearval + "_(Jul_Sep)";
        }

        int colsmerging = 6;
        String Sections[] = { "ART", "HTC", "PMTCT" };
        String headers[] = { "County", "Sub-County", "Facility", "Year", "Month" };
        String headergsn[] = { "County", "Sub-County", "Facility" };
        //if one wants gaps for one service area
        if (request.getParameterValues("gapsection") != null) {

            Sections = request.getParameterValues("gapsection");

        }
        //This is the loop that well use to create worksheets for each 

        String period = " 1=1 and Annee=" + yearval + " and yearmonth between " + startyearmonth + " and "
                + endyearmonth + " ";
        String gsnperiod = " 1=1  ";

        //______________________________________________________________________________________
        //______________________________________________________________________________________

        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 18);
        font.setFontName("Cambria");
        font.setColor((short) 0000);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font font2 = wb.createFont();
        font2.setFontName("Cambria");
        font2.setColor((short) 0000);
        CellStyle style2 = wb.createCellStyle();
        style2.setFont(font2);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        CellStyle stborder = wb.createCellStyle();
        stborder.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        CellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        CellStyle stylex1 = wb.createCellStyle();
        stylex1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        stylex1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex1.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        CellStyle stylex2 = wb.createCellStyle();
        stylex2.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        stylex2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        CellStyle stylex3 = wb.createCellStyle();
        stylex3.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        stylex3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex3.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex3.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        Font fontx = wb.createFont();
        fontx.setColor(HSSFColor.BLACK.index);
        fontx.setFontName("Cambria");
        stylex.setFont(fontx);
        stylex.setWrapText(true);
        stylex1.setFont(fontx);
        stylex1.setWrapText(true);

        stylex2.setFont(fontx);
        stylex2.setWrapText(true);

        //==================================================

        for (int a = 0; a < Sections.length; a++) {
            int column = 0;
            int Row = 3;

            Sheet shet = wb.createSheet(Sections[a]);

            Row rwx = shet.createRow(2);
            Row rw1 = null;
            Row rw2 = null;
            Row rw = shet.createRow(0);
            rw.setHeightInPoints(25);
            Cell cl0 = rw.createCell(0);
            cl0.setCellValue(Sections[a] + " GAP ANALYSIS");
            cl0.setCellStyle(stylex1);

            //this will depend on the length of the number of elements being checked

            for (int b = 1; b <= colsmerging; b++) {
                Cell clx = rw.createCell(b);
                clx.setCellValue("");
                clx.setCellStyle(stylex);
            }

            //now go to the database and do a query for each section
            int determinant = 2;
            String getqueries = " Select * from gap_analysis where active=1 and section='" + Sections[a] + "' ";

            conn.rs = conn.st.executeQuery(getqueries);
            while (conn.rs.next()) {

                //if an excel sheet exists, then get the row number 1

                if (shet.getRow(1) != null) {
                    rw1 = shet.getRow(1);
                } else {
                    rw1 = shet.createRow(1);
                    rw1.setHeightInPoints(25);
                }

                //print blanks before printing real header
                //for gsns, we only print three columns and no period
                if (conn.rs.getString("id").equals("1")) {

                    for (int p = 0; p < headergsn.length; p++) {
                        Cell cl2 = rw1.createCell(column + p);
                        cl2.setCellValue("");
                        cl2.setCellStyle(stylex);
                        shet.setColumnWidth(column + p, 5000);
                    }
                } else {

                    for (int p = 0; p < headers.length; p++) {
                        Cell cl2 = rw1.createCell(column + p);
                        cl2.setCellValue("");
                        cl2.setCellStyle(stylex);
                        shet.setColumnWidth(column + p, 5000);
                    }

                }
                determinant++;
                if (determinant % 2 == 0) {

                    Cell cl1 = rw1.createCell(column);
                    cl1.setCellValue(conn.rs.getString("rule"));
                    cl1.setCellStyle(stylex3);

                } else {

                    Cell cl1 = rw1.createCell(column);
                    cl1.setCellValue(conn.rs.getString("rule"));
                    cl1.setCellStyle(stylex2);

                }

                //Create the column header  

                if (shet.getRow(2) != null) {
                    rw2 = shet.getRow(2);
                } else {
                    rw2 = shet.createRow(2);
                    rw2.setHeightInPoints(25);
                }
                if (conn.rs.getString("id").equals("1")) {

                    for (int p = 0; p < headergsn.length; p++) {
                        Cell cl2 = rw2.createCell(column + p);
                        cl2.setCellValue(headergsn[p]);
                        cl2.setCellStyle(stylex);
                    }
                } else {

                    for (int p = 0; p < headers.length; p++) {
                        Cell cl2 = rw2.createCell(column + p);
                        cl2.setCellValue(headers[p]);
                        cl2.setCellStyle(stylex);
                    }

                }

                String currentqry = conn.rs.getString("query");
                //process each query as you 
                //pass the necessary period parameters from the interface
                //rem each query ends with a 'and'
                if (conn.rs.getString("id").equals("1")) {
                    currentqry += gsnperiod;
                } else {

                    currentqry += period + " and subpartnera." + Sections[a] + "= 1 ";

                }

                System.out.println("" + currentqry);
                Row = 3;
                conn.rs1 = conn.st1.executeQuery(currentqry);

                while (conn.rs1.next()) {

                    if (shet.getRow(Row) != null) {
                        rwx = shet.getRow(Row);
                    } else {
                        rwx = shet.createRow(Row);
                        rwx.setHeightInPoints(25);
                    }
                    Cell cly = rwx.createCell(column);
                    cly.setCellValue(conn.rs1.getString("County"));
                    cly.setCellStyle(style2);

                    Cell cly2 = rwx.createCell(column + 1);
                    cly2.setCellValue(conn.rs1.getString("DistrictNom"));
                    cly2.setCellStyle(style2);//gsn sites do not have a yearmonth

                    Cell cly1 = rwx.createCell(column + 2);
                    cly1.setCellValue(conn.rs1.getString("SubPartnerNom"));
                    cly1.setCellStyle(style2);

                    //if the current list is not inclusive of GSNs

                    if (!conn.rs.getString(1).equals("1")) {

                        Cell cly3 = rwx.createCell(column + 3);
                        cly3.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(0, 4)));
                        cly3.setCellStyle(style2);

                        //the month section

                        Cell cly3x = rwx.createCell(column + 4);
                        cly3x.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(4)));
                        cly3x.setCellStyle(style2);

                        //my key is a 
                        String mykey = Sections[a] + conn.rs1.getString("SubPartnerNom") + "_"
                                + conn.rs1.getString("yearmonth") + "_";
                        //add all the facilities at this point
                        //ignore the sites in ART since they are static
                        if (!keyal.contains(mykey)) {
                            keyal.add(mykey);
                            countyal.add(conn.rs1.getString("County"));
                            scountyal.add(conn.rs1.getString("DistrictNom"));
                            facilal.add(conn.rs1.getString("SubPartnerNom"));
                            sectional.add(Sections[a]);
                            yearmonthal.add(conn.rs1.getString("yearmonth"));
                            monthal.add(conn.rs1.getString("yearmonth").substring(4));

                        }

                    }

                    Row++;

                }

                if (conn.rs.getString(1).equals("1")) {
                    column += 3;
                } else {
                    column += 5;
                }
                if (conn.rs.getString("id").equals("1")) {
                    shet.addMergedRegion(new CellRangeAddress(1, 1, 0, column - 1));
                } else {
                    shet.addMergedRegion(new CellRangeAddress(1, 1, column - 5, column - 1));
                }

            } //end of all queries per section

            shet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1));

        } // end of sheets loop   

        //create a new sheet

        //county   subcounty   facility   yearmonth   section

        Sheet shet = wb.getSheet("Sheet1");

        Row rw = shet.createRow(0);
        Cell cl0 = rw.createCell(0);
        cl0.setCellValue("county");
        cl0.setCellStyle(stylex1);

        Cell cl1 = rw.createCell(1);
        cl1.setCellValue("subcounty");
        cl1.setCellStyle(stylex1);

        Cell cl2 = rw.createCell(2);
        cl2.setCellValue("facility");
        cl2.setCellStyle(stylex1);

        Cell cl3 = rw.createCell(3);
        cl3.setCellValue("year");
        cl3.setCellStyle(stylex1);

        Cell cl4 = rw.createCell(4);
        cl4.setCellValue("month");
        cl4.setCellStyle(stylex1);

        Cell cl5 = rw.createCell(5);
        cl5.setCellValue("section");
        cl5.setCellStyle(stylex1);

        for (int q = 0; q < keyal.size(); q++) {

            Row rwx = shet.createRow(q + 1);

            Cell cl01 = rwx.createCell(0);
            cl01.setCellValue(countyal.get(q).toString());
            cl01.setCellStyle(style2);

            Cell cl11 = rwx.createCell(1);
            cl11.setCellValue(scountyal.get(q).toString());
            cl11.setCellStyle(style2);

            Cell cl21 = rwx.createCell(2);
            cl21.setCellValue(facilal.get(q).toString());
            cl21.setCellStyle(style2);

            Cell cl31 = rwx.createCell(3);
            cl31.setCellValue(new Integer(yearmonthal.get(q).toString().substring(0, 4)));
            cl31.setCellStyle(style2);

            Cell cl41 = rwx.createCell(4);
            cl41.setCellValue(new Integer(monthal.get(q).toString()));
            cl41.setCellStyle(style2);

            Cell cl51 = rwx.createCell(5);
            cl51.setCellValue(sectional.get(q).toString());
            cl51.setCellStyle(style2);

        }

        IdGenerator IG = new IdGenerator();
        String createdOn = IG.CreatedOn();

        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=GapAnalysis_For" + periodname + "_Generatted_On_" + createdOn + ".xlsm");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
        outStream.close();
        pkg.close();

        if (conn.rs != null) {
            conn.rs.close();
        }
        if (conn.rs1 != null) {
            conn.rs1.close();
        }
        if (conn.st1 != null) {
            conn.st1.close();
        }
        if (conn.st != null) {
            conn.st.close();
        }

        File file = new File(pathtodelete);
        System.out.println("path: 2" + pathtodelete);

        if (file.delete()) {
            System.out.println(file.getName() + " is deleted!");
        } else {
            System.out.println("Delete operation  failed.");
        }

    } catch (SQLException ex) {
        Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex);
    } finally {

    }
}