重要建议:不要在同一个缓冲区上写入,而是在每次之后停止行nth记录。然后,阅读
再次查看工作表。然后,输出缓冲区到另一个文件上。在同一缓冲区上写入时无法更改文件。
这是整个解决方案的一部分:
静态变量:
public static int maxRecords = 250;
public static String directory = "C:\\Users\\User02\\workspace\\ExcelToCsv\\src\\";
public static String inputFile = directory + "inventory.xls";
Main:
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());
}
}
}