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

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

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

Introduction

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

Prototype

public static CellRangeAddress valueOf(String ref)

Source Link

Document

Creates a CellRangeAddress from a cell range reference string.

Usage

From source file:demo.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*  w w w  .  j a  v a2s .  c o m*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new HSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                // the 10th cell contains sum over week days, e.g.
                // SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    // finally set column widths, the width is measured in units of 1/256th
    // of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

    // Write the output to a file
    String file = "target/timesheet.xls";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:Demos.CalendarDemo.java

License:Apache License

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

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (int i = 0; i < args.length; i++) {
        if (args[i].charAt(0) == '-') {
            xlsx = args[i].equals("-xlsx");
        } else {//  www .  ja  va  2s.  c o  m
            calendar.set(Calendar.YEAR, Integer.parseInt(args[i]));
        }
    }
    int year = calendar.get(Calendar.YEAR);

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

    Map<String, CellStyle> 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
        Sheet sheet = wb.createSheet(months[month]);

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

        // the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        // the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        Cell 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
        Row monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            // set column widths, the width is measured 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));
            Cell 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++) {
            Row row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                Cell dayCell_1 = row.createCell(i * 2);
                Cell 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
    String file = "C:\\Users\\BaldiniHP\\Desktop\\calendar.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:domain.Excel.java

public void bordes(String rango, short borde) {

        HSSFRegionUtil.setBorderTop(borde, CellRangeAddress.valueOf(rango), (HSSFSheet) sheet, wb);
        HSSFRegionUtil.setBorderLeft(borde, CellRangeAddress.valueOf(rango), (HSSFSheet) sheet, wb);
        HSSFRegionUtil.setBorderRight(borde, CellRangeAddress.valueOf(rango), (HSSFSheet) sheet, wb);
        HSSFRegionUtil.setBorderBottom(borde, CellRangeAddress.valueOf(rango), (HSSFSheet) sheet, wb);
    }/*from  w  w w.j a v a  2 s .c  o m*/

From source file:domain.Excel.java

public void relleno(String rango, short color_relleno, short color_letra) {

        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        ConditionalFormattingRule rule1 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(color_relleno);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        FontFormatting font = rule1.createFontFormatting();
        font.setFontStyle(false, true);//  w w w. ja v a  2 s  . c  o m
        font.setFontColorIndex(color_letra);

        CellRangeAddress[] regions = { CellRangeAddress.valueOf(rango) };

        sheetCF.addConditionalFormatting(regions, rule1, rule1);
    }

From source file:domain.Excel.java

private void combinarRango(String rango) {

        sheet.addMergedRegion(CellRangeAddress.valueOf(rango));
    }

From source file:domain.Excel.java

public void reporteSesionPeso() {
        //        cargarLogo();

        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:E1"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("A2:E2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("A3:E4"));

        /*Name REPORT*/
        HSSFFont FontNameReport = wb.createFont();
        FontNameReport.setFontName("Calibri");
        FontNameReport.setFontHeightInPoints((short) 11);
        FontNameReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        FontNameReport.setColor(HSSFColor.DARK_RED.index);

        HSSFCellStyle styleNameReport = wb.createCellStyle();
        styleNameReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleNameReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleNameReport.setFont(FontNameReport);

        Row row = sheet.createRow(0);/*from  w  w w .  j  a  va  2s . c  om*/
        Cell cell = row.createCell(0);
        cell.setCellValue("REPORTE DE SESIONES POR PESOS");
        cell.setCellStyle(styleNameReport);
        /**/

        /*DATE REPORT*/
        HSSFFont FontDateReport = wb.createFont();
        FontDateReport.setFontName("Calibri");
        FontDateReport.setFontHeightInPoints((short) 10);
        FontDateReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        FontDateReport.setColor(HSSFColor.BLACK.index);

        HSSFCellStyle styleDateReport = wb.createCellStyle();
        styleDateReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleDateReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleDateReport.setFont(FontDateReport);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue("FECHA DE REPORTE: " + formatoDateTime.format(new Date()));
        cell.setCellStyle(styleDateReport);

        /*Etiqueta parametro*/
        HSSFFont FontParametroReport = wb.createFont();
        FontParametroReport.setFontName("Calibri");
        FontParametroReport.setFontHeightInPoints((short) 9);
        FontParametroReport.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        FontParametroReport.setColor(HSSFColor.BLACK.index);

        HSSFCellStyle styleParamReport = wb.createCellStyle();
        styleParamReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleParamReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleParamReport.setFont(FontParametroReport);

        row = sheet.createRow(2);
        cell = row.createCell(0);
        String etiqueta_parametro = "";

        switch (tipo) {
        case 1:
            etiqueta_parametro = "Sesion de dia " + formatoDate.format(fecha_ini);
            break;
        case 2:
            etiqueta_parametro = "Sesiones del " + formatoDate.format(fecha_ini) + " al "
                    + formatoDate.format(fecha_fin);
            break;
        case 3:
            etiqueta_parametro = "Sesiones del animal " + animal.arete_visual;
            break;
        }

        cell.setCellValue(etiqueta_parametro);
        cell.setCellStyle(styleParamReport);

        /**/
        sheet.createRow(5).createCell(0).setCellValue("Arete Visual");
        sheet.getRow(5).createCell(1).setCellValue("Arete Electronico");
        sheet.getRow(5).createCell(2).setCellValue("Fecha");
        sheet.getRow(5).createCell(3).setCellValue("Peso");
        sheet.getRow(5).createCell(4).setCellValue("Corral");

        sheet.setColumnWidth(0, 16 * Unidad);
        sheet.setColumnWidth(1, 16 * Unidad);
        sheet.setColumnWidth(2, 16 * Unidad);
        sheet.setColumnWidth(3, 16 * Unidad);
        sheet.setColumnWidth(4, 16 * Unidad);

        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        //Condition 1: Cell Value Is   greater than  70   (Blue Fill)
        ConditionalFormattingRule rule1 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.DARK_RED.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        //Condition 2: Cell Value Is  less than      50   (Green Fill)
        ConditionalFormattingRule rule2 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.LT, "50");
        PatternFormatting fill2 = rule2.createPatternFormatting();
        fill2.setFillBackgroundColor(IndexedColors.DARK_RED.index);
        fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        FontFormatting font = rule1.createFontFormatting();
        font.setFontStyle(false, true);
        font.setFontColorIndex(IndexedColors.WHITE.index);

        CellRangeAddress[] regions = { CellRangeAddress.valueOf("A6:E6") };

        sheetCF.addConditionalFormatting(regions, rule1, rule2);

        Integer fila_inicial = 6;

        for (int i = 0; i < this.t_tabla.getRowCount(); i++) {

            sheet.createRow(fila_inicial + i).createCell(0).setCellValue(t_tabla.getValueAt(i, 1).toString());
            sheet.getRow(fila_inicial + i).getCell(0).setCellStyle(styleCenter);

            for (int j = 1; j < 5; j++) {
                sheet.getRow(fila_inicial + i).createCell(j).setCellValue(t_tabla.getValueAt(i, j + 1).toString());
                sheet.getRow(fila_inicial + i).getCell(j).setCellStyle(styleCenter);
            }

            sheet.getRow(fila_inicial + i).getCell(3)
                    .setCellValue(Double.parseDouble(t_tabla.getValueAt(i, 4).toString()));
            sheet.getRow(fila_inicial + i).getCell(3).setCellStyle(styleRight);

        }
        cargarLogo();
    }

