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

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

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

Introduction

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

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

From source file:Almacen.Reporte2.java

private void jButton6ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton6ActionPerformed
    // TODO add your handling code here:
    h = new Herramientas(this.usr, 0);
    h.session(sessionPrograma);//from ww w.  j  ava  2s  . co  m
    javax.swing.JFileChooser jF1 = new javax.swing.JFileChooser();
    jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    String ruta = null;
    if (jF1.showSaveDialog(null) == jF1.APPROVE_OPTION) {
        ruta = jF1.getSelectedFile().getAbsolutePath();
        if (ruta != null) {
            File archivoXLS = new File(ruta + ".xls");
            try {
                if (archivoXLS.exists())
                    archivoXLS.delete();
                archivoXLS.createNewFile();
                Workbook libro = new HSSFWorkbook();
                FileOutputStream archivo = new FileOutputStream(archivoXLS);
                Sheet hoja = libro.createSheet("reporte2");
                for (int ren = 0; ren < (t_datos1.getRowCount() + 1); ren++) {
                    Row fila = hoja.createRow(ren);
                    for (int col = 0; col < t_datos1.getColumnCount(); col++) {
                        Cell celda = fila.createCell(col);
                        if (ren == 0) {
                            celda.setCellValue(t_datos1.getColumnName(col));
                        } else {
                            try {
                                celda.setCellValue(t_datos1.getValueAt(ren - 1, col).toString());
                            } catch (Exception e) {
                                celda.setCellValue("");
                            }
                        }
                    }
                }
                libro.write(archivo);
                archivo.close();
                Desktop.getDesktop().open(archivoXLS);
            } catch (Exception e) {
                System.out.println(e);
                JOptionPane.showMessageDialog(this,
                        "No se pudo realizar el reporte si el archivo esta abierto");
            }
        }
    }
}

From source file:Almacen.Reporte2.java

private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton4ActionPerformed
    // TODO add your handling code here:
    h = new Herramientas(this.usr, 0);
    h.session(sessionPrograma);/*from  w  w w. j  a v  a 2s  . co  m*/
    javax.swing.JFileChooser jF1 = new javax.swing.JFileChooser();
    jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    String ruta = null;
    if (jF1.showSaveDialog(null) == jF1.APPROVE_OPTION) {
        ruta = jF1.getSelectedFile().getAbsolutePath();
        if (ruta != null) {
            File archivoXLS = new File(ruta + ".xls");
            try {
                if (archivoXLS.exists())
                    archivoXLS.delete();
                archivoXLS.createNewFile();
                Workbook libro = new HSSFWorkbook();
                FileOutputStream archivo = new FileOutputStream(archivoXLS);
                Sheet hoja = libro.createSheet("reporte1");
                for (int ren = 0; ren < (t_datos.getRowCount() + 1); ren++) {
                    Row fila = hoja.createRow(ren);
                    for (int col = 0; col < t_datos.getColumnCount(); col++) {
                        Cell celda = fila.createCell(col);
                        if (ren == 0) {
                            celda.setCellValue(t_datos.getColumnName(col));
                        } else {
                            try {
                                celda.setCellValue(t_datos.getValueAt(ren - 1, col).toString());
                            } catch (Exception e) {
                                celda.setCellValue("");
                            }
                        }
                    }
                }
                libro.write(archivo);
                archivo.close();
                Desktop.getDesktop().open(archivoXLS);
            } catch (Exception e) {
                System.out.println(e);
                JOptionPane.showMessageDialog(this,
                        "No se pudo realizar el reporte si el archivo esta abierto");
            }
        }
    }
}

From source file:Almacen.Reporte2.java

