How To Export Data to CSV: Dynamic SQL Query and Dynamic Column Names and data on JTable (Java/Netbeans)

Question

I am trying to find out the easiest manner to export data from a JTable into an excel format like CSV or XLSX etc. The problem is the data is dynamic, so basically the user can run a dynamic query and he/she would require to export that data.

Answer 1

Here's a class I use to do the trick. It exports the JTable exactly as is presented to the user in the view (including the order of rows and columns). It can also extract text directly from JLabel custom renderers if you use them. I made some small changes for the post without compiling, so tell me if there are issues...

Note that this is a duplicate of questions How to export a JTable to a .csv file? and How to export data from JTable to CSV although the answers contain only pseudocode.

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import javax.swing.JLabel;
import javax.swing.JTable;

public class JTableCSVExporter {


    protected char columnDelimiter = ',';

    public char getColumnDelimiter() {
        return columnDelimiter;
    }

    public void setColumnDelimiter(char columnDelimiter) {
        this.columnDelimiter = columnDelimiter;
    }
    protected char rowDelimiter = '\n';

    public char getRowDelimiter() {
        return rowDelimiter;
    }

    public void setRowDelimiter(char rowDelimiter) {
        this.rowDelimiter = rowDelimiter;
    }

    protected char quote = '"';

    public char getQuote() {
        return quote;
    }

    public void setQuote(char quote) {
        this.quote = quote;
    }


    protected boolean useRenderers = true;

    public boolean isUseRenderers() {
        return useRenderers;
    }

    /**
     * If true, the value provided by table renderers (if they are JLabel) is used as value for CSV.
     * If false, or the renderer is not JLabel, the class uses toString of the cell value.
     */
    public void setUseRenderers(boolean useRenderers) {
        this.useRenderers = useRenderers;
    }

    protected boolean translateBools = true;

    public boolean isTranslateBools() {
        return translateBools;
    }

    /**
     * If true, bools are translated to "yes" a "no". Otherwise toString() is used
     */
    public void setTranslateBools(boolean translateBools) {
        this.translateBools = translateBools;
    }


    /**
     * Exports table to file.
     * @throws IOException 
     */
    public void exportCSV(JTable table, File file) throws IOException {
        FileWriter out = new FileWriter(file);
        for (int i = 0; i < table.getColumnCount(); i++) {
            int columnIndex = table.convertColumnIndexToView(i);
            if (columnIndex != -1) {
                out.write(table.getColumnName(columnIndex) + columnDelimiter);
            }
        }
        out.write(rowDelimiter);

        for (int rowIndex = 0; rowIndex < table.getRowCount(); rowIndex++) {
            for (int j = 0; j < table.getColumnCount(); j++) {
                int columnIndex = table.convertColumnIndexToView(j);
                if (columnIndex != -1) {
                    String toWrite;


                    Object value = table.getValueAt(rowIndex, columnIndex);

                    java.awt.Component rendererComponent = table.getCellRenderer(rowIndex, columnIndex).getTableCellRendererComponent(table, value, false, false, rowIndex, columnIndex);

                    if (isUseRenderers() && rendererComponent instanceof JLabel) {
                        toWrite = ((JLabel) rendererComponent).getText();
                    } else {
                        if (isTranslateBools() &&  value instanceof Boolean) {
                            if (value.equals(Boolean.TRUE)) {
                                toWrite = "yes";
                            } else {
                                toWrite = "no";
                            }
                        } else {
                            if (value == null) {
                                toWrite = "";
                            } else {
                                toWrite = value.toString();
                            }
                        }
                    }
                    out.write(quote + toWrite.replace(Character.toString(quote), "\\" + quote) + quote + columnDelimiter);
                }
            }
            out.write(rowDelimiter);
        }

        out.close();
    }
}

Answer 2

Most simple way would be to read all the rows from JTable and create a excel file using POI library.

You can get the table data using below method which you can store in list or something : table.getModel().getValueAt(rowIndex, columnIndex);

Now to create excel file you can use below code from POI library

WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));
WritableWorkbook workbook = null;
File newFile = new File(dir.getPath() + "\\" + fileName);
workbook = Workbook.createWorkbook(newFile, ws);
WritableSheet s = workbook.createSheet("mySheet", 0);
for (int i = 1; i <= <columncount>; ++i) {
Label l = new Label(i - 1, 0, <columnname>, cf);
s.addCell(l);
}

for (int j = 1; j <= <rowcount>; j++) {
    for (int i = 1; i <= <columncount>; i++) {
        Label m = new Label(i - 1, j, <rowvalue>, cf);
        s.addCell(m);
    }
}
workbook.write();
workbook.close();`