From source file:domain.Excel.java

private void reporteSesionMedicina() {
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:I1"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("A2:I2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("A3:I4"));

        /*Name REPORT*/
        HSSFFont FontNameReport = wb.createFont();
        FontNameReport.setFontName("Calibri");
        FontNameReport.setFontHeightInPoints((short) 11);
        FontNameReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        FontNameReport.setColor(HSSFColor.DARK_RED.index);

        HSSFCellStyle styleNameReport = wb.createCellStyle();
        styleNameReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleNameReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleNameReport.setFont(FontNameReport);

        Row row = sheet.createRow(0);/*  www .  j a va 2 s  . c o m*/
        Cell cell = row.createCell(0);
        cell.setCellValue("REPORTE DE SESIONES POR MEDICAMENTOS");
        cell.setCellStyle(styleNameReport);
        /**/

        /*DATE REPORT*/
        HSSFFont FontDateReport = wb.createFont();
        FontDateReport.setFontName("Calibri");
        FontDateReport.setFontHeightInPoints((short) 10);
        FontDateReport.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        FontDateReport.setColor(HSSFColor.BLACK.index);

        HSSFCellStyle styleDateReport = wb.createCellStyle();
        styleDateReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleDateReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleDateReport.setFont(FontDateReport);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue("FECHA DE REPORTE: " + formatoDateTime.format(new Date()));
        cell.setCellStyle(styleDateReport);

        /*Etiqueta parametro*/
        HSSFFont FontParametroReport = wb.createFont();
        FontParametroReport.setFontName("Calibri");
        FontParametroReport.setFontHeightInPoints((short) 9);
        FontParametroReport.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        FontParametroReport.setColor(HSSFColor.BLACK.index);

        HSSFCellStyle styleParamReport = wb.createCellStyle();
        styleParamReport.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleParamReport.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleParamReport.setFont(FontParametroReport);

        row = sheet.createRow(2);
        cell = row.createCell(0);
        String etiqueta_parametro = "";

        switch (tipo) {
        case 1:
            etiqueta_parametro = "Sesion de dia " + formatoDate.format(fecha_ini);
            break;
        case 2:
            etiqueta_parametro = "Sesiones del " + formatoDate.format(fecha_ini) + " al "
                    + formatoDate.format(fecha_fin);
            break;
        case 3:
            etiqueta_parametro = "Sesiones del animal " + animal.arete_visual;
            break;
        }

        cell.setCellValue(etiqueta_parametro);
        cell.setCellStyle(styleParamReport);

        /**/
        sheet.createRow(5).createCell(0).setCellValue("Arete Visual");
        sheet.getRow(5).createCell(1).setCellValue("Arete Electronico");
        sheet.getRow(5).createCell(2).setCellValue("Codigo");
        sheet.getRow(5).createCell(3).setCellValue("Medicamento");
        sheet.getRow(5).createCell(4).setCellValue("Fecha");
        sheet.getRow(5).createCell(5).setCellValue("Corral");
        sheet.getRow(5).createCell(6).setCellValue("Dosis");
        sheet.getRow(5).createCell(7).setCellValue("Costo");
        sheet.getRow(5).createCell(8).setCellValue("Importe");

        sheet.setColumnWidth(0, 15 * Unidad);
        sheet.setColumnWidth(1, 20 * Unidad);
        sheet.setColumnWidth(2, 15 * Unidad);
        sheet.setColumnWidth(3, 20 * Unidad);
        sheet.setColumnWidth(4, 20 * Unidad);
        sheet.setColumnWidth(5, 20 * Unidad);
        sheet.setColumnWidth(6, 15 * Unidad);
        sheet.setColumnWidth(7, 15 * Unidad);
        sheet.setColumnWidth(8, 15 * Unidad);

        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        //Condition 1: Cell Value Is   greater than  70   (Blue Fill)
        ConditionalFormattingRule rule1 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.GT, "1000");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.DARK_RED.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        //Condition 2: Cell Value Is  less than      50   (Green Fill)
        ConditionalFormattingRule rule2 = sheetCF
                .createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.LT, "50");
        PatternFormatting fill2 = rule2.createPatternFormatting();
        fill2.setFillBackgroundColor(IndexedColors.DARK_RED.index);
        fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

        FontFormatting font = rule1.createFontFormatting();
        font.setFontStyle(false, true);
        font.setFontColorIndex(IndexedColors.WHITE.index);

        CellRangeAddress[] regions = { CellRangeAddress.valueOf("A6:I6") };

        sheetCF.addConditionalFormatting(regions, rule1, rule2);
        Integer fila_inicial = 6;

        for (int i = 0; i < this.t_tabla.getRowCount(); i++) {
            agregarValor(fila_inicial + i, 0, t_tabla.getValueAt(i, 1).toString(), styleCenter);

            for (int j = 0; j < 9; j++) {
                agregarValor(fila_inicial + i, j, t_tabla.getValueAt(i, j + 1).toString(), styleCenter);
            }
        }
        cargarLogo();
    }