private void jButton7ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton7ActionPerformed
    // TODO add your handling code here:
    h = new Herramientas(this.usr, 0);
    h.session(sessionPrograma);/*w  ww .j  ava 2 s .c o m*/
    javax.swing.JFileChooser jF1 = new javax.swing.JFileChooser();
    jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    String ruta = null;
    if (jF1.showSaveDialog(null) == jF1.APPROVE_OPTION) {
        ruta = jF1.getSelectedFile().getAbsolutePath();
        if (ruta != null) {
            File archivoXLS = new File(ruta + ".xls");
            try {
                if (archivoXLS.exists())
                    archivoXLS.delete();
                archivoXLS.createNewFile();
                Workbook libro = new HSSFWorkbook();
                FileOutputStream archivo = new FileOutputStream(archivoXLS);
                Sheet hoja = libro.createSheet("reporte1");
                for (int ren = 0; ren < (t_datos2.getRowCount() + 1); ren++) {
                    Row fila = hoja.createRow(ren);
                    for (int col = 0; col < t_datos2.getColumnCount(); col++) {
                        Cell celda = fila.createCell(col);
                        if (ren == 0) {
                            celda.setCellValue(t_datos2.getColumnName(col));
                        } else {
                            try {
                                celda.setCellValue(t_datos2.getValueAt(ren - 1, col).toString());
                            } catch (Exception e) {
                                celda.setCellValue("");
                            }
                        }
                    }
                }
                libro.write(archivo);
                archivo.close();
                Desktop.getDesktop().open(archivoXLS);
            } catch (Exception e) {
                System.out.println(e);
                JOptionPane.showMessageDialog(this,
                        "No se pudo realizar el reporte si el archivo esta abierto");
            }
        }
    }
}

From source file:ar.edu.unrc.gametictactoe.performanceandtraining.configurations.StatisticExperiment.java

License:Open Source License

/**
 *
 * @param filePath/*from  ww w  .j a v  a2  s  .co  m*/
 * @param backupFiles
 * @param resultsPerFile
 * @param resultsRandom
 * @param randomPerceptronFile <p>
 * @throws IOException
 * @throws InvalidFormatException
 */
public void exportToExcel(String filePath, List<File> backupFiles, Map<File, StatisticForCalc> resultsPerFile,
        Map<File, StatisticForCalc> resultsRandom, File randomPerceptronFile)
        throws IOException, InvalidFormatException {
    InputStream inputXLSX = this.getClass()
            .getResourceAsStream("/ar/edu/unrc/gametictactoe/resources/EstadisticasTicTacToe.xlsx");
    Workbook wb = WorkbookFactory.create(inputXLSX);

    try (FileOutputStream outputXLSX = new FileOutputStream(
            filePath + "_" + dateFormater.format(dateForFileName) + "_STATISTICS" + ".xlsx")) {
        //============= imptimimos en la hoja de % Of Games Won ===================
        Sheet sheet = wb.getSheetAt(0);
        //Estilo par los titulos de las tablas
        int rowStartTitle = 0;
        int colStartTitle = 2;
        int rowStart = 1;
        int colStart = 3;
        Row rowPlayer1;
        Row rowPlayer2;
        Row rowDraw;
        // Luego creamos el objeto que se encargar de aplicar el estilo a la celda
        Font fontCellTitle = wb.createFont();
        fontCellTitle.setFontHeightInPoints((short) 10);
        fontCellTitle.setFontName("Arial");
        fontCellTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle CellStyleTitle = wb.createCellStyle();
        CellStyleTitle.setWrapText(true);
        CellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        CellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        CellStyleTitle.setFont(fontCellTitle);

        // Establecemos el tipo de sombreado de nuestra celda
        CellStyleTitle.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        CellStyleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle);
        //estilo titulo finalizado

        //Estilo de celdas con los valores de las estadisticas
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);
        /* We are now ready to set borders for this style */
        /* Draw a thin left border */
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        /* Add medium right border */
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        /* Add dashed top border */
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        /* Add dotted bottom border */
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        //estilo celdas finalizado
        //loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle);
        rowPlayer1 = sheet.getRow(rowStart);
        rowPlayer2 = sheet.getRow(rowStart + 1);
        rowDraw = sheet.getRow(rowStart + 2);
        for (int file = 0; file < backupFiles.size(); file++) {
            Cell cellPlayer1 = rowPlayer1.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC);
            Cell cellPlayer2 = rowPlayer2.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC);
            Cell cellDraw = rowDraw.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC);
            cellPlayer1.setCellStyle(cellStyle);
            cellPlayer2.setCellStyle(cellStyle);
            cellDraw.setCellStyle(cellStyle);
            Double cellValuePlayer1 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer1();
            Double cellValuePlayer2 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer2();
            Double cellValueDraw = resultsPerFile.get(backupFiles.get(file)).getDrawRate();
            assert cellValuePlayer1 <= 100 && cellValuePlayer1 >= 0;
            assert cellValuePlayer2 <= 100 && cellValuePlayer2 >= 0;
            assert cellValueDraw <= 100 && cellValueDraw >= 0;
            //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100;
            cellDraw.setCellValue(cellValueDraw);
            cellPlayer1.setCellValue(cellValuePlayer1);
            cellPlayer2.setCellValue(cellValuePlayer2);
        }
        if (!resultsRandom.isEmpty()) {
            int file = 0;//hay que ir a buscar el randomfile
            Cell cellDraw = rowDraw.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC);
            Cell cellPlayer1 = rowPlayer1.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC);
            Cell cellPlayer2 = rowPlayer2.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC);
            cellDraw.setCellStyle(cellStyle);
            cellPlayer1.setCellStyle(cellStyle);
            cellPlayer2.setCellStyle(cellStyle);
            //                StatisticForCalc get = resultsRandom.get(randomPerceptronFile);
            //                Double cellValuePlayer1 = get.getWinRatePlayer1();
            Double cellValuePlayer1 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer1();
            Double cellValuePlayer2 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer2();
            Double cellValueDraw = resultsRandom.get(randomPerceptronFile).getDrawRate();
            //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100;

            cellPlayer1.setCellValue(cellValuePlayer1);
            cellPlayer2.setCellValue(cellValuePlayer2);
            cellDraw.setCellValue(cellValueDraw);
        }
        wb.write(outputXLSX);
    }
}

