最近在实施一个项目,其中一项工作是处理历史数据。客户提供过来的数据是excel表格,超过20万条记录,由于目标系统导入限制,每次只能导入大小不超过8M的文件,所以需要对这些数据进行分割处理。在手工处理一遍后,觉得可以通过写一个程序来自动实现分割,于是用JAVA写了一个程序,用于针对特定文件实现给定记录数的分割功能。
详见代码:
package cn.sean.main;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class AccessExcel {
Cell[] titleCell;
Cell[][] allCell;
jxl.Workbook workBook;
Sheet sheet;
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "c:\\a.xls";
AccessExcel ae = new AccessExcel();
ae.readExcel(url);
ae.splitExcel(500, "c:\\");
}
/*
* 读取原excel文件,并将相关的数据存储到数组中
*/
public void readExcel(String source) {
File file = new File(source);
try {
workBook = Workbook.getWorkbook(file);
sheet = workBook.getSheet(0);
titleCell = new Cell[sheet.getColumns()];// 用于存储列标题
allCell = new Cell[sheet.getColumns()][sheet.getRows()];// 用于存储所有单元格数据
// 将列标题存储存到一个一维数组中
for (int i = 0; i < titleCell.length; i++) {
titleCell[i] = sheet.getCell(i, 0);
}
// 将所有单元格数据存储到一个二维数组中
for (int i = 0; i < sheet.getColumns(); i++) {
for (int j = 0; j < sheet.getRows(); j++) {
allCell[i][j] = sheet.getCell(i, j);
}
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*
*@param number代表需要分隔的行数
*@param destination代表分隔文件后存储的路径
*/
public void splitExcel(int number, String destination) {
int index = (int) Math.ceil(sheet.getRows() / number);//计算需要分隔多少个文件
File[] files = new File[index + 1];
//初始化文件数组
for (int i = 0; i <= index; i++) {
files[i] = new File(destination + i + ".xls");
}
int n = number;
int y = 1;//用于记录行的位置
for (int i = 0; i <= index; i++) {
try {
jxl.write.WritableWorkbook ww = Workbook
.createWorkbook(files[i]);
WritableSheet ws = ww.createSheet("sheet1", 0);
for (int t = 0; t < sheet.getColumns(); t++) {
ws.addCell(new Label(t, 0, allCell[t][0].getContents()));
}
out: for (int m = 1; y < sheet.getRows(); y++, m++) {
for (int x = 0; x < sheet.getColumns(); x++) {
if (y >number) {
number += n;
break out;
}
ws.addCell(new Label(x, m, allCell[x][y].getContents()));
}
}
ww.write();
ww.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}