本文实例讲述了Jexcel实现按一定规则分割excel文件的方法。分享给大家供大家参考。具体如下：
现有一个excel文档，需要读取它并按照一定的规则，分割之，分割出来的每一段记录需要单独创建一个excel文档并写入其中，一定要保证单元格格式的一致性。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
package edu.bjut.zhutong.excelParser;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class ExcelParser {
public static void main(String[] args) {
Workbook wb = null;
try {
//获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了
InputStream is = new FileInputStream("C:/excel/excel.xls");
wb = Workbook.getWorkbook(is);
// 获得第一个工作表对象
Sheet sheet = wb.getSheet(0);
//获得工作表的行数和列数
int rows = sheet.getRows();
int cols = sheet.getColumns();
System.out.println("一共 " + rows + " 行");
System.out.println("一共 " + cols + " 列");
int counter = 0; //工作表行游标
int fileCounts = 1; //用来标识创建的excel文档数目
while(counter<rows-1) {
//得到counter行的所有单元格
Cell[] rowCells = sheet.getRow(counter);
Cell cell0 = rowCells[0];
//判断单元格内容的类型
if(cell0.getType() == CellType.LABEL) {
System.out.println("正在解析第 " + fileCounts + " 个文件....");
//新建一个excel文档
File file = new File("C:/excel/excel" + fileCounts + ".xls");
WritableWorkbook wwb = Workbook.createWorkbook(file);
//设置excel文档的工作表
WritableSheet ws = wwb.createSheet("sheet1", 0);
//第一行合并第0到第8列
ws.mergeCells(0, 0, 8, 0);
//设置第7,8,9列的列宽
ws.setColumnView(6, 10);
ws.setColumnView(7, 45);
ws.setColumnView(8, 27);
//向新建的表中写入数据，首先第一行先写入标题
for(int k=0; k<rowCells.length; k++) {
//创建WritableFont对象用来格式化字体，这里是20号宋体，加粗
WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), 20, WritableFont.BOLD, false);
//使用WritableFont创建单元格格式化对象
WritableCellFormat wcf = new WritableCellFormat(wf);
//设置水平对齐方式
wcf.setAlignment(Alignment.CENTRE);
//设置垂直对齐方式
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置边框和颜色
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
Cell cell = rowCells[k];
Label label = new Label(k,0,cell.getContents(),wcf);
//添加单元格到表中
ws.addCell(label);
//设置第一行的行高
ws.setRowView(0, 30*20, false);
}
//向新建的表中写入数据，第二行写入表头
for(int c=0; c<cols; c++) {
String colCon = sheet.getCell(c, 1).getContents();
WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD, false);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
ws.addCell(new Label(c,1,colCon,wcf));
ws.setRowView(1, 18*20, false);
}
int rowCounts = 1; //用来遍历50
counter++; //将游标移动到下一行
if(counter == 1) //如果游标到了第二行 ，就自动把游标移动到第三行，第二行不需要处理
counter = 2;
int rowIndex = 2; //每篇excel文档的游标
rowCells = sheet.getRow(counter);
cell0 = rowCells[0];
while(cell0.getType() == CellType.NUMBER && counter<rows-1) {
rowCells = sheet.getRow(counter);
for(int k=0; k<rowCells.length; k++) {
WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
Label label = new Label(k,rowIndex,rowCells[k].getContents(),wcf);
ws.addCell(label);
}
//用来处理备注列的边框
{
WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
Label label = new Label(8,rowIndex,"",wcf);
ws.addCell(label);
}
ws.setRowView(rowIndex, 18*20, false);
rowIndex++;
counter++;
cell0 = sheet.getRow(counter)[0];
}
wwb.write();
wwb.close();
fileCounts++;
}
}
System.out.println("程序执行结束....");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
wb.close(); //关闭Workbook对象
}
}
}
|
希望本文所述对大家的java程序设计有所帮助。