对数据进行报表导出

在操作过程中，需要对数据进行统计分析和汇总，导出成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(0, 0, 0,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((short) 250);  
24.         font1.setFontName("宋体");  
25.         font1.setBoldweight((short) 13);  
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((short) 10);  
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(0, 5 * 256);// 设置列宽  
149.         sheet.setColumnWidth(1, 40 * 256);// 设置列宽  
150.         sheet.setColumnWidth(2, 13 * 256);// 设置列宽  
151.         sheet.setColumnWidth(3, 25 * 256);// 设置列宽  
152.         sheet.setColumnWidth(4, 8 * 256);// 设置列宽  
153.         sheet.setColumnWidth(5, 30 * 256);// 设置列宽  
154.       
155.         // 设置页边距  
156.         sheet.setMargin(HSSFSheet.BottomMargin, 0);  
157.         sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.4);  
158.         sheet.setMargin(HSSFSheet.RightMargin, (double) 0.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.     }