Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

From source file:poi.xslf.usermodel.PieChartDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    if (args.length < 2) {
        usage();/*from  w  w w.j a v  a2  s  .c om*/
        return;
    }

    BufferedReader modelReader = new BufferedReader(new FileReader(args[1]));

    String chartTitle = modelReader.readLine(); // first line is chart title

    XMLSlideShow pptx = new XMLSlideShow(new FileInputStream(args[0]));
    XSLFSlide slide = pptx.getSlides()[0];

    // find chart in the slide
    XSLFChart chart = null;
    for (POIXMLDocumentPart part : slide.getRelations()) {
        if (part instanceof XSLFChart) {
            chart = (XSLFChart) part;
            break;
        }
    }

    if (chart == null)
        throw new IllegalStateException("chart not found in the template");

    // embedded Excel workbook that holds the chart data
    POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    CTChart ctChart = chart.getCTChart();
    CTPlotArea plotArea = ctChart.getPlotArea();

    CTPieChart pieChart = plotArea.getPieChartArray(0);
    //Pie Chart Series
    CTPieSer ser = pieChart.getSerArray(0);

    // Series Text
    CTSerTx tx = ser.getTx();
    tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle);
    sheet.createRow(0).createCell(1).setCellValue(chartTitle);
    String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();
    tx.getStrRef().setF(titleRef);

    // Category Axis Data
    CTAxDataSource cat = ser.getCat();
    CTStrData strData = cat.getStrRef().getStrCache();

    // Values
    CTNumDataSource val = ser.getVal();
    CTNumData numData = val.getNumRef().getNumCache();

    strData.setPtArray(null); // unset old axis text
    numData.setPtArray(null); // unset old values

    // set model
    int idx = 0;
    int rownum = 1;
    String ln;
    while ((ln = modelReader.readLine()) != null) {
        String[] vals = ln.split("\\s+");
        CTNumVal numVal = numData.addNewPt();
        numVal.setIdx(idx);
        numVal.setV(vals[1]);

        CTStrVal sVal = strData.addNewPt();
        sVal.setIdx(idx);
        sVal.setV(vals[0]);

        idx++;
        XSSFRow row = sheet.createRow(rownum++);
        row.createCell(0).setCellValue(vals[0]);
        row.createCell(1).setCellValue(Double.valueOf(vals[1]));
    }
    numData.getPtCount().setVal(idx);
    strData.getPtCount().setVal(idx);

    String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
    val.getNumRef().setF(numDataRange);
    String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);
    cat.getStrRef().setF(axisDataRange);

    // updated the embedded workbook with the data
    OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
    wb.write(xlsOut);
    xlsOut.close();

    // save the result
    FileOutputStream out = new FileOutputStream("pie-chart-demo-output.pptx");
    pptx.write(out);
    out.close();
}

From source file:poi.xssf.usermodel.examples.CalendarDemo.java

License:Apache License

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

    Calendar calendar = Calendar.getInstance();
    if (args.length > 0)
        calendar.set(Calendar.YEAR, Integer.parseInt(args[0]));

    int year = calendar.get(Calendar.YEAR);

    XSSFWorkbook wb = new XSSFWorkbook();
    Map<String, XSSFCellStyle> styles = createStyles(wb);

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        XSSFSheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);/*  w  w w  .ja v a 2 s  . co  m*/
        XSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setOrientation(PrintOrientation.LANDSCAPE);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        //the header row: centered text in 48pt font
        XSSFRow headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        XSSFCell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        XSSFRow monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //for compatibility with HSSF we have to set column width in units of 1/256th of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            XSSFCell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            XSSFRow row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                XSSFCell dayCell_1 = row.createCell(i * 2);
                XSSFCell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

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

    // Write the output to a file
    FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx");
    wb.write(out);
    out.close();
}

