在 Java 中将 .csv 转换为 .xls
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3189308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert .csv to .xls in Java
提问by Justian Meyer
Does anyone here know of any quick, clean way to convert csv files to xls or xlsx files in java?
这里有人知道将 csv 文件转换为 java 中的 xls 或 xlsx 文件的任何快速、干净的方法吗？
I have something to manage csv files already in place and I need the extra compatibility for other programs.
我有一些东西可以管理已经到位的 csv 文件，我需要其他程序的额外兼容性。
Sample code in addition to package names is always well appreciated.
除了包名之外的示例代码总是很受欢迎。
Many thanks,
非常感谢，
Justian
贾斯蒂安
Here's my code thus far. I need to remove the returns ("\n") from the lines. Some of my cells contain multiple lines of information (a list), so I can use "\n" in csv to indicate multiple lines within a cell, but xls treats these as if I mean to put them on a new line.
到目前为止，这是我的代码。我需要从行中删除返回（“\n”）。我的一些单元格包含多行信息（一个列表），所以我可以在 csv 中使用 "\n" 来表示一个单元格内的多行，但 xls 将这些视为我打算将它们放在一个新行上。
The code is modified from the internet and a little messy at the moment. You might notice some deprecated methods, as it was written in 2004, and be sure to ignore the terrible return statements. I'm just using S.o.p at the moment for testing and I'll clean that up later.
代码是网上修改的，目前有点乱。您可能会注意到一些已弃用的方法，因为它是在 2004 年编写的，并且一定要忽略糟糕的 return 语句。我现在只是使用 Sop 进行测试，稍后我会清理它。
package jab.jm.io;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class FileConverter {
public static String ConvertCSVToXLS(String file) throws IOException {
if (file.indexOf(".csv") < 0)
return "Error converting file: .csv file not given.";
String name = FileManager.getFileNameFromPath(file, false);
ArrayList<ArrayList<String>> arList = new ArrayList<ArrayList<String>>();
ArrayList<String> al = null;
String thisLine;
DataInputStream myInput = new DataInputStream(new FileInputStream(file));
while ((thisLine = myInput.readLine()) != null) {
al = new ArrayList<String>();
String strar[] = thisLine.split(",");
for (int j = 0; j < strar.length; j++) {
// My Attempt (BELOW)
String edit = strar[j].replace('\n', ' ');
al.add(edit);
}
arList.add(al);
System.out.println();
}
try {
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");
for (int k = 0; k < arList.size(); k++) {
ArrayList<String> ardata = (ArrayList<String>) arList.get(k);
HSSFRow row = sheet.createRow((short) 0 + k);
for (int p = 0; p < ardata.size(); p++) {
System.out.print(ardata.get(p));
HSSFCell cell = row.createCell((short) p);
cell.setCellValue(ardata.get(p).toString());
}
}
FileOutputStream fileOut = new FileOutputStream(
FileManager.getCleanPath() + "/converted files/" + name
+ ".xls");
hwb.write(fileOut);
fileOut.close();
System.out.println(name + ".xls has been generated");
} catch (Exception ex) {
}
return "";
}
}
采纳答案by Carl Smotricz
Don't know if you know this already, but:
不知道你是否已经知道这一点，但是：
- Excel (if that's your real target) is easily able to read
.csvfiles directly, so any conversion you'd do would only be a courtesy to your less "gifted" users. - CSV is a lowest-common-denominator format. It's unlikely for any converter to add information to that found in a
.csvfile that will make it more useful. In other words, CSV is a "dumb" format and converting it to.xlswill (probably) increase file size but not make the format any smarter.
- Excel（如果那是您的真正目标）可以轻松地
.csv直接读取文件，因此您所做的任何转换都只是对不太“有天赋”的用户的一种礼貌。 - CSV 是最低公分母格式。任何转换器都不可能将信息添加到
.csv文件中找到的信息中以使其更有用。换句话说，CSV 是一种“哑”格式，将其转换为.xls（可能）会增加文件大小，但不会使格式更智能。
Curtis' suggestion of POI is the first thing that would come to my mind too.
Curtis 对 POI 的建议也是我想到的第一件事。
If you're doing this conversion on a Windows machine, another alternative could be Jacob, a Java-COM bridge that would allow you to effectively remote control Excel from a Java program so as to do things like open a file and save in a different format, perhaps even applying some formatting changes or such.
如果您在 Windows 机器上进行这种转换，另一种选择可能是Jacob，这是一个 Java-COM 桥，它允许您从 Java 程序有效地远程控制 Excel，以便执行诸如打开文件并保存在不同的格式，甚至可能应用一些格式更改等。
Finally, I've also had some success doing SQL INSERTs (via JDBC) into an Excel worksheet accessed via the JDBC-ODBC bridge. i.e. ODBC can make an Excel file look like a database. It's not very flexible though, you can't ask the DB to create arbitrarily named .XLSfiles.
最后，我在INSERT通过 JDBC-ODBC 桥访问的 Excel 工作表中执行 SQL （通过 JDBC）也取得了一些成功。即 ODBC 可以使 Excel 文件看起来像一个数据库。但是它不是很灵活，您不能要求数据库创建任意命名的.XLS文件。
EDIT:
编辑：
It looks to me like readLine()is already not giving you whole lines. How is it to know that carriage return is not a line terminator? You should be able to verify this with debug print statements right after the readLine().
在我看来，它readLine()已经没有给你完整的线条。怎么知道回车不是行终止符？您应该能够在 readLine() 之后立即使用调试打印语句来验证这一点。
If this is indeed so, it would suck because the way forward would be for you to
如果确实如此，那就太糟糕了，因为前进的道路是你
- either recognize incomplete lines and paste them together after the fact,
- or write your own substitute for readLine(). A simple approach would be to read character by character, replacing CRs within a CSV string and accumulating text in a StringBuilder until you feel you have a complete line.
- 要么识别不完整的线条并在事后将它们粘贴在一起，
- 或编写自己的 readLine() 替代品。一个简单的方法是逐个字符读取，替换 CSV 字符串中的 CR 并在 StringBuilder 中累积文本，直到您觉得有完整的一行。
Both alternatives are work you probably weren't looking forward to.
这两种选择都是您可能不期待的工作。
回答by Curtis
If you want to read or write XLS or XLSX files in Java, Apache POI is a good bet: http://poi.apache.org/
如果您想用 Java 读取或写入 XLS 或 XLSX 文件，Apache POI 是一个不错的选择：http: //poi.apache.org/
回答by Alan Krueger
You wrote:
你写了：
I have something to manage csv files already in place and I need the extra compatibility for other programs.
我有一些东西可以管理已经到位的 csv 文件，我需要其他程序的额外兼容性。
What are those other programs? Are they required to access your data through Excel files, or could they work with an JDBC or ODBC connection to a database? Using a database as the central location, you could extract the data into CSV files or other formats as needed.
那些其他程序是什么？他们是否需要通过 Excel 文件访问您的数据，或者他们是否可以使用 JDBC 或 ODBC 连接到数据库？使用数据库作为中心位置，您可以根据需要将数据提取为 CSV 文件或其他格式。
回答by ChopperCharles
The tools in Excel are not adequate for what the OP wants to do. He's on the right track there. Excel cannot import multiple CSV files into different worksheets in the same file, which is why you'd want to do it in code. My suggestion is to use OpenCSV to read the CSV, as it can automatically correct for newlines in data and missing columns, and it's free and open source. It's actually very, very robust and can handle all sorts of different non-standard CSV files.
Excel 中的工具不足以满足 OP 想要做的事情。他在那里走在正确的轨道上。Excel 无法将多个 CSV 文件导入同一文件中的不同工作表，这就是您希望在代码中执行此操作的原因。我的建议是使用 OpenCSV 来读取 CSV，因为它可以自动更正数据中的换行符和缺失的列，而且它是免费和开源的。它实际上非常非常健壮，可以处理各种不同的非标准 CSV 文件。
回答by user3065934
Copy paste the below program,I ran the program and it is working fine,Let me know if you have any concerns on this program.(You need Apache POI Jar to run this program)
复制粘贴下面的程序，我运行了该程序并且运行良好，如果您对此程序有任何疑问，请告诉我。（您需要 Apache POI Jar 才能运行该程序）
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
public class CSVToExcelConverter {
public static void main(String args[]) throws IOException
{
ArrayList arList=null;
ArrayList al=null;
String fName = "test.csv";
String thisLine;
int count=0;
FileInputStream fis = new FileInputStream(fName);
DataInputStream myInput = new DataInputStream(fis);
int i=0;
arList = new ArrayList();
while ((thisLine = myInput.readLine()) != null)
{
al = new ArrayList();
String strar[] = thisLine.split(",");
for(int j=0;j<strar.length;j++)
{
al.add(strar[j]);
}
arList.add(al);
System.out.println();
i++;
}
try
{
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");
for(int k=0;k<arList.size();k++)
{
ArrayList ardata = (ArrayList)arList.get(k);
HSSFRow row = sheet.createRow((short) 0+k);
for(int p=0;p<ardata.size();p++)
{
HSSFCell cell = row.createCell((short) p);
String data = ardata.get(p).toString();
if(data.startsWith("=")){
cell.setCellType(Cell.CELL_TYPE_STRING);
data=data.replaceAll("\"", "");
data=data.replaceAll("=", "");
cell.setCellValue(data);
}else if(data.startsWith("\"")){
data=data.replaceAll("\"", "");
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(data);
}else{
data=data.replaceAll("\"", "");
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(data);
}
//*/
// cell.setCellValue(ardata.get(p).toString());
}
System.out.println();
}
FileOutputStream fileOut = new FileOutputStream("test.xls");
hwb.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated");
} catch ( Exception ex ) {
ex.printStackTrace();
} //main method ends
}
}
