apache poi: saving jtable to a file

Question

I've recently started working with java and I'm facing some issues with the apache poi library when I need to create an excel file starting from a jTable.

I've read many threads and come up with some code which just won't work (even if this is pretty easy stuff and there were many examples, which makes me look even more dumb) and I was hoping someone could help me out.

So here are the questions:

a) why does the for loop, which is supposed to write the excel file, not fill every cell? (the only row with data in the excel file is the sixth one, which also makes me wonder why does it count null items in the table model for the getRowCount/Column methods..also I know it's printing a custom String and not the table itself but save that for point b)

b) how am I supposed to use the jtable model items to populate the excel file since when creating the table I had to choose Object as row type? (I'm also having issues with the kind of object especially, as long as it's a String||integer there's no issue at all but the table is supposed to have a mix of both which doesn't seem to work when you try to use the setCellValue() method with something different from the String||integer..or at least I couldn't make it work)

c) let's say I'd later want to populate the jtable from the file I previously created, would I simply have to use the solution to point b) (the other way around that is) after reading the file with the bufferedReader class?

Disclaimer: the first part of the code is auto-generated by netbeans as you can probably tell, the hssf part I came up with is at the end but I thought you might want to see the whole thing, sorry if it looks kind of messy.

Here's the code:

package poitest;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.TableModel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

public class POITestFrame extends javax.swing.JFrame {

    /**
     * Creates new form POITestFrame
     */
    public POITestFrame() {
        initComponents();
    }

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

        excelButton = new javax.swing.JButton();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        excelButton.setText("ESPORTA!");
        excelButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                excelButtonActionPerformed(evt);
            }
        });

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {"Boolean", "Integer", "String", "Array"},
                {"x*y", "x+y", "x/y", "x-y"},
                {"32", "43", "12", "24"},
                {"casa", "cantiere", "museo", "acquario"},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        jScrollPane1.setViewportView(jTable1);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 320, Short.MAX_VALUE)
            .addComponent(excelButton, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 91, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(excelButton)
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void excelButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_excelButtonActionPerformed
        try {
             PoiWriter(jTable1);
        } catch (FileNotFoundException ex) {
             Logger.getLogger(POITestFrame.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(POITestFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//GEN-LAST:event_excelButtonActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /*
         * Set the Nimbus look and feel
         */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /*
         * If Nimbus (introduced in Java SE 6) is not available, stay with the
         * default look and feel. For details see
         * http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
         */
        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 ex) {
            java.util.logging.Logger.getLogger(POITestFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(POITestFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(POITestFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(POITestFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /*
         * Create and display the form
         */
        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {
                new POITestFrame().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton excelButton;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTable jTable1;
    // End of variables declaration//GEN-END:variables

    private void PoiWriter(JTable jTable1) throws FileNotFoundException, IOException {
        TableModel model = jTable1.getModel();

    // create a workbook
    Workbook wb = new HSSFWorkbook();  // xls file
        // create a new sheet
        Sheet sheet = wb.createSheet("Foglio di Prova!");
        // declare a row object reference
        Row r = null;
        // declare a cell object reference
        Cell c = null;
        // create a new file
    FileOutputStream fos;
    fos = new FileOutputStream("File di Prova.xls");

        // create a sheet table rows
        int rownum;
        for (rownum = (short) 0; rownum < model.getRowCount(); rownum++) {
            // create a row
            r = sheet.createRow(rownum);
        }

        for (short cellnum = (short) 0; cellnum < model.getRowCount(); cellnum ++) {
            // create a numeric cell
            c = r.createCell(cellnum);

            // populate table with custom objects
            for(int i=0; i < model.getRowCount();i++){
            for(int j=0;j < model.getColumnCount();j++){
                String aplala = "blabla";       
                c.setCellValue(aplala);   
            }
        }


        }
        wb.write(fos);
    fos.close();
    }
}

PS: If you're wondering why I built the table with Object types: this isn't the project I'm working with, I made this snippet to test hssf out since the resulting excel is quite editable but things don't seem to go well.

PPS: I tried working with the tokenizer class as well but I'm not sure you can edit the resulting excel file as much as with poi lib.

PPPS: This is my very first attempt with java so, please, don't be too rough!

I hope the questions were clear enough and thanks in advance, I do am trying to get better at programming :P

EDIT: after a day of practice this is what I came up with which seems to work with the apache poi library, thanks for the help guys it gave good pointers!

    int rowNum;
    int colNum;
    int tempRows;
    int rowCount = model.getRowCount();
    int columnCount = model.getColumnCount();     

    // create the headers
    for (colNum = 0; colNum < columnCount; colNum++) {             
        if (colNum == 0) {
            r = sheet.createRow(0);
        }            
        c = r.createCell(colNum);  
        c.setCellValue(model.getColumnName(colNum)); 
    }

    for (rowNum = 0; rowNum < rowCount; rowNum++) {
        // create rows + 1 (to account for the headers) 
        tempRows = rowNum + 1;
        r = sheet.createRow(tempRows);      

        for (short cellnum = 0; cellnum < columnCount; cellnum ++) {
            // create cells 
            c = r.createCell(cellnum);
            // add values from table
            c.setCellValue(model.getValueAt(rowNum, cellnum).toString());
        } 
    } 

Feel free to comment if you think the code can be improved, suggestions are always welcome, especially to newcomers like myself ;)

Again, thanks for the tips, they really did the trick ^^

Answer 1

Please read the tutorial about JTable, Creating a Table Model and implemented data types, JTable knows those Column Classes, and returns proper value for output to POI; for most of types in the MS Excel:

1. you have to loop inside cells in current row, too

2. have to format output for various data types (Date, Double, String)

3. then you can focus on creating formula(s) and coloring cell(s)

Code to loop through TableModel can generate MS Excel File, too, with standard windows delimiters:

public class ExcelCustomerReport {

    public ExcelCustomerReport() {
    }

    public void exportTable(JTable table, File file) throws IOException {
        TableModel model = table.getModel();
        FileWriter out = new FileWriter(file);
        String groupExport = "";
        for (int i = 0; i < (model.getColumnCount()); i++) {//* disable export from TableHeaders
            groupExport = String.valueOf(model.getColumnName(i));
            out.write(String.valueOf(groupExport) + "\t");
        }
        out.write("\n");
        for (int i = 0; i < model.getRowCount(); i++) {
            for (int j = 0; j < (model.getColumnCount()); j++) {
                if (model.getValueAt(i, j) == null) {
                    out.write("null" + "\t");
                } else {
                    groupExport = String.valueOf(model.getValueAt(i, j));
                    out.write(String.valueOf(groupExport) + "\t");
                }
            }
            out.write("\n");
        }
        out.close();
    }
}

Answer 2

I know that probably this is late for the post, but maybe for others that want to have the code, here is something that works.

    TableModel model = raportTable.getModel();

    SXSSFWorkbook wb = new SXSSFWorkbook(-1);
    SXSSFSheet sh = wb.createSheet("Report");
    Row row = sh.createRow(0);
    for (int i = 0; i < model.getColumnCount(); i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(model.getColumnName(i));
    }

    for(int i = 0; i < model.getRowCount(); i++) {
        row = sh.createRow(i + 1);
        for(int j = 0; j < model.getColumnCount(); j++){
            Cell cell = row.createCell(j);
            cell.setCellValue(model.getValueAt(i,j).toString());
        }
    }

    try {
        FileOutputStream excel = new FileOutputStream(filename + ".xlsx");
        wb.write(excel);
        excel.flush();
        excel.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainForm.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainForm.class.getName()).log(Level.SEVERE, null, ex);
    }

Hope it helps.