使用Java和Jexcelapi从Excel写入多个CSV文件 [英] Writing Multiple CSV Files From Excel using Java and Jexcelapi

问题描述

我正在尝试创建一个程序，将所有数据从excel写入多个csv文件.

I'm trying to create a program that writes all the data from excel to multiple csv files.

当前，我的程序创建了 n 个文件，并且只有excel的最后一行写入了最后一个csv文件.

Currently, my program creates n number of files, and only the last row of excel is being written on the last csv file.

似乎文件正在写入.但是对于每个写入的行，它都会以某种方式创建相同的文件250次.它将擦除最后写入的行.

It seems like the file is writing. But for each written row, it somehow creates the same file 250 times. It erases the row that was written last.

使用 OutputStream 或 BufferedWriter 时，我可能会遇到一些问题.我就是弄不清楚它是什么.

I may have some problems using the OutputStream or BufferedWriter. I just can't figure out what it is.

writeRow()方法:

public static void writeRow(BufferedWriter bw, Cell[] row) throws IOException { 
    if (row.length > 0) {
        bw.write(row[0].getContents());
        for (int j = 1; j < row.length; j++) {
            bw.write(',');
            bw.write(row[j].getContents());
        }
    }
}

setFile()方法:

public static BufferedWriter setFile (int i) throws IOException {
    i=i/250;
    File f = new File(dir + "file-" + (i+1) + ".csv");
    // If i has changed, create a new file. Else, append to current file.
    String encoding = "UTF8";
    OutputStream os = null;
    OutputStreamWriter osw = null;
    BufferedWriter bw = null;
    Boolean append = null;
    try {
        // If i has changed, create a new file, else append.
        if (i%250==0) {
            append = new Boolean("TRUE");
            os = new FileOutputStream(f,append);
        } else {
            append = new Boolean("FALSE");
            os = new FileOutputStream(f, append);
        }
        osw = new OutputStreamWriter(os, encoding);
        bw = new BufferedWriter(osw);

    } finally {
        os.close();
        osw.close();
    }
    return bw;
}

这是例外.

Exception in thread "main" java.io.IOException: Stream closed
at sun.nio.cs.StreamEncoder.ensureOpen(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at java.io.OutputStreamWriter.write(Unknown Source)
at java.io.BufferedWriter.flushBuffer(Unknown Source)
at java.io.BufferedWriter.flush(Unknown Source)
at Main.main(Main.java:46)

下面是该代码的一些链接.

Below are some of the links to the code.

我在做什么错了?

这是svn中继线:

https://franz-opensource.googlecode.com/svn/trunk/ExcelToCsv -

• 单击此处以获取Java Main代码，就像上面的代码一样.
• 单击此处下载示例excel文件
• 单击此处下载jexcelapi

• Click here for the Java Main code like the one above.
• Click here to download the sample excel file
• Click here for the jexcelapi download

推荐答案

重要建议: 在每个 nth 记录之后，不要停止写入同一行，而是停止行.然后，阅读 再次工作表.然后，在另一个文件上输出缓冲区.在同一缓冲区上写入时，您将无法更改文件.

Important Advice: Instead of writing on the same buffer, stop the rows, after every nth records. Then, read the worksheet again. Then, output buffer on another file. You can not be able to change the file while writing on the same buffer.

下面是整个解决方案:

静态变量:

public static int maxRecords = 250;
public static String directory = "C:\\Users\\User02\\workspace\\ExcelToCsv\\src\\";
public static String inputFile = directory + "inventory.xls";

主要

public static void main(String[] args) throws BiffException, IOException {
Sheet s = getSheet();
    int countRows = s.getRows(); // counts the number of rows in the sheet.
    int numberOfFiles = (countRows/maxRecords)+1;

    for(int file=0; file<numberOfFiles; file++) {
        System.out.println("Create file number " + (file+1));
        int fileNumber = file+1;
        System.out.println("Start number: " + ((file*maxRecords)+1));
        int startNumber = (file*maxRecords);
        populateFile(fileNumber,startNumber);
        System.out.println("");
    }
}

填充列表:

public static void populateFile(int fileNumber, int startNumber) 
    throws IOException, BiffException {
        BufferedWriter bw = setFile(fileNumber);
        Sheet s = getSheet();
        Cell[] row = null;
        writeRow(bw,s.getRow(0));
        bw.newLine(); 
        int limit = getLimit(s,startNumber);
        System.out.println("End Number:" + limit);
        System.out.println();
        for (int i = startNumber; i < limit ; i++) {
            row = s.getRow(i); 
            //System.out.println(i);
            writeRow(bw,row);
            bw.newLine();
        }
        bw.flush();
        bw.close();
    }

获取工作表:

public static Sheet getSheet() throws BiffException, IOException {
    WorkbookSettings ws = new WorkbookSettings();
    ws.setLocale(new Locale("en", "EN"));
    Workbook w = Workbook.getWorkbook(new File(inputFile),ws);
    Sheet s = w.getSheet(0);
    return s;
}

设置要写入的文件:

public static BufferedWriter setFile(int fileNumber) throws IOException {
    String csvFilename = directory + "file-"+ fileNumber +".csv";
    FileWriter csvFile = new FileWriter(csvFilename);
    BufferedWriter bw = new BufferedWriter(csvFile);
    return bw;
}

获得限制:

public static int getLimit(Sheet s, int startNumber) {
    int limit;
    int countRows = s.getRows();
    if (startNumber+maxRecords<=countRows) {
        limit = startNumber + maxRecords;
    } else {
        limit = startNumber + (countRows-startNumber);
    }
    return limit;
}

将行写入文件:

public static void writeRow(BufferedWriter bw, Cell[] row) throws IOException { 
    if (row.length > 0) {
        bw.write(row[0].getContents());
        for (int j = 1; j < row.length; j++) {
            bw.write(',');
            bw.write(row[j].getContents());
        }
    }
}

这篇关于使用Java和Jexcelapi从Excel写入多个CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！