From source file:poi.xssf.usermodel.examples.LineChart.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("linechart");
    final int NUM_OF_ROWS = 3;
    final int NUM_OF_COLUMNS = 10;

    // Create a row and put some cells in it. Rows are 0 based.
    Row row;//ww  w . j av a 2 s. c  o  m
    Cell cell;
    for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
        row = sheet.createRow((short) rowIndex);
        for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
            cell = row.createCell((short) colIndex);
            cell.setCellValue(colIndex * (rowIndex + 1));
        }
    }

    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);

    Chart chart = drawing.createChart(anchor);
    ChartLegend legend = chart.getOrCreateLegend();
    legend.setPosition(LegendPosition.TOP_RIGHT);

    LineChartData data = chart.getChartDataFactory().createLineChartData();

    // Use a category axis for the bottom axis.
    ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
    ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
    leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

    ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet,
            new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
    ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet,
            new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
    ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet,
            new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));

    data.addSerie(xs, ys1);
    data.addSerie(xs, ys2);

    chart.plot(data, bottomAxis, leftAxis);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("ooxml-line-chart.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:poi.xssf.usermodel.examples.ScatterChart.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Sheet 1");
    final int NUM_OF_ROWS = 3;
    final int NUM_OF_COLUMNS = 10;

    // Create a row and put some cells in it. Rows are 0 based.
    Row row;/*from  ww w. ja v  a 2 s.  c  om*/
    Cell cell;
    for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
        row = sheet.createRow((short) rowIndex);
        for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
            cell = row.createCell((short) colIndex);
            cell.setCellValue(colIndex * (rowIndex + 1));
        }
    }

    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);

    Chart chart = drawing.createChart(anchor);
    ChartLegend legend = chart.getOrCreateLegend();
    legend.setPosition(LegendPosition.TOP_RIGHT);

    ScatterChartData data = chart.getChartDataFactory().createScatterChartData();

    ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
    ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
    leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

    ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet,
            new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
    ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet,
            new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
    ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet,
            new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));

    data.addSerie(xs, ys1);
    data.addSerie(xs, ys2);

    chart.plot(data, bottomAxis, leftAxis);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("ooxml-scatter-chart.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:preprocessing.EnemyInfoGenerator.java

private static void writeToSheet(XSSFWorkbook book, List<EnemyInfo> enemyList,
        HashMap<String, List<String[]>> skillMap, HashMap<String, List<String[]>> skillRoleMap,
        HashMap<String, String[]> enemyAiOrderMap, HashMap<String, String[]> enemyLvUpMap) {
    // Write data into sheet.
    Sheet sheet = book.createSheet();//from   w w w  . j av  a  2  s  . c om
    CellRangeAddress cra;
    Row row;
    Cell cell;
    int rowNum, colNum;
    createCellStyles(book);

    for (int i = 0; i < COL_WIDTH.length; i++) {
        sheet.setColumnWidth(i, COL_WIDTH[i]);
    }

    row = getNotNullRow(sheet, 0);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("?");
    cell.setCellStyle(headerStyle);
    for (int j = 1; j < 11; j++) {
        cell = getNotNullCell(row, 0);
        cell.setCellStyle(headerStyle);
    }
    cra = new CellRangeAddress(0, 0, 0, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    colNum = 0;
    row = getNotNullRow(sheet, 1);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("??");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("HP");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("ATK");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("INT");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("MND");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("DEF");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("MDF");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("?");
    cell.setCellStyle(headerStyle);

    rowNum = 2;
    for (int i = 0; i < enemyList.size(); i++) {
        colNum = 0;
        row = getNotNullRow(sheet, rowNum);
        cell = getNotNullCell(row, colNum++);
        cell.setCellStyle(contentStyle);
        cell = getNotNullCell(row, colNum++);
        cell.setCellValue(enemyList.get(i).getName());
        cell.setCellStyle(contentStyle);
        cell = getNotNullCell(row, colNum++);
        cell.setCellValue(EnumType.getNameById(enemyList.get(i).getType()));
        cell.setCellStyle(contentStyle);
        for (int j = 0; j < 6; j++) {
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(enemyList.get(i).getCurrentAttr(j));
            cell.setCellStyle(contentStyle);
        }
        String[] enemyPartLvUpArray = enemyLvUpMap.get(enemyList.get(i).getId());
        String findTypeRate = ((BattlePrepare) UIUtil.getBattlePrepare()).findTypeRate(enemyPartLvUpArray,
                enemyList.get(i).getType());
        cell = getNotNullCell(row, colNum++);
        cell.setCellValue(findTypeRate);
        cell.setCellStyle(contentStyle);
        cell = getNotNullCell(row, colNum++);
        cell.setCellValue(enemyList.get(i).getActionPoint());
        cell.setCellStyle(contentStyle);
        rowNum++;
    }
    cra = new CellRangeAddress(1, rowNum - 1, 0, 0);
    sheet.addMergedRegion(cra);

    row = getNotNullRow(sheet, rowNum++);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 1, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    row = getNotNullRow(sheet, rowNum++);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 0, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    colNum = 11;
    row = getNotNullRow(sheet, rowNum);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("ID");
    cell.setCellStyle(contentStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("AI?ID");
    cell.setCellStyle(contentStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(contentStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(contentStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("?");
    cell.setCellStyle(contentStyle);

    for (int p = 0; p < enemyAiOrderTitle.length; p++) {
        cell = getNotNullCell(row, colNum++);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(enemyAiOrderTitle[p]);
    }

    for (int n = 0; n < enemyList.size(); n++) {
        Integer oriRowNum = rowNum;
        row = getNotNullRow(sheet, rowNum++);
        cell = getNotNullCell(row, 0);
        cell.setCellValue(enemyList.get(n).getName());
        cell.setCellStyle(headerStyle);
        cell = getNotNullCell(row, 1);
        cell.setCellValue("??");
        cell.setCellStyle(headerStyle);
        cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 1, 4);
        sheet.addMergedRegion(cra);
        cell = getNotNullCell(row, 5);
        cell.setCellValue("??");
        cell.setCellStyle(headerStyle);
        cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 5, 8);
        sheet.addMergedRegion(cra);
        cell = getNotNullCell(row, 9);
        cell.setCellValue("");
        cell.setCellStyle(headerStyle);
        cell = getNotNullCell(row, 10);
        cell.setCellValue("");
        cell.setCellStyle(headerStyle);

        if (!enemyList.get(n).getPassiveSkill().isEmpty()) {
            row = getNotNullRow(sheet, rowNum++);
            cell = getNotNullCell(row, 9);
            cell.setCellValue("");
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, 10);
            cell.setCellValue("-");
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, 11);
            cell.setCellValue(enemyList.get(n).getPassiveSkill());
            cell.setCellStyle(contentStyle);
        }
        for (int i = 0; i < enemyList.get(n).getSkills().size(); i++) {
            EnemySkill skill = enemyList.get(n).getSkills().get(i);
            row = getNotNullRow(sheet, rowNum++);
            cell = getNotNullCell(row, 1);
            cell.setCellValue("");
            cell.setCellStyle(contentStyle);
            cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 1, 4);
            sheet.addMergedRegion(cra);
            cell = getNotNullCell(row, 5);
            cell.setCellValue("");
            cell.setCellStyle(contentStyle);
            cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 5, 8);
            sheet.addMergedRegion(cra);
            cell = getNotNullCell(row, 9);
            cell.setCellValue(skill.getCost());
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, 10);
            cell.setCellValue(skill.getPriority());
            cell.setCellStyle(contentStyle);

            colNum = 11;
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(Long.parseLong(skill.getSkillId()));
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(Long.parseLong(skill.getAiOrderId()));
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(skill.getTarget());
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(skill.getMaxTimes());
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(skill.getSuccessRate());
            cell.setCellStyle(contentStyle);

            String[] aiOrder = enemyAiOrderMap.get(skill.getAiOrderId());
            for (int p = 0; p < ENEMY_AI_ORDER_INDEX.length; p++) {
                if (aiOrder.length > ENEMY_AI_ORDER_INDEX[p]) {
                    cell = getNotNullCell(row, colNum++);
                    cell.setCellStyle(contentStyle);
                    if (aiOrder[ENEMY_AI_ORDER_INDEX[p]].matches("[\\d]+")) {
                        cell.setCellValue(Integer.parseInt(aiOrder[ENEMY_AI_ORDER_INDEX[p]]));
                    } else {
                        cell.setCellValue(aiOrder[ENEMY_AI_ORDER_INDEX[p]]);
                    }
                }
            }

        }
        cra = new CellRangeAddress(oriRowNum, rowNum - 1, 0, 0);
        sheet.addMergedRegion(cra);
    }
    Integer oriRowNum = rowNum;

    row = getNotNullRow(sheet, rowNum++);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 0, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    row = getNotNullRow(sheet, rowNum++);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);

    for (int i = 0; i < 11; i++) {
        row = getNotNullRow(sheet, rowNum);
        cell = getNotNullCell(row, 0);
        cell.setCellStyle(headerStyle);
        if (roundName[i].matches("[\\d]+")) {
            cell.setCellValue(Integer.parseInt(roundName[i]));
        } else {
            cell.setCellValue(roundName[i]);
        }
        cra = new CellRangeAddress(rowNum, rowNum + 2, 0, 0);
        sheet.addMergedRegion(cra);
        rowNum += 3;
    }
    cra = new CellRangeAddress(rowNum - 3, rowNum - 1, 1, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    TreeSet<String> skillSet = new TreeSet<>();
    TreeSet<String> skillRoleSet = new TreeSet<>();
    for (int n = 0; n < enemyList.size(); n++) {
        if (!enemyList.get(n).getPassiveSkill().isEmpty()) {
            String skillId = enemyList.get(n).getPassiveSkill();
            skillSet.add(skillId);
        }
        for (int i = 0; i < enemyList.get(n).getSkills().size(); i++) {
            String skillId = enemyList.get(n).getSkills().get(i).getSkillId();
            skillSet.add(skillId);
        }
    }

    rowNum = oriRowNum;
    rowNum++;
    row = getNotNullRow(sheet, rowNum++);
    colNum = 11;
    for (int p = 0; p < skillArrayTitle.length; p++) {
        cell = getNotNullCell(row, colNum++);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(skillArrayTitle[p]);
    }
    colNum = 19;
    for (int p = 0; p < skillRoleArrayTitle.length; p++) {
        cell = getNotNullCell(row, colNum++);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(skillRoleArrayTitle[p]);
    }
    String skillId;
    while ((skillId = skillSet.pollFirst()) != null) {
        List<String[]> skills = skillMap.get(skillId);
        for (String[] skill : skills) {
            row = getNotNullRow(sheet, rowNum);
            skillRoleSet.add(skill[SKILL_SKILLROLE_COL]);
            List<String[]> skillRoles = skillRoleMap.get(skill[SKILL_SKILLROLE_COL]);
            colNum = 11;
            for (int p = 0; p < SKILL_ARRAY_INDEX.length; p++) {
                if (skill.length > SKILL_ARRAY_INDEX[p]) {
                    cell = getNotNullCell(row, colNum++);
                    cell.setCellStyle(contentStyle);
                    if (skill[SKILL_ARRAY_INDEX[p]].matches("[\\d]+")) {
                        cell.setCellValue(Integer.parseInt(skill[SKILL_ARRAY_INDEX[p]]));
                    } else {
                        cell.setCellValue(skill[SKILL_ARRAY_INDEX[p]]);
                    }
                }
            }
            for (String[] skillRole : skillRoles) {
                row = getNotNullRow(sheet, rowNum);
                colNum = 19;
                for (int p = 0; p < skillRoleArrayIndex.length; p++) {
                    if (skillRole.length > skillRoleArrayIndex[p]) {
                        cell = getNotNullCell(row, colNum++);
                        cell.setCellStyle(contentStyle);
                        if (skillRole[skillRoleArrayIndex[p]].matches("[\\d]+")) {
                            cell.setCellValue(Integer.parseInt(skillRole[skillRoleArrayIndex[p]]));
                        } else {
                            cell.setCellValue(skillRole[skillRoleArrayIndex[p]]);
                        }
                    }
                }
                rowNum++;
            }
        }
    }

    rowNum++;
    //        row = getNotNullRow(sheet, rowNum++);
    //        colNum = 11;
    //        for (int p = 0; p < skillRoleArrayTitle.length; p++) {
    //            cell = getNotNullCell(row, colNum++);
    //            cell.setCellStyle(contentStyle);
    //            cell.setCellValue(skillRoleArrayTitle[p]);
    //        }
    //        String skillRoleId;
    //        while ((skillRoleId = skillRoleSet.pollFirst()) != null) {
    //            List<String[]> skillRoles = skillRoleMap.get(skillRoleId);
    //            for (String[] skillRole : skillRoles) {
    //                row = getNotNullRow(sheet, rowNum);
    //                colNum = 11;
    //                for (int p = 0; p < skillRoleArrayIndex.length; p++) {
    //                    if (skillRole.length > skillRoleArrayIndex[p]) {
    //                        cell = getNotNullCell(row, colNum++);
    //                        cell.setCellStyle(contentStyle);
    //                        if (skillRole[skillRoleArrayIndex[p]].matches("[\\d]+")) {
    //                            cell.setCellValue(Integer.parseInt(skillRole[skillRoleArrayIndex[p]]));
    //                        } else {
    //                            cell.setCellValue(skillRole[skillRoleArrayIndex[p]]);
    //                        }
    //                    }
    //                }
    //                rowNum++;
    //            }
    //        }

    for (int i = 0; i < rowNum + 30; i++) {
        getNotNullRow(sheet, i).setHeight(ROW_HEIGHT.shortValue());
    }

}