From source file:edu.duke.cabig.c3pr.service.Summary3ReportServiceTest.java

License:BSD License

public void testGenerateEXCEL() throws Exception {
    HealthcareSite hcs = healthcareSiteDao.getById(1100);

    DateFormat format = new SimpleDateFormat("MM/dd/yyyy");
    Date startDate = (Date) format.parse("01/11/1990");
    Date endDate = (Date) format.parse("01/01/2007");
    String grantNumber = "GRANT-NO 1232";

    Summary3Report summary3Report = new Summary3Report(hcs, grantNumber, startDate, endDate);
    String reportingSource = healthcareSiteDao.getById(1000).getName();
    summary3Report.setReportingSource(reportingSource);

    summaryReportService.buildSummary3Report(summary3Report);

    String xmlString = summaryReportService.generateXML(summary3Report);
    File file = new File("testReport.xml");

    FileWriter fileWriter = new FileWriter(file);
    fileWriter.write(xmlString);/*w  w  w .  j  a v  a  2  s . com*/
    fileWriter.flush();
    fileWriter.close();
    System.out.println(xmlString);

    // creating the workbook and the spreadsheet

    try {

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        InputStream inputStream = new FileInputStream(file);
        Document document = builder.parse(inputStream);

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report");

        PrintSetup printSetup = spreadSheet.getPrintSetup();
        printSetup.setLandscape(true);
        spreadSheet.setFitToPage(true);
        spreadSheet.setHorizontallyCenter(true);

        spreadSheet.setColumnWidth((short) 0, (short) (60 * 256));
        spreadSheet.setColumnWidth((short) 1, (short) (15 * 256));
        spreadSheet.setColumnWidth((short) 2, (short) (30 * 256));

        HSSFRow titleRow = spreadSheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell((short) 0);
        titleRow.setHeightInPoints(40);

        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER);
        titleCell.setCellStyle(titleCellStyle);

        String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null
                && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null)
                        ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue()
                        : "";
        titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols"
                + "                      " + nullSafeGrantNumber);

        HSSFRow orgRow = spreadSheet.createRow(1);
        orgRow.setHeightInPoints(30);
        HSSFCell organizationCell = orgRow.createCell((short) 0);

        HSSFCellStyle orgCellStyle = wb.createCellStyle();
        orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        orgCellStyle.setFont(font);
        organizationCell.setCellStyle(orgCellStyle);
        organizationCell
                .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0)))
                        .getElementsByTagName("name").item(0).getFirstChild().getNodeValue());

        HSSFRow reportingPeriodRow = spreadSheet.createRow(2);
        HSSFFont reportingPeriodFont = wb.createFont();
        reportingPeriodFont.setFontHeightInPoints((short) 9);
        HSSFCellStyle reportingPeriodStyle = wb.createCellStyle();
        reportingPeriodStyle.setFont(reportingPeriodFont);

        reportingPeriodRow.setHeightInPoints(20);
        HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0);
        reportingPeriodCell.setCellStyle(titleCellStyle);

        reportingPeriodCell.setCellValue("Reporting Period "
                + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - "
                + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue()));

        // creating the first row of table the table header
        HSSFRow row = spreadSheet.createRow(3);
        HSSFCell tableHeaderCell1 = row.createCell((short) 0);
        HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle();
        tableHeaderCellStyle1.setWrapText(true);

        tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        tableHeaderCellStyle1.setFont(font);
        tableHeaderCell1.setCellStyle(tableHeaderCellStyle1);
        tableHeaderCell1.setCellValue("Disease Site");

        // creating table header 2nd & 3rd cells

        HSSFCell tableHeaderCell2 = row.createCell((short) 1);
        HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle();

        tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setWrapText(true);

        tableHeaderCell2.setCellStyle(tableHeaderCellStyle2);
        tableHeaderCell2.setCellValue("Newly Registered Patients");

        HSSFCell tableHeaderCell3 = row.createCell((short) 2);

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

        tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setWrapText(true);
        tableHeaderCell3.setCellStyle(tableHeaderCellStyle3);
        tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols");

        NodeList nodeList = document.getElementsByTagName("reportData");

        HSSFCellStyle tableCellStyle = wb.createCellStyle();
        tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3"));
        for (int i = 4; i < nodeList.getLength() + 4; i++) {

            row = spreadSheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);
            if (i == (4 + nodeList.getLength() - 1)) {
                HSSFCellStyle totalCellStyle = wb.createCellStyle();
                totalCellStyle.setFont(font);
                totalCellStyle.setRightBorderColor((short) 10);
                totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
                tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
                cell.setCellStyle(totalCellStyle);
            } else {
                cell.setCellStyle(tableCellStyle);
            }
            cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0))
                    .getAttribute("name"));

            cell = row.createCell((short) 1);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue("");

            cell = row.createCell((short) 2);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue(
                    ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3)))
                            .getFirstChild().getNodeValue());
        }
        File outputFile = new File(System.getProperty("user.home") + File.separator + "Summary3Report.xls");
        FileOutputStream output = new FileOutputStream(outputFile);
        wb.write(output);
        output.flush();
        output.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParserConfigurationException e) {
        e.printStackTrace();
    } catch (SAXException e) {
        e.printStackTrace();
    }

}

