在Java中将.csv转换为.xls [英] Convert .csv to .xls in Java

问题描述

有没有人知道任何快速，干净的方式将csv文件转换为java的xls或xlsx文件？

Does anyone here know of any quick, clean way to convert csv files to xls or xlsx files in java?

我有东西来管理csv文件我需要额外的兼容性为其他程序。

I have something to manage csv files already in place and I need the extra compatibility for other programs.

除了包名称之外，示例代码总是非常受欢迎。

Sample code in addition to package names is always well appreciated.

非常感谢，

Justian

这里是我的代码。我需要从行中删除返回（\\\
）。我的一些单元格包含多行信息（列表），因此我可以使用csv中的\\\
来表示单元格中的多行，但xls将这些看作是我的意思他们在一个新的行。

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.

代码是从互联网修改，有点凌乱。您可能会注意到一些已弃用的方法，因为它是在2004年编写的，并确保忽略可怕的返回语句。我现在只是使用Sop进行测试，我会在以后清理。

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.

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 "";
    }
}

推荐答案

Don不知道这是否已经知道，但是：

Don't know if you know this already, but:

• Excel（如果这是你的真实目标）很容易阅读 .csv 文件，因此您所做的任何转换都只是为了更少的天才用户。


• 公分母格式。任何转换器不太可能添加到 .csv 文件中的信息，这将使它更有用。换句话说，CSV是一个哑格式，转换为 .xls 将（可能）增加文件大小，但不使格式更聪明。

• Excel (if that's your real target) is easily able to read .csv files 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 .csv file that will make it more useful. In other words, CSV is a "dumb" format and converting it to .xls will (probably) increase file size but not make the format any smarter.

Curtis对POI的建议是我心目中的第一件事。

Curtis' suggestion of POI is the first thing that would come to my mind too.

如果您在Windows计算机上执行此转换，则另一个备选方案可以是 Jacob ，这是一个Java-COM桥，它允许您从Java程序有效地远程控制Excel，以便执行打开文件和保存为不同格式，甚至应用一些格式化更改或者这样。

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.

最后，我也有一些成功做SQL INSERT 通过JDBC-ODBC桥访问的Excel工作表。即ODBC可以使Excel文件看起来像数据库。它不是很灵活，但你不能要求DB创建任意命名的 .XLS 文件。

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

编辑：

看起来像 readLine（）不给你整条线。如何知道回车不是行终止符？你应该能够在readLine（）之后用debug print语句验证这一点。

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

如果这是真的，它会吸引，因为前进的方向是你的到

If this is indeed so, it would suck because the way forward would be for you to

• 识别不完整的行并在事实后将它们粘贴在一起，


• 替换readLine（）。一个简单的方法是逐个字符读取，替换CSV字符串中的CR，并在StringBuilder中累积文本，直到您感觉到一个完整的行。

这两种方法都是你可能不期待的工作。

Both alternatives are work you probably weren't looking forward to.

这篇关于在Java中将.csv转换为.xls的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！