Write to worksheet from last line without overwriting

Question

I am trying to write in an excel spreadsheet from the last line written without deleting the content that already existed, I have already managed to pick up the last line and write from it, but when I write erases what was before. I used an IDE to generate my screens and adapted an example I found on the internet to write to the spreadsheet, plus I used the Java JEXCEL API.

Follows used classes:

Screen with the table:

import java.awt.Desktop;
import java.io.File;
import java.io.IOException;
import javax.swing.JOptionPane;

public class NewJFrame extends javax.swing.JFrame {

public NewJFrame() {
    initComponents();
}

@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">                          
private void initComponents() {

    jScrollPane1 = new javax.swing.JScrollPane();
    jTable1 = new javax.swing.JTable();
    jTextField1 = new javax.swing.JTextField();
    jFormattedTextField1 = new javax.swing.JFormattedTextField();
    jButton1 = new javax.swing.JButton();

    setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

    jTable1.setModel(new javax.swing.table.DefaultTableModel(
        new Object [][] {
            {null, null, null},
            {null, null, null},
            {null, null, null},
            {null, null, null}
        },
        new String [] {
            "Title 1", "Title 2", "Title 3"
        }
    ));
    jScrollPane1.setViewportView(jTable1);

    jTextField1.setText("jTextField1");

    try {
        jFormattedTextField1.setFormatterFactory(new javax.swing.text.DefaultFormatterFactory(new javax.swing.text.MaskFormatter("##/##/####")));
    } catch (java.text.ParseException ex) {
        ex.printStackTrace();
    }

    jButton1.setText("Exportar");
    jButton1.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(java.awt.event.ActionEvent evt) {
            jButton1ActionPerformed(evt);
        }
    });

    javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
    getContentPane().setLayout(layout);
    layout.setHorizontalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(layout.createSequentialGroup()
            .addContainerGap()
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 341, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 121, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(80, 80, 80)
                        .addComponent(jFormattedTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 130, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(jButton1, javax.swing.GroupLayout.Alignment.TRAILING))))
    );
    layout.setVerticalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(layout.createSequentialGroup()
            .addContainerGap()
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                .addComponent(jTextField1, javax.swing.GroupLayout.DEFAULT_SIZE, 31, Short.MAX_VALUE)
                .addComponent(jFormattedTextField1))
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
            .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 167, javax.swing.GroupLayout.PREFERRED_SIZE)
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
            .addComponent(jButton1)
            .addContainerGap(19, Short.MAX_VALUE))
    );

    pack();
}// </editor-fold>                        

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    Excel e = new Excel();
    String aux = e.m(jTextField1.getText(), jTable1, jFormattedTextField1.getText());
    if (!aux.equals("")) {
        int i = JOptionPane.showConfirmDialog(null, "Deseja abrir o arquivo?", "", 0);
        if (i == 0) {
            File file = new File(aux);
            try {
                Desktop.getDesktop().open(file);
            } catch (IOException ex) {
                JOptionPane.showMessageDialog(null, "Erro ao tentar abrir o arquivo.\n" + ex.getMessage(),
                        "Erro na Exportação", 2);
            }
        }
    }
}                                        


public static void main(String args[]) {
    try {
        for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
            if ("Nimbus".equals(info.getName())) {
                javax.swing.UIManager.setLookAndFeel(info.getClassName());
                break;
            }
        }
    } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | javax.swing.UnsupportedLookAndFeelException ex) {
        java.util.logging.Logger.getLogger(NewJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    }
    //</editor-fold>

    //</editor-fold>

    /* Create and display the form */
    java.awt.EventQueue.invokeLater(new Runnable() {
        public void run() {
            new NewJFrame().setVisible(true);
        }
    });
}

// Variables declaration - do not modify                     
private javax.swing.JButton jButton1;
private javax.swing.JFormattedTextField jFormattedTextField1;
private javax.swing.JScrollPane jScrollPane1;
private javax.swing.JTable jTable1;
private javax.swing.JTextField jTextField1;
// End of variables declaration                   
}

Select Location screen to save:

import java.io.File;
import javax.swing.JFileChooser; 
import javax.swing.filechooser.FileNameExtensionFilter;

