Export the result of the sql query to an excel with Java

Question

Export the result of the sql query to an excel with Java

Navigation

#1 by (0 votes)

-1

That query throws me 3 rows what I want to put in an excel, thank you very much for your help.

public class CrearFicherosExcel extends conect {

    public void CEXML(String url, String bd, String puerto, String usuario, String pasw) throws ClassNotFoundException {
        String originalUrl = new String(Base64.getDecoder().decode(url));
        String originalBd = new String(Base64.getDecoder().decode(bd));
        String originalPto = new String(Base64.getDecoder().decode(puerto));
        String originalUsuario = new String(Base64.getDecoder().decode(usuario));
        String originalPasw = new String(Base64.getDecoder().decode(pasw));

        Connection cn = mod_conexion(originalUrl, originalBd, originalPto, originalUsuario, originalPasw);
        try {
            PreparedStatement ps = null;
            ResultSet rs = null;
            ps = cn.prepareStatement("SELECT CtbPolizas.idPoliza," + "CtbPolizas.NumPoliza," + "CtbPolizas.Fecha,"
                    + "Proyectos.Nombre," + "CtbPolizas.Descripcion," + "CtbPolizasDet.idProyecto,"
                    + "CtbPolizasDet.idCuenta," + "CtbCuentas.Cuenta," + "CtbCuentas.Descripcion,"
                    + "CtbPolizasDet.Observaciones," + "Proyectos.Proyecto," + "CtbPolizasDet.Cargo,"
                    + "CtbPolizasDet.Abono " + "FROM CtbCuentas,Proyectos,CtbPolizas"
                    + " INNER JOIN CtbPolizasDet ON CtbPolizas.idPoliza=CtbPolizasDet.idPoliza"
                    + " WHERE CtbPolizas.idPoliza=4825" + " and CtbPolizasDet.idCuenta=CtbCuentas.idCuenta"
                    + " and CtbPolizas.idProyecto=Proyectos.idProyecto;");
            rs = ps.executeQuery();
            for (int i = 0; rs.next(); i++) {
                JOptionPane.showMessageDialog(null, "Resultado " + rs.getInt(2));
            }
            rs.close();
        } catch (SQLException e) {
            System.out.println("ERROR: " + e);
            JOptionPane.showMessageDialog(null, "ERROR: " + e);
        }
    }

java

asked by Jesus Guzman 01.09.2017 в 20:05

source

1 answer

insert div in css? [closed] C ++ - Error loading data in a struct array located in a header file

score 0 · Answer 1

I recommend that you first place the query in a table, and then dump the data from the table to the excel:

JFileChooser s = new JFileChooser();
                s.setCurrentDirectory(new File(""));  
                s.setFileSelectionMode(JFileChooser.FILES_ONLY);
                s.setFileFilter(new FileNameExtensionFilter("Excel Document", "xlsx"));
                int returnVal = 0;
                File destino = null;               
                returnVal = s.showSaveDialog(null);         
                int banExistente=0;
                if(returnVal==0)  {
                    String ruta =s.getSelectedFile() + ".xlsx";
                    destino = new File(ruta);
                    if (destino.exists()) {
                        JOptionPane.showMessageDialog(null, "HAY UN ARCHIVO CON EL MISMO NOMBRE");
                        int ax = JOptionPane.showConfirmDialog(null, "REMPLAZAR EL ARCHIVO?");
                        if(ax == JOptionPane.YES_OPTION) {
                            banExistente = 0;
                        }
                        else {
                            banExistente = 1;
                            JOptionPane.showMessageDialog(null, "SE CANCELO LA ACCION!");
                        }                       
                    }
                    else {
                        banExistente = 0;
                    }                   
                    if(banExistente == 0) {
                        XSSFWorkbook libro = new XSSFWorkbook();
                        XSSFRow fila;
                        XSSFCell celda;
                        XSSFCellStyle styleTitulo = libro.createCellStyle();
                        XSSFCellStyle styleRelleno = libro.createCellStyle();                       
                        XSSFSheet hoja = libro.createSheet("INFO TRANSACCIONES SUMATORIA");                     
                        fila = hoja.createRow(0);                       
                        styleTitulo.setFillPattern(HSSFCellStyle.ALIGN_LEFT);
                        styleTitulo.setFillForegroundColor(new HSSFColor.CORAL().getIndex());
                        styleTitulo.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                        styleTitulo.setBottomBorderColor(HSSFColor.BLACK.index);
                        styleTitulo.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                        styleTitulo.setLeftBorderColor(HSSFColor.BLACK.index);
                        styleTitulo.setBorderRight(HSSFCellStyle.BORDER_THIN);
                        styleTitulo.setRightBorderColor(HSSFColor.BLACK.index);
                        styleTitulo.setBorderTop(HSSFCellStyle.BORDER_THIN);
                        styleTitulo.setTopBorderColor(HSSFColor.BLACK.index);
                        for(int cTitulo = 0; cTitulo<tbSuma.getColumnCount(); cTitulo++) {
                            celda = fila.createCell(cTitulo);
                            celda.setCellValue(String.valueOf(tbSuma.getColumnName(cTitulo)));
                            celda.setCellStyle(styleTitulo);
                        }
                        styleRelleno.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                        styleRelleno.setLeftBorderColor(HSSFColor.BLACK.index);
                        styleRelleno.setBorderRight(HSSFCellStyle.BORDER_THIN);
                        styleRelleno.setRightBorderColor(HSSFColor.BLACK.index);
                        styleRelleno.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                        styleRelleno.setBottomBorderColor(HSSFColor.BLACK.index);
                        styleRelleno.setBorderTop(HSSFCellStyle.BORDER_THIN);
                        styleRelleno.setTopBorderColor(HSSFColor.BLACK.index);
                        for(int f = 0; f<tbSuma.getRowCount(); f++) {
                            fila = hoja.createRow(f+1);
                                for(int c = 0; c<tbSuma.getColumnCount(); c++) {
                                    celda = fila.createCell(c);
                                    celda.setCellValue(String.valueOf(tbSuma.getValueAt(f, c)));
                                    celda.setCellStyle(styleRelleno);
                                }
                        }
                        try {
                            FileOutputStream elFichero = new FileOutputStream(destino);
                            libro.write(elFichero);
                            elFichero.close();
                            JOptionPane.showMessageDialog(null, "SE GUARDO CORRECTAMENTE EL ARCHIVO!");
                        } 
                        catch (IOException es) {
                            JOptionPane.showMessageDialog(null, "PROBLEMA AL GUARDAR! " +es);
                        }
                        finally {
                            try {
                                libro.close();
                            } 
                            catch (IOException es) {
                                JOptionPane.showMessageDialog(null, "ERROR AL CERRAR EL LIBRO.\n" + es.getMessage());
                            }
                        }
                    }
                }
                else {
                    JOptionPane.showMessageDialog(null, "SE CANCELO");
                }

where tbSuma is the table in which you will be loading the results. This function you have to place in it in some event, such as when pressing a button. In order to use the classes with which you will pass the data to excel, you need to download the apache poi library. Good luck!