Exporting JTable to excel - escaping the value of

问题:

In my file writer, I have these codes, the j == 4 and j == 8 is to execute a different code for the column that contains numbers

for(int i=0; i<model.getRowCount();i++){
        for(int j=0; j<model.getColumnCount();j++){
            if(j==4 || j==8){
                excelWriter.write("\""+model.getValueAt(i, j) + "\t"+"\""); 
            }
            else{
                excelWriter.write(model.getValueAt(i, j) + "\t");
            }
        }
        excelWriter.write("\n");
    }

the problem is after that column with number had been printed, the next column will not be printed.. for example

This is my JTable

| texta | textb | textc | textd | number | texte | textf | ....... 

in the excel file, it will be printed as....

| texta | textb | textc | textd | number | textf | ....... 

it skips the column beside the column with number...

when i remove the 2nd "\" in the code, the rest of the column concatenate to the column with number.

Any idea how could I fix this? Thanks in advance

回答1:

The second \ have to be written before \t I would guess.

Wait... I have created an Excel sheet with text columns and number columns. Then I exported that sheet to a tab separated file. When I open that file in a text editor the number isn't escaped on any kind. So I don't think you need this if.

回答2:

You probably meant:

excelWriter.write("\"" + model.getValueAt(i, j) + "\"\t");

回答3:

for example, File can be with .xsl or .xlsx

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import javax.swing.JTable;
import javax.swing.table.TableModel;

class ExcelExporterTodayFxDeals {

    ExcelExporterTodayFxDeals() {
    }

    public void exportTable(JTable table, File file) throws IOException {
        TableModel model = table.getModel();
        FileWriter out = new FileWriter(file);
        String diakritika = "áäÁÄčČďĎéěÉĚíÍľĺĽĹňŇóôöőÓÔÖŐŕřŔŘšŠťŤúüůÚÜŮýÝžŽ";
        String diakritikaBez = "aaAAcCdDeeEEiIllLLnNooooOOOOrrRRsStTuuuUUUyYzZ";
        String novyPreklad = "";
        String groupExport = "";
        Integer remDia = 1;
        for (int i = 0; i < (model.getColumnCount()); i++) {//* disable export from TableHeaders
            groupExport = String.valueOf(model.getColumnName(i));
            novyPreklad = groupExport;
            if (groupExport != (null)) {
                remDia = 1;
                for (remDia = 0; remDia < (diakritika.length());) {
                    novyPreklad = diakritika.substring(1, 2);
                    novyPreklad = (diakritika.substring(remDia, (remDia + 1))).toString();
                    groupExport = groupExport.replace((diakritika.substring(remDia, (remDia + 1))),
                            diakritikaBez.substring(remDia, (remDia + 1)));
                    remDia++;
                }
            } else {
                groupExport = "";
            }
            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++) {//16
                if (model.getValueAt(i, j) == null) {
                    out.write("null" + "\t");
                } else {
                    groupExport = String.valueOf(model.getValueAt(i, j));
                    novyPreklad = groupExport;
                    if (groupExport != (null)) {
                        remDia = 1;
                        if (j == 0) {//Replace non_ACSII chars, not required in most cases
                            for (remDia = 0; remDia < (diakritika.length());) {
                                novyPreklad = diakritika.substring(1, 2);
                                novyPreklad = (diakritika.substring(remDia, (remDia + 1))).toString();
                                groupExport = groupExport.replace((diakritika.substring(remDia, (remDia + 1))),
                                        diakritikaBez.substring(remDia, (remDia + 1)));
                                remDia++;
                            }
                            out.write(34);
                            out.write(String.valueOf(groupExport));
                            out.write(34);
                            out.write("\t");
                        }  else if (j == 5) {//Number Instance
                            if (((model.getValueAt(i, j)) != null) && ((model.getValueAt(i, j)) instanceof Number)) {
                                groupExport = String.format("%.2f", (Number) (model.getValueAt(i, j)));
                            }
                            groupExport = groupExport.replace(".", ",");
                            out.write(34);
                            out.write(String.valueOf(groupExport));
                            out.write(34);
                            out.write("\t");
                        }  else if (j == 10) {//Date instance
                            groupExport = groupExport.replace("-", "/");
                            out.write(34);
                            out.write(String.valueOf(groupExport));
                            out.write(34);
                            out.write("\t");
                        } 
                    } else {
                        groupExport = "";
                        out.write(34);
                        out.write(String.valueOf(groupExport));
                        out.write(34);
                        out.write("\t");
                    }
                }
            }
            out.write("\n");
        }
        out.close();
    }
}