本文實(shí)例講述了Jexcel實(shí)現(xiàn)按一定規(guī)則分割excel文件的方法。分享給大家供大家參考。具體如下：
現(xiàn)有一個(gè)excel文檔，需要讀取它并按照一定的規(guī)則，分割之，分割出來(lái)的每一段記錄需要單獨(dú)創(chuàng)建一個(gè)excel文檔并寫(xiě)入其中，一定要保證單元格格式的一致性。
|
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對(duì)象之后，就可以通過(guò)它得到Sheet（工作表）對(duì)象了 InputStream is = new FileInputStream("C:/excel/excel.xls"); wb = Workbook.getWorkbook(is); // 獲得第一個(gè)工作表對(duì)象 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; //工作表行游標(biāo) int fileCounts = 1; //用來(lái)標(biāo)識(shí)創(chuàng)建的excel文檔數(shù)目 while(counter<rows-1) { //得到counter行的所有單元格 Cell[] rowCells = sheet.getRow(counter); Cell cell0 = rowCells[0]; //判斷單元格內(nèi)容的類(lèi)型 if(cell0.getType() == CellType.LABEL) { System.out.println("正在解析第 " + fileCounts + " 個(gè)文件...."); //新建一個(gè)excel文檔 File file = new File("C:/excel/excel" + fileCounts + ".xls"); WritableWorkbook wwb = Workbook.createWorkbook(file); //設(shè)置excel文檔的工作表 WritableSheet ws = wwb.createSheet("sheet1", 0); //第一行合并第0到第8列 ws.mergeCells(0, 0, 8, 0); //設(shè)置第7,8,9列的列寬 ws.setColumnView(6, 10); ws.setColumnView(7, 45); ws.setColumnView(8, 27); //向新建的表中寫(xiě)入數(shù)據(jù)，首先第一行先寫(xiě)入標(biāo)題 for(int k=0; k<rowCells.length; k++) { //創(chuàng)建WritableFont對(duì)象用來(lái)格式化字體，這里是20號(hào)宋體，加粗 WritableFont wf = new WritableFont(WritableFont.createFont("宋體"), 20, WritableFont.BOLD, false); //使用WritableFont創(chuàng)建單元格格式化對(duì)象 WritableCellFormat wcf = new WritableCellFormat(wf); //設(shè)置水平對(duì)齊方式 wcf.setAlignment(Alignment.CENTRE); //設(shè)置垂直對(duì)齊方式 wcf.setVerticalAlignment(VerticalAlignment.CENTRE); //設(shè)置邊框和顏色 wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); Cell cell = rowCells[k]; Label label = new Label(k,0,cell.getContents(),wcf); //添加單元格到表中 ws.addCell(label); //設(shè)置第一行的行高 ws.setRowView(0, 30*20, false); } //向新建的表中寫(xiě)入數(shù)據(jù)，第二行寫(xiě)入表頭 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; //用來(lái)遍歷50 counter++; //將游標(biāo)移動(dòng)到下一行 if(counter == 1) //如果游標(biāo)到了第二行 ，就自動(dòng)把游標(biāo)移動(dòng)到第三行，第二行不需要處理 counter = 2; int rowIndex = 2; //每篇excel文檔的游標(biāo) 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); } //用來(lái)處理備注列的邊框 { 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(); //關(guān)閉Workbook對(duì)象 } }} |
希望本文所述對(duì)大家的java程序設(shè)計(jì)有所幫助。
