Example usage for org.apache.poi.ss.usermodel Sheet setAutobreaks

List of usage examples for org.apache.poi.ss.usermodel Sheet setAutobreaks

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. setAutobreaks

Introduction

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

Prototype

void setAutobreaks(boolean value);

Source Link

Document

Flag indicating whether the sheet displays Automatic Page Breaks.

Usage

From source file:demo.poi.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Business Plan");

    // turn off gridlines
    sheet.setDisplayGridlines(false);/*from  ww w . j av a 2 s .c  om*/
    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(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    // columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    // freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            String str = data[i][j];
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    // group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    // set column widths, the width is measured in units of 1/256th of a
    // character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "target/businessplan.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  .  j a va 2  s . c  om
            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:Documentos.ClaseAlmacenGeneral.java

public void crearExcel() {
    try {//from  w w w.  ja  v  a2  s .c  o  m

        // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet1 = wb.createSheet("Productos");
        Sheet sheet2 = wb.createSheet("hoja2");
        Sheet sheet3 = wb.createSheet("hoja3");

        // quito las lineas del libro para darle un mejor acabado
        //            sheet.setDisplayGridlines(false);
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        // creo una nueva fila
        Row trow = sheet1.createRow((short) 0);
        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual());

        // Creo la cabecera de mi listado en Excel
        Row row = sheet1.createRow((short) 2);

        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true,
                true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true,
                true);

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", "");

        try ( // Creamos un Statement para poder hacer peticiones a la bd
                Statement stat = con.createStatement()) {
            ResultSet resultado = stat.executeQuery(
                    "select idProducto, nombre, cantidad,UnidadExistencia,minStock  where tipoProducto=2 from producto");
            while (resultado.next()) {

                //creamos la fila
                Row fila = sheet1.createRow(3 + i);

                String idProducto = String.valueOf(resultado.getString("idProducto"));
                String nombre = String.valueOf(resultado.getString("nombre"));
                String cantidad = String.valueOf(resultado.getInt("cantidad"));
                String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia"));
                String minStock = String.valueOf(resultado.getInt("minStock"));
                // Creo las celdas de mi fila, se puede poner un diseo a la celda
                System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - "
                        + UnidadExistencia + " - " + minStock);

                creandoCelda(wb, fila, 0, idProducto);
                creandoCelda(wb, fila, 1, nombre);
                creandoCelda(wb, fila, 2, cantidad);
                creandoCelda(wb, fila, 3, UnidadExistencia);
                creandoCelda(wb, fila, 4, minStock);
                i++;
            }
        }
        con.close();

        //            Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //            la celda se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.autoSizeColumn(0);
        ssheet.autoSizeColumn(1);
        ssheet.autoSizeColumn(2);
        ssheet.autoSizeColumn(3);
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);
        ssheet.autoSizeColumn(7);

        //Ajustando la hoja de una pagina

        Sheet sheet = wb.createSheet("format sheet");
        PrintSetup ps = sheet.getPrintSetup();
        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        //Area de impresion
        wb.setPrintArea(0, 0, 1, 0, 9);

        String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports"
                + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls";
        //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls";

        try (FileOutputStream fileOut = new FileOutputStream(strRuta)) {
            wb.write(fileOut);
        }
        JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta);

    } catch (Exception e) {
        e.printStackTrace();
        //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo");
    }
}

From source file:Documentos.ClaseAlmacenProducto.java