From source file:archivocsv.models.Reporte.java

public static ArrayList<Reporte> generarReporteExcel(String anio, String mes) throws IOException {
    ArrayList<Reporte> archivoExcel = new ArrayList<>();
    try {/*from ww  w. java2 s. c om*/
        FileChooser elegirArchivo = new FileChooser();
        FileChooser.ExtensionFilter filtroExt = new FileChooser.ExtensionFilter("Archivos Excel (*.xls)",
                "*.xls");
        elegirArchivo.getExtensionFilters().add(filtroExt);
        File archivo = elegirArchivo.showSaveDialog(archivoCsv.getVentanaPrincipal());
        archivo.createNewFile();
        Workbook libro = new HSSFWorkbook();
        FileOutputStream enviar = new FileOutputStream(archivo);
        Sheet hoja = libro.createSheet("Reportes");
        Sheet hoja2 = libro.createSheet("Entradas Tarde");
        Row fila = hoja.createRow(0);
        Row fila2 = hoja2.createRow(0);
        for (int c = 0; c < 5; c++) {
            Cell celda = fila.createCell(c);
            if (c == 0) {
                celda.setCellValue("ID");
            }
            if (c == 1) {
                celda.setCellValue("Nombre");
            }
            if (c == 2) {
                celda.setCellValue("Entrada");
            }
            if (c == 3) {
                celda.setCellValue("Salida");
            }
            if (c == 4) {
                celda.setCellValue("Horas Trabajadas");
            }
        }
        for (int c2 = 0; c2 < 3; c2++) {
            Cell celda2 = fila2.createCell(c2);
            if (c2 == 0) {
                celda2.setCellValue("ID");
            }
            if (c2 == 1) {
                celda2.setCellValue("Nombre");
            }
            if (c2 == 2) {
                celda2.setCellValue("Entrada");
            }
        }
        Connection con = Helper.getConnection();
        String entrada = "SELECT * FROM t_marcacion WHERE marcacion LIKE '%/" + mes + "/" + anio + "%'"
                + " AND marcacion LIKE '%a%'";
        ResultSet rs = con.createStatement().executeQuery(entrada);
        ResultSet rs3 = con.createStatement().executeQuery(entrada);
        int contar = 0;
        int contar2 = 1;
        while (rs.next()) {
            contar += 1;
            Row filaDatos = hoja.createRow(contar);
            Cell celdaID = filaDatos.createCell(0);
            Cell celdaNombre = filaDatos.createCell(1);
            Cell celdaEntrada = filaDatos.createCell(2);
            Cell celdaSalida = filaDatos.createCell(3);
            Cell celdaHoras = filaDatos.createCell(4);
            Reporte reporte = new Reporte();
            reporte.setEntrada(rs.getString("marcacion"));
            celdaEntrada.setCellValue(reporte.getEntrada());
            reporte.setId(rs.getString("id"));
            celdaID.setCellValue(reporte.getId());
            reporte.setNombre(rs.getString("nombre"));
            celdaNombre.setCellValue(reporte.getNombre());
            //Query que verifica si existe el registro de salida
            String coincidir = "SELECT * FROM t_marcacion WHERE id=" + rs.getString("id")
                    + " AND marcacion LIKE '%" + rs.getString("marcacion").substring(0, 10) + "%'"
                    + " AND marcacion LIKE '%p%'";
            ResultSet rs2 = con.createStatement().executeQuery(coincidir);
            if (rs2.next()) {
                reporte.setSalida(rs2.getString("marcacion"));
                celdaSalida.setCellValue(reporte.getSalida());
                String horaInicial = rs.getString("marcacion").substring(11, 15) + " AM";
                String horaFinal = rs2.getString("marcacion").substring(11, 15) + " PM";
                if (horaFinal.substring(0, 2).equals("12")) {
                    horaFinal = rs2.getString("marcacion").substring(11, 15) + " AM";
                }
                DateFormat sdf = new SimpleDateFormat("KK:mm a");
                Date date = sdf.parse(horaInicial);
                Date date2 = sdf.parse(horaFinal);

                double hrsInicialMs = date.getTime();
                double hrsFinalMs = date2.getTime();
                double diferencia = hrsFinalMs - hrsInicialMs;
                double resta = (diferencia / (1000 * 60 * 60));
                int primerNumero = (int) resta;
                double segundoNumero = resta - primerNumero;
                int convertirNumero = (int) (segundoNumero * 60);
                int restarAlumerzo = primerNumero - 1;
                if (convertirNumero == 0) {
                    reporte.setHorasTrabajadas(restarAlumerzo + ":00");
                    celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                } else {
                    if (convertirNumero > 0 & convertirNumero < 10) {
                        reporte.setHorasTrabajadas(restarAlumerzo + ":0" + convertirNumero);
                        celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                    } else {
                        reporte.setHorasTrabajadas(restarAlumerzo + ":" + convertirNumero);
                        celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                    }
                }
            } else {
                reporte.setSalida("Sin registro");
                celdaSalida.setCellValue(reporte.getSalida());
                reporte.setHorasTrabajadas("Sin registro");
                celdaHoras.setCellValue(reporte.getHorasTrabajadas());
            }
        }
        while (rs3.next()) {
            Row filaDatos2 = hoja2.createRow(contar2);
            Cell celdaId = filaDatos2.createCell(0);
            Cell celdaNombre = filaDatos2.createCell(1);
            Cell celdaMarcacion = filaDatos2.createCell(2);
            Reporte reporte = new Reporte();
            DateFormat sdf = new SimpleDateFormat("KK:mm a");
            String horaInicio = "08:00 AM";
            String horaFin = "12:00 PM";
            String horaEntrada = rs3.getString("marcacion");
            if (horaEntrada.substring(11, 12).equals("0")) {
                horaEntrada = rs3.getString("marcacion").substring(11, 16) + " AM";
            } else {
                if (horaEntrada.substring(11, 13).equals("10") || horaEntrada.substring(11, 13).equals("11")) {
                    horaEntrada = rs3.getString("marcacion").substring(11, 16) + " AM";
                } else {
                    horaEntrada = rs3.getString("marcacion").substring(11, 15) + " AM";
                }
            }
            Date hrInicio = sdf.parse(horaInicio);
            Date hrFin = sdf.parse(horaFin);
            Date hrEntrada = sdf.parse(horaEntrada);
            Calendar calInicio = new GregorianCalendar();
            Calendar calFin = new GregorianCalendar();
            Calendar calEntrada = new GregorianCalendar();
            calInicio.setTime(hrInicio);
            calFin.setTime(hrFin);
            calEntrada.setTime(hrEntrada);

            if (calEntrada.after(calInicio) & calEntrada.before(calFin)) {
                contar2 += 1;
                reporte.setId(rs3.getString("id"));
                celdaId.setCellValue(reporte.getId());
                reporte.setNombre(rs3.getString("nombre"));
                celdaNombre.setCellValue(reporte.getNombre());
                reporte.setEntrada(rs3.getString("marcacion"));
                celdaMarcacion.setCellValue(reporte.getEntrada());
                archivoExcel.add(reporte);
            }
        }
        hoja.setColumnWidth(0, 850);
        hoja.setColumnWidth(1, 3000);
        hoja.setColumnWidth(2, 6000);
        hoja.setColumnWidth(3, 6000);
        hoja.setColumnWidth(4, 4000);
        hoja2.setColumnWidth(0, 850);
        hoja2.setColumnWidth(1, 3000);
        hoja2.setColumnWidth(2, 6000);
        libro.write(enviar);
        enviar.close();
        Desktop.getDesktop().open(archivo);
    } catch (Exception e) {

    }
    return archivoExcel;
}

