Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createSheet

Introduction

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

Prototype

@Override
public HSSFSheet createSheet(String sheetname)

Source Link

Document

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

Usage

From source file:com.openitech.util.HSSFWrapper.java

License:Apache License

public static final HSSFWorkbook getWorkbook(JTable source, boolean countRows) {
    HSSFWorkbook xls_workbook = new HSSFWorkbook();
    HSSFSheet xls_sheet = xls_workbook.createSheet("Pregled podatkov");
    HSSFPrintSetup xls_sheet_printsetup = xls_sheet.getPrintSetup();
    xls_sheet_printsetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    xls_sheet_printsetup.setFitWidth((short) 1);

    TableColumnModel columnModel = source.getColumnModel();
    Enumeration<TableColumn> columns = columnModel.getColumns();

    HSSFRow xls_row = xls_sheet.createRow(0);
    short cell = 1;
    HSSFCellStyle xls_header_cell_style = xls_workbook.createCellStyle();
    HSSFFont xls_header_font = xls_workbook.createFont();

    xls_header_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    xls_header_cell_style.setFont(xls_header_font);
    xls_header_cell_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    xls_header_cell_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    xls_header_cell_style.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());
    //xls_header_cell_style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);

    java.util.Map<String, HSSFCellStyle> cellStyles = new java.util.HashMap<String, HSSFCellStyle>();

    HSSFDataFormat xls_data_format = xls_workbook.createDataFormat();

    HSSFCellStyle xls_date_cell_style = xls_workbook.createCellStyle();
    xls_date_cell_style.setDataFormat(xls_data_format.getFormat("d.m.yyyy"));
    cellStyles.put("d.m.yyyy", xls_date_cell_style);

    HSSFCellStyle xls_double_cell_style = xls_workbook.createCellStyle();
    xls_double_cell_style.setDataFormat(xls_data_format.getFormat("#,##0.00"));
    cellStyles.put("#,##0.00", xls_double_cell_style);

    while (columns.hasMoreElements()) {
        TableColumn column = columns.nextElement();

        HSSFCell xls_cell = xls_row.createCell(cell++);
        xls_cell.setCellValue(new HSSFRichTextString(column.getHeaderValue().toString()));
        xls_cell.setCellStyle(xls_header_cell_style);
    }/*w w  w . j  ava  2s. c om*/

    TableModel tableModel = source.getModel();
    DbTableModel dbTableModel = (tableModel instanceof DbTableModel) ? (DbTableModel) tableModel : null;
    Integer fetchSize = null;

    if (dbTableModel != null) {
        try {
            fetchSize = dbTableModel.getDataSource().getFetchSize();
            dbTableModel.getDataSource().setFetchSize(2000);
        } catch (SQLException ex) {
            Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex);
            fetchSize = null;
        }
    }

    short row = 1;

    JWProgressMonitor progress = new JWProgressMonitor((java.awt.Frame) null);

    progress.setTitle("Izvoz podatkov v Excel");
    progress.setMax(tableModel.getRowCount());

    progress.setVisible(true);

    try {
        while (row <= tableModel.getRowCount()) {
            xls_row = xls_sheet.createRow(row);
            cell = 0;

            HSSFCell xls_cell = xls_row.createCell(cell++);
            if (countRows) {
                xls_cell.setCellValue(new HSSFRichTextString(Short.toString(row)));
            }

            while (cell <= columnModel.getColumnCount()) {
                Object value = tableModel.getValueAt(source.convertRowIndexToModel(row - 1),
                        source.convertColumnIndexToModel(cell - 1));
                if (value != null) {
                    if (value instanceof DbTableModel.ColumnDescriptor.ValueMethod) {
                        DbTableModel.ColumnDescriptor.ValueMethod vm = (DbTableModel.ColumnDescriptor.ValueMethod) value;

                        if (vm.getColumnNames().size() == 1) {
                            java.util.List<Object> values = vm.getValues();
                            java.util.List<String> cellFormats = vm.getCellFormats();

                            for (String cellFormat : cellFormats) {
                                if (cellFormat != null) {
                                    if (!cellStyles.containsKey(cellFormat)) {
                                        HSSFCellStyle xls_cell_style = xls_workbook.createCellStyle();
                                        xls_cell_style.setDataFormat(xls_data_format.getFormat(cellFormat));
                                        cellStyles.put(cellFormat, xls_cell_style);
                                    }
                                }
                            }

                            Object vm_value = values.get(0);
                            HSSFCellStyle xls_cell_style = cellFormats.get(0) == null ? null
                                    : cellStyles.get(cellFormats.get(0));

                            if (vm_value != null) {
                                xls_cell = xls_row.createCell(cell);

                                if (vm_value instanceof java.util.Date) {
                                    xls_cell.setCellValue((java.util.Date) vm_value);
                                    xls_cell.setCellStyle(
                                            xls_cell_style == null ? xls_date_cell_style : xls_cell_style);
                                } else if (vm_value instanceof java.lang.Number) {
                                    xls_cell.setCellValue(((java.lang.Number) vm_value).doubleValue());
                                    if ((vm_value instanceof java.math.BigDecimal)
                                            || (vm_value instanceof java.lang.Double)
                                            || (vm_value instanceof java.lang.Float)) {
                                        xls_cell.setCellStyle(xls_cell_style == null ? xls_double_cell_style
                                                : xls_cell_style);
                                    }
                                } else if (vm_value instanceof java.lang.Boolean) {
                                    xls_cell.setCellValue(((java.lang.Boolean) vm_value).booleanValue());
                                } else {
                                    xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                                }
                            }
                        } else {
                            xls_cell = xls_row.createCell(cell);
                            xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                        }
                    } else {
                        xls_cell = xls_row.createCell(cell);
                        xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                    }
                }
                cell++;
            }

            row++;
            progress.next();
        }

        for (cell = 0; cell <= columnModel.getColumnCount(); cell++) {
            xls_sheet.autoSizeColumn(cell);
        }

        xls_sheet.createFreezePane(1, 1);
    } finally {
        progress.setVisible(false);

        if (fetchSize != null) {
            try {
                dbTableModel.getDataSource().setFetchSize(fetchSize);
            } catch (SQLException ex) {
                Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex);
            }
        }
    }

    return xls_workbook;
}

