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

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createDrawingPatriarch

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createDrawingPatriarch

Introduction

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

Prototype

@Override
public HSSFPatriarch createDrawingPatriarch()

Source Link

Document

Creates the top-level drawing patriarch.

Usage

From source file:poi.hssf.usermodel.examples.CellComments.java

License:Apache License

public static void main(String[] args) throws IOException {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF");

    // Create the drawing patriarch. This is the top level container for all shapes including cell comments.
    HSSFPatriarch patr = sheet.createDrawingPatriarch();

    //create a cell in row 3
    HSSFCell cell1 = sheet.createRow(3).createCell(1);
    cell1.setCellValue(new HSSFRichTextString("Hello, World"));

    //anchor defines size and position of the comment in worksheet
    HSSFComment comment1 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));

    // set text in the comment
    comment1.setString(new HSSFRichTextString("We can set comments in POI"));

    //set comment author.
    //you can see it in the status bar when moving mouse over the commented cell
    comment1.setAuthor("Apache Software Foundation");

    // The first way to assign comment to a cell is via HSSFCell.setCellComment method
    cell1.setCellComment(comment1);//from ww w  .  ja va2  s. c o m

    //create another cell in row 6
    HSSFCell cell2 = sheet.createRow(6).createCell(1);
    cell2.setCellValue(36.6);

    HSSFComment comment2 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 8, (short) 6, 11));
    //modify background color of the comment
    comment2.setFillColor(204, 236, 255);

    HSSFRichTextString string = new HSSFRichTextString("Normal body temperature");

    //apply custom font to the text in the comment
    HSSFFont font = wb.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);
    string.applyFont(font);

    comment2.setString(string);
    comment2.setVisible(true); //by default comments are hidden. This one is always visible.

    comment2.setAuthor("Bill Gates");

    /**
     * The second way to assign comment to a cell is to implicitly specify its row and column.
     * Note, it is possible to set row and column of a non-existing cell.
     * It works, the comment is visible.
     */
    comment2.setRow(6);
    comment2.setColumn(1);

    FileOutputStream out = new FileOutputStream("poi_comment.xls");
    wb.write(out);
    out.close();
}

From source file:poi.hssf.usermodel.examples.OfficeDrawing.java

License:Apache License

private static void drawSheet1(HSSFSheet sheet1) {
    // Create a row and size one of the cells reasonably large.
    HSSFRow row = sheet1.createRow(2);/*  w w w.ja v  a 2  s.  com*/
    row.setHeight((short) 2800);
    row.createCell(1);
    sheet1.setColumnWidth(2, 9000);

    // Create the drawing patriarch.  This is the top level container for
    // all shapes.
    HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();

    // Draw some lines and an oval.
    drawLinesToCenter(patriarch);
    drawManyLines(patriarch);
    drawOval(patriarch);
    drawPolygon(patriarch);

    // Draw a rectangle.
    HSSFSimpleShape rect = patriarch
            .createSimpleShape(new HSSFClientAnchor(100, 100, 900, 200, (short) 0, 0, (short) 0, 0));
    rect.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
}

From source file:poi.hssf.usermodel.examples.OfficeDrawing.java

License:Apache License

private static void drawSheet2(HSSFSheet sheet2) {
    // Create a row and size one of the cells reasonably large.
    HSSFRow row = sheet2.createRow(2);//from w  w w .j  a  va  2 s  . co  m
    row.createCell(1);
    row.setHeightInPoints(240);
    sheet2.setColumnWidth(2, 9000);

    // Create the drawing patriarch.  This is the top level container for
    // all shapes. This will clear out any existing shapes for that sheet.
    HSSFPatriarch patriarch = sheet2.createDrawingPatriarch();

    // Draw a grid in one of the cells.
    drawGrid(patriarch);
}

From source file:poi.hssf.usermodel.examples.OfficeDrawing.java

License:Apache License

private static void drawSheet3(HSSFSheet sheet3) {
    // Create a row and size one of the cells reasonably large
    HSSFRow row = sheet3.createRow(2);// www .j a  va 2 s. c  om
    row.setHeightInPoints(140);
    row.createCell(1);
    sheet3.setColumnWidth(2, 9000);

    // Create the drawing patriarch.  This is the top level container for
    // all shapes. This will clear out any existing shapes for that sheet.
    HSSFPatriarch patriarch = sheet3.createDrawingPatriarch();

    // Create a shape group.
    HSSFShapeGroup group = patriarch
            .createGroup(new HSSFClientAnchor(0, 0, 900, 200, (short) 2, 2, (short) 2, 2));

    // Create a couple of lines in the group.
    HSSFSimpleShape shape1 = group.createShape(new HSSFChildAnchor(3, 3, 500, 500));
    shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
    ((HSSFChildAnchor) shape1.getAnchor()).setAnchor((short) 3, 3, 500, 500);
    HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor((short) 1, 200, 400, 600));
    shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);

}

From source file:poi.hssf.usermodel.examples.OfficeDrawing.java

License:Apache License

private static void drawSheet4(HSSFSheet sheet4, HSSFWorkbook wb) {
    // Create the drawing patriarch.  This is the top level container for
    // all shapes. This will clear out any existing shapes for that sheet.
    HSSFPatriarch patriarch = sheet4.createDrawingPatriarch();

    // Create a couple of textboxes
    HSSFTextbox textbox1 = patriarch//from   www  .  jav a  2 s  . c o  m
            .createTextbox(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 2, 2));
    textbox1.setString(new HSSFRichTextString("This is a test"));
    HSSFTextbox textbox2 = patriarch
            .createTextbox(new HSSFClientAnchor(0, 0, 900, 100, (short) 3, 3, (short) 3, 4));
    textbox2.setString(new HSSFRichTextString("Woo"));
    textbox2.setFillColor(200, 0, 0);
    textbox2.setLineStyle(HSSFSimpleShape.LINESTYLE_DOTGEL);

    // Create third one with some fancy font styling.
    HSSFTextbox textbox3 = patriarch
            .createTextbox(new HSSFClientAnchor(0, 0, 900, 100, (short) 4, 4, (short) 5, 4 + 1));
    HSSFFont font = wb.createFont();
    font.setItalic(true);
    font.setUnderline(HSSFFont.U_DOUBLE);
    HSSFRichTextString string = new HSSFRichTextString("Woo!!!");
    string.applyFont(2, 5, font);
    textbox3.setString(string);
    textbox3.setFillColor(0x08000030);
    textbox3.setLineStyle(HSSFSimpleShape.LINESTYLE_NONE); // no line around the textbox.
    textbox3.setNoFill(true); // make it transparent
}

