EDITED Write JTable to Excel

Question

I am trying to export my JTable to Excel file. Column and Row names are fine, but all the information I am adding into JTable does not get written. I tried System.out.println() and it prints Null values everywhere apart from column and row names. I tried to get answers from mother google but after 2 hours of reading and trying, still no progress. What stays in my head is that there could be some mistake in code at Write to Excel part or everything that is added to JTable is just a picture on my monitor, not an actual data in it.? Correct me if I am wrong and any help is highly appreciated.

Here is Write to Excel Part. in first For loop I am getting headers and in second For loop, i should get everything that is inside my JTable but I'm not.

TableColumnModel tcm = nrp.rotaTable.getColumnModel();

    String nameOfFile = JOptionPane.showInputDialog("Name of the file");

    Workbook wb = new HSSFWorkbook();
    CreationHelper createhelper = wb.getCreationHelper();

    Sheet sheet = wb.createSheet("new sheet");
    Row row = null;
    Cell cell = null;

    for (int i = 0; i < nrp.tableModel.getRowCount(); i++) {
        row = sheet.createRow(i);
        for (int j = 0; j < tcm.getColumnCount(); j++) {

            cell = row.createCell(j);
            cell.setCellValue(tcm.getColumn(j).getHeaderValue().toString());

        }
    }
    for (int i = 1; i < nrp.tableModel.getRowCount(); i++) {
        row = sheet.createRow(i);
        System.out.println("");
        for (int j = 0; j < nrp.tableModel.getColumnCount(); j++) {

            cell = row.createCell(j);
            cell.setCellValue((String) nrp.tableModel.getValueAt(i, j)+" ");
            System.out.print((String) nrp.tableModel.getValueAt(i, j)+" ");
        }
    }


    File file = new File("Some name.xls");
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
    wb.close();
  }
}

And here is FocusListener code.

rotaTable.addFocusListener(new FocusListener() {
            public void focusGained(FocusEvent e) {
            }
            public void focusLost(FocusEvent e) {
                CellEditor cellEditor = rotaTable.getCellEditor();
                if (cellEditor != null)
                    if (cellEditor.getCellEditorValue() != null)
                        cellEditor.stopCellEditing();
                    else
                        cellEditor.cancelCellEditing();
            }
        });

I am using 'DefaultTableModel'

 DefaultTableModel tableModel = new DefaultTableModel(12,8); 
JTable rotaTable = new JTable(tableModel); 

This is first time when I am working with POI library.

Picture of my JTable https://i.sstatic.net/iG3zG.jpg

Picture of printed results in console https://i.sstatic.net/iG3zG.jpg

Picture of Excel file created. https://i.sstatic.net/iG3zG.jpg

Answer 1

import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.ListSelectionModel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

@SuppressWarnings("serial")
public class EmployeePanel extends JPanel {
    public ArrayList columnNames = new ArrayList();
    ArrayList data = new ArrayList();
    Connection conn;
    String query;
    String sql = "SELECT * FROM employee";
    String url = "jdbc:mysql://localhost:3306/nandos_db";
    String userid = "User1";
    String password = "Password1";
    Statement stmt;
    ResultSet rs;
    int rows = 0;
    int columns = 0;
    JTable employeeTable;

