一步一步使用POI做java报表

钟鼓楼

读取和重写 Workbooks POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("workbook.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(2); HSSFCell cell = row.getCell((short)3); if (cell == null) cell = row.createCell((short)3); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("a test"); // 写入文件 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 在单元格中换行 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); cs = wb.createCellStyle(); cs.setFont( f2 ); //开启Word Wrap cs.setWrapText( true ); r = s.createRow( (short) 2 ); r.setHeight( (short) 0x349 ); c = r.createCell( (short) 2 ); c.setCellType( HSSFCell.CELL_TYPE_STRING ); c.setCellValue( "Use \n with word wrap on to create a new line" ); c.setCellStyle( cs ); s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) ); FileOutputStream fileOut = new FileOutputStream( "workbook.xls" ); wb.write( fileOut ); fileOut.close(); 数据格式化 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("format sheet"); HSSFCellStyle style; HSSFDataFormat format = wb.createDataFormat(); HSSFRow row; HSSFCell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum ); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(rowNum ); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 使得一个Sheet适合一页 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("format sheet"); HSSFPrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short)1); ps.setFitWidth((short)1); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 设置打印区域 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); wb.setPrintArea(0, "$A$1C$2"); //为第一个Sheet页设置打印区域 //也可以这样 //wb.setPrintArea(0, 0, 1, 0, 0) ，详细参考java doc FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 设置页脚的页数 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("format sheet"); HSSFFooter footer = sheet.getFooter() footer.setRight( "Page " HSSFFooter.page() " of " HSSFFooter.numPages() ); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 使用简便的函数 这些函数保存在contrib并且提供了一些使用特征功能，例如设置合并单元格的边框，不用创建新样式改变样式属性。 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet( "new sheet" ); // 合并单元格 HSSFRow row = sheet1.createRow( (short) 1 ); HSSFRow row2 = sheet1.createRow( (short) 2 ); HSSFCell cell = row.createCell( (short) 1 ); cell.setCellValue( "This is a test of merging" ); Region region = new Region( 1, (short) 1, 4, (short) 4 ); sheet1.addMergedRegion( region ); // 设置边框和颜色. final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED; [1] [2] [3] HSSFRegionUtil.setBorderBottom( borderMediumDashed, region, sheet1, wb ); HSSFRegionUtil.setBorderTop( borderMediumDashed, region, sheet1, wb ); HSSFRegionUtil.setBorderLeft( borderMediumDashed, region, sheet1, wb ); HSSFRegionUtil.setBorderRight( borderMediumDashed, region, sheet1, wb ); HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); // 展示HSSFCellUtil类的用法 HSSFCellStyle style = wb.createCellStyle(); style.setIndention((short)4); HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style); HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell"); HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER); // 写入文件 FileOutputStream fileOut = new FileOutputStream( "workbook.xls" ); wb.write( fileOut ); fileOut.close(); 在Sheet页中上下移动行 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("row sheet"); //创建不同的行列 // 将6-11行移动到0-5行 sheet.shiftRows(5, 10, -5); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 设置一个Sheet页为被选中的 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("row sheet"); sheet.setSelected(true); // 创建不同的行列。。。 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 设置放大属性 The zoom is expressed as a fraction. For example to express a zoom of 75% use 3 for the numerator and 4 for the denominator. Zoom被明确为一个分数，例如下面的75%使用3作为分子，4作为分母。 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("new sheet"); sheet1.setZoom(3,4); // 75%放大 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 拆分和冻结窗口 这里有你创建的两种窗口：冻结窗口和拆分窗口。 一个冻结窗口是被行和列拆分开的，可以按照如下设置创建冻结窗口。 sheet1.createFreezePane( 3, 2, 3, 2 ); 前两个参数是你要用来拆分的列数和行数。后两个参数是下面窗口的可见象限，其中第三个参数是右边区域可见的左边列数，第四个参数是下面区域可见的首行。 拆分可以将区域分成四个工作区。拆分发生在像素级别而且用户可以通过拖拽到新的位置来判断。 用如下方式拆分窗口: sheet2.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 wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("new sheet"); HSSFSheet sheet2 = wb.createSheet("second sheet"); HSSFSheet sheet3 = wb.createSheet("third sheet"); HSSFSheet sheet4 = wb.createSheet("fourth sheet"); // 冻结第一行 sheet1.createFreezePane( 0, 1, 0, 1 ); // 冻结第一列 sheet2.createFreezePane( 1, 0, 1, 0 ); // 冻结列和行 sheet3.createFreezePane( 2, 2 ); // 拆分窗口并且使左下方有焦点 sheet4.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT ); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 反复的行和列（设置打印标题） 在打印输出的时候需要使用HSSFWorkbook类的setRepeatingRowsAndColumns()方法反复的设置行和列。 这个方法包含5个参数，第一个参数是Sheet页的索引（从0开始算），第二、三个参数是重写的列的范围。不使用重写功能的话可以设置-1代替。第四、五个参数是重写的行的范围。不使用重写功能的话可以设置-1代替。 HSSFWorkbook wb = new HSSFWorkbook(); 上一页 [1] [2] [3] HSSFSheet sheet1 = wb.createSheet("new sheet"); HSSFSheet sheet2 = wb.createSheet("second sheet"); // 第一个Sheet设置从0到2的列 wb.setRepeatingRowsAndColumns(0,0,2,-1,-1); // 第二个Sheet设置行和列 wb.setRepeatingRowsAndColumns(1,4,5,1,2); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 页眉和页脚 这是个页眉的例子，但是页脚也同样适用. HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); HSSFHeader header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") HSSFHeader.fontSize((short) 16) "Right w/ Stencil-Normal Italic font and size 16"); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 上一页 [1] [2] [3]