From source file:poi.hssf.usermodel.examples.OfficeDrawing.java

License:Apache License

private static void drawSheet5(HSSFSheet sheet5, HSSFWorkbook wb) throws IOException {

    // Create the drawing patriarch.  This is the top level container for
    // all shapes. This will clear out any existing shapes for that sheet.
    HSSFPatriarch patriarch = sheet5.createDrawingPatriarch();

    HSSFClientAnchor anchor;/*from w  w  w.jav a 2 s .  c  o  m*/
    anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 2, 2, (short) 4, 7);
    anchor.setAnchorType(2);
    patriarch.createPicture(anchor, loadPicture("src/resources/logos/logoKarmokar4.png", wb));

    anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 4, 2, (short) 5, 7);
    anchor.setAnchorType(2);
    patriarch.createPicture(anchor, loadPicture("src/resources/logos/logoKarmokar4edited.png", wb));

    anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, 2, (short) 8, 7);
    anchor.setAnchorType(2);
    HSSFPicture picture = patriarch.createPicture(anchor,
            loadPicture("src/resources/logos/logoKarmokar4s.png", wb));
    //Reset the image to the original size.
    picture.resize();
    picture.setLineStyle(picture.LINESTYLE_DASHDOTGEL);

}

From source file:poi.hssf.usermodel.examples.OfficeDrawingWithGraphics.java

License:Apache License

public static void main(String[] args) throws IOException {
    // Create a workbook with one sheet and size the first three somewhat
    // larger so we can fit the chemical structure diagram in.
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("my drawing");
    sheet.setColumnWidth(1, 256 * 27);//from  w w  w.jav  a 2  s  .c  om
    HSSFRow row1 = sheet.createRow(0);
    row1.setHeightInPoints(10 * 15);
    HSSFRow row2 = sheet.createRow(1);
    row2.setHeightInPoints(5 * 15);
    HSSFRow row3 = sheet.createRow(2);
    row3.setHeightInPoints(10 * 15);

    // Add some cells so we can test that the anchoring works when we
    // sort them.
    row1.createCell(0).setCellValue("C");
    row2.createCell(0).setCellValue("A");
    row3.createCell(0).setCellValue("B");

    // Create the top level drawing patriarch.
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    HSSFClientAnchor a;
    HSSFShapeGroup group;
    EscherGraphics g;
    EscherGraphics2d g2d;
    // Anchor entirely within one cell.
    a = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, 0, (short) 1, 0);
    group = patriarch.createGroup(a);
    group.setCoordinates(0, 0, 320, 276);
    float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / Math.abs(group.getY2() - group.getY1());
    g = new EscherGraphics(group, wb, Color.black, verticalPointsPerPixel);
    g2d = new EscherGraphics2d(g);
    drawStar(g2d);

    a = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, 1, (short) 1, 1);
    group = patriarch.createGroup(a);
    group.setCoordinates(0, 0, 640, 276);
    verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / Math.abs(group.getY2() - group.getY1());
    //        verticalPixelsPerPoint = (float)Math.abs(group.getY2() - group.getY1()) / a.getAnchorHeightInPoints(sheet);
    g = new EscherGraphics(group, wb, Color.black, verticalPointsPerPixel);
    g2d = new EscherGraphics2d(g);
    drawStar(g2d);

    FileOutputStream out = new FileOutputStream("workbook.xls");
    wb.write(out);
    out.close();

}

