本文暫時給出一部分基礎運用，其他內容待本人翻譯完成之后再寫出了。
創建一個新的Workbook
1
HSSFWorkbook wb = new HSSFWorkbook();
2
3 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
4
5 wb.write(fileOut);
6
7 fileOut.close();
8
9
10
創建一個新的 Sheet
1 HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet1 = wb.createSheet("new sheet");
4
5 HSSFSheet sheet2 = wb.createSheet("second sheet");
6
7 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
8
9 wb.write(fileOut);
10
11 fileOut.close();
12
創建單元格 Cells
1 HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("new sheet");
4
5// 創建一個行row同時在上面設置一些單元格，注意，行是從0開始,這里創建第一行。
6
7HSSFRow row = sheet.createRow((short)0);
8
9 // 創建單元格并為他設置一個值，注意，單元格也是從0開始
10
11 HSSFCell cell = row.createCell((short)0);
12
13 cell.setCellValue(1);
14
15 // 可以在同一行創建多個單元格.
16
17 row.createCell((short)1).setCellValue(1.2);
18
19 row.createCell((short)2).setCellValue("This is a string");
20
21 row.createCell((short)3).setCellValue(true);
22
23 // 將這些數據輸出為Excel
24
25 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
26
27 wb.write(fileOut);
28
29 fileOut.close();
30
31
創建關于時間的單元格 Cells
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("new sheet");
4
5 //創建一個行row同時在上面設置一些單元格，注意，行是從0開始,這里創建第一行
6
7 HSSFRow row = sheet.createRow((short)0);
8
9 // 創建一個單元格并為其設置時間值，第一個單元格是原始時間值Double類型，沒有格式
10
11 HSSFCell cell = row.createCell((short)0);
12
13 cell.setCellValue(new Date());
14
15 // 現在將第二個單元格格式化為日期+時間.
16
17// 通過workbook創建一個新的單元格風格（cell style）是很重要的
18
19// 否則你在修改這個單元格的風格的時候可能會影響到其他單元格的風格。
20
21HSSFCellStyle cellStyle = wb.createCellStyle();
22
23 cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
24
25 cell = row.createCell((short)1);
26
27 cell.setCellValue(new Date());
28
29 cell.setCellStyle(cellStyle);
30
31 // 寫出文件
32
33 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
34
35 wb.write(fileOut);
36
37 fileOut.close();
38
39
操作不同類型的單元格
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("new sheet");
4
5 HSSFRow row = sheet.createRow((short)2);
6
7 row.createCell((short) 0).setCellValue(1.1);
8
9 row.createCell((short) 1).setCellValue(new Date());
10
11 row.createCell((short) 2).setCellValue("a string");
12
13 row.createCell((short) 3).setCellValue(true);
14
15 row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);
16
17 // 寫出文件
18
19 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
20
21 wb.write(fileOut);
22
23fileOut.close();
24
25
//一點廢話：由此可見，在上面的關于日期的格式化我們不一定要通過poi來做，我們可以將數據都格式化好了以后，轉換成String類型來寫入Excel，這樣從一定程度上統一寫入單元格的代碼，實現代碼復用。
示范不同的隊列選項（也就是單元格內容居左、居右等等）
1public static void main(String[] args)
2
3 throws IOException
4
5
{
6
7 HSSFWorkbook wb = new HSSFWorkbook();
8
9 HSSFSheet sheet = wb.createSheet("new sheet");
10
11 HSSFRow row = sheet.createRow((short) 2);
12
13 createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);
14
15 createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);
16
17 createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);
18
19 createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);
20
21 createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);
22
23 createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);
24
25 createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);
26
27 // 寫入文件
28
29 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
30
31 wb.write(fileOut);
32
33 fileOut.close();
34
35
}
36
37 /** *//**
38
39 * 創建一個單元格使其按照某種方式排列
40
41 *
42
43 * @param wb the workbook
44
45 * @param row 生成單元格的行
46
47 * @param column 在這個行中單元格所處的列數
48
49 * @param align 單元格內容的排列方式.
50
51 */
52
53 private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)
54
55 {
56
57 HSSFCell cell = row.createCell(column);
58
59 cell.setCellValue("Align It");
60
61 HSSFCellStyle cellStyle = wb.createCellStyle();
62
63 cellStyle.setAlignment(align);
64
65 cell.setCellStyle(cellStyle);
66
67 }
68
69
70
操作邊框
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("new sheet");
4
5 // 創建一行（row）并為其設置單元格，行從0開始.
6
7 HSSFRow row = sheet.createRow((short) 1);
8
9 // 創建單元格并為其設置數值.
10
11 HSSFCell cell = row.createCell((short) 1);
12
13 cell.setCellValue(4);
14
15 // 在單元格周圍設置邊框.
16
17 HSSFCellStyle style = wb.createCellStyle();
18
19 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
20
21 style.setBottomBorderColor(HSSFColor.BLACK.index);
22
23 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
24
25 style.setLeftBorderColor(HSSFColor.GREEN.index);
26
27 style.setBorderRight(HSSFCellStyle.BORDER_THIN);
28
29 style.setRightBorderColor(HSSFColor.BLUE.index);
30
31 style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
32
33 style.setTopBorderColor(HSSFColor.BLACK.index);
34
35 cell.setCellStyle(style);
36
37 // 寫入文件
38
39 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
40
41 wb.write(fileOut);
42
43 fileOut.close();
44
45
46
迭代遍歷行及其單元格
有時，我們喜歡遍歷一個Sheet的所有行，或者一行的所有單元格，這時可以通過循環來遍歷還是比較簡單的。
幸運的是，我們這有個非常簡單的方法。HSSFRow定義了一個CellIterator的內部類來迭代遍歷所有的單元格（通過調用row.celIterator獲?。?，而且HSSFSheet也提供了一個rowIterator方法給出了遍歷所有行的迭代器。
(Unfortunately, due to the broken and backwards-incompatible way that Java 5 foreach loops were implemented, it isn't possible to use them on a codebase that supports Java 1.4, as POI does)
1HSSFSheet sheet = wb.getSheetAt(0);
2
3 for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {
4
5 HSSFRow row = (HSSFRow)rit.next();
6
7
for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {
8
9 HSSFCell cell = (HSSFCell)cit.next();
10
11 // Do something here
12
13
}
14
15 }
16
17
18
19 HSSFSheet sheet = wb.getSheetAt(0);
20
21 for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) {
22
23 HSSFRow row = rit.next();
24
25 for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) {
26
27 HSSFCell cell = cit.next();
28
29 // Do something here
30
31 }
32
33 }
34
35
36
使用java5的特有的前端循環（foreach loops）遍歷行和單元格- OOXML Branch Only
有時候我們需要遍歷一個Sheet的所有行或者一行的所有列，這時如果使用java5或者更高版本的話，可以采用這種新的循環方式的處理。
幸運的是，這也非常簡單。HSSFSheet和HSSFRow都實現了java.lang.Iterator接口來允許新的循環方式的運行。HSSFRow允許通過CellIterator內部類來處理單元格的循環，HSSFSheet給出了rowIterator方法來遍歷所有的行。
1 HSSFSheet sheet = wb.getSheetAt(0);
2
3 for (HSSFRow row : sheet.rowIterator()) {
4
5 for (HSSFCell cell : row.cellIterator()) {
6
7 // Do something here
8
9 }
10
11 }
12
13
內容提取
對于大多數的內容提取需求，標準ExcelExtractor類應該能滿足您所有的需求。
1InputStream inp = new FileInputStream("workbook.xls");
2
3 HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
4
5 ExcelExtractor extractor = new ExcelExtractor(wb);
6
7 extractor.setFormulasNotResults(true);
8
9 extractor.setIncludeSheetNames(false);
10
11 String text = extractor.getText();
12
13
填充和顏色
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("new sheet");
4
5 // 創建一行，并為其設置單元格，行從0開始 .
6
7 HSSFRow row = sheet.createRow((short) 1);
8
9 // Aqua 背景色
10
11 HSSFCellStyle style = wb.createCellStyle();
12
13 style.setFillBackgroundColor(HSSFColor.AQUA.index);
14
15 style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
16
17 HSSFCell cell = row.createCell((short) 1);
18
19 cell.setCellValue("X");
20
21 cell.setCellStyle(style);
22
23 // Orange "前景色".
24
25 style = wb.createCellStyle();
26
27 style.setFillForegroundColor(HSSFColor.ORANGE.index);
28
29 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
30
31 cell = row.createCell((short) 2);
32
33 cell.setCellValue("X");
34
35 cell.setCellStyle(style);
36
37 // 寫入文件
38
39 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
40
41 wb.write(fileOut);
42
43 fileOut.close();
44
45
46
合并單元格
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("new sheet");
4
5 HSSFRow row = sheet.createRow((short) 1);
6
7 HSSFCell cell = row.createCell((short) 1);
8
9 cell.setCellValue("This is a test of merging");
10
11 sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));
12
13 // 寫入文件
14
15 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
16
17 wb.write(fileOut);
18
19 fileOut.close();
20
21
一點廢話：關于new Region(1,(short)1,1,(short)2)：四個參數分別為起始行、起始列、終止行、終止列，如果要在合并的單元格中寫入數據，首先在起始行列的單元格中寫入內容，之后從起始行列處開始拉伸合并單元格到終止行列。
字體設置
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("new sheet");
4
5 //創建行
6
7 HSSFRow row = sheet.createRow((short) 1);
8
9 // 創建一個系的呢字體并設置其屬性.
10
11 HSSFFont font = wb.createFont();
12
13 font.setFontHeightInPoints((short)24);
14
15 font.setFontName("Courier New");
16
17 font.setItalic(true);
18
19 font.setStrikeout(true);
20
21 // 字體設置給一個HSSFCellStyle對象.
22
23 HSSFCellStyle style = wb.createCellStyle();
24
25 style.setFont(font);
26
27 // 創建一個單元格并為其設置值
28
29 HSSFCell cell = row.createCell((short) 1);
30
31 cell.setCellValue("This is a test of fonts");
32
33 cell.setCellStyle(style);
34
35 //寫入文件
36
37 FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
38
39 wb.write(fileOut);
40
41 fileOut.close();
42
43
注意：一個workbook中字體數目被限制在32767個以內。你應該在程序中重用字體來代替為每個單元格創建新字體
下面寫法是錯誤的:
1for (int i = 0; i < 10000; i++) {
2
3 HSSFRow row = sheet.createRow(i);
4
5 HSSFCell cell = row.createCell((short) 0);
6
7 HSSFCellStyle style = workbook.createCellStyle();
8
9 HSSFFont font = workbook.createFont();
10
11 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
12
13 style.setFont(font);
14
15 cell.setCellStyle(style);
16
17 }
18
19
修正后:
1HSSFCellStyle style = workbook.createCellStyle();
2
3 HSSFFont font = workbook.createFont();
4
5 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
6
7 style.setFont(font);
8
9 for (int i = 0; i < 10000; i++) {
10
11 HSSFRow row = sheet.createRow(i);
12
13 HSSFCell cell = row.createCell((short) 0);
14
15 cell.setCellStyle(style);
16
17 }
18
19
一點廢話：將創建字體的代碼移出循環體。
如何讀取超鏈接
1HSSFSheet sheet = workbook.getSheetAt(0);
2
3 HSSFCell cell = sheet.getRow(0).getCell((short)0);
4
5 HSSFHyperlink link = cell.getHyperlink();
6
7 if(link != null){
8
9 System.out.println(link.getAddress());
10
11 }
12
13
14
15 HSSFWorkbook wb = new HSSFWorkbook();
16
17 //超鏈接的單元格風格
18
19 //超鏈接默認的是藍色底邊框
20
21 HSSFCellStyle hlink_style = wb.createCellStyle();
22
23 HSSFFont hlink_font = wb.createFont();
24
25 hlink_font.setUnderline(HSSFFont.U_SINGLE);
26
27 hlink_font.setColor(HSSFColor.BLUE.index);
28
29 hlink_style.setFont(hlink_font);
30
31 HSSFCell cell;
32
33 HSSFSheet sheet = wb.createSheet("Hyperlinks");
34
35 //URL
36
37 cell = sheet.createRow(0).createCell((short)0);
38
39 cell.setCellValue("URL Link");
40
41 HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
42
43 link.setAddress("http://poi.apache.org/");
44
45 cell.setHyperlink(link);
46
47 cell.setCellStyle(hlink_style);
48
49 //鏈接到當前路徑的一個文件
50
51 cell = sheet.createRow(1).createCell((short)0);
52
53 cell.setCellValue("File Link");
54
55 link = new HSSFHyperlink(HSSFHyperlink.LINK_FILE);
56
57 link.setAddress("link1.xls");
58
59 cell.setHyperlink(link);
60
61 cell.setCellStyle(hlink_style);
62
63 //鏈接到e-mail
64
65 cell = sheet.createRow(2).createCell((short)0);
66
67 cell.setCellValue("Email Link");
68
69 link = new HSSFHyperlink(HSSFHyperlink.LINK_EMAIL);
70
71 //note, if subject contains white spaces, make sure they are url-encoded
72
73 link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
74
75 cell.setHyperlink(link);
76
77 cell.setCellStyle(hlink_style);
78
79 //鏈接到 workbook的某個地方
80
81 //創建一個目標Sheet和單元格
82
83 HSSFSheet sheet2 = wb.createSheet("Target Sheet");
84
85 sheet2.createRow(0).createCell((short)0).setCellValue("Target Cell");
86
87 cell = sheet.createRow(3).createCell((short)0);
88
89 cell.setCellValue("Worksheet Link");
90
91 link = new HSSFHyperlink(HSSFHyperlink.LINK_DOCUMENT);
92
93 link.setAddress("'Target Sheet'!A1");
94
95 cell.setHyperlink(link);
96
97 cell.setCellStyle(hlink_style);
98
99 FileOutputStream out = new FileOutputStream("c://hssf-links.xls");
100
101 wb.write(out);
102
103 out.close();
104
105
106
一點廢話：link.setAddress("'Target Sheet'!A1");這一句中，Target Sheet是Sheet的名字，A是Excel中的列號，1是Excel中的行號，A1表示第一行第一列。
posted on 2008-11-25 12:50
Vincent-chen 閱讀(557)
評論(0) 編輯 收藏 所屬分類:
POI 、
Print