对数据进行报表导出

在操作过程中,需要对数据进行统计分析和汇总,导出成excel格式

核心思想:将需要导出的数据以集合的形式查询出来,并填充到具体的excel表中。进行for循环填充,注意:不要在循环中查数据库

[java] view plain copy
  1. public void exportReportTeachers(String name,String sex,String username){  
  2.           
  3.         Teacher teacher=new Teacher();  
  4.         teacher.setName(name);  
  5.         teacher.setUsername(username);  
  6.         teacher.setSex(sex);  
  7.         List<Teacher> teachers = teacherDAO.getConditionList(teacher);  
  8.           
  9.         //生成一个xls文件  
  10.         HSSFWorkbook work =new HSSFWorkbook();  
  11.           
  12.         HSSFSheet sheet=work.createSheet("教师统计表");  
  13.         CellRangeAddress cellRangeAddress=new CellRangeAddress(000,5);  
  14.         sheet.addMergedRegion(cellRangeAddress);  
  15.         // 设置单元格样式  
  16.         HSSFCellStyle cellStyle1 = work.createCellStyle();// 创建表格样式  
  17.         // 设置字体居中  
  18.         cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
  19.         // 设置字体  
  20.         HSSFFont font1 = work.createFont();  
  21.         font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
  22.         font1.setColor(HSSFColor.BLACK.index);  
  23.         font1.setFontHeight((short250);  
  24.         font1.setFontName("宋体");  
  25.         font1.setBoldweight((short13);  
  26.         font1.setColor(HSSFColor.BLACK.index);  
  27.         cellStyle1.setFont(font1);  
  28.   
  29.         // 垂直居中  
  30.         cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
  31.         cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
  32.         // 加边框  
  33.         cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框  
  34.         cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框  
  35.         cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框  
  36.         cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框  
  37.         HSSFRow headRow = sheet.createRow(0);  
  38.         headRow.setHeight((short) (42 * 15.625));  
  39.         for (int i = 0; i <= 5; i++) {  
  40.             HSSFCell cell = headRow.createCell(i);  
  41.             cell.setCellValue("教师统计表");  
  42.             cell.setCellStyle(cellStyle1);  
  43.         }  
  44.   
  45.         // 设置单元格样式  
  46.         HSSFCellStyle cellStyle = work.createCellStyle();// 创建表格样式  
  47.         // 设置字体  
  48.         HSSFFont font = work.createFont();  
  49.         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
  50.         font.setColor(HSSFColor.BLACK.index);  
  51.         // font.setFontHeight((short)250);  
  52.         font.setFontName("宋体");  
  53.         font.setBoldweight((short10);  
  54.         font.setColor(HSSFColor.BLACK.index);  
  55.         cellStyle.setFont(font);  
  56.         // 设置背景色  
  57.         cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
  58.         cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);  
  59.         cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);  
  60.         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
  61.         // 加边框  
  62.         cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框  
  63.         cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框  
  64.         cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框  
  65.         cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框  
  66.         // 设置垂直居中  
  67.         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
  68.         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
  69.         HSSFRow row = sheet.createRow(1);  
  70.   
  71.         // 创建单元格  
  72.         HSSFCell cell = row.createCell(0);  
  73.         cell.setCellValue("序号");  
  74.         cell.setCellStyle(cellStyle);  
  75.           
  76.         HSSFCell cell1 = row.createCell(1);  
  77.         cell1.setCellValue("教师Id");  
  78.         cell1.setCellStyle(cellStyle);  
  79.           
  80.         HSSFCell cell2 = row.createCell(2);  
  81.         cell2.setCellValue("教师姓名");  
  82.         cell2.setCellStyle(cellStyle);  
  83.           
  84.         HSSFCell cell3 = row.createCell(3);  
  85.         cell3.setCellValue("登录用户名");  
  86.         cell3.setCellStyle(cellStyle);  
  87.           
  88.         HSSFCell cell4 = row.createCell(4);  
  89.         cell4.setCellValue("性别");  
  90.         cell4.setCellStyle(cellStyle);  
  91.           
  92.         HSSFCell cell5 = row.createCell(5);  
  93.         cell5.setCellValue("所任课程");  
  94.         cell5.setCellStyle(cellStyle);  
  95.           
  96.         // 设置单元格样式  
  97.         HSSFCellStyle valueCellStyle = work.createCellStyle();// 创建表格样式  
  98.         HSSFFont valueFont = work.createFont();  
  99.         valueFont.setColor(HSSFColor.BLACK.index);  
  100.         valueFont.setFontName("宋体");  
  101.         valueCellStyle.setFont(valueFont);  
  102.         // 设置自动换行  
  103.         // valueCellStyle.setWrapText(true);  
  104.   
  105.         // 垂直居中  
  106.         valueCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
  107.         valueCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
  108.         // 加边框  
  109.         valueCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框  
  110.         valueCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框  
  111.         valueCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框  
  112.         valueCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框  
  113.   
  114.         int rowNum = 2;  
  115.         for (int j = 0; j < teachers.size(); j++) {  
  116.             // 一个教师对应多门课程  
  117.           
  118.             HSSFRow valueRow = sheet.createRow(rowNum);// 前面已经写两行了  
  119.               
  120.             HSSFCell valueCell = valueRow.createCell(0);  
  121.             valueCell.setCellValue(j + 1);// 设置序号  
  122.             valueCell.setCellStyle(valueCellStyle);  
  123.               
  124.             HSSFCell valueCell1 = valueRow.createCell(1);  
  125.             valueCell1.setCellValue(teachers.get(j).getId());// 教师id  
  126.             valueCell1.setCellStyle(valueCellStyle);  
  127.               
  128.             HSSFCell valueCell2 = valueRow.createCell(2);  
  129.             valueCell2.setCellValue(teachers.get(j).getName());// 教师姓名  
  130.             valueCell2.setCellStyle(valueCellStyle);  
  131.               
  132.             HSSFCell valueCell3 = valueRow.createCell(3);  
  133.             valueCell3.setCellValue(teachers.get(j).getUsername());// 教师用户名  
  134.             valueCell3.setCellStyle(valueCellStyle);  
  135.               
  136.             HSSFCell valueCell4 = valueRow.createCell(4);  
  137.             valueCell4.setCellValue(teachers.get(j).getSexStr());//性别  
  138.             valueCell4.setCellStyle(valueCellStyle);  
  139.               
  140.             HSSFCell valueCell5 = valueRow.createCell(5);  
  141.             valueCell5.setCellValue(teachers.get(j).getCourseNames());//课程名称  
  142.             valueCell5.setCellStyle(valueCellStyle);  
  143.   
  144.             //行号,起始列号 ,行号 ,终止列号           
  145.             rowNum++;  
  146.         }  
  147.         // =================指定列宽======================  
  148.         sheet.setColumnWidth(05 * 256);// 设置列宽  
  149.         sheet.setColumnWidth(140 * 256);// 设置列宽  
  150.         sheet.setColumnWidth(213 * 256);// 设置列宽  
  151.         sheet.setColumnWidth(325 * 256);// 设置列宽  
  152.         sheet.setColumnWidth(48 * 256);// 设置列宽  
  153.         sheet.setColumnWidth(530 * 256);// 设置列宽  
  154.       
  155.         // 设置页边距  
  156.         sheet.setMargin(HSSFSheet.BottomMargin, 0);  
  157.         sheet.setMargin(HSSFSheet.LeftMargin, (double0.4);  
  158.         sheet.setMargin(HSSFSheet.RightMargin, (double0.8);  
  159.         sheet.setMargin(HSSFSheet.TopMargin, 0);  
  160.         /* 
  161.          * //设置打印为横板 HSSFPrintSetup ps = sheet.getPrintSetup(); //true为横向 
  162.          * false为纵向 ps.setLandscape(true); 
  163.          */  
  164.         File dir = new File(AttachmentUtil.getTempDir());  
  165.         if (!dir.exists()) {  
  166.             dir.mkdirs();  
  167.         }  
  168.         try {  
  169.             String fileName = "教师统计表";  
  170.             File file = new File(dir.getAbsolutePath() + "/" + UUID.getUUID());  
  171.             OutputStream out = new FileOutputStream(file);  
  172.             work.write(out);  
  173.             FileUtil.download(file, fileName + ".xls");  
  174.         } catch (Exception e) {  
  175.             new BusinessException("文件传输出错");  
  176.             e.printStackTrace();  
  177.         }  
  178.     } 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值