From source file:reports.barCharts.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./* ww  w  .  j a  v  a 2 s . 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 {

    try {
        dbConn conn = new dbConn();

        wb = new HSSFWorkbook();

        HSSFSheet shet2 = null;

        String year = "";
        String site = "";
        String period = "";
        String cbo = "";
        String startdate = "2015-01-01";
        String enddate = "2015-03-30";

        startdate = request.getParameter("startdate");
        enddate = request.getParameter("enddate");

        //            year=request.getParameter("year");
        //site=request.getParameter("sitecbo");
        //period=request.getParameter("period");
        //cbo=request.getParameter("staffcbo");

        String sitename = "";
        String cboname = "";

        //begin a loop that will create as many reports as possible

        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_25_PERCENT.index);

        style.setAlignment(style.ALIGN_CENTER);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
        HSSFFont font_header = wb.createFont();
        font_header.setFontHeightInPoints((short) 10);
        font_header.setFontName("Eras Bold ITC");
        //    font.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.GREY_40_PERCENT.index);
        style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style_header.setAlignment(style_header.ALIGN_CENTER);

        //            style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //            style_header.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //            style_header.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //            style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

        //font data
        HSSFFont datafont = wb.createFont();
        datafont.setBoldweight((short) 03);
        datafont.setColor(HSSFColor.BLACK.index);
        datafont.setFontHeightInPoints((short) 10);
        datafont.setFontName("Cambria");
        datafont.setItalic(true);

        //bold font 
        HSSFFont bolfont = wb.createFont();

        bolfont.setBoldweight((short) 05);
        bolfont.setColor(HSSFColor.BLACK.index);
        bolfont.setFontHeightInPoints((short) 12);
        bolfont.setFontName("Cambria");

        //=========================ROW STYLE===============================

        HSSFCellStyle rowstyle = wb.createCellStyle();
        rowstyle.setWrapText(true);

        //=======INNER DATA STYLING===========================

        CellStyle innerdata_style = wb.createCellStyle();
        innerdata_style.setFont(datafont);
        innerdata_style.setWrapText(true);
        innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER);
        innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index);
        innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //            innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //            innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //            innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //            innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        CellStyle lastcellrighborder = wb.createCellStyle();
        lastcellrighborder.setFont(datafont);
        lastcellrighborder.setWrapText(true);
        lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER);
        lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index);
        lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //            lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //            lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN);
        lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //            lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        CellStyle innerdata_style2 = wb.createCellStyle();
        innerdata_style2.setFont(bolfont);
        innerdata_style2.setWrapText(true);
        innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT);
        innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index);
        innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //            innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //            innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //            innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //            innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        CellStyle orangestyle = wb.createCellStyle();
        orangestyle.setFont(bolfont);
        orangestyle.setWrapText(true);
        orangestyle.setAlignment(orangestyle.ALIGN_CENTER);
        orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index);
        orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //          innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //          innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //          innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //          innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //Code colors

        CellStyle lg = wb.createCellStyle();
        lg.setFont(bolfont);
        lg.setWrapText(true);
        lg.setAlignment(lg.ALIGN_CENTER);
        lg.setFillForegroundColor(HSSFColor.GREEN.index);
        lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        lg.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        lg.setBorderTop(HSSFCellStyle.BORDER_THIN);
        lg.setBorderRight(HSSFCellStyle.BORDER_THIN);
        lg.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        CellStyle Y = wb.createCellStyle();
        Y.setFont(bolfont);
        Y.setWrapText(true);
        Y.setAlignment(Y.ALIGN_CENTER);
        Y.setFillForegroundColor(HSSFColor.WHITE.index);
        Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        Y.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        Y.setBorderTop(HSSFCellStyle.BORDER_THIN);
        Y.setBorderRight(HSSFCellStyle.BORDER_THIN);
        Y.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        CellStyle R = wb.createCellStyle();
        R.setFont(bolfont);
        R.setWrapText(true);
        R.setAlignment(R.ALIGN_CENTER);
        R.setFillForegroundColor(HSSFColor.RED.index);
        R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        R.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        R.setBorderTop(HSSFCellStyle.BORDER_THIN);
        R.setBorderRight(HSSFCellStyle.BORDER_THIN);
        R.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //=======INNER LEFT DATA STYLING===========================

        CellStyle binnerdata_style2 = wb.createCellStyle();
        binnerdata_style2.setFont(datafont);
        binnerdata_style2.setWrapText(true);
        binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT);
        binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index);
        binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //create a header

        //=======================Domainname styles
        CellStyle dnamestyle = wb.createCellStyle();
        dnamestyle.setFont(bolfont);
        dnamestyle.setWrapText(true);
        dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT);
        dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index);
        dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        shet2 = wb.createSheet("Column Charts Per Cbo");
        shet2.setColumnWidth(0, 12000);
        shet2.setColumnWidth(1, 12000);
        shet2.setColumnWidth(2, 4000);
        shet2.setColumnWidth(3, 10000);
        shet2.setColumnWidth(4, 5000);
        shet2.setColumnWidth(5, 5000);
        shet2.setColumnWidth(6, 5000);
        shet2.setColumnWidth(7, 5000);
        shet2.setColumnWidth(8, 5000);
        shet2.setColumnWidth(9, 5000);
        shet2.setColumnWidth(10, 5000);
        shet2.setColumnWidth(11, 5000);
        shet2.setColumnWidth(12, 5000);
        shet2.setColumnWidth(13, 5000);

        //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111           
        //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111           
        //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111           
        //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111  

        String gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name, section_name,cbo,avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id join ( domains join sections on domains.section_id=sections.section_id ) on domain_totals.domainid=domains.domain_id where  date between '"
                + startdate + "' and '" + enddate + "' group by cbo.cboid,domainid order by cbo,domainid";

        System.out.println(gettables);
        conn.rs = conn.st.executeQuery(gettables);
        int rwcount = 0;
        int rowcountcopy = 0;
        String tableheaders[] = { "Section", "Domain", "% Overall Achievement", "Column chart" };

        HSSFRow rwx = null;
        int monitorrows = 0;
        int secAcopy = 0;
        int secBcopy = 0;

        HSSFCell celx = null;
        int noofcols = 3;
        boolean isrow1 = true;
        while (conn.rs.next()) {
            //if the section has changed
            monitorrows++;
            String domainid = conn.rs.getString("domainid");
            float domainvalue = conn.rs.getFloat("domainvalue");
            domainvalue = domainvalue * 100;
            domainvalue = Math.round(domainvalue);
            float totalsum = conn.rs.getFloat("aggregate_sum");
            int dmn = (int) domainvalue;
            totalsum = Math.round(totalsum);
            //determine the cell to print data on
            int ttlsm = (int) totalsum;
            int hearderheight = 40;
            //if its the first row in each 
            if (isrow1) {
                isrow1 = false;
                rwx = shet2.createRow(rwcount);

                HSSFCell headercel = rwx.createCell(0);
                headercel.setCellValue(conn.rs.getString("cbo"));
                headercel.setCellStyle(style);
                rwx.setHeightInPoints(hearderheight);

                //create a blank
                HSSFCell cel = null;

                for (int b = 1; b < tableheaders.length; b++) {
                    cel = rwx.createCell(b);
                    cel.setCellValue("");
                    cel.setCellStyle(style);
                }
                //now merge the header cell
                shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, tableheaders.length - 1));
                rwcount++;

                //now create the header part

                HSSFRow headerrw = shet2.createRow(rwcount);
                rwx.setHeightInPoints(hearderheight);
                for (int b = 0; b < tableheaders.length; b++) {
                    HSSFCell cel1 = headerrw.createCell(b);
                    cel1.setCellValue(tableheaders[b]);
                    cel1.setCellStyle(style);
                }

                rwcount++;
            }

            //create the section part

            HSSFRow rw = shet2.createRow(rwcount);

            rw.setHeightInPoints(25);
            //column one --- section

            HSSFCell seccell = rw.createCell(0);
            seccell.setCellValue(conn.rs.getString("section_name"));
            seccell.setCellStyle(dnamestyle);

            HSSFCell domcell = rw.createCell(1);
            domcell.setCellValue(conn.rs.getString("domain_name"));
            domcell.setCellStyle(dnamestyle);

            //values only
            HSSFCell domval = rw.createCell(2);
            domval.setCellValue(dmn);
            domval.setCellStyle(dnamestyle);

            HSSFCell blank = rw.createCell(3);
            blank.setCellValue("");
            blank.setCellStyle(dnamestyle);

            //now, draw the chart
            HSSFPatriarch patriarch = shet2.createDrawingPatriarch();
            HSSFTextbox textbox1 = patriarch.createTextbox(
                    new HSSFClientAnchor(0, 0, (dmn * 10), 255, (short) 3, rwcount, (short) 3, rwcount));
            textbox1.setString(new HSSFRichTextString("" + dmn));
            textbox1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //green 18,174,55
            //red 250 32 32
            //yellow 248 255 9
            if (dmn >= 75) {
                textbox1.setFillColor(18, 174, 55);
            } else if (dmn > 59 && dmn < 75) {

                textbox1.setFillColor(248, 255, 9);

            }

            else {

                textbox1.setFillColor(250, 32, 32);

            }
            rwcount++;

            if (monitorrows == 4) {
                shet2.addMergedRegion(new CellRangeAddress(rwcount - 4, rwcount - 1, 0, 0));
            }

            if (monitorrows == 12) {

                shet2.addMergedRegion(new CellRangeAddress(rwcount - 8, rwcount - 1, 0, 0));

                HSSFRow lastrw = shet2.createRow(rwcount);
                lastrw.setHeightInPoints(25);
                //now create a row with average
                HSSFCell avcell0 = lastrw.createCell(0);
                avcell0.setCellValue("Average");
                avcell0.setCellStyle(dnamestyle);

                HSSFCell avcell = lastrw.createCell(1);
                avcell.setCellValue("Average");
                avcell.setCellStyle(dnamestyle);

                HSSFCell avcell1 = lastrw.createCell(2);
                avcell1.setCellValue(ttlsm);
                avcell1.setCellStyle(dnamestyle);

                HSSFCell blank1 = lastrw.createCell(3);
                blank1.setCellValue("");
                blank1.setCellStyle(dnamestyle);
                HSSFTextbox textbox = patriarch.createTextbox(
                        new HSSFClientAnchor(0, 0, (dmn * (10)), 255, (short) 3, rwcount, (short) 3, rwcount));
                textbox.setString(new HSSFRichTextString("" + ttlsm));
                if (dmn >= 75) {

                    textbox.setFillColor(18, 174, 55);

                } else if (dmn > 59 && dmn < 75) {

                    textbox.setFillColor(248, 255, 9);

                }

                else {

                    textbox.setFillColor(250, 32, 32);

                }
                textbox.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                isrow1 = true;
                monitorrows = 0;
                //dont print anything 
                rwcount++;

                //last blank cell

                HSSFRow blankrow = shet2.createRow(rwcount);
                blankrow.setHeightInPoints(30);
                for (int b = 0; b < tableheaders.length; b++) {
                    HSSFCell cel1 = blankrow.createCell(b);
                    cel1.setCellValue("");
                    cel1.setCellStyle(innerdata_style);
                }

                rwcount++;
            }

        }

        ///=========================end of while loop 

        //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=OVC_CBO_CHARTS_FROM_" + startdate + "_TO_" + enddate + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
    } catch (SQLException ex) {
        Logger.getLogger(resultspercbo.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:reports.OverallCharts.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from w  w  w. ja  va 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 {

    try {
        dbConn conn = new dbConn();

        wb = new HSSFWorkbook();

        HSSFSheet shet2 = null;

        String year = "";
        String site = "";
        String period = "";
        String cbo = "";
        String startdate = "2015-01-01";
        String enddate = "2015-03-30";

        // startdate=request.getParameter("startdate");
        //enddate=request.getParameter("enddate");

        String getdistinctsites = "SELECT county.county_id as countyid,county_name FROM ovc_lip.backgroundinfor join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on backgroundinfor.site=sites.site_id where ass_date between '"
                + startdate + "' and '" + enddate + "' group by county_name ";

        ArrayList countyids = new ArrayList();
        ArrayList countynames = new ArrayList();
        countyids.add("1000");
        countynames.add("OVERALL COUNTIES REPORT");
        //ArrayList years=new ArrayList();
        //ArrayList periods=new ArrayList();
        //ArrayList cbos=new ArrayList();
        conn.rs = conn.st.executeQuery(getdistinctsites);
        while (conn.rs.next()) {

            countyids.add(conn.rs.getString(1));
            countynames.add(conn.rs.getString(2).toUpperCase() + " COUNTY");

        }
        String sitename = "";
        String cboname = "";

        //begin a loop that will create as many reports as possible

        for (int u = 0; u < countyids.size(); u++) {

            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_25_PERCENT.index);

            style.setAlignment(style.ALIGN_CENTER);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
            HSSFFont font_header = wb.createFont();
            font_header.setFontHeightInPoints((short) 10);
            font_header.setFontName("Eras Bold ITC");
            //    font.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.GREY_40_PERCENT.index);
            style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style_header.setAlignment(style_header.ALIGN_CENTER);

            //            style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //            style_header.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //            style_header.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //            style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN);

            //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

            //font data
            HSSFFont datafont = wb.createFont();
            datafont.setBoldweight((short) 03);
            datafont.setColor(HSSFColor.BLACK.index);
            datafont.setFontHeightInPoints((short) 10);
            datafont.setFontName("Cambria");
            datafont.setItalic(true);

            //bold font 
            HSSFFont bolfont = wb.createFont();

            bolfont.setBoldweight((short) 05);
            bolfont.setColor(HSSFColor.BLACK.index);
            bolfont.setFontHeightInPoints((short) 12);
            bolfont.setFontName("Cambria");

            //=========================ROW STYLE===============================

            HSSFCellStyle rowstyle = wb.createCellStyle();
            rowstyle.setWrapText(true);

            //=======INNER DATA STYLING===========================

            CellStyle innerdata_style = wb.createCellStyle();
            innerdata_style.setFont(datafont);
            innerdata_style.setWrapText(true);
            innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER);
            innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index);
            innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //            innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //            innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //            innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //            innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            CellStyle lastcellrighborder = wb.createCellStyle();
            lastcellrighborder.setFont(datafont);
            lastcellrighborder.setWrapText(true);
            lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER);
            lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index);
            lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //            lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //            lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN);
            lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //            lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            CellStyle innerdata_style2 = wb.createCellStyle();
            innerdata_style2.setFont(bolfont);
            innerdata_style2.setWrapText(true);
            innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT);
            innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index);
            innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //            innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //            innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //            innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //            innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            CellStyle orangestyle = wb.createCellStyle();
            orangestyle.setFont(bolfont);
            orangestyle.setWrapText(true);
            orangestyle.setAlignment(orangestyle.ALIGN_CENTER);
            orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index);
            orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //          innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //          innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //          innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //          innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            //Code colors

            CellStyle lg = wb.createCellStyle();
            lg.setFont(bolfont);
            lg.setWrapText(true);
            lg.setAlignment(lg.ALIGN_CENTER);
            lg.setFillForegroundColor(HSSFColor.GREEN.index);
            lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            lg.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            lg.setBorderTop(HSSFCellStyle.BORDER_THIN);
            lg.setBorderRight(HSSFCellStyle.BORDER_THIN);
            lg.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            CellStyle Y = wb.createCellStyle();
            Y.setFont(bolfont);
            Y.setWrapText(true);
            Y.setAlignment(Y.ALIGN_CENTER);
            Y.setFillForegroundColor(HSSFColor.WHITE.index);
            Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            Y.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            Y.setBorderTop(HSSFCellStyle.BORDER_THIN);
            Y.setBorderRight(HSSFCellStyle.BORDER_THIN);
            Y.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            CellStyle R = wb.createCellStyle();
            R.setFont(bolfont);
            R.setWrapText(true);
            R.setAlignment(R.ALIGN_CENTER);
            R.setFillForegroundColor(HSSFColor.RED.index);
            R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            R.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            R.setBorderTop(HSSFCellStyle.BORDER_THIN);
            R.setBorderRight(HSSFCellStyle.BORDER_THIN);
            R.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            //=======INNER LEFT DATA STYLING===========================

            CellStyle binnerdata_style2 = wb.createCellStyle();
            binnerdata_style2.setFont(datafont);
            binnerdata_style2.setWrapText(true);
            binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT);
            binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index);
            binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
            binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
            binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //create a header

            //=======================Domainname styles
            CellStyle dnamestyle = wb.createCellStyle();
            dnamestyle.setFont(bolfont);
            dnamestyle.setWrapText(true);
            dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT);
            dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index);
            dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            shet2 = wb.createSheet(countynames.get(u).toString().toUpperCase());
            shet2.setColumnWidth(0, 12000);
            shet2.setColumnWidth(1, 12000);
            shet2.setColumnWidth(2, 4000);
            shet2.setColumnWidth(3, 10000);
            shet2.setColumnWidth(4, 5000);
            shet2.setColumnWidth(5, 5000);
            shet2.setColumnWidth(6, 5000);
            shet2.setColumnWidth(7, 5000);
            shet2.setColumnWidth(8, 5000);
            shet2.setColumnWidth(9, 5000);
            shet2.setColumnWidth(10, 5000);
            shet2.setColumnWidth(11, 5000);
            shet2.setColumnWidth(12, 5000);
            shet2.setColumnWidth(13, 5000);

            //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111           
            //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111           
            //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111           
            //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111  

            //String gettables= "SELECT  avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id join ( domains join sections on domains.section_id=sections.section_id ) on domain_totals.domainid=domains.domain_id "+mywhere+" group by cbo.cboid,domainid order by cbo,domainid";
            // String gettables= "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name, section_name,cbo,avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id join ( domains join sections on domains.section_id=sections.section_id ) on domain_totals.domainid=domains.domain_id "+mywhere+" group by cbo.cboid,domainid order by cbo,domainid";

            String gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name,section_name ,avg(aggregate_sum) as aggregate_sum , domains.section_id as secid FROM ovc_lip.domain_totals join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on domain_totals.site=sites.site_id join (domains join sections on domains.section_id=sections.section_id) on domain_totals.domainid=domains.domain_id  where county.county_id='"
                    + countyids.get(u) + "' and date between '" + startdate + "' and '" + enddate
                    + "' group by domain_totals.domainid,county_name order by domainid";
            //if its the first county, themn skip the county part
            if (countyids.get(u).toString().equalsIgnoreCase("1000")) {

                gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name,section_name ,avg(aggregate_sum) as aggregate_sum , domains.section_id as secid FROM ovc_lip.domain_totals  join (domains join sections on domains.section_id=sections.section_id) on domain_totals.domainid=domains.domain_id  where  date between '"
                        + startdate + "' and '" + enddate
                        + "' group by domain_totals.domainid order by domainid";

            }

            System.out.println(gettables);
            conn.rs = conn.st.executeQuery(gettables);
            int rwcount = 0;
            int rowcountcopy = 0;
            String tableheaders[] = { "Section", "Domain", "% Overall Achievement", "Column chart" };

            HSSFRow rwx = null;
            int monitorrows = 0;
            int secAcopy = 0;
            int secBcopy = 0;

            HSSFCell celx = null;
            int noofcols = 3;
            boolean isrow1 = true;
            while (conn.rs.next()) {
                //if the section has changed
                monitorrows++;
                String domainid = conn.rs.getString("domainid");
                float domainvalue = conn.rs.getFloat("domainvalue");
                domainvalue = domainvalue * 100;
                domainvalue = Math.round(domainvalue);
                float totalsum = conn.rs.getFloat("aggregate_sum");
                int dmn = (int) domainvalue;
                totalsum = Math.round(totalsum);
                //determine the cell to print data on
                int ttlsm = (int) totalsum;
                int hearderheight = 40;
                //if its the first row in each 
                if (isrow1) {
                    isrow1 = false;
                    rwx = shet2.createRow(rwcount);

                    HSSFCell headercel = rwx.createCell(0);
                    headercel.setCellValue(countynames.get(u).toString().toUpperCase());
                    headercel.setCellStyle(style);
                    rwx.setHeightInPoints(hearderheight);

                    //create a blank
                    HSSFCell cel = null;

                    for (int b = 1; b < tableheaders.length; b++) {
                        cel = rwx.createCell(b);
                        cel.setCellValue("");
                        cel.setCellStyle(style);
                    }
                    //now merge the header cell
                    shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, tableheaders.length - 1));
                    rwcount++;

                    //now create the header part

                    HSSFRow headerrw = shet2.createRow(rwcount);
                    rwx.setHeightInPoints(hearderheight);
                    for (int b = 0; b < tableheaders.length; b++) {
                        HSSFCell cel1 = headerrw.createCell(b);
                        cel1.setCellValue(tableheaders[b]);
                        cel1.setCellStyle(style);
                    }

                    rwcount++;
                }

                //create the section part

                HSSFRow rw = shet2.createRow(rwcount);

                rw.setHeightInPoints(25);
                //column one --- section

                HSSFCell seccell = rw.createCell(0);
                seccell.setCellValue(conn.rs.getString("section_name"));
                seccell.setCellStyle(dnamestyle);

                HSSFCell domcell = rw.createCell(1);
                domcell.setCellValue(conn.rs.getString("domain_name"));
                domcell.setCellStyle(dnamestyle);

                //values only
                HSSFCell domval = rw.createCell(2);
                domval.setCellValue(dmn);
                domval.setCellStyle(dnamestyle);

                HSSFCell blank = rw.createCell(3);
                blank.setCellValue("");
                blank.setCellStyle(dnamestyle);

                //now, draw the chart
                HSSFPatriarch patriarch = shet2.createDrawingPatriarch();
                HSSFTextbox textbox1 = patriarch.createTextbox(
                        new HSSFClientAnchor(0, 0, (dmn * 10), 255, (short) 3, rwcount, (short) 3, rwcount));
                textbox1.setString(new HSSFRichTextString("" + dmn));
                textbox1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                //green 18,174,55
                //red 250 32 32
                //yellow 248 255 9
                if (dmn >= 75) {
                    textbox1.setFillColor(18, 174, 55);
                } else if (dmn > 59 && dmn < 75) {

                    textbox1.setFillColor(248, 255, 9);

                }

                else {

                    textbox1.setFillColor(250, 32, 32);

                }
                rwcount++;

                if (monitorrows == 4) {
                    shet2.addMergedRegion(new CellRangeAddress(rwcount - 4, rwcount - 1, 0, 0));
                }

                if (monitorrows == 12) {

                    shet2.addMergedRegion(new CellRangeAddress(rwcount - 8, rwcount - 1, 0, 0));

                    HSSFRow lastrw = shet2.createRow(rwcount);
                    lastrw.setHeightInPoints(25);
                    //now create a row with average
                    HSSFCell avcell0 = lastrw.createCell(0);
                    avcell0.setCellValue("Average");
                    avcell0.setCellStyle(dnamestyle);

                    HSSFCell avcell = lastrw.createCell(1);
                    avcell.setCellValue("Average");
                    avcell.setCellStyle(dnamestyle);

                    HSSFCell avcell1 = lastrw.createCell(2);
                    avcell1.setCellValue(ttlsm);
                    avcell1.setCellStyle(dnamestyle);

                    HSSFCell blank1 = lastrw.createCell(3);
                    blank1.setCellValue("");
                    blank1.setCellStyle(dnamestyle);
                    HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor(0, 0, (dmn * (10)), 255,
                            (short) 3, rwcount, (short) 3, rwcount));
                    textbox.setString(new HSSFRichTextString("" + ttlsm));
                    if (dmn >= 75) {

                        textbox.setFillColor(18, 174, 55);

                    } else if (dmn > 59 && dmn < 75) {

                        textbox.setFillColor(248, 255, 9);

                    }

                    else {

                        textbox.setFillColor(250, 32, 32);

                    }
                    textbox.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

                    isrow1 = true;
                    monitorrows = 0;
                    //dont print anything 
                    rwcount++;

                    //last blank cell

                    HSSFRow blankrow = shet2.createRow(rwcount);
                    blankrow.setHeightInPoints(30);
                    for (int b = 0; b < tableheaders.length; b++) {
                        HSSFCell cel1 = blankrow.createCell(b);
                        cel1.setCellValue("");
                        cel1.setCellStyle(innerdata_style);
                    }

                    rwcount++;
                }

            }

            ///=========================end of while loop 

        } //end of each loop

        //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=OVC_COUNTY_CHARTS_FROM_" + startdate + "_TO_" + enddate + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
    } catch (SQLException ex) {
        Logger.getLogger(resultspercbo.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:tw.edu.chit.struts.action.registration.ReportPrintAction.java

/**
 * ???//from   ww  w.  j  a  va2 s . c  o m
 * 
 * @param mapping org.apache.struts.action.ActionMapping object
 * @param form org.apache.struts.action.ActionForm object
 * @param request request javax.servlet.http.HttpServletRequest object
 * @param response response javax.servlet.http.HttpServletResponse object
 * @param sterm 
 */