From source file:pt.ist.expenditureTrackingSystem.presentationTier.actions.organization.OrganizationAction.java

License:Open Source License

public final ActionForward downloadUnitResponsibles(final ActionMapping mapping, final ActionForm form,
        final HttpServletRequest request, final HttpServletResponse response) throws IOException, SQLException {

    response.setContentType("application/xls ");
    response.setHeader("Content-disposition", "attachment; filename=ResponsaveisUnidades.xls");

    final ServletOutputStream outputStream = response.getOutputStream();
    final HSSFWorkbook workbook = new HSSFWorkbook();
    final ExcelStyle excelStyle = new ExcelStyle(workbook);

    final HSSFSheet sheet = workbook.createSheet("Responsaveis");
    sheet.setDefaultColumnWidth(20);/*from   ww  w  . ja v  a 2 s .  c  om*/

    final HSSFRow row = sheet.createRow(sheet.getLastRowNum());
    createHeaderCell(excelStyle, row, 0, "Centro de Custo");
    createHeaderCell(excelStyle, row, 1, "Unidade");
    createHeaderCell(excelStyle, row, 2, "Responsvel Aprovao");
    createHeaderCell(excelStyle, row, 4, "Responsvel Despesa");

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 3));
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 5));

    for (final Unit unit : ExpenditureTrackingSystem.getInstance().getTopLevelUnitsSet()) {
        writeUnitResponsibleInfo(excelStyle, sheet, unit);
    }

    workbook.write(outputStream);

    outputStream.flush();
    outputStream.close();

    return null;
}

