如何将数据导出到CSV:动态SQL查询和JTable上的动态列名和数据(Java/Netbeans)

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.

我正在寻找一种最简单的方法来将数据从JTable导出为excel格式，如CSV或XLSX等。问题是数据是动态的，所以基本上用户可以运行动态查询，他/她需要导出数据。

2 个解决方案

#1

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

最简单的方法是从JTable中读取所有的行并使用POI库创建一个excel文件。

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

您可以使用下面的方法获取表数据，您可以将其存储在list或其他东西中:table. getmodel()。getValueAt(rowIndex columnIndex);

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

现在，要创建excel文件，您可以使用下面来自POI库的代码

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();`

#2

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...

这是我用来做这个魔术的一门课。它将JTable导出为视图中呈现的用户(包括行和列的顺序)。如果您使用JLabel定制渲染器，它还可以直接从它们中提取文本。我对这篇文章做了一些小小的改动，没有进行编辑，所以如果有什么问题，请告诉我……

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.

注意，这是如何将JTable导出到.csv文件的问题的副本。以及如何将数据从JTable导出到CSV，尽管答案中只包含伪代码。

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();
    }
}

#1