public void crearExcel() {
    try {//from   w  ww  .  ja v  a 2  s  . co m

        // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet1 = wb.createSheet("Productos");
        Sheet sheet2 = wb.createSheet("hoja2");
        Sheet sheet3 = wb.createSheet("hoja3");

        // quito las lineas del libro para darle un mejor acabado
        //            sheet.setDisplayGridlines(false);
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        // creo una nueva fila
        Row trow = sheet1.createRow((short) 0);
        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "Productos existentes-Repostera AnaIS " + ControllerFechas.getFechaActual());

        // Creo la cabecera de mi listado en Excel
        Row row = sheet1.createRow((short) 2);

        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true,
                true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Precio de Venta", true,
                true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Ganancia", true, true);
        createCell(wb, row, 5, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true);

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", "");

        try ( // Creamos un Statement para poder hacer peticiones a la bd
                Statement stat = con.createStatement()) {
            ResultSet resultado = stat.executeQuery(
                    "select idproducto,nombre,format(cantidad,0) as cantidad,preciocompra,precioVenta,concat('$ ',format(((precioventa-preciocompra)*cantidad),2)) as ganancia, UnidadExistencia from producto where tipoProducto = 3;");
            while (resultado.next()) {

                //creamos la fila
                Row fila = sheet1.createRow(3 + i);

                String idProducto = String.valueOf(resultado.getString("idProducto"));
                String nombre = String.valueOf(resultado.getString("nombre"));
                String cantidad = String.valueOf(resultado.getInt("cantidad"));
                String precioVenta = String.valueOf(resultado.getString("precioVenta"));
                String ganancia = String.valueOf(resultado.getString("ganancia"));
                String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia"));
                //String Image = String.valueOf(resultado.getBlob("Image"));
                // Creo las celdas de mi fila, se puede poner un diseo a la celda
                System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - "
                        + precioVenta + " - " + ganancia + " - " + UnidadExistencia);

                creandoCelda(wb, fila, 0, idProducto);
                creandoCelda(wb, fila, 1, nombre);
                creandoCelda(wb, fila, 2, cantidad);
                creandoCelda(wb, fila, 3, precioVenta);
                creandoCelda(wb, fila, 4, ganancia);
                creandoCelda(wb, fila, 5, UnidadExistencia);
                //creandoCelda(wb, fila, 5, Image);
                i++;
            }
        }
        con.close();

        //            Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //            la celda se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.autoSizeColumn(0);
        ssheet.autoSizeColumn(1);
        ssheet.autoSizeColumn(2);
        ssheet.autoSizeColumn(3);
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);
        ssheet.autoSizeColumn(7);

        //Ajustando la hoja de una pagina

        Sheet sheet = wb.createSheet("format sheet");
        PrintSetup ps = sheet.getPrintSetup();
        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        //Area de impresion
        wb.setPrintArea(0, 0, 1, 0, 9);

        String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports"
                + System.getProperty("file.separator") + "Productos" + ControllerFechas.getFechaActual()
                + ".xls";
        //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls";

        try (FileOutputStream fileOut = new FileOutputStream(strRuta)) {
            wb.write(fileOut);
        }
        JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta);

    } catch (Exception e) {
        e.printStackTrace();
        //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo");
    }
}

From source file:Documentos.ClaseAlmacenXLS.java