From source file:pt.ist.expenditureTrackingSystem.presentationTier.actions.organization.OrganizationAction.java

License:Open Source License

private void writeUnitResponsibleInfo(final ExcelStyle excelStyle, final HSSFSheet sheet, final Unit unit,
        final Set<Unit> processed) {
    final int rowIndex = sheet.getLastRowNum() + 1;
    HSSFRow row = sheet.createRow(rowIndex);
    if (unit instanceof CostCenter) {
        final CostCenter costCenter = (CostCenter) unit;
        createCell(excelStyle, row, 0, costCenter.getCostCenter());
    }//from   w  w  w  . j  a v  a 2 s  .co  m
    createCell(excelStyle, row, 1, unit.getName());

    final List<Person>[] approvalsAndAuthorizations = getApprovalsAndAuthorizations(unit);
    final List<Person> approvals = approvalsAndAuthorizations[0];
    final List<Person> authorizations = approvalsAndAuthorizations[1];

    Collections.sort(approvals, Person.COMPARATOR_BY_NAME);
    Collections.sort(authorizations, Person.COMPARATOR_BY_NAME);

    for (int i = 0; i < approvals.size() || i < authorizations.size(); i++) {
        final Person approval = i < approvals.size() ? approvals.get(i) : null;
        final Person authorization = i < authorizations.size() ? authorizations.get(i) : null;

        if (i > 0) {
            row = sheet.createRow(sheet.getLastRowNum() + 1);
        }

        if (approval != null) {
            createCell(excelStyle, row, 2, approval.getUsername());
            createCell(excelStyle, row, 3, approval.getUser().getDisplayName());
        }
        if (authorization != null) {
            createCell(excelStyle, row, 4, authorization.getUsername());
            createCell(excelStyle, row, 5, authorization.getUser().getDisplayName());
        }
    }

    final int totalRows = Math.max(approvals.size(), authorizations.size());
    if (totalRows > 0) {
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + totalRows - 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + totalRows - 1, 1, 1));
    }

    for (final Unit subUnit : sortUnitsByCostCenter(unit.getSubUnitsSet())) {
        if (!processed.contains(subUnit)) {
            processed.add(subUnit);
            if (!(subUnit instanceof Project)) {
                writeUnitResponsibleInfo(excelStyle, sheet, subUnit, processed);
            }
        }
    }
}

