本文使用jxl,如果使用maven 直接引入即可。
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
注意: ecexl每个sheet工作表的数据资源容纳6万多一点,如果写入数据超过6万要注意生成追加新的sheet(代码以实现),
代码如下:
public static void main(String[] args) {
File file = new File("D:\\江苏幼儿家长.txt");// 将读取的txt文件
File file2 = new File("D:\\测试EXECL1.xls");// 将生成的excel表格
List<String> list = new ArrayList<String>();
if (file.exists() && file.isFile()) {
InputStreamReader read = null;
String line = "";
BufferedReader input = null;
WritableWorkbook wbook = null;
WritableSheet sheet;
String [] work2 =new String[4];
int count=0;
int s=0;
try {
read = new InputStreamReader(new FileInputStream(file), "UTF-8");
input = new BufferedReader(read);
wbook = Workbook.createWorkbook(file2);// 根据路径生成excel文件
sheet = wbook.createSheet("数据"+s, s);// 新标签页
int m = 1;// excel行数
int n = 0;// excel列数
Label t;
//execl有最大容量
//不能够超过Excel的最大容量,如果超过会追加新的sheet
//根据自己的情况自定义
int maxRowCount = 60000;
while ((line = input.readLine()) != null) {
try {
Label company = new Label(0, 0, "家长ID");// 如下皆为列名
sheet.addCell(company);
Label position = new Label(1, 0, "家长姓名");
sheet.addCell(position);
Label salary = new Label(2, 0, "手机号");
sheet.addCell(salary);
Label status = new Label(3, 0, "密码");
sheet.addCell(status);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
String[] words = line.split(",");// 把读出来的这行根据,分割开
/**
* 下面的if 是自己需要,需要对原先的读的文件进行筛选 然后写入文件
*/
if(list.indexOf(words[12]) == -1 && words[11].equals("3")){
count++;
work2[0]=generateRandomId();
work2[1]=words[14];
work2[2]=words[12];
work2[3]="";
list.add(words[12]);
//======到此 自己需要=========================
for (int i = 0; i < work2.length; i++) {
if (!words[i].matches("\\s*")) { // 当不是空行时
t = new Label(n, m, work2[i].trim());
sheet.addCell(t);
n++;
}
}
n = 0;// 回到列头部
m++;// 向下移动一行
}
//如果sheet工作表的容量超出,则新追加一个
if(count> maxRowCount)
{
s++;
sheet = wbook.createSheet("数据"+s, s);// 新标签页
count=0;
m=0;
}
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
try {
wbook.write();
wbook.close();
input.close();
read.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
System.out.println("over!");
System.exit(0);
} else {
System.out.println("file is not exists or not a file");
System.exit(0);
}
}