public class BuscaArquivo extends javax.swing.JDialog {

private String diretorio;//DECLARA VARIÁVEL QUE IRÁ RECEBER O DIRETÓRIO DO ARQUIVO SELECIONADO
private FileNameExtensionFilter fileFilter = null;

public String getDiretorio() {
    return diretorio;
}

public BuscaArquivo(java.awt.Frame parent, boolean modal, String nome, String[] tipo, String suge) {
    super(parent, modal);
    initComponents();
    jFileChooser1.setCurrentDirectory(new File("C://"));//DEFINE O DIRETÓRIO INICIAL QUE IRÁ ABRIR A TELA
    jFileChooser1.setSelectedFile(new File(suge));//SUGERE NOME PARA ARQUIVO
    if(tipo[0].equals(""))
        jFileChooser1.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
    else{
        fileFilter = new FileNameExtensionFilter(nome, tipo);
        //INSTANCIA OBJETO QUE FILTRA AS EXTENSÕES  
        jFileChooser1.setFileFilter(fileFilter);//DEFINE FILTRO DE EXTENSÕES ACEITAS
    }
}

@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">                          
private void initComponents() {

    jFileChooser1 = new javax.swing.JFileChooser();

    setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);

    jFileChooser1.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(java.awt.event.ActionEvent evt) {
            jFileChooser1ActionPerformed(evt);
        }
    });

    javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
    getContentPane().setLayout(layout);
    layout.setHorizontalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addComponent(jFileChooser1, javax.swing.GroupLayout.PREFERRED_SIZE, 605, javax.swing.GroupLayout.PREFERRED_SIZE)
    );
    layout.setVerticalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(layout.createSequentialGroup()
            .addComponent(jFileChooser1, javax.swing.GroupLayout.PREFERRED_SIZE, 434, javax.swing.GroupLayout.PREFERRED_SIZE)
            .addGap(0, 0, Short.MAX_VALUE))
    );

    pack();
}// </editor-fold>                        

private void jFileChooser1ActionPerformed(java.awt.event.ActionEvent evt) {                                              
    // TODO add your handling code here:
    jFileChooser1.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);//DEFINE QUE A TELA IRÁ PEGAR APENAS O DIRETÓRIO DOS ARQUIVOS
    try {//CLAUSULA TRY PARA TRATAMENTO DE EXEÇÃO
        if(fileFilter == null){
            if(!jFileChooser1.getSelectedFile().getAbsolutePath().substring(jFileChooser1.getSelectedFile().getAbsolutePath().length() - 1).equals("\\"))
                diretorio = jFileChooser1.getSelectedFile().getAbsolutePath() + "\\";
            else
                diretorio = jFileChooser1.getSelectedFile().getAbsolutePath();
        }
        else
            diretorio = jFileChooser1.getSelectedFile().getAbsolutePath();//INSERE NA VARIÁVEL 'diretorio' O DIRETÓRIO DO ARQUIVO SELECIONADO
        this.setVisible(false);
        this.dispose();//ENCERRA A TELA
    } catch (NullPointerException e) {
        this.setVisible(false);
        this.dispose();//ENCERRA A TELA
    }
}                                             

// Variables declaration - do not modify                     
private javax.swing.JFileChooser jFileChooser1;
// End of variables declaration                   
}

Class writing excel spreadsheets:

