使用Java和Jexcelapi从Excel写入多个CSV文件

使用Java和Jexcelapi从Excel写入多个CSV文件

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

以下是整个解决方案：

静态变量：

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());        }    }}