private void printClassStudentsRegistrationCard(ActionMapping mapping, DynaActionForm form,
        HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME);
    CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME);
    ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME);
    ServletContext context = request.getSession().getServletContext();
    String year = cm.getNowBy("School_year"); // ?????
    // String term = form.getString("sterm"); // ??
    List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)),
            getUserCredential(session).getClassInChargeAry(), false);
    if (!clazzes.isEmpty()) {
        List<Student> students = null;
        HSSFSheet sheet = null;
        String departClass = null;
        RegistrationCard rc = null;
        StdImage image = null;
        String studentNo = null;
        int sheetIndex = 0, pictureIndex = 0;
        byte[] bytes = null;
        // CreationHelper helper = null;
        Drawing drawing = null;
        // ClientAnchor anchor = null;
        HSSFClientAnchor anchor1 = null;
        Picture pict = null;
        DateFormat df = new SimpleDateFormat("yyyy/MM/dd");

        File templateXLS = new File(context.getRealPath("/WEB-INF/reports/ClassStudentsRegistrationCard.xls"));
        HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS);
        HSSFFont fontSize18 = workbook.createFont();
        fontSize18.setFontHeightInPoints((short) 18);
        fontSize18.setFontName("Arial Unicode MS");

        HSSFFont fontSize12 = workbook.createFont();
        fontSize12.setFontHeightInPoints((short) 12);
        fontSize12.setFontName("Arial Unicode MS");

        HSSFFont fontSize11 = workbook.createFont();
        fontSize11.setFontHeightInPoints((short) 11);
        fontSize11.setFontName("Arial Unicode MS");

        HSSFFont fontSize8 = workbook.createFont();
        fontSize8.setFontHeightInPoints((short) 8);
        fontSize8.setFontName("Arial Unicode MS");

        for (Clazz clazz : clazzes) {
            if (!Toolket.isNewStudentClass(clazz.getClassNo()))
                continue;

            departClass = clazz.getClassNo();
            students = mm.findStudentsByClassNo(departClass);
            if (!students.isEmpty()) {

                sheetIndex = 0;
                for (Student student : students) {
                    studentNo = student.getStudentNo();
                    sheet = workbook.getSheetAt(sheetIndex);
                    workbook.setSheetName(sheetIndex++, studentNo.toUpperCase());
                    // Header
                    Toolket.setCellValue(workbook, sheet, 1, 0,
                            Toolket.getCellValue(sheet, 1, 0).replaceAll("YEAR", year), fontSize18,
                            HSSFCellStyle.ALIGN_CENTER, false, null, null);
                    Toolket.setCellValue(workbook, sheet, 1, 13,
                            Toolket.getCellValue(sheet, 1, 13).replaceAll("YEAR", year), fontSize18,
                            HSSFCellStyle.ALIGN_CENTER, false, null, null);

                    // Columns
                    Toolket.setCellValue(workbook, sheet, 2, 1, student.getStudentNo(), fontSize12,
                            HSSFCellStyle.ALIGN_LEFT, true, null, null);
                    Toolket.setCellValue(workbook, sheet, 2, 4, student.getStudentName(), fontSize12,
                            HSSFCellStyle.ALIGN_LEFT, true, null, null);
                    Toolket.setCellValue(workbook, sheet, 2, 8,
                            Toolket.getClassFullName(student.getDepartClass()), fontSize12,
                            HSSFCellStyle.ALIGN_LEFT, true, null, null);
                    Toolket.setCellValue(workbook, sheet, 3, 1,
                            StringUtils.isBlank(student.getStudentEname()) ? "" : student.getStudentEname(),
                            fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                    Toolket.setCellValue(workbook, sheet, 3, 9, student.getIdno(), fontSize12,
                            HSSFCellStyle.ALIGN_LEFT, true, null, null);
                    Toolket.setCellValue(workbook, sheet, 4, 1, Toolket.getSex(student.getSex()), fontSize12,
                            HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                    // Toolket.setCellValue(workbook, sheet, 4, 3, df
                    // .format(student.getBirthday()), fontSize11,
                    // HSSFCellStyle.ALIGN_LEFT, true, null, null);
                    Toolket.setCellValue(workbook, sheet, 4, 3,
                            printNativeDate(df.format(student.getBirthday())), fontSize11,
                            HSSFCellStyle.ALIGN_LEFT, true, null, null);
                    Toolket.setCellValue(workbook, sheet, 4, 9,
                            StringUtils.isBlank(student.getIdent()) ? ""
                                    : Toolket.getIdentity(student.getIdent()),
                            fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                    Toolket.setCellValue(workbook, sheet, 9, 1,
                            (StringUtils.isBlank(student.getPermPost()) ? "" : student.getPermPost()) + "  "
                                    + (StringUtils.isBlank(student.getPermAddr()) ? "" : student.getPermAddr()),
                            fontSize12, HSSFCellStyle.ALIGN_LEFT, true, null, null);
                    Toolket.setCellValue(workbook, sheet, 9, 9, student.getTelephone(), fontSize12,
                            HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                    Toolket.setCellValue(workbook, sheet, 10, 9, student.getCellPhone(), fontSize12,
                            HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                    Toolket.setCellValue(workbook, sheet, 11, 2, student.getParentName(), fontSize12,
                            HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                    Toolket.setCellValue(workbook, sheet, 12, 2,
                            student.getCurrPost() + " " + student.getCurrAddr(), fontSize8,
                            HSSFCellStyle.ALIGN_LEFT, true, null, null);

                    image = new StdImage();
                    image.setStudentNo(student.getStudentNo());
                    image = mm.findStdImageBy(image);
                    if (image != null) {
                        bytes = image.getImage().getBytes(1l, (int) image.getImage().length());
                        // Image im =
                        // java.awt.Toolkit.getDefaultToolkit().getImage(filename);
                        try {
                            pictureIndex = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                        } catch (Exception e) {
                            try {
                                pictureIndex = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                            } catch (Exception e1) {
                                pictureIndex = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PICT);
                            }
                        }

                        // helper = workbook.getCreationHelper();
                        drawing = sheet.createDrawingPatriarch();
                        // anchor = helper.createClientAnchor();
                        anchor1 = new HSSFClientAnchor(0, 0, 400, 100, (short) 13, 19, (short) 17, 29);
                        anchor1.setAnchorType(0);
                        // anchor.setCol1(13);
                        // anchor.setRow1(19);
                        pict = drawing.createPicture(anchor1, pictureIndex);
                        try {
                            pict.resize(); // 154611
                            // pict.resize(0.5);
                        } catch (Exception e) {
                            e.printStackTrace();
                            log.error(e.getMessage(), e);
                        }
                    }

                    if (student.getRegistrationCard() != null) {
                        rc = student.getRegistrationCard();
                        Toolket.setCellValue(workbook, sheet, 2, 8,
                                Toolket.getCellValue(sheet, 2, 8) + " "
                                        + (rc.getDiviName() == null ? "" : rc.getDiviName().trim()),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);

                        Toolket.setCellValue(workbook, sheet, 3, 5,
                                StringUtils.isBlank(rc.getBirthCountry()) ? "" : rc.getBirthCountry().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 4, 5,
                                StringUtils.isBlank(rc.getBirthPlace()) ? "" : rc.getBirthPlace().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 5, 2,
                                StringUtils.isBlank(rc.getAborigine()) ? "" : rc.getAborigine().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 5, 5,
                                StringUtils.isBlank(rc.getForeignPlace()) ? "" : rc.getForeignPlace().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 5, 9,
                                rc.getArmyIn() == null ? "?"
                                        : "?" + df.format(rc.getArmyIn()),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 6, 9,
                                rc.getArmyOut() == null ? "?"
                                        : "?" + df.format(rc.getArmyOut()),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 7, 3,
                                StringUtils.isBlank(rc.getBeforeSchool()) ? "" : rc.getBeforeSchool().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 7, 9,
                                StringUtils.isBlank(rc.getGradeYear()) ? "" : rc.getGradeYear(), fontSize12,
                                HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 7, 11,
                                rc.getGradeType() == null ? "" : rc.getGradeType().toString(), fontSize12,
                                HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 8, 3,
                                StringUtils.isBlank(rc.getBeforeDept()) ? "" : rc.getBeforeDept().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 11, 5,
                                StringUtils.isBlank(rc.getParentAge()) ? "" : rc.getParentAge().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 11, 7,
                                StringUtils.isBlank(rc.getParentCareer()) ? "" : rc.getParentCareer().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 11, 11,
                                StringUtils.isBlank(rc.getParentRelationship()) ? ""
                                        : rc.getParentRelationship().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 12, 10,
                                StringUtils.isBlank(rc.getEmergentPhone()) ? "" : rc.getEmergentPhone().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 13, 10,
                                StringUtils.isBlank(rc.getEmergentCell()) ? "" : rc.getEmergentCell().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_LEFT, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 15, 1,
                                StringUtils.isBlank(rc.getMemberTitle1()) ? "" : rc.getMemberTitle1().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 15, 2,
                                StringUtils.isBlank(rc.getMemberName1()) ? "" : rc.getMemberName1().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 15, 4,
                                StringUtils.isBlank(rc.getMemberAge1()) ? "" : rc.getMemberAge1().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 15, 5,
                                StringUtils.isBlank(rc.getMemberCareer1()) ? "" : rc.getMemberCareer1().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);

                        Toolket.setCellValue(workbook, sheet, 15, 7,
                                StringUtils.isBlank(rc.getMemberTitle2()) ? "" : rc.getMemberTitle2().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 15, 8,
                                StringUtils.isBlank(rc.getMemberName2()) ? "" : rc.getMemberName2().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 15, 10,
                                StringUtils.isBlank(rc.getMemberAge2()) ? "" : rc.getMemberAge2().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 15, 11,
                                StringUtils.isBlank(rc.getMemberCareer2()) ? "" : rc.getMemberCareer2().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);

                        Toolket.setCellValue(workbook, sheet, 16, 1,
                                StringUtils.isBlank(rc.getMemberTitle3()) ? "" : rc.getMemberTitle3().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 16, 2,
                                StringUtils.isBlank(rc.getMemberName3()) ? "" : rc.getMemberName3().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 16, 4,
                                StringUtils.isBlank(rc.getMemberAge3()) ? "" : rc.getMemberAge3().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 16, 5,
                                StringUtils.isBlank(rc.getMemberCareer3()) ? "" : rc.getMemberCareer3().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);

                        Toolket.setCellValue(workbook, sheet, 16, 7,
                                StringUtils.isBlank(rc.getMemberTitle4()) ? "" : rc.getMemberTitle4().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 16, 8,
                                StringUtils.isBlank(rc.getMemberName4()) ? "" : rc.getMemberName4().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 16, 10,
                                StringUtils.isBlank(rc.getMemberAge4()) ? "" : rc.getMemberAge4().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                        Toolket.setCellValue(workbook, sheet, 16, 11,
                                StringUtils.isBlank(rc.getMemberCareer4()) ? "" : rc.getMemberCareer4().trim(),
                                fontSize12, HSSFCellStyle.ALIGN_CENTER, true, false, null, null);
                    }

                }

            }
        }

        File tempDir = new File(
                context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                        + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
        if (!tempDir.exists())
            tempDir.mkdirs();

        File output = new File(tempDir, "ClassStudentRegistrationCard.xls");
        FileOutputStream fos = new FileOutputStream(output);
        workbook.write(fos);
        fos.close();

        JasperReportUtils.printXlsToFrontEnd(response, output);
        output.delete();
        tempDir.delete();
    } else {
        Map<String, String> param = new HashMap<String, String>();
        File image = new File(context.getRealPath("/pages/images/2002chitS.jpg"));
        param.put("IMAGE", image.getAbsolutePath());
        byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param,
                new JREmptyDataSource());
        JasperReportUtils.printPdfToFrontEnd(response, bytes);
    }
}