在Java中将csv转换为xls时出现问题？只需核心Java经验-与导入无关的问题

Question

首先，我了解到我想像这样进行上转换是不寻常的，但是请耐心等待。我们是通过网站导出获取这些csv文件的，我们没有选择以其他任何形式获取它的选项。

现在，进入一个问题：

我有这个旧代码，可以为我完成此过程。它基本上读取每一行，然后选择,s
之间的每个值。这对于我转换的某些样本效果很好，但是当归结为使用给定的样本时，某些值就不合适了。

我在Notepad ++中打开文件，并意识到某些单元格本身包含,s。CSV文件通过用"s 包围这些单元来解决此问题。请参阅以下示例：

。

这样可以正常工作：

John,Smith,[e-mail],burgers

但是，这不会：

John,Smith,[e-mail],"burgers, french fries"

。

不幸的是，我的代码（String strar[] = thisLine.split(",");）没有考虑到某些单元格包含逗号，会将它们分成不同的列，例如："burgers和french fries"。

。

如何使程序有效地将"s 包围的文本视为单个值，而不是两个单独的值？

。

让我知道是否可以为您清除其他任何内容。

非常感谢您的帮助，

贾斯汀

Answer 1

设法回答了我自己的问题。经过一些搜索，我设法在这里找到了这个小pdf：

http://www.objectmentor.com/resources/articles/tfd.pdf

从那里，我设法采用了第35页上的代码来与我的程序一起使用。所有功劳归于Jeff Langr, 2001。我所做的就是使其与Java当前的某些标准兼容。

这是将来可能会遇到此问题的所有人员的代码。

import java.io.BufferedReader;
import java.io.IOException;
import java.util.ArrayList;

public class CSVReader {

    private BufferedReader reader;
    private String line;
    private static final String DOUBLE_QUOTE = "\"";
    private static final String COMMENT_SYMBOL = "#";
    private static final char stateINIT = 'S';
    private static final char stateCOMMENT = '#';
    private static final char stateQUOTED_DATA = 'q';
    private static final char stateQUOTE_IN_QUOTED_DATA = 'Q';
    private static final char stateDATA = 'D';
    private static final char stateNEW_TOKEN = 'N';
    private static final char stateWHITESPACE = 'W';

    public CSVReader(String filename) throws IOException {
        reader = new BufferedReader(new java.io.FileReader(filename));
        loadNextNonCommentLine();
    }

    public ArrayList<String> next() throws IOException {
        if (line == null)
            throw new IOException("Read past end of file");
        ArrayList<String> columns = columnsFromCSVRecord(line);
        loadNextNonCommentLine();
        return columns;
    }

    public boolean hasNext() {
        return line != null;
    }

    void loadNextNonCommentLine() throws IOException {
        do
            line = reader.readLine();
        while (line != null && line.startsWith(COMMENT_SYMBOL));
        if (line == null)
            reader.close();
    }

    public ArrayList<String> columnsFromCSVRecord(String line) throws IOException {
        char state = stateINIT;
        char ch;
        int i = 0;
        ArrayList<String> tokens = new ArrayList<String>();
        StringBuffer buffer = new StringBuffer();
        char[] charArray = line.toCharArray();
        while (i < charArray.length) {
            ch = charArray[i++];
            switch (state) {
            case stateINIT:
                switch (ch) {
                case '"':
                    buffer.append(ch);
                    state = stateQUOTED_DATA;
                    break;
                case ',':
                    state = stateNEW_TOKEN;
                    tokens.add(clean(buffer));
                    buffer = new StringBuffer();
                    break;
                case '\t':
                case ' ':
                    break;
                case '#':
                    state = stateCOMMENT;
                    break;
                default:
                    state = stateDATA;
                    buffer.append(ch);
                    break;
                }
                break;
            case stateCOMMENT:
                break;
            case stateQUOTED_DATA:
                switch (ch) {
                case '"':
                    buffer.append(ch);
                    state = stateQUOTE_IN_QUOTED_DATA;
                    break;
                default:
                    buffer.append(ch);
                    break;
                }
                break;
            case stateQUOTE_IN_QUOTED_DATA:
                switch (ch) {
                case '"':
                    state = stateQUOTED_DATA;
                    break;
                case ',':
                    state = stateNEW_TOKEN;
                    tokens.add(clean(buffer));
                    buffer = new StringBuffer();
                    break;
                case ' ':
                case '\t':
                    break;
                case '#':
                    tokens.add(clean(buffer));
                    state = stateCOMMENT;
                    break;
                default:
                    throw new IOException("badly formed CSV record:" + line);
                }
                break;
            case stateDATA:
                switch (ch) {
                case '#':
                    tokens.add(clean(buffer));
                    state = stateCOMMENT;
                    break;
                case ',':
                    state = stateNEW_TOKEN;
                    tokens.add(clean(buffer));
                    buffer = new StringBuffer();
                    break;
                default:
                    buffer.append(ch);
                    break;
                }
                break;
            case stateNEW_TOKEN:
                switch (ch) {
                case '#':
                    tokens.add(clean(buffer));
                    state = stateCOMMENT;
                    break;
                case ',':
                    tokens.add(clean(buffer));
                    buffer = new StringBuffer();
                    break;
                case ' ':
                case '\t':
                    state = stateWHITESPACE;
                    break;
                case '"':
                    buffer.append(ch);
                    state = stateQUOTED_DATA;
                    break;
                default:
                    state = stateDATA;
                    buffer.append(ch);
                    break;
                }
                break;
            case stateWHITESPACE:
                switch (ch) {
                case '#':
                    state = stateCOMMENT;
                    break;
                case ',':
                    state = stateNEW_TOKEN;
                    // ACCEPT NEW EMPTY COLUMN HERE??
                    break;
                case '"':
                    buffer.append(ch);
                    state = stateQUOTED_DATA;
                    break;
                case ' ':
                case '\t':
                    break;
                default:
                    state = stateDATA;
                    buffer.append(ch);
                    break;
                }
                break;
            default:
                break;
            }
        }
        if (state == stateQUOTED_DATA)
            throw new IOException("Unmatched quotes in line:\n" + line);
        if (state != stateCOMMENT)
            tokens.add(clean(buffer));
        return tokens;
    }

    public String clean(StringBuffer buffer) {
        String string = buffer.toString().trim();
        if (string.startsWith(DOUBLE_QUOTE))
            return string.substring(1, string.length() - 1);
        return string;
    }
}