public void crearExcel() {
    try {/*from  w  w w  .j  a  v  a 2s. c o m*/

        // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet1 = wb.createSheet("Productos");
        Sheet sheet2 = wb.createSheet("hoja2");
        Sheet sheet3 = wb.createSheet("hoja3");

        // quito las lineas del libro para darle un mejor acabado
        //            sheet.setDisplayGridlines(false);
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        // creo una nueva fila
        Row trow = sheet1.createRow((short) 0);
        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual());

        // Creo la cabecera de mi listado en Excel
        Row row = sheet1.createRow((short) 2);

        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true,
                true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true,
                true);

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", "");

        try ( // Creamos un Statement para poder hacer peticiones a la bd
                Statement stat = con.createStatement()) {
            ResultSet resultado = stat.executeQuery(
                    "select idProducto, nombre, cantidad,UnidadExistencia,minStock from producto where tipoProducto=2 ");
            while (resultado.next()) {

                //creamos la fila
                Row fila = sheet1.createRow(3 + i);

                String idProducto = String.valueOf(resultado.getString("idProducto"));
                String nombre = String.valueOf(resultado.getString("nombre"));
                String cantidad = String.valueOf(resultado.getInt("cantidad"));
                String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia"));
                String minStock = String.valueOf(resultado.getInt("minStock"));
                // Creo las celdas de mi fila, se puede poner un diseo a la celda
                System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - "
                        + UnidadExistencia + " - " + minStock);

                creandoCelda(wb, fila, 0, idProducto);
                creandoCelda(wb, fila, 1, nombre);
                creandoCelda(wb, fila, 2, cantidad);
                creandoCelda(wb, fila, 3, UnidadExistencia);
                creandoCelda(wb, fila, 4, minStock);
                i++;
            }
        }
        con.close();

        //            Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //            la celda se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.autoSizeColumn(0);
        ssheet.autoSizeColumn(1);
        ssheet.autoSizeColumn(2);
        ssheet.autoSizeColumn(3);
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);
        ssheet.autoSizeColumn(7);

        //Ajustando la hoja de una pagina

        Sheet sheet = wb.createSheet("format sheet");
        PrintSetup ps = sheet.getPrintSetup();
        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        //Area de impresion
        wb.setPrintArea(0, 0, 1, 0, 9);

        String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports"
                + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls";
        //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls";

        try (FileOutputStream fileOut = new FileOutputStream(strRuta)) {
            wb.write(fileOut);
        }
        JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta);

    } catch (Exception e) {
        e.printStackTrace();
        //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo");
    }
}

From source file:featurescomparison.workingwithworkbook.fittoonepage.java.ApacheFitSheetToOnePage.java

License:Apache License

public static void main(String[] args) throws Exception {
    String dataPath = "src/featurescomparison/workingwithworkbook/fittoonepage/data/";

    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("format sheet");
    PrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);

    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream(dataPath + "ApacheFitSheetToOnePage.xlsx");
    wb.write(fileOut);//  ww w . j av  a 2s.c  om
    fileOut.close();
    System.out.println("Done.");
}

From source file:featurescomparison.workingwithworksheets.fittoonepage.java.ApacheFitSheetToOnePage.java

License:Apache License

public static void main(String[] args) throws Exception {
    String dataPath = "src/featurescomparison/workingwithworksheets/fittoonepage/data/";

    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("format sheet");
    PrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);

    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream(dataPath + "ApacheFitSheetToOnePage.xlsx");
    wb.write(fileOut);//from w w w  .j  a v a  2 s .  co  m
    fileOut.close();
    System.out.println("Done.");
}

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

License:Apache License

protected void finalizeSheet(Sheet sheet, boolean landscapePrintSetup) {
    sheet.getPrintSetup().setLandscape(landscapePrintSetup);
    sheet.setAutobreaks(true);
    sheet.getPrintSetup().setFitWidth((short) 1);
    sheet.getPrintSetup().setFitHeight((short) 10000);
}

From source file:itpreneurs.itp.report.archive.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*www  .ja v a  2  s . c  o m*/

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

    wb = new XSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Business Plan");

    // 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(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    // columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    // freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    // group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    // set column widths, the width is measured in units of 1/256th of a
    // character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:org.cerberus.service.export.ExportServiceFactory.java

License:Open Source License

