读取和重写 Workbooks
1POIFSFileSystem fs =
2
3 new POIFSFileSystem(new FileInputStream("workbook.xls"));
4
5 HSSFWorkbook wb = new HSSFWorkbook(fs);
6
7 HSSFSheet sheet = wb.getSheetAt(0);
8
9 HSSFRow row = sheet.getRow(2);
10
11 HSSFCell cell = row.getCell((short)3);
12
13 if (cell == null)
14
15 cell = row.createCell((short)3);
16
17 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
18
19 cell.setCellValue("a test");
20
21 // 写入文件
22
23 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
24
25 wb.write(fileOut);
26
27 fileOut.close();
28
29
在单元格中换行
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet s = wb.createSheet();
4
5 HSSFRow r = null;
6
7 HSSFCell c = null;
8
9 HSSFCellStyle cs = wb.createCellStyle();
10
11 HSSFFont f = wb.createFont();
12
13 HSSFFont f2 = wb.createFont();
14
15 cs = wb.createCellStyle();
16
17 cs.setFont( f2 );
18
19 //开启Word Wrap
20
21 cs.setWrapText( true );
22
23 r = s.createRow( (short) 2 );
24
25 r.setHeight( (short) 0x349 );
26
27 c = r.createCell( (short) 2 );
28
29 c.setCellType( HSSFCell.CELL_TYPE_STRING );
30
31 c.setCellValue( "Use "n with word wrap on to create a new line" );
32
33 c.setCellStyle( cs );
34
35 s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );
36
37 FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
38
39 wb.write( fileOut );
40
41 fileOut.close();
42
43
数据格式化
1 HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("format sheet");
4
5 HSSFCellStyle style;
6
7 HSSFDataFormat format = wb.createDataFormat();
8
9 HSSFRow row;
10
11 HSSFCell cell;
12
13 short rowNum = 0;
14
15 short colNum = 0;
16
17 row = sheet.createRow(rowNum++);
18
19 cell = row.createCell(colNum);
20
21 cell.setCellValue(11111.25);
22
23 style = wb.createCellStyle();
24
25 style.setDataFormat(format.getFormat("0.0"));
26
27 cell.setCellStyle(style);
28
29 row = sheet.createRow(rowNum++);
30
31 cell = row.createCell(colNum);
32
33 cell.setCellValue(11111.25);
34
35 style = wb.createCellStyle();
36
37 style.setDataFormat(format.getFormat("#,##0.0000"));
38
39 cell.setCellStyle(style);
40
41 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
42
43 wb.write(fileOut);
44
45 fileOut.close();
46
47
使得一个Sheet适合一页
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("format sheet");
4
5 HSSFPrintSetup ps = sheet.getPrintSetup();
6
7 sheet.setAutobreaks(true);
8
9 ps.setFitHeight((short)1);
10
11ps.setFitWidth((short)1);
12
13 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
14
15 wb.write(fileOut);
16
17 fileOut.close();
18
19
设置打印区域
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("Sheet1");
4
5 wb.setPrintArea(0, "$A$1:$C$2");
6
7 //为第一个Sheet页设置打印区域
8
9 //也可以这样
10
11 //wb.setPrintArea(0, 0, 1, 0, 0) ,详细参考java doc
12
13 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
14
15 wb.write(fileOut);
16
17 fileOut.close();
18
设置页脚的页数
1 HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("format sheet");
4
5 HSSFFooter footer = sheet.getFooter()
6
7 footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
8
9 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
10
11 wb.write(fileOut);
12
13 fileOut.close();
14
15
使用简便的函数
这些函数保存在contrib并且提供了一些使用特征功能,例如设置合并单元格的边框,不用创建新样式改变样式属性。
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet1 = wb.createSheet( "new sheet" );
4
5 // 合并单元格
6
7 HSSFRow row = sheet1.createRow( (short) 1 );
8
9 HSSFRow row2 = sheet1.createRow( (short) 2 );
10
11 HSSFCell cell = row.createCell( (short) 1 );
12
13 cell.setCellValue( "This is a test of merging" );
14
15 Region region = new Region( 1, (short) 1, 4, (short) 4 );
16
17 sheet1.addMergedRegion( region );
18
19 // 设置边框和颜色.
20
21 final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED;
22
23 HSSFRegionUtil.setBorderBottom( borderMediumDashed,
24
25 region, sheet1, wb );
26
27 HSSFRegionUtil.setBorderTop( borderMediumDashed,
28
29 region, sheet1, wb );
30
31 HSSFRegionUtil.setBorderLeft( borderMediumDashed,
32
33 region, sheet1, wb );
34
35 HSSFRegionUtil.setBorderRight( borderMediumDashed,
36
37 region, sheet1, wb );
38
39 HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
40
41 HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
42
43 HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
44
45 HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
46
47 // 展示HSSFCellUtil类的用法
48
49 HSSFCellStyle style = wb.createCellStyle();
50
51 style.setIndention((short)4);
52
53 HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);
54
55 HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");
56
57 HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);
58
59 // 写入文件
60
61 FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
62
63 wb.write( fileOut );
64
65 fileOut.close();
66
67
在Sheet页中上下移动行
1 HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("row sheet");
4
5 //创建不同的行列
6
7 // 将6-11行移动到0-5行
8
9 sheet.shiftRows(5, 10, -5);
10
11 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
12
13 wb.write(fileOut);
14
15 fileOut.close();
16
17
设置一个Sheet页为被选中的
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("row sheet");
4
5 sheet.setSelected(true);
6
7 // 创建不同的行列。。。
8
9 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
10
11 wb.write(fileOut);
12
13 fileOut.close();
14
15
设置放大属性
1The zoom is expressed as a fraction. For example to express a zoom of 75% use 3 for the numerator and 4 for the denominator.
2
3Zoom被明确为一个分数,例如下面的75%使用3作为分子,4作为分母。
4
5 HSSFWorkbook wb = new HSSFWorkbook();
6
7 HSSFSheet sheet1 = wb.createSheet("new sheet");
8
9 sheet1.setZoom(3,4); // 75%放大
10
11 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
12
13 wb.write(fileOut);
14
15 fileOut.close();
16
拆分和冻结窗口
这里有你创建的两种窗口:冻结窗口和拆分窗口。
一个冻结窗口是被行和列拆分开的,可以按照如下设置创建冻结窗口。
1sheet1.createFreezePane( 3, 2, 3, 2 );
前两个参数是你要用来拆分的列数和行数。后两个参数是下面窗口的可见象限,其中第三个参数是右边区域可见的左边列数,第四个参数是下面区域可见的首行。
拆分可以将区域分成四个工作区。拆分发生在像素级别而且用户可以通过拖拽到新的位置来判断。
用如下方式拆分窗口:
1sheet2.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT );
The first parameter is the x position of the split. This is in 1/20th of a point. A point in this case seems to equate to a pixel. The second parameter is the y position of the split. Again in 1/20th of a point.
第一个参数是拆分的x位置。这里单位是一个点的1/20,在这种比例下点看起来是一个像素。第二个参数是拆分的y位置,也是一个点的1/20。第三、四个参数同上。最后一个参数说明当前哪个窗口有焦点,四个选择HSSFSheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT。
反复的行和列(设置打印标题)
在打印输出的时候需要使用HSSFWorkbook类的setRepeatingRowsAndColumns()方法反复的设置行和列。
这个方法包含5个参数,第一个参数是Sheet页的索引(从0开始算),第二、三个参数是重写的列的范围。不使用重写功能的话可以设置-1代替。第四、五个参数是重写的行的范围。不使用重写功能的话可以设置-1代替。
1HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet1 = wb.createSheet("new sheet");
4
5 HSSFSheet sheet2 = wb.createSheet("second sheet");
6
7 // 第一个Sheet设置从0到2的列
8
9 wb.setRepeatingRowsAndColumns(0,0,2,-1,-1);
10
11 // 第二个Sheet设置行和列
12
13 wb.setRepeatingRowsAndColumns(1,4,5,1,2);
14
15 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
16
17 wb.write(fileOut);
18
19 fileOut.close();
20
21
页眉和页脚
这是个页眉的例子,但是页脚也同样适用.
1 HSSFWorkbook wb = new HSSFWorkbook();
2
3 HSSFSheet sheet = wb.createSheet("new sheet");
4
5 HSSFHeader header = sheet.getHeader();
6
7 header.setCenter("Center Header");
8
9 header.setLeft("Left Header");
10
11 header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
12
13 HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
14
15 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
16
17 wb.write(fileOut);
18
19 fileOut.close();
posted on 2008-11-25 12:01
Vincent-chen 阅读(1155)
评论(0) 编辑 收藏 所属分类:
POI 、
Print