From source file:com.project.jsica.cdi.ReporteBean.java

public void reporte2(List<ReportePermisoBean> reporte) {
    LOG.info("TAMAO reporte: " + reporte.size());
    FacesContext fc = FacesContext.getCurrentInstance();
    HttpServletResponse response = (HttpServletResponse) fc.getExternalContext().getResponse();

    HSSFWorkbook libro = new HSSFWorkbook();

    HSSFFont fuente = libro.createFont();
    fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle estiloCeldaCabecera = libro.createCellStyle();
    estiloCeldaCabecera.setFont(fuente);
    estiloCeldaCabecera.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    DataFormat format = libro.createDataFormat();

    HSSFCellStyle style = libro.createCellStyle();
    style.setDataFormat(format.getFormat("hh:mm:ss"));

    HSSFCellStyle fechas = libro.createCellStyle();
    fechas.setDataFormat(format.getFormat("dd.MM.yyyy"));

    HSSFSheet hoja = libro.createSheet("hoja 1");

    //CREAR LAS CABECERAS
    String[] cabeceras = { "CODIGO", "NOMBRE", "HORA INICIO", "HORA FIN", "HORAS", "MINUTOS", "FECHA",
            "MOTIVO" };

    HSSFRow filaCabecera = hoja.createRow(0);

    for (int x = 0; x < cabeceras.length; x++) {
        HSSFCell cabecera = filaCabecera.createCell(x);
        cabecera.setCellValue(cabeceras[x]);
        cabecera.setCellStyle(estiloCeldaCabecera);
    }/*from ww w  . j ava2s  .co m*/
    //FIN DE CABECERAS
    for (int i = 0; i < reporte.size(); i++) {

        HSSFRow fila = hoja.createRow(i + 1);

        HSSFCell columna1 = fila.createCell(0);
        columna1.setCellValue(reporte.get(i).getCodigo());

        HSSFCell columna2 = fila.createCell(1);
        columna2.setCellValue(reporte.get(i).getNombre());

        HSSFCell columna3 = fila.createCell(2);
        columna3.setCellValue(reporte.get(i).getHoraInicio());
        columna3.setCellStyle(style);

        HSSFCell columna4 = fila.createCell(3);
        columna4.setCellValue(reporte.get(i).getHoraFin());
        columna4.setCellStyle(style);

        HSSFCell columna5 = fila.createCell(4);
        columna5.setCellValue(reporte.get(i).getHoras());

        HSSFCell columna6 = fila.createCell(5);
        columna6.setCellValue(reporte.get(i).getMinutos());

        HSSFCell columna7 = fila.createCell(6);
        columna7.setCellValue(reporte.get(i).getFechaReal());
        columna7.setCellStyle(fechas);

        HSSFCell columna8 = fila.createCell(7);
        columna8.setCellValue(reporte.get(i).getMotivo());
    }

    try {

        OutputStream output = response.getOutputStream();

        libro.write(output);
        output.close();

        fc.responseComplete();

    } catch (IOException ex) {
        LOG.info("ERROR: " + ex);
    }
}