private void createReportByTagExport(Workbook workbook) {
    //handles the export of the execution by tag data
    HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>();

    HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>();
    List<String> mapCountries = new ArrayList<String>();
    List<CellStyle> stylesList = new LinkedList<CellStyle>();

    if (exportOptions.contains("chart") || exportOptions.contains("list")) {
        //then we need to create the default colors for each cell
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();

        CellStyle okStyle = workbook.createCellStyle();

        // get the color which most closely matches the color you want to use
        // code to get the style for the cell goes here
        okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex());
        okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //okStyle.setFont();

        stylesList.add(okStyle);/*from  w w w .j a va2  s  .c  o  m*/

    }
    for (TestCaseExecution execution : (List<TestCaseExecution>) list) {
        //check if the country and application shows

        if (exportOptions.contains("chart") || exportOptions.contains("summary")) {
            String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " "
                    + execution.getEnvironment();
            SummaryStatisticsDTO stats;

            String status = execution.getControlStatus();

            if (summaryMap.containsKey(keySummaryTable)) {
                stats = summaryMap.get(keySummaryTable);
            } else {
                stats = new SummaryStatisticsDTO();
                stats.setApplication(execution.getApplication());
                stats.setCountry(execution.getCountry());
                stats.setEnvironment(execution.getEnvironment());
            }
            stats.updateStatisticByStatus(status);
            summaryMap.put(keySummaryTable, stats); //updates the map
        }
        if (exportOptions.contains("list")) {
            if (exportOptions.contains("filter")) {
                //filter active
            } else {
                //all data is saved

            }
            HashMap<String, List<TestCaseExecution>> listExecution;
            List<TestCaseExecution> testCaseList;
            String testKey = execution.getTest();
            String testCaseKey = execution.getTestCase();

            if (mapList.containsKey(testKey)) {
                listExecution = mapList.get(testKey);
            } else {
                listExecution = new HashMap<String, List<TestCaseExecution>>();
            }
            if (listExecution.containsKey(testCaseKey)) {
                testCaseList = listExecution.get(testCaseKey);
            } else {
                testCaseList = new ArrayList<TestCaseExecution>();
            }
            testCaseList.add(execution);
            listExecution.put(testCaseKey, testCaseList);
            mapList.put(testKey, listExecution);

            if (mapCountries.indexOf(execution.getCountry()) == -1) {
                mapCountries.add(execution.getCountry());
            }

        }

    }
    int rowCount = -1;

    //Create a blank sheet
    Sheet sheet = workbook.createSheet("Report by Tag");
    sheet.getPrintSetup().setLandscape(true);

    PrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    //ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);
    sheet.setFitToPage(true);
    sheet.setColumnWidth(0, 9000);

    if (exportOptions.contains("chart")) {
        SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap);

        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Report By Status");

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Status");
        row.createCell(1).setCellValue("Total");
        row.createCell(2).setCellValue("Percentage");

        row = sheet.createRow(++rowCount);
        CellStyle okStyle = stylesList.get(0);
        Cell cellOk = row.createCell(0);
        cellOk.setCellValue("OK");
        cellOk.setCellStyle(okStyle);

        row.createCell(1).setCellValue(sumsTotal.getOk());
        row.createCell(2).setCellValue(sumsTotal.getPercOk());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("KO");
        row.createCell(1).setCellValue(sumsTotal.getKo());
        row.createCell(2).setCellValue(sumsTotal.getPercKo());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("FA");
        row.createCell(1).setCellValue(sumsTotal.getFa());
        row.createCell(2).setCellValue(sumsTotal.getPercFa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NA");
        row.createCell(1).setCellValue(sumsTotal.getNa());
        row.createCell(2).setCellValue(sumsTotal.getPercNa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NE");
        row.createCell(1).setCellValue(sumsTotal.getNe());
        row.createCell(2).setCellValue(sumsTotal.getPercNe());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("PE");
        row.createCell(1).setCellValue(sumsTotal.getPe());
        row.createCell(2).setCellValue(sumsTotal.getPercPe());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("CA");
        row.createCell(1).setCellValue(sumsTotal.getCa());
        row.createCell(2).setCellValue(sumsTotal.getPercCa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue(sumsTotal.getTotal());

        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");

    }
    if (exportOptions.contains("summary")) {
        //draw the table with data

        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Summary Table");

        //start creating data
        row = sheet.createRow(++rowCount);

        row.createCell(0).setCellValue("Application");
        row.createCell(1).setCellValue("Country");
        row.createCell(2).setCellValue("Environment");
        row.createCell(3).setCellValue("OK");
        row.createCell(4).setCellValue("KO");
        row.createCell(5).setCellValue("FA");
        row.createCell(6).setCellValue("NA");
        row.createCell(7).setCellValue("NE");
        row.createCell(8).setCellValue("PE");
        row.createCell(9).setCellValue("CA");
        row.createCell(10).setCellValue("NOT OK");
        row.createCell(11).setCellValue("Total");

        /*temporary styles*/
        CellStyle styleBlue = workbook.createCellStyle();
        CellStyle styleGreen = workbook.createCellStyle();
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();
        // get the color which most closely matches the color you want to use
        HSSFColor myColor = palette.findSimilarColor(66, 139, 202);

        // get the palette index of that color 
        short palIndex = myColor.getIndex();
        // code to get the style for the cell goes here
        styleBlue.setFillForegroundColor(palIndex);
        styleBlue.setFillPattern(CellStyle.SPARSE_DOTS);

        HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0);
        styleGreen.setFillForegroundColor(myColorGreen.getIndex());
        styleGreen.setFillPattern(CellStyle.SPARSE_DOTS);

        int startRow = (rowCount + 2);
        TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>(
                summaryMap);
        for (String key : sortedSummaryMap.keySet()) {
            row = sheet.createRow(++rowCount);
            SummaryStatisticsDTO sumStats = summaryMap.get(key);
            //application
            row.createCell(0).setCellValue((String) sumStats.getApplication());
            //country
            row.createCell(1).setCellValue((String) sumStats.getCountry());
            //environment
            row.createCell(2).setCellValue((String) sumStats.getEnvironment());

            //OK
            row.createCell(3).setCellValue(sumStats.getOk());
            //KO
            row.createCell(4).setCellValue(sumStats.getKo());
            //FA
            row.createCell(5).setCellValue(sumStats.getFa());
            //NA
            row.createCell(6).setCellValue(sumStats.getNa());
            //NE
            row.createCell(7).setCellValue(sumStats.getNe());
            //PE
            row.createCell(8).setCellValue(sumStats.getPe());
            //CA
            row.createCell(9).setCellValue(sumStats.getCa());
            int rowNumber = row.getRowNum() + 1;
            //NOT OK
            //row.createCell(11).setCellValue(sumStats.getNotOkTotal());
            row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")");
            //Total
            row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")");
            //row.createCell(12).setCellValue(sumStats.getTotal());

            if (sumStats.getOk() == sumStats.getTotal()) {
                for (int i = 0; i < 12; i++) {
                    row.getCell(i).setCellStyle(styleGreen);
                }
            }
        }
        //TODO:FN percentages missing
        //Total row
        row = sheet.createRow(++rowCount);

        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue("");
        row.createCell(2).setCellValue("");
        //OK
        row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")");
        //KO
        row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")");
        //FA
        row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")");
        //NA
        row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")");
        //NE
        row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")");
        //PE
        row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")");
        //CA
        row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")");

        int rowNumberTotal = row.getRowNum() + 1;
        //NOT OK
        row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")");
        //Total
        row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")");
        for (int i = 0; i < 12; i++) {
            row.getCell(i).setCellStyle(styleBlue);
        }

        //add some empty rows
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");

    }

    if (exportOptions.contains("list")) {
        //exports the data from test cases' executions
        Row r = sheet.createRow(++rowCount);
        r.createCell(0).setCellValue("Test");
        r.createCell(1).setCellValue("Test Case");
        r.createCell(2).setCellValue("Description");
        r.createCell(3).setCellValue("Application");
        r.createCell(4).setCellValue("Environment");
        r.createCell(5).setCellValue("Browser");
        //creates the country list

        Collections.sort(mapCountries);//sorts the list of countries
        int startIndexForCountries = 6;
        for (String country : mapCountries) {
            r.createCell(startIndexForCountries).setCellValue(country);
            startIndexForCountries++;
        }

        TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>(
                mapList);
        rowCount++;
        for (String keyMapList : sortedKeys.keySet()) {
            rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries);
        }
    }
}