一步一步使用POI做java报表--打印设置篇

最新推荐文章于 2022-06-28 15:37:00 发布

qiudawei115

最新推荐文章于 2022-06-28 15:37:00 发布

阅读量8.6k

点赞数 1

文章标签： java 报表 header express null string

读取和重写 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$1:$C$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;

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 );

// 拆分窗口并且使左下方有焦点