From source file:com.project.jsica.cdi.ReporteBean.java

public void reporte3() {
    if (nuevoReporte) {
        LOG.info("OPCION: " + opcionReporte);
        String nombreReporte = "";
        int filas = 0;
        if (opcionReporte == 2) {
            reporte = registroAsistenciaController.buscarXArea(areaSeleccionada, desde, hasta);
            LOG.info("TAMAO reporte: " + reporte.size());
            nombreReporte = "Reporte de asistencia por area";
            filas = 1;/*from  w  w w  . jav a 2 s .  c om*/
        } else if (opcionReporte == 1) {
            reporte = registroAsistenciaController.buscarXEmpleado(empleado, desde, hasta);
            LOG.info("TAMAO reporte: " + reporte.size());
            nombreReporte = "Reporte de asistencia por empleado";
            filas = 0;
        }

        FacesContext fc = FacesContext.getCurrentInstance();
        HttpServletResponse response = (HttpServletResponse) fc.getExternalContext().getResponse();

        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=" + nombreReporte + ".xls");

        HSSFWorkbook libro = new HSSFWorkbook();

        HSSFFont fuente = libro.createFont();
        fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle estiloCeldaCabecera = libro.createCellStyle();
        estiloCeldaCabecera.setFont(fuente);
        estiloCeldaCabecera.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        DataFormat format = libro.createDataFormat();

        HSSFCellStyle style = libro.createCellStyle();
        style.setDataFormat(format.getFormat("hh:mm:ss"));

        HSSFCellStyle fechas = libro.createCellStyle();
        fechas.setDataFormat(format.getFormat("dd.MM.yyyy"));

        HSSFSheet hoja = libro.createSheet("Reporte de Asistencias");

        //CREAR LAS CABECERAS
        String[] cabeceras = { "CODIGO", "APELLIDOS Y NOMBRES", "FECHA", "TIPO", "HORA DE INGRESO",
                "HORA DE SALIDA", "MARCACION DE ENTRADA", "MARCACION DE SALIDA", "TARDANZA(Minutos)",
                "SALIDA REFRIGERIO", "ENTRADA REFRIGERIO", "MARCACION SALIDA R", "MARCACION DE ENTRADA R",
                "TARDANZA(refrigerio)", "TARDANZA TOTAL" };

        if (filas == 1) {
            HSSFRow filaArea = hoja.createRow(0);
            HSSFCell Area = filaArea.createCell(0);
            Area.setCellValue("AREA");
            Area.setCellStyle(estiloCeldaCabecera);

            HSSFCell nombre = filaArea.createCell(1);
            nombre.setCellValue(areaSeleccionada.getNombre() + "");
        }

        HSSFRow filaCabecera = hoja.createRow(filas);

        for (int x = 0; x < cabeceras.length; x++) {
            HSSFCell cabecera = filaCabecera.createCell(x);
            cabecera.setCellValue(cabeceras[x]);
            cabecera.setCellStyle(estiloCeldaCabecera);
        }
        //FIN DE CABECERAS
        for (int i = filas; i < reporte.size(); i++) {

            HSSFRow fila = hoja.createRow(i + 1);

            HSSFCell columna1 = fila.createCell(0);
            columna1.setCellValue(reporte.get(i).getEmpleado().getCodigo());

            HSSFCell columna2 = fila.createCell(1);
            columna2.setCellValue(reporte.get(i).getEmpleado().getNombreCompleto());

            HSSFCell columna3 = fila.createCell(2);
            columna3.setCellValue(reporte.get(i).getFecha());
            columna3.setCellStyle(fechas);

            HSSFCell columna4 = fila.createCell(3);
            columna4.setCellValue(reporte.get(i).getTipo() + "");

            HSSFCell columna5 = fila.createCell(4);
            columna5.setCellValue(reporte.get(i).getHoraEntrada());
            columna5.setCellStyle(style);

            HSSFCell columna6 = fila.createCell(5);
            columna6.setCellValue(reporte.get(i).getHoraSalida());
            columna6.setCellStyle(style);

            HSSFCell columna7 = fila.createCell(6);
            if (reporte.get(i).getMarcacionInicio() != null) {
                columna7.setCellValue(reporte.get(i).getMarcacionInicio());
                columna7.setCellStyle(style);
            } else {
                columna7.setCellValue("No marco.");
            }

            HSSFCell columna8 = fila.createCell(7);
            if (reporte.get(i).getMarcacionFin() != null) {
                columna8.setCellValue(reporte.get(i).getMarcacionFin());
                columna8.setCellStyle(style);
            } else {
                columna8.setCellValue("No marco.");
            }

            HSSFCell columna9 = fila.createCell(8);
            int minutos = (int) ((reporte.get(i).getMilisTardanzaTotal() / (1000 * 60)) % 60);
            columna9.setCellValue(minutos);

            HSSFCell columna10 = fila.createCell(9);
            columna10.setCellValue(reporte.get(i).getHoraSalidaRefrigerio());
            columna10.setCellStyle(style);

            HSSFCell columna11 = fila.createCell(10);
            columna11.setCellValue(reporte.get(i).getHoraEntradaRefrigerio());
            columna11.setCellStyle(style);

            HSSFCell columna12 = fila.createCell(11);
            if (reporte.get(i).getMarcacionInicioRefrigerio() != null) {
                columna12.setCellValue(reporte.get(i).getMarcacionInicioRefrigerio());
                columna12.setCellStyle(style);
            } else {
                columna12.setCellValue("No marco.");
            }

            HSSFCell columna13 = fila.createCell(12);
            if (reporte.get(i).getMarcacionFinRefrigerio() != null) {
                columna13.setCellValue(reporte.get(i).getMarcacionFinRefrigerio());
                columna13.setCellStyle(style);
            } else {
                columna13.setCellValue("No marco.");
            }

            HSSFCell columna14 = fila.createCell(13);
            columna14.setCellValue((int) ((reporte.get(i).getMilisTardanzaRefrigerio() / (1000 * 60)) % 60));

            HSSFCell columna15 = fila.createCell(14);
            columna15.setCellValue((int) ((reporte.get(i).getMilisTardanzaTotalFinal() / (1000 * 60)) % 60));

        }

        try {
            OutputStream output = response.getOutputStream();

            libro.write(output);
            output.close();

            fc.responseComplete();
        } catch (IOException ex) {
            LOG.info("ERROR: " + ex);
        }

        nuevoReporte = false;
    }

}

