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

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

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

Introduction

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

Prototype

void setFillPattern(FillPatternType fp);

Source Link

Document

setting to one fills the cell with the foreground color...

Usage

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *//* ww  w  .j a va  2s  .com*/
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        LOGGER.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40 * 10;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xls");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wb = new HSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(CellStyle.ALIGN_RIGHT);

        cs.setFillPattern(CellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment());

        LOGGER.debug("getFillPattern : {}", cs1.getFillPattern());
        assertEquals(CellStyle.DIAMONDS, cs1.getFillPattern());

        LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor());
        LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *///from   ww w. j a va 2s.com
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        log.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            log.debug("Delete file...." + sb.toString());
        }

        SXSSFWorkbook wb = new SXSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        cs.setFillPattern(HSSFCellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        log.debug("getAlignment : " + cs1.getAlignment());
        assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());

        log.debug("getFillPattern : " + cs1.getFillPattern());
        assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern());

        log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor());
        log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:eu.alpinweiss.filegen.service.impl.GenerateXlsxFileServiceImpl.java

License:Apache License

public void generateExcel(String excelFilename, int rowCount, List<FieldDefinition> fieldDefinitionList,
        int sheetCount) {

    long startTime = new Date().getTime();

    outputWriterHolder.writeValueInLine("Excel data generation started");

    Workbook wb = new SXSSFWorkbook();

    try {/*w w  w  . ja va2 s .c o  m*/
        CellStyle cs = wb.createCellStyle();
        cs.setFillForegroundColor(IndexedColors.LIME.getIndex());
        cs.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f.setFontHeightInPoints((short) 12);
        cs.setFont(f);

        SXSSFSheet sheet1 = (SXSSFSheet) wb.createSheet("dataSheet");

        int columnCount = fieldDefinitionList.size();

        Map<Integer, Input2TableInfo> input2TableInfoMap = new LinkedHashMap<>(columnCount);

        for (int i = 0; i < columnCount; i++) {
            Input2TableInfo input2TableInfo = new Input2TableInfo();
            FieldDefinition fieldDefinition = fieldDefinitionList.get(i);
            input2TableInfo.setFieldText(fieldDefinition.getFieldName());
            input2TableInfo.setFieldDefinition(fieldDefinition);
            input2TableInfo.initCellStyle(wb);
            input2TableInfo.initGenerator();
            input2TableInfoMap.put(i, input2TableInfo);
        }

        if (sheetCount > 1) {
            CountDownLatch startSignal = new CountDownLatch(1);
            CountDownLatch doneSignal;

            doneSignal = new CountDownLatch(sheetCount);

            ParameterVault parameterVault = new DefaultParameterVault(0, rowCount);
            SheetProcessor stringProcessorSheet1 = new SheetProcessor(parameterVault, startSignal, doneSignal,
                    cs, sheet1, columnCount, input2TableInfoMap, outputWriterHolder);
            new Thread(stringProcessorSheet1, "Processor-" + sheetCount).start();

            for (int i = 0; i < sheetCount - 1; i++) {
                SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("dataSheet_" + i);
                ParameterVault parameterVaultRest = new DefaultParameterVault(i + 1, rowCount);
                SheetProcessor stringProcessor = new SheetProcessor(parameterVaultRest, startSignal, doneSignal,
                        cs, sheet, columnCount, input2TableInfoMap, outputWriterHolder);
                new Thread(stringProcessor, "Processor-" + i).start();
            }

            startSignal.countDown();
            doneSignal.await();
        } else {
            ParameterVault parameterVault = new DefaultParameterVault(0, rowCount);
            new SheetProcessor(outputWriterHolder).generateSheetData(parameterVault, cs, sheet1, columnCount,
                    input2TableInfoMap);
        }

        outputWriterHolder.writeValueInLine("Excel data generation finished.");
        long generationTime = new Date().getTime();
        outputWriterHolder.writeValueInLine("Time used " + ((generationTime - startTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Writing to file.");

        FileOutputStream fileOut = new FileOutputStream(excelFilename.trim());

        wb.write(fileOut);
        fileOut.close();

        long writeTime = new Date().getTime();
        outputWriterHolder.writeValueInLine("Time used " + ((writeTime - generationTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Total time used " + ((writeTime - startTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Done");
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        try {
            wb.close();
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
    }

}

From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java

License:Open Source License

/**
 * @param workbook the workbook of the cell
 * @param strikeOut true, if cell should be striked out
 * @return the highlighted cell style//from  ww  w .j  a  va2 s .c  o m
 */
public static CellStyle getHighlightedStyle(Workbook workbook, boolean strikeOut) {

    // create highlight style for type cells
    CellStyle highlightStyle = workbook.createCellStyle();
    DataFormat df = workbook.createDataFormat();
    // set thin border around the cell
    highlightStyle.setBorderBottom(CellStyle.BORDER_THIN);
    highlightStyle.setBorderLeft(CellStyle.BORDER_THIN);
    highlightStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    highlightStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    highlightStyle.setWrapText(true);
    highlightStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    highlightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    if (strikeOut) {
        Font disabledTypeFont = workbook.createFont();
        disabledTypeFont.setStrikeout(true);
        disabledTypeFont.setColor(IndexedColors.BLACK.getIndex());
        highlightStyle.setFont(disabledTypeFont);
    }

    return highlightStyle;
}

From source file:eventHandlers.CompareDrotVSRoster.java

private void foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook) {
    Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1);
    DataFormatter df = new DataFormatter();
    String ssnString = df.formatCellValue(ssnCell); //return ***-**-****
    ssnString = ssnString.replace("-", "");

    Cell soCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1);
    String signOutDateString = df.formatCellValue(soCell); //return ***-**-****

    Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1);
    String ctrlNumString = ctrlNumCell.getStringCellValue();
    if (proclibrary.containsKey(ssnString)) { // Map< SSN, Map<ctrlNum, signOutdate>>
        Map<String, String> leaves = proclibrary.get(ssnString);
        if (leaves.containsKey(ctrlNumString)) {
            String date = leaves.get(ctrlNumString);
            if (date.equals(signOutDateString)) {
                CellStyle style = createStandardStyle(myWorkBook);
                style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                ctrlNumCell.setCellStyle(style);
            }/*from  w w w  .j a  v a 2  s  .  c  o m*/
        }
    } else if (rejlibrary.containsKey(ssnString)) {
        Map<String, String> leaves = rejlibrary.get(ssnString);
        if (leaves.containsKey(ctrlNumString)) {
            String date = leaves.get(ctrlNumString);
            if (date.equals(signOutDateString)) {
                CellStyle style = createStandardStyle(myWorkBook);
                style.setFillForegroundColor(IndexedColors.RED.getIndex());
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                ctrlNumCell.setCellStyle(style);
            }
        }
    } else if (recylibrary.containsKey(ssnString)) {
        Map<String, String> leaves = recylibrary.get(ssnString);
        if (leaves.containsKey(ctrlNumString)) {
            String date = leaves.get(ctrlNumString);
            if (date.equals(signOutDateString)) {
                CellStyle style = createStandardStyle(myWorkBook);
                style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                ctrlNumCell.setCellStyle(style);
            }
        }
    }
}

From source file:ExcelFx.FXMLDocumentController.java

private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();
    DataFormat df = wb.createDataFormat();

    CellStyle styleNormal;//w  w w  . j av  a  2  s  .  c  o  m
    CellStyle styleTotal;
    CellStyle styleHeader;

    Font font1 = wb.createFont();
    font1.setBoldweight(Font.BOLDWEIGHT_NORMAL);

    Font font2 = wb.createFont();
    font2.setBoldweight(Font.BOLDWEIGHT_BOLD);

    styleNormal = borderedStyleNormal(wb);
    styleNormal.setFont(font1);
    styles.put("normal", styleNormal);

    styleTotal = borderedStyleTotal(wb);
    styleTotal.setFont(font2);
    styleTotal.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    styleTotal.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styles.put("total", styleTotal);

    styleHeader = borderedStyleHeader(wb);
    styleHeader.setFont(font1);
    styleHeader.setRotation((short) 90);
    styles.put("header", styleHeader);

    return styles;
}

From source file:exportex.ExportEx.java

public static void cellFillBlue(CellStyle style) {
    style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

}

From source file:FILING.cboreport.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//www. 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./*  w ww.  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 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  w w w  .  j a  v  a  2s  .c  om
 *
 * @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();
    }
}