From source file:au.gov.ansto.bragg.quokka.experiment.util.ExperimentModelUtils.java

License:Open Source License

public static void saveExperimentToExcel(Experiment experiment, String filename) throws IOException {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Quokka Multi-Sample Scan");
    int rowCounter = 0;
    if (experiment.isControlledEnvironment()) {
        for (ControlledAcquisition acquisition : experiment.getAcquisitionGroups()) {
            for (SampleEnvironment sampleEnvironment : experiment.getSampleEnvironments()) {
                Row row = sheet.createRow(rowCounter++);
                Cell cell = row.createCell(0);
                cell.setCellValue(sampleEnvironment.getControllerId());
                cell = row.createCell(1);
                cell.setCellValue(acquisition.getEnvSettings().get(sampleEnvironment).getPreset());
            }/*from   w  w w.j a v a 2s  .c  om*/
            rowCounter = saveAcquisitionToExcel(acquisition, sheet, rowCounter);
        }
    } else {
        rowCounter = saveAcquisitionToExcel(experiment.getNormalAcquisition(), sheet, rowCounter);
    }
    FileOutputStream fileOut = new FileOutputStream(filename);
    workbook.write(fileOut);
    fileOut.close();
}

From source file:balony.tableWriter.java

public static void writeTable(JFrame parent, Object[][] tableData, String[] colNames) {
    String opts[] = { "Tab-delimited text (raw)", "Excel .xls", "Excel 2007+ .xlsx" };
    int i = JOptionPane.showOptionDialog(parent, "Choose output format:", "Export Table",
            JOptionPane.DEFAULT_OPTION, JOptionPane.QUESTION_MESSAGE, null, opts, opts[0]);
    if (i == JOptionPane.CLOSED_OPTION) {
        return;/* w  w w .jav a 2 s  .c  o  m*/
    }

    String[] chars = { ":", "[", "]", "/", "\\", "?", "*" };
    String fname = parent.getTitle();
    for (String c : chars) {
        fname = fname.replace(c, "");
    }

    JFileChooser jfc = new JFileChooser();
    if (i == 0) {
        jfc.setFileFilter(new FileNameExtensionFilter("Tab-delimited text files", "txt"));
        jfc.setSelectedFile(new File(fname.concat(".txt")));
    }

    if (i == 1) {
        jfc.setFileFilter(new FileNameExtensionFilter("Excel .xls files", "xls"));
        jfc.setSelectedFile(new File(fname.concat(".xls")));
    }

    if (i == 2) {
        jfc.setFileFilter(new FileNameExtensionFilter("Excel .xlsx files", "xlsx"));
        jfc.setSelectedFile(new File(fname.concat(".xlsx")));
    }

    int rv = jfc.showSaveDialog(parent);
    if (rv == JFileChooser.APPROVE_OPTION) {
        File f = jfc.getSelectedFile();

        try {

            if (i == 0) {
                BufferedWriter out = new BufferedWriter(new FileWriter(f));
                for (String columnName : colNames) {
                    out.write(columnName);
                    out.write("\t");
                }
                out.newLine();
                for (Object[] tableData1 : tableData) {
                    for (int k = 0; k < colNames.length; k++) {
                        if (tableData1[k] != null) {
                            out.write(tableData1[k].toString());
                        }
                        out.write("\t");
                    }
                    out.newLine();
                }
                out.close();
            }

            if (i > 0) {

                Workbook wb;

                if (i == 1) {
                    wb = new HSSFWorkbook();
                } else {
                    wb = new XSSFWorkbook();
                }

                Sheet sheet = wb.createSheet(fname);
                Row r;
                CellStyle style;
                sheet.createFreezePane(0, 1);

                for (int j = 0; j < tableData.length; j++) {
                    r = sheet.createRow(j + 1);
                    for (int k = 0; k < colNames.length; k++) {

                        if (tableData[j][k] != null) {
                            Cell c = r.createCell(k);

                            if (tableData[j][k] instanceof Integer) {
                                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                                int v = ((Integer) tableData[j][k]);
                                c.setCellValue(v);
                            } else {
                                if (tableData[j][k] instanceof Double) {
                                    c.setCellType(Cell.CELL_TYPE_NUMERIC);
                                    double v = ((Double) tableData[j][k]);
                                    c.setCellValue(v);
                                } else {

                                    c.setCellType(Cell.CELL_TYPE_STRING);
                                    c.setCellValue(tableData[j][k].toString());

                                }
                            }
                        }
                    }
                }

                r = sheet.createRow(0);
                style = wb.createCellStyle();
                Font font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                style.setFont(font);

                for (int k = 0; k < colNames.length; k++) {
                    Cell c = r.createCell(k);
                    c.setCellType(Cell.CELL_TYPE_STRING);
                    c.setCellStyle(style);
                    c.setCellValue(colNames[k]);
                }

                FileOutputStream fos = new FileOutputStream(f);
                wb.write(fos);
                fos.close();
            }

        } catch (Exception ex) {
            Logger.getLogger(dataTable.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(parent, "File error: ".concat(ex.getLocalizedMessage()), "File Error",
                    JOptionPane.ERROR_MESSAGE);
            return;
        }
        int n = JOptionPane.showOptionDialog(parent, "File saved. Open in default application?", "Message",
                JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, null, null);
        if (n == JOptionPane.YES_OPTION) {
            try {
                Desktop d = Desktop.getDesktop();
                d.open(f);
            } catch (IOException e) {
                System.out.println(e.getLocalizedMessage());
            }
        }
    }
}

From source file:bandaru_excelreadwrite.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*/*from  w ww  .ja v  a  2 s .  com*/
    Use XSSF for xlsx format and for xls use HSSF
     */
    Workbook workbook = new XSSFWorkbook();

    /*
    create new sheet 
     */
    Sheet songsSheet = workbook.createSheet("Albums");

    XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle();
    /* Create XSSFFont object from the workbook */
    XSSFFont my_font = (XSSFFont) workbook.createFont();

    /*
    setting cell color
     */
    CellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    /*
     setting Header color
     */
    CellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
    style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style2.setAlignment(style2.ALIGN_CENTER);

    Row rowName = songsSheet.createRow(1);

    /*
    Merging the cells
     */
    songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));

    /*
    Applying style to attribute name
     */
    int nameCellIndex = 1;
    Cell namecell = rowName.createCell(nameCellIndex++);
    namecell.setCellValue("Name");
    namecell.setCellStyle(style);

    Cell cel = rowName.createCell(nameCellIndex++);
    cel.setCellValue("Bandaru, Sreekanth");

    /*
    Applying underline to Name
     */
    my_font.setUnderline(XSSFFont.U_SINGLE);
    my_style.setFont(my_font);
    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.cloneStyleFrom(my_style);
    combined.cloneStyleFrom(style);
    combined.setAlignment(combined.ALIGN_CENTER);

    cel.setCellStyle(combined);

    /*
    Applying  colors to header 
     */
    Row rowMain = songsSheet.createRow(3);
    SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3");

    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIME.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    /*
    setting new rule to apply alternate colors to cells having same Genre
     */
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"),
            CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"),
            CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"),
            CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"),
            CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"),
            CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26")

    };

    /*        
    setting new rule to apply alternate colors to cells having same Fenre
     */
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill3 = rule3.createPatternFormatting();
    fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"),
            CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"),
            CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"),
            CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"),
            CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"),
            CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"),
            CellRangeAddress.valueOf("A29:F29") };

    /*
    Applying above created rule formatting to cells
     */
    sheetCF.addConditionalFormatting(regionsAction, rule2);
    sheetCF.addConditionalFormatting(regionsAdv, rule3);

    /*
     Setting coloumn header values
     */
    int mainCellIndex = 0;
    CellStyle style4 = workbook.createCellStyle();
    XSSFFont my_font2 = (XSSFFont) workbook.createFont();
    my_font2.setBold(true);
    style4.setFont(my_font2);
    rowMain.setRowStyle(style4);
    rowMain.createCell(mainCellIndex++).setCellValue("SNO");
    rowMain.createCell(mainCellIndex++).setCellValue("Genre");
    rowMain.createCell(mainCellIndex++).setCellValue("Rating");
    rowMain.createCell(mainCellIndex++).setCellValue("Movie Name");
    rowMain.createCell(mainCellIndex++).setCellValue("Director");
    rowMain.createCell(mainCellIndex++).setCellValue("Release Date");

    /*
    populating cell values
     */
    int rowIndex = 4;
    int sno = 1;
    for (Song song : songList) {
        if (song.getSno() != 0) {

            Row row = songsSheet.createRow(rowIndex++);
            int cellIndex = 0;

            /*
            first place in row is Sno
             */
            row.createCell(cellIndex++).setCellValue(sno++);

            /*
            second place in row is  Genre
             */
            row.createCell(cellIndex++).setCellValue(song.getGenre());

            /*
            third place in row is Critic score
             */
            row.createCell(cellIndex++).setCellValue(song.getCriticscore());

            /*
            fourth place in row is Album name
             */
            row.createCell(cellIndex++).setCellValue(song.getAlbumname());

            /*
            fifth place in row is Artist
             */
            row.createCell(cellIndex++).setCellValue(song.getArtist());

            /*
            sixth place in row is marks in date
             */
            if (song.getReleasedate() != null) {

                Cell date = row.createCell(cellIndex++);

                DataFormat format = workbook.createDataFormat();
                CellStyle dateStyle = workbook.createCellStyle();

                dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy"));
                date.setCellStyle(dateStyle);

                date.setCellValue(song.getReleasedate());

                /*
                auto-resizing columns
                 */
                songsSheet.autoSizeColumn(6);
                songsSheet.autoSizeColumn(5);
                songsSheet.autoSizeColumn(4);
                songsSheet.autoSizeColumn(3);
                songsSheet.autoSizeColumn(2);
            }

        }
    }

    /*
    writing this workbook to excel file.
     */
    try {
        FileOutputStream fos = new FileOutputStream(FILE_PATH);
        workbook.write(fos);
        fos.close();

        System.out.println(FILE_PATH + " is successfully written");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:biz.webgate.dominoext.poi.component.kernel.simpleviewexport.WorkbooklExportProcessor.java

License:Apache License

public void process2HTTP(ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr,
        DateTimeHelper dth) {/*  ww w  .  jav  a  2s  . c  o m*/
    try {
        String strFileName = uis.getDownloadFileName();

        Workbook wbCurrent = null;
        if (strFileName.toLowerCase().endsWith(".xlsx")) {
            wbCurrent = new XSSFWorkbook();
        } else {
            wbCurrent = new HSSFWorkbook();
        }
        HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>();
        CreationHelper cr = wbCurrent.getCreationHelper();
        CellStyle csDate = wbCurrent.createCellStyle();
        csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern()));

        CellStyle csDateTime = wbCurrent.createCellStyle();
        csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern()));

        CellStyle csTime = wbCurrent.createCellStyle();
        csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern()));

        hsCS.put("DATE", csDate);
        hsCS.put("TIME", csTime);
        hsCS.put("DATETIME", csDateTime);

        Sheet sh = wbCurrent.createSheet("SVE Export");
        int nRowCount = 0;

        // BUILDING HEADER
        if (uis.isIncludeHeader()) {
            Row rw = sh.createRow(nRowCount);
            int nCol = 0;
            for (ExportColumn expColumn : expModel.getColumns()) {
                rw.createCell(nCol).setCellValue(expColumn.getColumnName());
                nCol++;
            }
            nRowCount++;
        }
        // Processing Values
        for (ExportDataRow expRow : expModel.getRows()) {
            Row rw = sh.createRow(nRowCount);
            int nCol = 0;
            for (ExportColumn expColumn : expModel.getColumns()) {
                Cell clCurrent = rw.createCell(nCol);
                setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS);
                nCol++;
            }
            nRowCount++;
        }
        for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) {
            sh.autoSizeColumn(nCol);
        }
        if (strFileName.toLowerCase().endsWith(".xlsx")) {
            hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        } else if (strFileName.toLowerCase().endsWith("xls")) {
            hsr.setContentType("application/vnd.ms-excel");

        } else {
            hsr.setContentType("application/octet-stream");
        }
        hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\"");
        OutputStream os = hsr.getOutputStream();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wbCurrent.write(bos);
        bos.writeTo(os);
        os.close();
    } catch (Exception e) {
        ErrorPageBuilder.getInstance().processError(hsr, "Error during SVE-Generation (Workbook Export)", e);
    }
}