From source file:quickreports.masterlist.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from   w  w  w . j a  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, SQLException {
    response.setContentType("text/html;charset=UTF-8");
    //PrintWriter out = response.getWriter();

    /* TODO output your page here. You may use following sample code. */
    //______________________________________________________________________________________
    //                       CREATE THE WORKSHEETS          
    //______________________________________________________________________________________  
    HSSFWorkbook wb = new HSSFWorkbook();

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

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

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

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

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

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

    HSSFSheet shet = wb.createSheet("Masterlist");

    String year = "";

    if (request.getParameter("year") != null) {

        year = request.getParameter("year");

    }
    dbConn conn = new dbConn();
    //========Query 1=================

    HSSFRow rw0 = shet.createRow(1);
    HSSFCell cell = rw0.createCell(0);
    cell.setCellValue("APHIAPLUS Nuru Ya Bonde Supported Sites Summary " + year);
    cell.setCellStyle(style);
    shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

    int count1 = 3;

    String qry1 = "call rpt_masterlist_summary('2015-10-01','2016-09-30','" + year + "')";

    conn.rs = conn.st.executeQuery(qry1);

    ResultSetMetaData metaData = conn.rs.getMetaData();
    int columnCount = metaData.getColumnCount();

    ArrayList mycolumns1 = new ArrayList();

    while (conn.rs.next()) {

        if (count1 == 3) {
            //header rows
            HSSFRow rw = shet.createRow(count1);
            rw.setHeightInPoints(26);
            for (int i = 1; i <= columnCount; i++) {

                mycolumns1.add(metaData.getColumnLabel(i));
                HSSFCell cell0 = rw.createCell(i - 1);
                cell0.setCellValue(metaData.getColumnLabel(i));
                cell0.setCellStyle(stylex);

                //create row header
            } //end of for loop
            count1++;
        } //end of if
          //data rows     
        HSSFRow rw = shet.createRow(count1);

        for (int a = 0; a < columnCount; a++) {
            // System.out.print(mycolumns1.get(a) + ":" + conn.rs.getString("" + mycolumns1.get(a)));

            HSSFCell cell0 = rw.createCell(a);
            if (a > 0) {

                cell0.setCellValue(conn.rs.getInt(mycolumns1.get(a).toString()));

            } else {

                cell0.setCellValue(conn.rs.getString("" + mycolumns1.get(a)));
            }

            cell0.setCellStyle(style2);

        }

        // System.out.println("");
        count1++;
    }

    HSSFRow rw01 = shet.createRow(count1 + 1);
    HSSFCell cell1 = rw01.createCell(0);
    cell1.setCellValue("APHIAPLUS Nuru Ya Bonde supported sites details " + year);
    cell1.setCellStyle(style);
    shet.addMergedRegion(new CellRangeAddress(count1 + 1, count1 + 1, 0, 3));

    //========Query two====Facility Details==============

    String qry = "call rpt_masterlist('2015-10-01','2016-09-30','" + year + "')";

    conn.rs = conn.st.executeQuery(qry);

    metaData = conn.rs.getMetaData();
    columnCount = metaData.getColumnCount();
    int count = count1 + 3;
    ArrayList mycolumns = new ArrayList();

    while (conn.rs.next()) {

        if (count == (count1 + 3)) {
            //header rows
            HSSFRow rw = shet.createRow(count);
            rw.setHeightInPoints(26);
            for (int i = 1; i <= columnCount; i++) {

                mycolumns.add(metaData.getColumnLabel(i));
                HSSFCell cell0 = rw.createCell(i - 1);
                cell0.setCellValue(metaData.getColumnLabel(i));
                cell0.setCellStyle(stylex);

                //create row header
            } //end of for loop
            count++;
        } //end of if
          //data rows     
        HSSFRow rw = shet.createRow(count);

        for (int a = 0; a < columnCount; a++) {
            //System.out.print(mycolumns.get(a) + ":" + conn.rs.getString("" + mycolumns.get(a)));

            HSSFCell cell0 = rw.createCell(a);
            if ((a >= 3 && a <= 14) || (a >= 18 && a <= 22) || (a >= 27 && a <= 29)) {

                cell0.setCellValue(conn.rs.getInt(mycolumns.get(a).toString()));
            } else {
                cell0.setCellValue(conn.rs.getString("" + mycolumns.get(a)));
            }

            cell0.setCellStyle(style2);

        }

        // System.out.println("");
        count++;
    }

    //Autofreeze  || Autofilter  || Remove Gridlines ||  

    shet.setAutoFilter(new CellRangeAddress(count1 + 3, count - 1, 0, columnCount - 1));

    //System.out.println("1,"+rowpos+",0,"+colposcopy);
    for (int i = 0; i <= columnCount; i++) {
        shet.autoSizeColumn(i);
    }

    shet.setDisplayGridlines(false);
    shet.createFreezePane(4, 14);

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

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

    System.out.println("" + "MasterList_Gen_" + createdOn.trim() + ".xls");

    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=" + "MasterList_Gen_" + createdOn.trim() + ".xls");
    OutputStream outStream = response.getOutputStream();
    outStream.write(outArray);
    outStream.flush();

}

