下面的代码展示了利用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();
}
}
扫一扫
2560
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
