Overwrite an Excel file with Java and Apache POI

0

Very good.

I have created an application that receives a date and writes an excel file with necessary information to fill data in a dynamic table in excel. The class so far creates the file without any problem, but, I would like to know if I can do, instead of creating a new document, go and overwrite an existing one with a dynamic table and formulas already created, that only overwrites a sheet called " BASE "(Which is the one that contains the data to fill the dynamic table) and do not overwrite it completely leaving only the Sheet that creates and already.

This is the code I have for the creation of the book:

    public class EscribirExcel {
Calendar calendario = new GregorianCalendar();
String horaNueva = new SimpleDateFormat("HH:mm:ss").format(Calendar.getInstance().getTime());
String fechaNueva = new SimpleDateFormat("yyyyMMdd").format(Calendar.getInstance().getTime());
String d = String.valueOf(calendario.get(Calendar.DAY_OF_MONTH));
String M = String.valueOf(calendario.get(Calendar.MONTH)+1);
String y = String.valueOf(calendario.get(Calendar.YEAR));
String s = horaNueva.substring(6, 8);
String m = horaNueva.substring(3, 5);
String H = horaNueva.substring(0, 2);
String ruta = "Informes_Nomina/ReporteNomina.xlsx";
File archivoXLS = new File(ruta);
XSSFWorkbook libro =  new XSSFWorkbook();
XSSFSheet hoja = libro.createSheet("BASE");
XSSFRow fila;
XSSFCell celda;
int f = 1;
int c = 1;
int t = 1;
int max = 1;
public void crearArchivo(ResultSet rs) throws FileNotFoundException, IOException, SQLException{
    String [] arrEncabezados = {"EMPLEADO",
                                "NOMBRE_EMPLEADO",
                                "LAPSO",
                                "CO_DE_MOVIMIENTO",
                                "NOMBRE_CENTROCOSTO",
                                "SALARIO",
                                "SUB_TRANSPORTE",
                                "H.E",
                                "OTROS",
                                "I.P",
                                "TOTAL",
                                "EMPRESA",
                                "003",
                                "004",
                                "008",
                                "009",
                                "010",
                                "011",
                                "015",
                                "018",
                                "051",
                                "052",
                                "053",
                                "055",
                                "660",
                                "750",
                                "850",
                                "860",
                                "EMPRESA"};
    FileOutputStream archivo = new FileOutputStream(archivoXLS);
    fila = hoja.createRow(0);
    for(int x = 0; x < arrEncabezados.length; x++){
        celda = fila.createCell(x);
        celda.setCellValue(arrEncabezados[x]);
    }

    if(rs.next()){
        while(rs.next()){
        fila = hoja.createRow(f);
            for(c = 1; c <= arrEncabezados.length; c++){
                celda = fila.createCell(c-1);
                celda.setCellValue(rs.getString(c));
            }  
        f++;
        }
        libro.write(archivo);
        archivo.close();
        Desktop.getDesktop().open(archivoXLS);
    }else{
        JOptionPane.showMessageDialog(null,"Esta consulta no trae ninguna información");
        return;
    }


} 

Thank you very much.

    
asked by Salth95 04.07.2017 в 19:51
source

1 answer

0

You still need to obtain the last row that has the file written, you can obtain it by following the following method:

sheet.getLastRowNum()

I leave you a link of an example and the official documentation:

Link Example

Official Documentation

    
answered by 05.07.2017 в 18:14