Write in the spreadsheet from the last line without writing

Question

Write in the spreadsheet from the last line without writing

Navigation

#1 by (1 votes)

0

I'm trying to write in an excel spreadsheet from the last line written without deleting the content that already existed, I've been able to pick up the last line and write from it, but when I type it deletes what was previously. I used an IDE to generate my screens and adapted an example that I found on the internet to write in the spreadsheet, in addition I used the JEXCEL API of java.

Here are the classes used:

Screen with 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 to save screen:

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 that writes 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);
        }
    }
}
}

java excel

asked by anonymous 03.09.2018 / 16:57

1 answer

Return HD size and free space for client-server PHP - Receive the ID of a value via select option $ _POST

score 1 · Accepted Answer

As I had already picked up the worksheet and placed it in the sheet variable I just rewrote what I already had before I started writing the next part. I do not know if this is the best way to do it, but it was how I did it.

Here is an adapted code snippet where 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();
            }