本文實例講述了Jexcel實現(xiàn)按一定規(guī)則分割excel文件的方法。分享給大家供大家參考。具體如下：
現(xiàn)有一個excel文檔，需要讀取它并按照一定的規(guī)則，分割之，分割出來的每一段記錄需要單獨創(chuàng)建一個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); //獲得工作表的行數(shù)和列數(shù) int rows = sheet.getRows(); int cols = sheet.getColumns(); System.out.println("一共 " + rows + " 行"); System.out.println("一共 " + cols + " 列"); int counter = 0; //工作表行游標 int fileCounts = 1; //用來標識創(chuàng)建的excel文檔數(shù)目 while(counter<rows-1) { //得到counter行的所有單元格 Cell[] rowCells = sheet.getRow(counter); Cell cell0 = rowCells[0]; //判斷單元格內(nèi)容的類型 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); //向新建的表中寫入數(shù)據(jù)，首先第一行先寫入標題 for(int k=0; k<rowCells.length; k++) { //創(chuàng)建WritableFont對象用來格式化字體，這里是20號宋體，加粗 WritableFont wf = new WritableFont(WritableFont.createFont("宋體"), 20, WritableFont.BOLD, false); //使用WritableFont創(chuàng)建單元格格式化對象 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); } //向新建的表中寫入數(shù)據(jù)，第二行寫入表頭 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("程序執(zhí)行結(jié)束...."); } 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程序設計有所幫助。
