Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:com.b510.excel.client.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 {//from www .  j  ava2s .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 = "calendar.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.b510.excel.client.LoanCalculator.java

License:Apache License

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

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else//  w w w. ja  v  a 2s  . c  om
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

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

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "loan-calculator.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.b510.excel.client.TimesheetDemo.java

License:Apache License

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

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else/*from   www  .  j a  v  a  2 s. c om*/
        wb = new XSSFWorkbook();

    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 = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.base2.kagura.core.ExportHandler.java

License:Apache License

/**
 * Takes the output and transforms it into a Excel file.
 * @param out Output stream.//from   ww w. ja v  a2s . com
 * @param rows Rows of data from reporting-core
 * @param columns Columns to list on report
 */
public void generateXls(OutputStream out, List<Map<String, Object>> rows, List<ColumnDef> columns) {
    try {
        Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
        String safeName = WorkbookUtil.createSafeSheetName("Report"); // returns " O'Brien's sales   "
        Sheet reportSheet = wb.createSheet(safeName);

        short rowc = 0;
        Row nrow = reportSheet.createRow(rowc++);
        short cellc = 0;
        if (rows == null)
            return;
        if (columns == null && rows.size() > 0) {
            columns = new ArrayList<ColumnDef>(CollectionUtils.collect(rows.get(0).keySet(), new Transformer() {
                @Override
                public Object transform(final Object input) {
                    return new ColumnDef() {
                        {
                            setName((String) input);
                        }
                    };
                }
            }));
        }
        if (columns != null) {
            for (ColumnDef column : columns) {
                Cell cell = nrow.createCell(cellc++);
                cell.setCellValue(column.getName());
            }
        }
        for (Map<String, Object> row : rows) {
            nrow = reportSheet.createRow(rowc++);
            cellc = 0;
            for (ColumnDef column : columns) {
                Cell cell = nrow.createCell(cellc++);
                cell.setCellValue(String.valueOf(row.get(column.getName())));
            }
        }
        wb.write(out);
    } catch (IOException e) {
        e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
    }

}

From source file:com.bonsoft.test.Report.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
    ArrayList<ReportLine> lines = new ArrayList<>();
    try {/*from  w w  w.j  a v  a 2s . c  om*/
        Orgs org = (Orgs) jComboBox1.getSelectedItem();
        Stores store = (Stores) jComboBox2.getSelectedItem();
        Calendar period = Calendar.getInstance();
        try {
            period.setTime((new SimpleDateFormat("dd.MM.yyyy")).parse(jFormattedTextField1.getText()));
        } catch (ParseException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }

        Connection connection = null;
        Statement statement = null;
        ResultSet result = null;
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://88.201.248.46:5432/personal", "vitaly",
                    "m127rqu4");
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            statement = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        String sql = "select operations.descr, date_part('hour', mhr_period) as hr, sum(mhr_qty) as cnt from mhr, operations "
                + "where mhr.operation_id = operations.id and mhR_qty > 0 and mhr.org_id = " + org.getId()
                + " and store_id = " + store.getId() + " and " + "date_part('day', mhr_period) = "
                + period.get(Calendar.DAY_OF_MONTH) + " and date_part('month', mhr_period) = "
                + (period.get(Calendar.MONTH) + 1) + " and date_part('year', mhr_period) = "
                + period.get(Calendar.YEAR)
                + " and mhr.operation_id in (select id from operations) group by operations.descr, hr having count(mhr_qty) > 0 order by "
                + "operations.descr, hr";
        try {
            result = statement.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("?: " + org.getName() + ", id = " + org.getId());
        System.out.println(": " + store.getDescr() + ", id = " + store.getId());
        System.out.println(": " + period.get(Calendar.DAY_OF_MONTH));
        System.out.println("??: " + (period.get(Calendar.MONTH) + 1));
        System.out.println(": " + period.get(Calendar.YEAR));
        String oldDescr = "";
        ReportLine line = null;
        while (result.next()) {
            String descr = result.getString("descr");
            double hr = result.getDouble("hr");
            double cnt = result.getDouble("cnt");
            cnt = Math.ceil(cnt);
            if (oldDescr.equals(descr)) {
                line.add(hr, cnt);
            } else {
                oldDescr = descr;
                line = new ReportLine();
                line.setName(descr);
                line.add(hr, cnt);
                lines.add(line);
            }
        }
        result.close();
        statement.close();
        connection.close();
    } catch (SQLException ex) {
        Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("");
    Row title = sheet.createRow(0);
    Cell cell = title.createCell(0);
    cell.setCellValue("?");
    Row row = null;
    int x = 0, y = 0;
    for (ReportLine line : lines) {
        row = sheet.createRow(++y);
        cell = row.createCell(0);
        cell.setCellValue(line.getName());
        for (int i = 0; i < line.getLen(); i++) {
            x = line.getHours().get(i) - ReportLine.getMinH() + 1;
            cell = title.createCell(x);
            cell.setCellValue(line.getHours().get(i) + ":00");
            cell = row.createCell(x);
            cell.setCellValue(line.getCounts().get(i));
        }
    }
    sheet.autoSizeColumn(0);
    try (FileOutputStream fileExcel = new FileOutputStream("Report.xls")) {
        workbook.write(fileExcel);
    } catch (IOException ex) {
        Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.catexpress.util.FormatosPOI.java

public void formatoSolicitud(Solicitud solicitud, Set<Proveedor> proveedores)
        throws FileNotFoundException, IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet;//from w  w w .j a  v a  2s  .  c  om
    int cont = 0;
    for (Proveedor proveedor : proveedores) {
        sheet = wb.createSheet(proveedor.getNombre());
        Row rTitulo = sheet.createRow(0);
        CellRangeAddress craTitulo = new CellRangeAddress(0, //first row (0-based)
                0, //last row  (0-based)
                0, //first column (0-based)
                6 //last column  (0-based)
        );
        sheet.addMergedRegion(craTitulo);
        Cell titulo = rTitulo.createCell(0);
        titulo.setCellValue("SOLICITUD DE MERCANC?A");
        titulo.setCellStyle(estiloHeader(wb, TITULO));
        rTitulo.setHeightInPoints(20);

        Row rUsuario = sheet.createRow(1);
        CellRangeAddress craUsuario = new CellRangeAddress(1, 1, 0, 6);
        sheet.addMergedRegion(craUsuario);
        Cell usuario = rUsuario.createCell(0);
        usuario.setCellValue((solicitud.getUsuario().getNombre() + " " + solicitud.getUsuario().getApPaterno()
                + " " + solicitud.getUsuario().getApMaterno()).toUpperCase());
        usuario.setCellStyle(estiloHeader(wb, USUARIO));
        rUsuario.setHeightInPoints(25);

        Row rSucursal = sheet.createRow(2);
        CellRangeAddress craSucursal = new CellRangeAddress(2, 2, 0, 6);
        sheet.addMergedRegion(craSucursal);
        Cell sucursal = rSucursal.createCell(0);
        sucursal.setCellValue("Sucursal: " + solicitud.getSucursal().getNombre());
        sucursal.setCellStyle(estiloHeader(wb, SUCURSAL));
        RegionUtil.setBorderTop(sucursal.getCellStyle().getBorderTop(), craSucursal, sheet, wb);
        RegionUtil.setBorderLeft(sucursal.getCellStyle().getBorderLeft(), craSucursal, sheet, wb);
        RegionUtil.setBorderRight(sucursal.getCellStyle().getBorderRight(), craSucursal, sheet, wb);
        RegionUtil.setBorderBottom(sucursal.getCellStyle().getBorderBottom(), craSucursal, sheet, wb);
        rSucursal.setHeightInPoints(20);

        Row rBlank = sheet.createRow(3);
        Cell blank;
        for (int i = 0; i <= 6; i++) {
            blank = rBlank.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rFecha = sheet.createRow(4);
        Cell labelFecha = rFecha.createCell(0);
        labelFecha.setCellValue("FECHA:");
        labelFecha.setCellStyle(estiloHeader(wb, LABEL));
        CellRangeAddress craFecha = new CellRangeAddress(4, 4, 1, 3);
        sheet.addMergedRegion(craFecha);
        Cell fecha = rFecha.createCell(1);
        fecha.setCellValue(solicitud.getFechaSolicitud());
        fecha.setCellStyle(estiloHeader(wb, FECHA));
        for (int i = 4; i <= 6; i++) {
            blank = rFecha.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rVigencia = sheet.createRow(5);
        Cell labelVigencia = rVigencia.createCell(0);
        labelVigencia.setCellValue("VIGENCIA:");
        labelVigencia.setCellStyle(estiloHeader(wb, LABEL));
        CellRangeAddress craVigencia = new CellRangeAddress(5, 5, 1, 3);
        sheet.addMergedRegion(craVigencia);
        Cell vigencia = rVigencia.createCell(1);
        Calendar clndr = Calendar.getInstance();
        clndr.setTime(solicitud.getFechaSolicitud());
        clndr.add(Calendar.DAY_OF_MONTH, 3);
        vigencia.setCellValue(clndr.getTime());
        vigencia.setCellStyle(estiloHeader(wb, FECHA));
        blank = rVigencia.createCell(4);
        blank.setCellStyle(estiloVacio(wb));
        Cell labelNoPedido = rVigencia.createCell(5);
        labelNoPedido.setCellValue("PEDIDO No:");
        labelNoPedido.setCellStyle(estiloCuadro(wb, AMARILLO));
        Cell noPedido = rVigencia.createCell(6);
        noPedido.setCellValue(solicitud.getId());
        noPedido.setCellStyle(estiloCuadro(wb, AMARILLO));

        Row rHoja = sheet.createRow(6);
        for (int i = 0; i <= 4; i++) {
            blank = rHoja.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }
        Cell labelHoja = rHoja.createCell(5);
        labelHoja.setCellValue("HOJA:");
        labelHoja.setCellStyle(estiloCuadro(wb, LABEL));
        Cell hoja = rHoja.createCell(6);
        hoja.setCellValue(++cont + "/" + proveedores.size());
        hoja.setCellStyle(estiloCuadro(wb, LABEL));

        Row rProveedor = sheet.createRow(7);
        CellRangeAddress craProveedor = new CellRangeAddress(7, 8, 0, 2);
        sheet.addMergedRegion(craProveedor);
        Cell prov = rProveedor.createCell(0);
        prov.setCellValue(proveedor.getNombre());
        prov.setCellStyle(estiloProveedor(wb));
        for (int i = 3; i <= 6; i++) {
            blank = rProveedor.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rProveedor2 = sheet.createRow(8);
        for (int i = 3; i <= 6; i++) {
            blank = rProveedor2.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rTotales = sheet.createRow(9);
        for (int i = 0; i <= 1; i++) {
            blank = rTotales.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }
        Cell labelTotales = rTotales.createCell(2);
        labelTotales.setCellValue("TOTALES: ");
        labelTotales.setCellStyle(estiloTotales(wb));
        blank = rTotales.createCell(3);
        blank.setCellStyle(estiloTotales(wb));
        Cell totalSolicitado = rTotales.createCell(4);
        totalSolicitado.setCellStyle(estiloTotales(wb));
        totalSolicitado.setCellType(CellType.FORMULA);
        totalSolicitado.setCellFormula("SUM(E12:E" + (11 + solicitud.getDetalles().size()) + ")");
        Cell totalSurtido = rTotales.createCell(5);
        totalSurtido.setCellStyle(estiloTotales(wb));
        totalSurtido.setCellType(CellType.FORMULA);
        totalSurtido.setCellFormula("SUM(F12:F" + (11 + solicitud.getDetalles().size()) + ")");
        Cell totalNegado = rTotales.createCell(6);
        totalNegado.setCellStyle(estiloTotales(wb));
        totalNegado.setCellType(CellType.FORMULA);
        totalNegado.setCellFormula("SUM(G12:G" + (11 + solicitud.getDetalles().size()) + ")");

        Row rColumnas = sheet.createRow(10);
        Cell labelCodigo = rColumnas.createCell(0);
        labelCodigo.setCellValue("CODIGO");
        labelCodigo.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelOpciones = rColumnas.createCell(1);
        labelOpciones.setCellValue("OPCIONES");
        labelOpciones.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelModelo = rColumnas.createCell(2);
        labelModelo.setCellValue("MODELO / MATERIAL / COLOR");
        labelModelo.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelTalla = rColumnas.createCell(3);
        labelTalla.setCellValue("TALLA");
        labelTalla.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelSolicitado = rColumnas.createCell(4);
        labelSolicitado.setCellValue("SOLICITADO");
        labelSolicitado.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelSurtido = rColumnas.createCell(5);
        labelSurtido.setCellValue("SURTIDO");
        labelSurtido.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelNegado = rColumnas.createCell(6);
        labelNegado.setCellValue("NEGADO");
        labelNegado.setCellStyle(estiloColumnas(wb, COLUMNA));

        Row rValues = sheet.createRow(11);
        Cell codigo;
        Cell opciones;
        Cell modelo;
        Cell talla;
        Cell solicitado;
        Cell surtido;
        Cell negado;
        for (Dsolicitud detalle : solicitud.getDetalles()) {
            if (detalle.getProducto().getProvedor().equals(proveedor)) {
                codigo = rValues.createCell(0);
                codigo.setCellValue(detalle.getProducto().getCBarras());
                codigo.setCellStyle(estiloColumnas(wb, 0));
                opciones = rValues.createCell(1);
                opciones.setCellValue(" - ");
                opciones.setCellStyle(estiloColumnas(wb, 0));
                modelo = rValues.createCell(2);
                modelo.setCellValue(detalle.getProducto().getModelo().getNombre() + " / "
                        + detalle.getProducto().getColor().getNombre());
                modelo.setCellStyle(estiloColumnas(wb, 0));
                talla = rValues.createCell(3);
                talla.setCellValue(detalle.getProducto().getTalla().getNombre());
                talla.setCellStyle(estiloColumnas(wb, 0));
                solicitado = rValues.createCell(4);
                solicitado.setCellValue(detalle.getCantidad());
                solicitado.setCellStyle(estiloColumnas(wb, 0));
                surtido = rValues.createCell(5);
                surtido.setCellStyle(estiloColumnas(wb, SURTIDO));
                negado = rValues.createCell(6);
                negado.setCellStyle(estiloColumnas(wb, 0));
            }
        }

        for (int i = 0; i <= 6; i++) {
            sheet.autoSizeColumn(i, true);
        }
    }
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("Solicitud" + solicitud.getId() + ".xls");
    wb.write(fileOut);
    fileOut.close();
}

From source file:com.centurylink.mdw.common.service.JsonExport.java

License:Apache License

public Workbook exportXlsx(String name) throws JSONException {

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet(name == null ? jsonable.getClass().getSimpleName() : name);

    if (jsonable instanceof JsonArray) {
        JSONArray jsonArray = ((JsonArray) jsonable).getArray();
        for (int i = 0; i < jsonArray.length(); i++) {
            JSONObject jsonObj = jsonArray.getJSONObject(i);
            addNames(jsonObj);/*from  w ww  .  j  a v  a  2 s.com*/
            setRowValues(sheet, i + 1, jsonObj);
        }
        setColumnLabels(sheet);
    } else if (jsonable instanceof InstanceList) {
        InstanceList<?> instanceList = (InstanceList<?>) jsonable;
        List<? extends Jsonable> items = instanceList.getItems();
        for (int i = 0; i < items.size(); i++) {
            JSONObject jsonObj = items.get(i).getJson();
            addNames(jsonObj);
            setRowValues(sheet, i + 1, jsonObj);
        }
        setColumnLabels(sheet);
    } else if (jsonable instanceof JsonListMap) {
        JsonListMap<?> listMap = (JsonListMap<?>) jsonable;
        int row = 1;
        List<String> keys = new ArrayList<String>();
        keys.addAll(listMap.getJsonables().keySet());
        Collections.sort(keys);
        for (String key : keys) {
            List<? extends Jsonable> jsonableList = listMap.getJsonables().get(key);
            for (Jsonable jsonable : jsonableList) {
                addNames(jsonable.getJson());
            }
            if (!"".equals(names.get(0))) {
                names.add(0, ""); // key column
            }
            for (Jsonable jsonable : jsonableList) {
                Row valuesRow = setRowValues(sheet, row, jsonable.getJson(), 1);
                Cell cell = valuesRow.createCell(0);
                cell.setCellValue(key);
                row++;
            }
        }
        setColumnLabels(sheet);
    } else {
        throw new UnsupportedOperationException("Unsupported JSON type: " + jsonable);
    }

    return workbook;
}

From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java

License:LGPL

@SuppressWarnings("unchecked")
public void writeExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception {
    Workbook book = null;
    InputStream is = null;//w  ww  .  j ava2s  . c  o  m
    try {
        is = execBean.getInputStream();
    } catch (FileNotFoundException e) {

    }
    if (is != null) {
        book = WorkbookFactory.create(is);
        is.close();
    } else {
        book = new HSSFWorkbook();
    }
    CreationHelper createHelper = book.getCreationHelper();
    CellStyle dateStyle = book.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));

    CellStyle numStyle = book.createCellStyle();
    numStyle.setDataFormat(createHelper.createDataFormat().getFormat("0.00000000"));

    CellStyle intNumStyle = book.createCellStyle();
    intNumStyle.setDataFormat(createHelper.createDataFormat().getFormat("0"));

    List<Object> result = context.getAttribute(execBean.getParamName());
    String[] columns = execBean.getColumns();
    if (execBean.getColumns() != null) {
        columns = execBean.getColumns();
    } else {
        columns = ((List<String>) context.getAttribute(execBean.getColumnsVarName())).toArray(new String[] {});
    }
    String sheetName;
    if (StringUtils.isNotEmpty(execBean.getSheetName())) {
        sheetName = execBean.getSheetName();
    } else {
        sheetName = context.getAttribute(execBean.getSheetVarName()).toString();
    }
    // int number = book.getNumberOfSheets();
    Sheet sheet = book.createSheet(sheetName);
    int rowNum = 0;
    Row firstRow = sheet.createRow(rowNum++);
    for (int i = 0; i < columns.length; i++) {
        Cell cell = firstRow.createCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(columns[i]);
    }

    for (int i = 0; i < result.size(); i++) {
        Object row = result.get(i);
        Row dataRow = sheet.createRow(rowNum++);

        for (int j = 0; j < columns.length; j++) {
            Object obj = PropertyUtils.getNestedProperty(row, columns[j]);
            Cell cell = dataRow.createCell(j);
            if (obj == null) {
                cell.setCellType(Cell.CELL_TYPE_BLANK);
            } else {
                if (obj instanceof String) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(obj.toString());
                } else if (obj instanceof Date) {
                    cell.setCellValue((Date) obj);
                    cell.setCellStyle(dateStyle);
                } else if (obj instanceof Integer || obj instanceof Long) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(intNumStyle);
                    cell.setCellValue(new Double(obj.toString()));
                } else if (obj instanceof Number) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(numStyle);
                    cell.setCellValue(new Double(obj.toString()));
                } else {
                    throw new DataProcessException("??Excel?");
                }
            }
        }
    }

    OutputStream os = null;
    try {
        os = execBean.getOutputStream();
        book.write(os);
    } finally {
        if (os != null) {
            os.close();
        }
    }
}

From source file:com.company.et.service.XlsService.java

public static void generateFile(List<TreeItem<Task>> root, List<ArrayList<ArrayList<Double>>> waitingParts,
        ObservableList<Professor> professors) throws FileNotFoundException, IOException {
    FileOutputStream out = new FileOutputStream(filename);
    Workbook wb = new HSSFWorkbook();

    for (int k = 0; k < professors.size(); k++) {

        Sheet s = wb.createSheet(professors.get(k).getFio());

        Row rowFirst = s.createRow(0);/*from  w ww. j ava2 s.  com*/

        createConstStringCells(wb, s, "??", rowFirst, 0, 0, 1, 0, 0);
        createConstStringCells(wb, s, "?? ", rowFirst, 1, 0, 0, 1, 3);
        createConstStringCells(wb, s, "? ", rowFirst, 4, 0, 0, 4, 6);
        createConstStringCells(wb, s, "?? ", rowFirst, 7, 0, 0, 7, 9);
        createConstStringCells(wb, s, "?? ", rowFirst, 10, 0, 0, 10, 12);
        createConstStringCells(wb, s, "?? ", rowFirst, 13, 0, 0, 13, 15);

        Row rowSecond = s.createRow(1);
        for (int i = 0, j = 1; i < 5; i++, j += 3) {
            createConstStringCells(wb, s, "", rowSecond, j, 0, 0, 0, 0);
            createConstStringCells(wb, s, "", rowSecond, j + 1, 0, 0, 0, 0);
            createConstStringCells(wb, s, "", rowSecond, j + 2, 0, 0, 0, 0);
        }

        for (int i = 1; i < 16; i++) {
            Row row = s.createRow(i + 1);
            for (int j = 0; j < root.get(k).getChildren().size(); j++) {
                createCellOfDouble(wb, s, row, j * 3 + 2,
                        root.get(k).getChildren().get(j).getValue().getCapacities().get(i));
                createCellOfDouble(wb, s, row, j * 3 + 1, waitingParts.get(k).get(j).get(i));
                createReserveCell(wb, s, row, (j + 1) * 3);

            }
            createMonthCell(wb, s, row, DoubleCapacities.getDoubleCapacitiesByIndex(i).toString());
            createFullTasksActualCell(wb, s, row, root.get(k).getChildren().size() * 3 + 1);
            createFullTasksCell(wb, s, row, root.get(k).getChildren().size() * 3 + 2);
            createFullTasksReserveCell(wb, s, row, (root.get(k).getChildren().size() + 1) * 3);

        }
    }
    FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator();
    //all year report
    Sheet s = wb.createSheet("?");
    Row rowFirst = s.createRow(0);
    Row rowSecond = s.createRow(1);
    createConstStringCells(wb, s, "/", rowFirst, 0, 0, 1, 0, 0);
    createConstStringCells(wb, s, "", rowFirst, 1, 0, 1, 1, 1);
    createConstStringCells(wb, s, "?", rowFirst, 2, 0, 1, 2, 2);
    createConstStringCells(wb, s, ". .", rowFirst, 3, 0, 1, 3, 3);
    for (int i = 1; i < 16; i++) {
        createConstStringCells(wb, s, DoubleCapacities.getDoubleCapacitiesByIndex(i).toString(), rowFirst,
                (i * 3) + 1, 0, 0, (i * 3) + 1, (i * 3) + 3);
        createConstStringCells(wb, s, "", rowSecond, (i * 3) + 1, 1, 1, (i * 3) + 1, (i * 3) + 1);
        createConstStringCells(wb, s, "", rowSecond, (i * 3) + 2, 1, 1, (i * 3) + 2, (i * 3) + 2);
        createConstStringCells(wb, s, "", rowSecond, (i * 3) + 3, 1, 1, (i * 3) + 3, (i * 3) + 3);
    }
    for (int i = 0; i < professors.size(); i++) {
        Row row = s.createRow(i + 2);
        createConstStringCells(wb, s, Integer.toString(i + 1), row, 0, i + 2, i + 2, 0, 0);
        createConstStringCells(wb, s, professors.get(i).getFio(), row, 1, i + 2, i + 2, 1, 1);
        createConstStringCells(wb, s, professors.get(i).getRate().toString(), row, 3, i + 2, i + 2, 3, 3);
        for (int j = 1; j < 16; j++) {
            createConstStringCells(wb, s,
                    formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(13))
                            .formatAsString(),
                    row, (j * 3) + 1, i + 2, i + 2, (j * 3) + 1, (j * 3) + 1);
            createConstStringCells(wb, s,
                    formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(14))
                            .formatAsString(),
                    row, (j * 3) + 2, i + 2, i + 2, (j * 3) + 2, (j * 3) + 2);
            createConstStringCells(wb, s,
                    formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(15))
                            .formatAsString(),
                    row, (j * 3) + 3, i + 2, i + 2, (j * 3) + 3, (j * 3) + 3);
        }
    }
    wb.write(out);

    out.close();

}

From source file:com.company.et.service.XlsService.java

public static void createReportForMonthForOnePerson(int numOfMonth, Professor professor,
        ArrayList<ArrayList<Double>> waitingParts, TreeItem<Task> root)
        throws FileNotFoundException, IOException {

    FileOutputStream out = new FileOutputStream(filename);
    Workbook wb = new HSSFWorkbook();
    Sheet s = wb.createSheet(professor.getFio());
    createReportForMonth(wb, s, numOfMonth, professor, waitingParts, root);
    wb.write(out);//  ww  w  .  jav  a 2s. c  om
    out.close();
}