在Java中将csv转换为xls的问题？只需要核心Java经验 - 与导入无关的问题 [英] Issues converting csv to xls in Java? Only core Java experience needed - question not related to import
问题描述
首先，我明白，我想要这样升级是不寻常的，但请承认我。
现在，在问题上：
我们通过网站导出获得这些csv文件，我们没有任何选项。
我有这个旧代码，将为我做这个过程。它基本上读取每一行，然后选择，之间的每个值。这对于我转换的一些样品非常有效，但是当它与给定的样品一起使用时，一些值是不合适的。
我在Notepad ++中打开了文件并意识到一些细胞本身包含，。 CSV文件通过使用围绕这些单元格来解决此问题。请参阅下面的示例：
。
这将工作正常：
John，Smith，johnsmith @ email.com，burgers
但这不会：
Smith，johnsmith @ email.com，burgers，french fries
。
不幸的是，我的代码（ String strar [] = thisLine.split（，）; ）不考虑某些单元格包含逗号，它们分成不同的列，例如：burgers 和 french fries。
$ b b
。 如何让我的程序有效地处理由作为单个值，而不是两个单独的值？
。
非常感谢您的帮助，
Justian
管理以回答我自己的问题。有一点搜索，我设法找到这里小pdf：
http://www.objectmentor.com/resources/articles/tfd.pdf
从那里，我设法采用第35页的代码来使用我的程序。所有信用到 Jeff Langr，2001 。
这里是所有可能在未来遇到这个问题的人的代码。
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
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;
默认值：
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''：
'\t'：
break;
case'＃'：
tokens.add（clean（buffer））;
state = stateCOMMENT;
break;
default：
throw new IOException（form form CSV form：+ line）;
}
break;
case stateDATA：
switch {
case'＃'：
tokens.add（clean（buffer））;
state = stateCOMMENT;
break;
case'，'：
state = stateNEW_TOKEN;
tokens.add（clean（buffer））;
buffer = new StringBuffer（）;
break;
默认值：
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;
//接受新的空列COLUMN
break ;
case''：
buffer.append（ch）;
state = stateQUOTED_DATA;
break;
case''：
case'\t'：
break;
默认值：
state = stateDATA;
buffer.append（ch）;
break;
}
break;
默认值：
break;
}
}
if（state == stateQUOTED_DATA）
throw new IOException（Unimched quotes in line：\\\
+ 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;
}
}
First of all, I understand that it's unusual that I want to up-convert like this, but please bear with me. We get these csv files via website export and we have no options to get it in any other form.
Now, onto the question:
I have this old code that will do this process for me. It basically reads each line, then picks out each value between the ,s. This worked great for some samples that I converted, but when it came down to working with the samples given, some values were out of place.
I opened the files in Notepad++ and realized that some of the cells, themselves, contained ,s. CSV files work around this by surrounding these cells with "s. See examples below:
.
This would work fine:
John,Smith,johnsmith@email.com,burgers
This, however, would not:
John,Smith,johnsmith@email.com,"burgers, french fries"
.
Unfortunately, my code (String strar[] = thisLine.split(",");) doesn't take into account that some cells contain commas, and will separate them into different columns like: "burgers and french fries".
.
How can I get my program to efficiently treat text surrounded by "s as a single value, not two separate ones?
.
Let me know if I can clear anything else up for you guys.
Many thanks for the help,
Justian
Managed to answer my own question. With a bit of searching, I managed to find this little pdf here:
http://www.objectmentor.com/resources/articles/tfd.pdf
From there, I managed to adopt the code on page 35 to work with my program. All credit goes to Jeff Langr, 2001. All I did was make it work with some of Java's current standards.
Here's the code for all the people who may encounter this problem in the future.
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;
}
}
这篇关于在Java中将csv转换为xls的问题？只需要核心Java经验 - 与导入无关的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