From source file:com.pureinfo.srm.reports.table.data.pinggu.ExcelExporterMultiSheetImpl.java

License:Open Source License

public void export(HSSFWorkbook workbook, IExportGoods _goods) throws PureException {
    // 1. to create sheet
    HSSFSheet sheet = workbook.createSheet(_goods.getName());
    workbook.setSheetOrder(_goods.getName(), 0);

    // 2. to export headers
    if (_goods.hasHeader()) {
        HSSFCellStyle headerStyle = getHeaderStyle(workbook);
        exportHeaders(sheet, _goods.getHeaders(), headerStyle);
    }/*from   w w  w.j  a va  2 s.c om*/

    // 3. to export data
    Object[] values;
    HSSFCellStyle dateStyle = getDateStyle(workbook);

    int nRowNum = 1;
    Iterator iter = _goods.iterator();
    while (iter.hasNext()) {
        values = _goods.unpackGoods(iter.next());
        exportRow(sheet, values, nRowNum++, dateStyle);
    }
}

From source file:com.qcadoo.mes.qualityControls.print.QualityControlForBatchXlsView.java

License:Open Source License

@Override
protected final String addContent(final Map<String, Object> model, final HSSFWorkbook workbook,
        final Locale locale) {
    HSSFSheet sheet = workbook.createSheet(
            translationService.translate("qualityControls.qualityControlForBatch.report.title", locale));
    sheet.setZoom(4, 3);/*w w  w .  j  ava2  s. c  o  m*/
    addOrderHeader(sheet, locale);
    addOrderSeries(model, sheet);
    return translationService.translate("qualityControls.qualityControlForBatch.report.fileName", locale);
}