From source file:ReadExcel.HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells.//ww w .ja  v  a 2  s .c o  m
 */
private static void testCreateSampleSheet(String outputFilename) throws IOException {
    int rownum;
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    f.setFontHeightInPoints((short) 12);
    f.setColor((short) 0xA);
    f.setBold(true);
    f2.setFontHeightInPoints((short) 10);
    f2.setColor((short) 0xf);
    f2.setBold(true);
    cs.setFont(f);
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.setBorderBottom(BorderStyle.THIN);
    cs2.setFillPattern((short) 1); // fill w fg
    cs2.setFillForegroundColor((short) 0xA);
    cs2.setFont(f2);
    wb.setSheetName(0, "HSSF Test");
    for (rownum = 0; rownum < 300; rownum++) {
        HSSFRow r = s.createRow(rownum);
        if ((rownum % 2) == 0) {
            r.setHeight((short) 0x249);
        }
        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            HSSFCell c = r.createCell(cellnum);
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs);
            }
            c = r.createCell(cellnum + 1);
            c.setCellValue(new HSSFRichTextString("TEST"));
            // 50 characters divided by 1/20th of a point
            s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs2);
            }
        }
    }
    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    HSSFRow r = s.createRow(rownum);
    cs3.setBorderBottom(BorderStyle.THICK);
    for (int cellnum = 0; cellnum < 50; cellnum++) {
        HSSFCell c = r.createCell(cellnum);
        c.setCellStyle(cs3);
    }
    s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3));
    s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110));

    // end draw thick black border
    // create a sheet, set its title then delete it
    wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);
    // end deleted sheet
    FileOutputStream out = new FileOutputStream(outputFilename);
    try {
        wb.write(out);
    } finally {
        out.close();
    }
    wb.close();
}