import java.io.File;
import java.io.IOException;
import java.util.Locale;
import javax.swing.JOptionPane;
import javax.swing.JTable; 
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class Excel {

public String m(String t, JTable jt, String data) {
    try {
        String[] aux = new String[2];
        aux[0] = "XLS";
        aux[1] = "xls";
        data = data.replaceAll("/", "");
        BuscaArquivo ba = new BuscaArquivo(null, true, "XLS", aux, "TABELA - " + data);
        //TELA PARA ESCOLHER ONDE SERÁ SALVO O ARQUIVO
        ba.setVisible(true);
        String filename;
        if (ba.getDiretorio() != null) {//EXECUTA CASO ALGUM DIRETÓRIO TENHA SIDO SELECIONADO
            filename = ba.getDiretorio();
            if(!filename.contains(".xls"))
                filename = filename + ".xls";//COMPLETA CAMINHA CASO NÃO TENHA SIDO DIGITADO O .xls
            if(new File(filename).exists()){//SE O ARQUIVO JÁ EXISTIR
                Workbook workbookAux = Workbook.getWorkbook(new File(filename));
                Sheet sheet = workbookAux.getSheet(0);//PEGA A ULTIMA LINHA DA PLANILHA
                int linha = sheet.getRows();
                WorkbookSettings ws = new WorkbookSettings();
                ws.setLocale(new Locale("pt", "BR"));
                WritableWorkbook workbook
                        = Workbook.createWorkbook(new File(filename), ws);
                WritableSheet s = workbook.createSheet("Folha1", 0);
                writeDataSheet(s, t, jt, linha);
                workbook.write();
                workbook.close();
            } else{
                WorkbookSettings ws = new WorkbookSettings();
                ws.setLocale(new Locale("pt", "BR"));
                WritableWorkbook workbook
                        = Workbook.createWorkbook(new File(filename), ws);
                WritableSheet s = workbook.createSheet("Folha1", 0);
                writeDataSheet(s, t, jt, 0);
                workbook.write();
                workbook.close();
            }
            return filename;
        } else {
            return "";
        }
    } catch (IOException | WriteException | BiffException e) {
        JOptionPane.showMessageDialog(null, e.getMessage(), "Erro Ao Gravar", 0);
        return "";
    }
}

private static void writeDataSheet(WritableSheet s, String t, JTable jt, int linha)
        throws WriteException {

    /* Formata a fonte */
    WritableFont wf = new WritableFont(WritableFont.ARIAL,
            10, WritableFont.BOLD);//DEFINE COMO NEGRITO
    WritableCellFormat cf = new WritableCellFormat(wf);
    cf.setWrap(false);

    /* Cria um label e escreve um float numver em uma célula da folha*/
    Label l = new Label(0, linha, t, cf);
    s.addCell(l);

    cf = new WritableCellFormat(wf);
    cf.setBorder(Border.ALL, BorderLineStyle.THIN);//DEFINE BRODA

    for (int i = 1; i < jt.getColumnCount(); i++) {
        l = new Label(i - 1, linha + 1, jt.getColumnName(i), cf);
        s.addCell(l);
    }

    wf = new WritableFont(WritableFont.ARIAL,
            10, WritableFont.NO_BOLD);
    cf = new WritableCellFormat(wf);
    cf.setBorder(Border.ALL, BorderLineStyle.THIN);

    linha += 2;
    //TRECHO PARA ESCREVER TODA A TABELA NA PLANILHA
    for (int i = 0; i < jt.getRowCount(); i++, linha++) {
        for (int j = 1; j < jt.getColumnCount(); j++) {
            l = new Label(j - 1, linha, jt.getValueAt(i, j).toString(), cf);
            s.addCell(l);
        }
    }
}
}

Answer 1

How I had already taken the spreadsheet and put in the variable sheet I just rewrote what I already had before I started writing the next part. I don’t know if it’s the best way to do it, but it’s how I did it.

Follows adapted code snippet in which I get the last line:

            if(new File(filename).exists()){//SE O ARQUIVO JÁ EXISTIR
                Workbook workbookAux = Workbook.getWorkbook(new File(filename));
                Sheet sheet = workbookAux.getSheet(0);
                int linha = sheet.getRows();//PEGA A ULTIMA LINHA DA PLANILHA
                WorkbookSettings ws = new WorkbookSettings();
                ws.setLocale(new Locale("pt", "BR"));
                WritableWorkbook workbook
                        = Workbook.createWorkbook(new File(filename), ws);
                WritableSheet s = workbook.createSheet("Folha1",0);
                for(int i=0 ; i<linha ; i++){
                    for(int j=0 ; j<sheet.getColumns() ; j++){
                        if(!sheet.getCell(j, i).getContents().equals("")){//EXECUTA APENAS SE ACHAR ALGUM VALOR
                            Label l = new Label(j, i, 
                                    sheet.getCell(j, i).getContents(), sheet.getCell(j, i).getCellFormat());
                            //PEGA TODA CONFIGURAÇÃO DA CÉLULA
                            s.addCell(l);
                        }
                    }
                }
                writeDataSheet(s, t, jt, linha);
                workbook.write();
                workbook.close();
            }