一步一步使用POI做java报表

2008-09-22 08:35:14　来源:中国自学编程网　作者:佚名 点击:

---

读取和重写 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;

9 7 3 1 2 3 4 8 :