From source file:reports.allformsreportstracker.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {

    String form = "kmmp";
    String tablename = "";
    if (request.getParameter("form") != null) {
        form = request.getParameter("form");
    }// w w  w  .j a  va  2  s . co m

    //get the table name from the form name

    tablename = form.toLowerCase();
    if (form.equalsIgnoreCase("MOH 711A")) {
        tablename = "moh711";
    }

    //for now, redirect Hei to 711. this will be corrected later
    if (form.equalsIgnoreCase("MOH 711 (New)")) {
        tablename = "moh711_new";
    } else if (tablename.equals("hei")) {
        tablename = "moh711";
    }

    String kmmpor = "subpartnera.KMMP=1";
    String vmmcor = "subpartnera.VMMC=1";
    String genderor = "subpartnera.Gender=1";
    String nutritionor = "subpartnera.Gender=1";
    String tbor = "subpartnera.TB=1";
    String form711or = "subpartnera.HTC=1 OR subpartnera.PMTCT OR subpartnera.FP OR subpartnera.Maternity";
    //Maureen to add more conditions in 711 or

    String orquery = kmmpor;

    if (form.equalsIgnoreCase("KMMP")) {
        orquery = kmmpor;
    } else if (form.equalsIgnoreCase("VMMC")) {
        orquery = vmmcor;
    } else if (form.equalsIgnoreCase("Gender")) {
        orquery = genderor;
    } else if (form.equalsIgnoreCase("Nutrition")) {
        orquery = nutritionor;
    } else if (form.equalsIgnoreCase("MOH 711A")) {
        orquery = form711or;
    } else if (form.equalsIgnoreCase("TB")) {
        orquery = tbor;
    } else {
        orquery = " subpartnera.PMTCT=1 OR subpartnera.ART=1 OR subpartnera.PEP=1 ";
    } //731

    dbConn conn = new dbConn();
    session = request.getSession();

    year = Integer.parseInt(request.getParameter("year"));
    //        year=2015;
    prevYear = year - 1;
    IdGenerator IG = new IdGenerator();
    allMonths.clear();
    allReports.clear();
    duration = "WHERE (" + tablename + ".yearmonth BETWEEN " + prevYear + "" + 10 + " AND " + year
            + "09) AND ( " + orquery + " )";

    currentMonth = IG.CurrentMonth();

    monthsData = "";
    //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet shet1 = wb.createSheet(form + " REPORTS TRACKER");

    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Arial Black");
    font.setColor((short) 0000);
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont font2 = wb.createFont();
    font2.setFontName("Arial Black");
    font2.setColor((short) 0000);
    CellStyle style2 = wb.createCellStyle();
    style2.setFont(font2);

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

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

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

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

    HSSFFont fontHeader = wb.createFont();
    fontHeader.setColor(HSSFColor.DARK_BLUE.index);
    styleHeader.setFont(fontx);
    styleHeader.setWrapText(true);

    for (int i = 0; i <= 2; i++) {
        shet1.setColumnWidth(i, 8000);
    }
    HSSFRow rw1S1 = shet1.createRow(0);
    HSSFCell S1cell = rw1S1.createCell(0);
    S1cell.setCellValue("COUNTY NAME");
    S1cell.setCellStyle(stylex);

    HSSFCell S1cellX = rw1S1.createCell(1);
    S1cellX.setCellValue("SUB COUNTY");
    S1cellX.setCellStyle(stylex);

    S1cellX = rw1S1.createCell(2);
    S1cellX.setCellValue("HEALTH FACILITY");
    S1cellX.setCellStyle(stylex);

    S1cellX = rw1S1.createCell(3);
    S1cellX.setCellValue("MFL CODE");
    S1cellX.setCellStyle(stylex);

    S1cellX = rw1S1.createCell(4);
    S1cellX.setCellValue("EXPECTED REPORTS");
    S1cellX.setCellStyle(stylex);

    counterHeader = 5;
    String getMaxandMin = "SELECT DISTINCT(month.name),month.id FROM " + tablename + " JOIN month ON "
            + tablename + ".Mois=month.id JOIN subpartnera ON " + tablename
            + ".SubPartnerID=subpartnera.SubPartnerID " + duration + " " + " ORDER BY  " + tablename
            + ".yearmonth";
    conn.rs = conn.st.executeQuery(getMaxandMin);
    while (conn.rs.next()) {
        monthName = conn.rs.getString(1);
        monthid = conn.rs.getInt(2);
        if (monthid <= 9) {
            currentYear = year;
        } else {
            currentYear = prevYear;
        }
        System.out.println(" Months are : " + monthName);
        allMonths.add(monthName);
        allReports.add(0);
        S1cellX = rw1S1.createCell(counterHeader);
        S1cellX.setCellValue(monthName);
        S1cellX.setCellStyle(stylex);
        counterHeader++;
    }
    prevFacility = currentFacility = "";
    currentDistrict = prevDistrict = "";
    currentCounty = prevCounty = "";
    counter = districtCounter = countyCounter = districtsMerged = 0;
    arraySize = allReports.size();

    if (allMonths.size() > 0) {
        String checkReports = "SELECT county.County,district.DistrictNom,subpartnera.SubPartnerNom,"
                + "subpartnera.CentreSanteId,COUNT(" + tablename
                + ".SubPartnerID),month.name,subpartnera.SubPartnerID " + "FROM subpartnera " + "LEFT JOIN "
                + tablename + " ON " + tablename + ".SubPartnerID=subpartnera.SubPartnerID "
                + "LEFT JOIN district ON subpartnera.DistrictID=district.DistrictID "
                + "LEFT JOIN county ON county.CountyID=district.CountyID " + "LEFT JOIN month ON " + tablename
                + ".Mois=month.id " + " " + duration + " " + " GROUP BY subpartnera.SubPartnerNom," + tablename
                + ".Annee," + tablename + ".Mois "
                + "ORDER BY county.County,district.DistrictNom,subpartnera.SubPartnerNom," + tablename
                + ".Mois";
        System.out.println(checkReports);
        conn.rs = conn.st.executeQuery(checkReports);
        while (conn.rs.next()) {
            countyName = conn.rs.getString(1);
            districtName = conn.rs.getString(2);
            facilityName = conn.rs.getString(3);
            mflcode = conn.rs.getString(4);
            status = conn.rs.getInt(5);
            selectedMonth = conn.rs.getString(6);
            currentFacility = conn.rs.getString(7);
            currentDistrict = districtName;
            currentCounty = countyName;
            //     CHECK WHERE TO PLACE THE NUMBER; 

            monthPosition = allMonths.indexOf(selectedMonth);

            if (!prevFacility.equals(currentFacility)) {

                if (!prevDistrict.equals(currentDistrict) && !prevDistrict.equals("")) {
                    counter++;
                    noReports = districtCounter + 1;
                    HSSFRow rwTotal = shet1.createRow(counter);
                    HSSFCell SX = rwTotal.createCell(0);
                    SX.setCellStyle(stborder);

                    SX = rwTotal.createCell(1);
                    SX.setCellValue(prevDistrict + " TOTALS : ");
                    SX.setCellStyle(styleHeader);

                    SX = rwTotal.createCell(2);
                    SX.setCellStyle(styleHeader);

                    SX = rwTotal.createCell(3);
                    SX.setCellStyle(styleHeader);

                    SX = rwTotal.createCell(4);
                    SX.setCellValue(noReports);
                    SX.setCellStyle(styleHeader);
                    shet1.addMergedRegion(new CellRangeAddress(counter, counter, 1, 3));

                    for (int j = 0; j < allReports.size(); j++) {
                        //         System.out.println("district name : "+prevDistrict+" no of reports : "+allReports.get(j).toString()+" for month : "+allMonths.get(j).toString());
                        int dataPos = 5 + j;
                        SX = rwTotal.createCell(dataPos);
                        SX.setCellValue(Integer.parseInt(allReports.get(j).toString()));
                        SX.setCellStyle(styleHeader);
                    }
                    for (int k = 0; k < arraySize; k++) {
                        allReports.set(k, 0);
                    }
                }
                counter++;
                HSSFRow rw1 = shet1.createRow(counter);
                HSSFCell S1 = rw1.createCell(0);
                S1.setCellValue(countyName);
                S1.setCellStyle(stborder);

                S1 = rw1.createCell(1);
                S1.setCellValue(districtName);
                S1.setCellStyle(stborder);

                S1 = rw1.createCell(2);
                S1.setCellValue(facilityName);
                S1.setCellStyle(stborder);

                S1 = rw1.createCell(3);
                S1.setCellValue(mflcode);
                S1.setCellStyle(stborder);

                S1 = rw1.createCell(4);
                S1.setCellValue(1);
                S1.setCellStyle(stborder);

                for (int j = 0; j < allMonths.size(); j++) {
                    int cellPos = j + 5;
                    S1 = rw1.createCell(cellPos);
                    // System.out.println("counter : "+counter+" datapos : "+cellPos+" status : "+status);
                    S1.setCellStyle(stborder);

                }
                int dataPos = 5 + monthPosition;
                S1 = rw1.getCell(dataPos);
                S1.setCellValue(status);

                if (!prevDistrict.equals(currentDistrict) && !prevDistrict.equals("")) {
                    int distStart = counter - districtCounter - 2;
                    int distEnd = counter - 2;
                    shet1.addMergedRegion(new CellRangeAddress(distStart, distEnd, 1, 1));
                    districtsMerged++;
                    districtCounter = 0;

                    for (int j = 0; j < arraySize; j++) {
                        allReports.set(j, 0);
                    }

                } else {
                    if (counter == 1) {
                    } else {
                        districtCounter++;
                    }
                }
                if (!prevCounty.equals(currentCounty) && !prevCounty.equals("")) {
                    int countyStart = counter - countyCounter - districtsMerged - 1;
                    int countyEnd = counter - 1;
                    shet1.addMergedRegion(new CellRangeAddress(countyStart, countyEnd, 0, 0));
                    countyCounter = 0;
                    districtsMerged = 0;
                } else {
                    if (counter == 1) {
                    } else {
                        countyCounter++;
                    }
                }
                prevCounty = currentCounty;
                prevDistrict = currentDistrict;
            } else {

                HSSFRow rw1 = shet1.getRow(counter);
                int dataPos = 5 + monthPosition;
                HSSFCell S1 = rw1.getCell(dataPos);
                S1.setCellValue(status);
                //    
            }

            if (status == 1) {
                int currentData = Integer.parseInt(allReports.get(monthPosition).toString()) + 1;
                allReports.set(monthPosition, currentData);
            }

            prevFacility = currentFacility;

        }
        //    MATCH THE LAST DISTRICTS
        counter++;
        noReports = districtCounter + 1;
        HSSFRow rwTotal = shet1.createRow(counter);
        HSSFCell SX = rwTotal.createCell(0);
        SX.setCellStyle(stborder);

        SX = rwTotal.createCell(1);
        SX.setCellValue(prevDistrict + " TOTALS : ");
        SX.setCellStyle(styleHeader);

        SX = rwTotal.createCell(2);
        SX.setCellStyle(styleHeader);

        SX = rwTotal.createCell(3);
        SX.setCellStyle(styleHeader);

        SX = rwTotal.createCell(4);
        SX.setCellValue(noReports);
        SX.setCellStyle(styleHeader);
        shet1.addMergedRegion(new CellRangeAddress(counter, counter, 1, 3));

        int distStart = counter - districtCounter - 1;
        int distEnd = counter - 1;
        //     System.out.println("MERGE BETWEEN : START : "+distStart+" END : "+distEnd);
        shet1.addMergedRegion(new CellRangeAddress(distStart, distEnd, 1, 1));
        districtCounter = 0;

        int countyStart = counter - countyCounter - 2;
        int countyEnd = counter;
        //        System.out.println("MERGE BETWEEN : START : "+distStart+" END : "+distEnd);
        shet1.addMergedRegion(new CellRangeAddress(countyStart, countyEnd, 0, 0));
        countyCounter = 0;

        for (int j = 0; j < allReports.size(); j++) {
            //         System.out.println("district name : "+prevDistrict+" no of reports : "+allReports.get(j).toString()+" for month : "+allMonths.get(j).toString());
            int dataPos = 5 + j;
            SX = rwTotal.createCell(dataPos);
            SX.setCellValue(Integer.parseInt(allReports.get(j).toString()));
            SX.setCellStyle(styleHeader);
        }

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

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

        createdOn = IG.CreatedOn();

        // 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=" + tablename + "_ReportsTracker_YEAR("
                + year + ")_Generated_on_" + createdOn.trim() + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
    } else {

        session.setAttribute("noTrackerReport",
                "<font color=\"red\"><b>SORRY:</b> No report was found for " + year + ".</red>");
        response.sendRedirect("reportsTracker.jsp");
    }

}