From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java

License:Apache License

public void generateNewFile(ITemplateSource itsCurrent, List<Spreadsheet> lstSP, String strFileName,
        HttpServletResponse httpResponse, FacesContext context, UIWorkbook uiWB) {
    Logger logCurrent = LoggerFactory.getLogger(this.getClass().getCanonicalName());

    try {// w  ww  .  j  av  a2  s . c o m
        logCurrent.finer("First getting the File");
        // First getting the File
        int nTemplateAccess = itsCurrent.accessTemplate();
        if (nTemplateAccess == 1) {
            Workbook wbCurrent = processWorkbook(itsCurrent, lstSP, context, uiWB);

            logCurrent.finer("Push the Result to the HttpServlet");
            // Push the Result to the HttpServlet
            if (strFileName.toLowerCase().endsWith(".xlsx")) {
                httpResponse
                        .setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            } else if (strFileName.toLowerCase().endsWith("xls")) {
                httpResponse.setContentType("application/vnd.ms-excel");

            } else {
                httpResponse.setContentType("application/octet-stream");
            }
            httpResponse.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\"");
            OutputStream os = httpResponse.getOutputStream();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            wbCurrent.write(bos);
            bos.writeTo(os);
            os.close();
        } else {
            ErrorPageBuilder.getInstance().processError(httpResponse,
                    "TemplateAccess Problem NR: " + nTemplateAccess, null);
        }
    } catch (Exception e) {
        ErrorPageBuilder.getInstance().processError(httpResponse, "Error during Workbookgeneration", e);
    }

}