java利用poi生成excel报表

下面的代码展示了利用poi的类库生成excel表格的代码，初步展示了excel的建立，格式的渲染和表格的合并等等

/**excel表格处理类 * @author luosheng * */public class ExcelUtil {public static void main(String[] args) throws Exception{System.out.println("---创建成功----");ExcelUtil.exportBonusExcel2();}public static void exportBonusExcel2()throws Exception {HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象  HSSFSheet sheet = wb.createSheet("四月份奖金");//建立新的sheet对象  HSSFCell cell = null;//单元格HSSFRow row = null;//行HSSFCellStyle style = wb.createCellStyle();HSSFCellStyle style2 = wb.createCellStyle();HSSFCellStyle style3 = wb.createCellStyle();HSSFFont font = wb.createFont();font.setFontHeightInPoints((short) 20);//设置字体大小HSSFFont font2 = wb.createFont();font2.setFontHeightInPoints((short)10);//设置表格样式style.setAlignment(CellStyle.ALIGN_CENTER);style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);style.setBorderBottom(CellStyle.BORDER_THIN);style.setBorderLeft(CellStyle.BORDER_THIN);style.setBorderTop(CellStyle.BORDER_THIN);style.setBorderRight(CellStyle.BORDER_THIN);style.setFont(font);style2.setAlignment(CellStyle.ALIGN_CENTER);style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);style2.setBorderBottom(CellStyle.BORDER_THIN);style2.setBorderLeft(CellStyle.BORDER_THIN);style2.setBorderTop(CellStyle.BORDER_THIN);style2.setBorderRight(CellStyle.BORDER_THIN);style2.setFont(font2);style3.setAlignment(CellStyle.ALIGN_CENTER);style3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);style3.setAlignment(CellStyle.ALIGN_LEFT);style3.setFont(font2);//表格标题部分HSSFRow row0 = sheet.createRow(0);//标题行row0.setHeight((short) 550);cell = row0.createCell(0);cell.setCellValue("2012年4月份人员薪酬绩效发放表");sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));//合并单元格（第0行到第0行，第0列到第14列）cell.setCellStyle(style);//创建并渲染顶部表格样式for(int i=2;i<= 13;i++){row = sheet.createRow(i);for(int j= 0;j<=14;j++){cell = row.createCell(j);cell.setCellStyle(style2);}}row = sheet.getRow(2);cell = row.getCell(0);cell.setCellValue("序号");sheet.setColumnWidth(0, 35*30);cell = row.getCell(1);cell.setCellValue("绩效项目");for(int i=2;i<=11;i++){sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 3));}cell = row.getCell(4);cell.setCellValue("数据来源");sheet.setColumnWidth(4, 35*150);cell = row.getCell(5);cell.setCellValue("金额");sheet.setColumnWidth(5, 35*150);cell = row.getCell(6);cell.setCellValue("领导审批");sheet.setColumnWidth(6, 35*80);sheet.addMergedRegion(new CellRangeAddress(2, 9, 7, 14));sheet.addMergedRegion(new CellRangeAddress(2, 9, 6, 6));row = sheet.getRow(3);cell = row.getCell(0);cell.setCellValue("A");cell = row.getCell(1);cell.setCellValue("本年人员经费预算");row = sheet.getRow(4);cell = row.getCell(0);cell.setCellValue("B");cell = row.getCell(1);cell.setCellValue("本月人员经费预算");cell = row.getCell(4);cell.setCellValue("B=A/12");row = sheet.getRow(5);cell = row.getCell(0);cell.setCellValue("C");cell = row.getCell(1);cell.setCellValue("本月已发工资");row = sheet.getRow(6);cell = row.getCell(0);cell.setCellValue("D");cell = row.getCell(1);cell.setCellValue("本月已缴五险一金及其他款");row = sheet.getRow(7);cell = row.getCell(0);cell.setCellValue("E");cell = row.getCell(1);cell.setCellValue("本月绩效及福利预算");cell = row.getCell(4);cell.setCellValue("E=B-C-D");row = sheet.getRow(8);cell = row.getCell(0);cell.setCellValue("F");cell = row.getCell(1);cell.setCellValue("本月预发80%绩效及福利");cell = row.getCell(4);cell.setCellValue("F=E*80%");row = sheet.getRow(9);cell = row.getCell(0);cell.setCellValue("G");cell = row.getCell(1);cell.setCellValue("本月代扣个税及其他款项等");row = sheet.getRow(10);cell = row.getCell(0);cell.setCellValue("H");cell = row.getCell(1);cell.setCellValue("本月实发绩效及福利");cell = row.getCell(4);cell.setCellValue("H=F-G");cell = row.getCell(6);cell.setCellValue("复核:");sheet.addMergedRegion(new CellRangeAddress(10, 11, 7, 10));sheet.addMergedRegion(new CellRangeAddress(10, 11, 6, 6));cell = row.getCell(11);cell.setCellValue("制表:");sheet.addMergedRegion(new CellRangeAddress(10, 11, 12, 14));sheet.addMergedRegion(new CellRangeAddress(10, 11, 11, 11));row = sheet.getRow(11);cell = row.getCell(0);cell.setCellValue("H");cell = row.getCell(1);cell.setCellValue("本月预发绩效及福利未分配额");cell = row.getCell(4);cell.setCellValue("I=F-H");row = sheet.getRow(12);cell = row.getCell(0);cell.setCellValue("序号");sheet.addMergedRegion(new CellRangeAddress(12, 13, 0, 0));cell = row.getCell(1);cell.setCellValue("人员编号");sheet.addMergedRegion(new CellRangeAddress(12, 13, 1, 1));cell = row.getCell(2);cell.setCellValue("栏目/科室");sheet.addMergedRegion(new CellRangeAddress(12, 13, 2, 2));sheet.setColumnWidth(2, 35*100);cell = row.getCell(3);cell.setCellValue("姓名");sheet.addMergedRegion(new CellRangeAddress(12, 13, 3, 3));cell = row.getCell(4);cell.setCellValue("证件号");sheet.addMergedRegion(new CellRangeAddress(12, 13, 4, 4));cell = row.getCell(5);cell.setCellValue("银行帐号");sheet.addMergedRegion(new CellRangeAddress(12, 13, 5, 5));cell = row.getCell(6);cell.setCellValue("本月预发80%绩效及福利");sheet.addMergedRegion(new CellRangeAddress(12, 12, 6, 9));cell = row.getCell(10);cell.setCellValue("本月代扣款项");sheet.addMergedRegion(new CellRangeAddress(12, 12, 10, 12));cell = row.getCell(13);cell.setCellValue("本月实发");cell = row.getCell(14);cell.setCellValue("签名");sheet.addMergedRegion(new CellRangeAddress(12, 13, 14, 14));row = sheet.getRow(13);cell = row.getCell(6);cell.setCellValue("绩效");cell = row.getCell(7);cell.setCellValue("节日慰问");cell = row.getCell(8);cell.setCellValue("其他");cell = row.getCell(9);cell.setCellValue("小计");cell = row.getCell(10);cell.setCellValue("代扣个税");cell = row.getCell(11);cell.setCellValue("代扣水电");cell = row.getCell(12);cell.setCellValue("小计");cell = row.getCell(13);cell.setCellValue("绩效及福利");Integer rowNumber = 14;//创建并渲染表格内容样式for(int i=0;i<10+1;i++){row = sheet.createRow(rowNumber+i);for(int j= 0;j<=14;j++){cell = row.createCell(j);cell.setCellStyle(style2);}}//填充表格内容数据for(int i= 0;i<10;i++){row = sheet.getRow(rowNumber);cell = row.getCell(0);cell.setCellValue(rowNumber+1);cell = row.getCell(1);//人员编号cell.setCellValue("");cell = row.getCell(2);cell.setCellValue("房地产");cell = row.getCell(3);cell.setCellValue("user"+i);cell = row.getCell(4);cell.setCellValue("511011188525361234");cell = row.getCell(5);cell.setCellValue("6222222210254825556");cell = row.getCell(6);//绩效cell.setCellValue(66625.4);cell = row.getCell(10);//个税cell.setCellValue(560);cell = row.getCell(13);//本月实发cell.setCellValue(25456);rowNumber++;}row = sheet.getRow(rowNumber);cell = row.getCell(0);cell.setCellValue("总计");sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, 5));cell = row.getCell(6);cell.setCellValue(1250012);//奖金总额cell = row.getCell(10);cell.setCellValue(1250012);//个税总额cell = row.getCell(13);cell.setCellValue(12253);//实际发放rowNumber = rowNumber + 2;//渲染最后总结格式for(int i = 0;i<2;i++){row = sheet.createRow(rowNumber+i);for(int j = 5; j<10;j++){cell = row.createCell(j);cell.setCellStyle(style2);}}row = sheet.getRow(rowNumber++);cell = row.getCell(5);cell.setCellValue("平均奖金");cell = row.getCell(6);cell.setCellValue("总点数");cell = row.getCell(7);cell.setCellValue("奖金总数");cell = row.getCell(8);cell.setCellValue("代扣个税");cell = row.getCell(9);cell.setCellValue("实发奖金");row = sheet.getRow(rowNumber++);cell = row.getCell(5);cell.setCellValue("平均奖金");cell = row.getCell(6);cell.setCellValue("总点数");cell = row.getCell(7);cell.setCellValue("奖金总数");cell = row.getCell(8);cell.setCellValue("代扣个税");cell = row.getCell(9);cell.setCellValue("实发奖金");FileOutputStream fileOut = new FileOutputStream("E://bonus.xls");  wb.write(fileOut);//把Workbook对象输出到文件workbook.xls中  fileOut.close();  }}