From source file:edu.duke.cabig.c3pr.xml.Summary3ReportGenerator.java

License:BSD License

public void generateExcel(String summary3ReportXml, String file) throws Exception {

    // creating the workbook and the spreadsheet
    try {//w  w  w. j  a  v  a2 s .  c  o  m

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        String fileName = "";

        fileName = file;

        InputStream inputStream = new FileInputStream((fileName + ".xml"));
        Document document = builder.parse(inputStream);

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report");

        PrintSetup printSetup = spreadSheet.getPrintSetup();
        printSetup.setLandscape(true);
        spreadSheet.setFitToPage(true);
        spreadSheet.setHorizontallyCenter(true);

        spreadSheet.setColumnWidth((short) 0, (short) (60 * 256));
        spreadSheet.setColumnWidth((short) 1, (short) (15 * 256));
        spreadSheet.setColumnWidth((short) 2, (short) (30 * 256));

        HSSFRow titleRow = spreadSheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell((short) 0);
        titleRow.setHeightInPoints(40);

        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER);
        titleCell.setCellStyle(titleCellStyle);

        String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null
                && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null)
                        ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue()
                        : "";
        titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols"
                + "                      " + nullSafeGrantNumber);

        HSSFRow orgRow = spreadSheet.createRow(1);
        orgRow.setHeightInPoints(30);
        HSSFCell organizationCell = orgRow.createCell((short) 0);

        HSSFCellStyle orgCellStyle = wb.createCellStyle();
        orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        orgCellStyle.setFont(font);
        organizationCell.setCellStyle(orgCellStyle);
        organizationCell
                .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0)))
                        .getElementsByTagName("name").item(0).getFirstChild().getNodeValue());

        HSSFRow reportingPeriodRow = spreadSheet.createRow(2);
        HSSFFont reportingPeriodFont = wb.createFont();
        reportingPeriodFont.setFontHeightInPoints((short) 9);
        HSSFCellStyle reportingPeriodStyle = wb.createCellStyle();
        reportingPeriodStyle.setFont(reportingPeriodFont);

        reportingPeriodRow.setHeightInPoints(20);
        HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0);
        reportingPeriodCell.setCellStyle(titleCellStyle);

        reportingPeriodCell.setCellValue("Reporting Period "
                + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - "
                + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue()));

        // creating the first row of table the table header
        HSSFRow row = spreadSheet.createRow(3);
        HSSFCell tableHeaderCell1 = row.createCell((short) 0);
        HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle();
        tableHeaderCellStyle1.setWrapText(true);

        tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        tableHeaderCellStyle1.setFont(font);
        tableHeaderCell1.setCellStyle(tableHeaderCellStyle1);
        tableHeaderCell1.setCellValue("Disease Site");

        // creating table header 2nd & 3rd cells

        HSSFCell tableHeaderCell2 = row.createCell((short) 1);
        HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle();

        tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setWrapText(true);

        tableHeaderCell2.setCellStyle(tableHeaderCellStyle2);
        tableHeaderCell2.setCellValue("Newly Registered Patients");

        HSSFCell tableHeaderCell3 = row.createCell((short) 2);

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

        tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setWrapText(true);
        tableHeaderCell3.setCellStyle(tableHeaderCellStyle3);
        tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols");

        NodeList nodeList = document.getElementsByTagName("reportData");

        HSSFCellStyle tableCellStyle = wb.createCellStyle();
        tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3"));
        for (int i = 4; i < nodeList.getLength() + 4; i++) {

            row = spreadSheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);
            if (i == (4 + nodeList.getLength() - 1)) {
                HSSFCellStyle totalCellStyle = wb.createCellStyle();
                totalCellStyle.setFont(font);
                totalCellStyle.setRightBorderColor((short) 10);
                totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
                tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
                cell.setCellStyle(totalCellStyle);
            } else {
                cell.setCellStyle(tableCellStyle);
            }
            cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0))
                    .getAttribute("name"));

            cell = row.createCell((short) 1);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue("");

            cell = row.createCell((short) 2);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue(
                    ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3)))
                            .getFirstChild().getNodeValue());
        }
        FileOutputStream output = new FileOutputStream(new File(addExtension(file, "Excel")));
        wb.write(output);
        output.flush();
        output.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParserConfigurationException e) {
        e.printStackTrace();
    } catch (SAXException e) {
        e.printStackTrace();
    }
}

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

License:Open Source License

/**
 * Permet d'indiquer que toutes les lignes de l1  l2 seront imprims sur toutes les pages
 * //from   w w  w  .j a  v a2  s .  c  om
 *  Attention : l1 et l2 sont 1-based index 
 */
public void setRepeatingRow(int l1, int l2) {
    sheet.setRepeatingRows(CellRangeAddress.valueOf(l1 + ":" + l2));
}