From source file:com.qcadoo.mes.qualityControls.print.QualityControlForOperationXlsView.java

License:Open Source License

@Override
protected final String addContent(final Map<String, Object> model, final HSSFWorkbook workbook,
        final Locale locale) {
    HSSFSheet sheet = workbook.createSheet(
            translationService.translate("qualityControls.qualityControlForOperation.report.title", locale));
    sheet.setZoom(4, 3);//from w  w  w. ja  v a  2  s  . c om
    addOrderHeader(sheet, locale);
    addOrderSeries(model, sheet, locale);
    return translationService.translate("qualityControls.qualityControlForOperation.report.fileName", locale);
}

From source file:com.qcadoo.mes.qualityControls.print.QualityControlForOrderXlsView.java

License:Open Source License

@Override
protected final String addContent(final Map<String, Object> model, final HSSFWorkbook workbook,
        final Locale locale) {
    HSSFSheet sheet = workbook.createSheet(
            translationService.translate("qualityControls.qualityControlForOrder.report.title", locale));
    sheet.setZoom(4, 3);/*from w w  w  .  j  a  v a 2 s  .com*/
    addOrderHeader(sheet, locale);
    addOrderSeries(model, sheet, locale);
    return translationService.translate("qualityControls.qualityControlForOrder.report.fileName", locale);
}

From source file:com.qcadoo.mes.qualityControls.print.QualityControlForUnitXlsView.java

License:Open Source License

@Override
protected final String addContent(final Map<String, Object> model, final HSSFWorkbook workbook,
        final Locale locale) {
    HSSFSheet sheet = workbook.createSheet(
            translationService.translate("qualityControls.qualityControlForUnit.report.title", locale));
    sheet.setZoom(4, 3);/*from   w  ww . j ava 2s  . co  m*/
    addOrderHeader(sheet, locale);
    addOrderSeries(model, sheet);
    return translationService.translate("qualityControls.qualityControlForUnit.report.fileName", locale);
}

From source file:com.qcadoo.mes.technologies.print.TechnologiesTechnologyDetailsXlsView.java

License:Open Source License

@Override
protected String addContent(final Map<String, Object> model, final HSSFWorkbook workbook, final Locale locale) {
    HSSFSheet sheet = workbook.createSheet(
            translationService.translate("technologies.technologiesTechnologyDetails.report.title", locale));

    addOrderHeader(sheet, locale);/*from   www .  ja v  a2s . com*/
    addOrderSeries(model, sheet, locale);

    return translationService.translate("technologies.technologiesTechnologyDetails.report.fileName", locale);
}

From source file:com.qcadoo.report.api.xls.XlsDocumentService.java

License:Open Source License

@Override
public final void generateDocument(final Entity entity, final Locale locale, final Rectangle pageSize)
        throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet(getReportTitle(locale));
    addHeader(sheet, locale, entity);/*ww w. ja  va2  s. c  o m*/
    addSeries(sheet, entity);
    sheet.setZoom(4, 3);
    FileOutputStream outputStream = null;
    try {
        outputStream = new FileOutputStream(fileService.createReportFile(
                (String) entity.getField("fileName") + "." + ReportService.ReportType.XLS.getExtension()));
        workbook.write(outputStream);
    } catch (IOException e) {
        LOG.error("Problem with generating document - " + e.getMessage());
        if (outputStream != null) {
            outputStream.close();
        }
        throw e;
    }
    outputStream.close();
}