    @SuppressWarnings({ "unchecked" })
    public EmployeePanel() {
        super();
        this.setBackground(Color.GREEN);
        this.setLayout(null);
        this.setVisible(true);
        try {
            conn = DriverManager.getConnection(url, userid, password);
            stmt = (Statement) conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();

            int columns = md.getColumnCount();
            for (int i = 1; i <= 3; i++) {
                columnNames.add(md.getColumnName(i));
            }
            while (rs.next()) {
                ArrayList row = new ArrayList(columns);
                for (int i = 1; i <= columns; i++) {
                    row.add(rs.getObject(i));
                }
                data.add(row);
            }
            // Get row data
        } catch (SQLException e) {
            // System.out.println(e.getMessage());
            e.printStackTrace();
        }
        // Create Vectors and copy over elements from ArrayLists to them
        // a custom defined class which inherits from the AbstractTableModel
        // class
        Vector columnNamesVector = new Vector();
        Vector dataVector = new Vector();
        for (int i = 0; i < data.size(); i++) {
            ArrayList subArray = (ArrayList) data.get(i);
            Vector subVector = new Vector();
            for (int j = 0; j < subArray.size(); j++) {
                subVector.add(subArray.get(j));
            }
            dataVector.add(subVector);
        }
        for (int i = 0; i < columnNames.size(); i++) {
            columnNamesVector.add(columnNames.get(i));
        }
        // Create table with database data
        employeeTable = new JTable(dataVector, columnNamesVector);
        employeeTable.getTableHeader().setOpaque(false);
        employeeTable.getTableHeader().setBackground(Color.pink);
        employeeTable.setBackground(Color.orange);
        employeeTable.setSelectionMode((ListSelectionModel.SINGLE_SELECTION));
        employeeTable.setEnabled(true);
        employeeTable.setDragEnabled(false);
        employeeTable.setCellEditor(null);

        JScrollPane sp = new JScrollPane(employeeTable);
        sp.setSize(1030, 540);
        sp.setLocation(10, 10);
        this.add(sp);
    }

    public static void main(String... args) throws IOException {
        JFrame f = new JFrame();
        EmployeePanel t = new EmployeePanel();
        f.add(t);
        f.setVisible(true);
        f.pack();

        System.out.println(t.employeeTable.getValueAt(0, 0));

        Workbook wb = new HSSFWorkbook();
        CreationHelper createhelper = wb.getCreationHelper();

        Sheet sheet = wb.createSheet("new sheet");
        Row row = null;
        Cell cell = null;
        row = sheet.createRow(0);
        for (int i = 0; i < t.employeeTable.getColumnCount(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(String.valueOf(t.employeeTable.getColumnName(i)));
        }

        for (int j = 1; j <= t.employeeTable.getRowCount(); j++) {
            row = sheet.createRow(j);
            for (int i = 0; i < t.employeeTable.getColumnCount(); i++) {
                cell = row.createCell(i);

                cell.setCellValue(String.valueOf(t.employeeTable.getValueAt(j - 1, i)));

            }
        }

        File file = new File("C:\\Users\\test\\Desktop\\Some name.xls");
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();
        wb.close();

    }

}

if i let this code connect to my database, ill get an excel sheet with the exact same content as the table in the frame. as you obviously got some more code you didn't show yet, you might need to adapt it properly. this code is working all in 1 class

Answer 2

Once I wrote this code, is used to write a set of JTables to a Excel file (one next to the other). As I can see it's practically the same code you wrote. Could you try this instead of you block. You must replace the Entry object or wrap your table into a Map

for (int i = 1; i < nrp.tableModel.getRowCount(); i++) {
...
}

As I needed to write the tables one next to the other I used the variable fila and columna to get the current position to put the next table. You could set them to 0 or to 1 in order to make your tests. Maybe it could helps you to come with new ideas.

Also the only difference I notice is: if (row == null) {...}

private void escribirContenido(Sheet hoja, Entry<String, JTable> e) {
    Row row;
    Cell cell;
    for (int i = fila; i < e.getValue().getRowCount() + fila; i++) {
        row = hoja.getRow(i);
        if (row == null) {
            row = hoja.createRow(i);
        }
        for (int j = 0; j < e.getValue().getColumnCount(); j++) {
            cell = row.createCell(j + columna);
            String value = String.valueOf(e.getValue().getValueAt(i - fila, j));
            try {
                cell.setCellValue(new Double(value));
            } catch (NumberFormatException e1) {
                cell.setCellValue(value);
            }
        }
    }
}

Answer 3

You have to start row index by 0 because table row is start from 0 and create excel row from 1 because first for you write column names.I modified your second for loop like this:

for (int i= 0; i < nrp.tableModel.getRowCount(); i++) {
    row = sheet.createRow(i+1);
    System.out.println("");
    for (int j = 0; j < nrp.tableModel.getColumnCount(); j++) {
        cell = row.createCell(j);
        if(nrp.tableModel.getValueAt(i, j)!=null){
        cell.setCellValue((String) nrp.tableModel.getValueAt(i, j));
        }
        System.out.print((String) nrp.tableModel.getValueAt(i, j)+" ");
    }
}