1. 报表
报表：简单的说，报表就是用表格、图表等格式来动态显示数据，可以用公式表示为：“报表 = 多样的格式 + 动态的数据”。
报表的种类有很多：Excel报表，PDF报表，网页报表等，他们各有优缺点
在本课程中，我们主要来将Excel报表。
对于Excel报表的技术实现上也有很多种选择：
- JXL：支持xls文件操作
- POI：支持xls和xlsx文件操作
我们只要来讲POI技术，要使用POI就要导入其坐标，如下
<!--POI--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>4.0.1</version> </dependency>
1.1 POI写Excel文件
在测试包下创建POI测试类：com.itheima.service.store.PoiTest
public class PoiTest { @Test public void testWriteByPoi() throws IOException { //1.获取到对应的Excel文件，工作簿文件 Workbook wb = new XSSFWorkbook(); //2.创建工作表 Sheet sheet = wb.createSheet(); wb.createSheet("这是啥呀"); //3.创建工作表中的行对象 Row row = sheet.createRow(1); //4.创建工作表中行中的列对象 Cell cell = row.createCell(1); //5.在列中写数据 cell.setCellValue("测试一下单元格"); //创建一个文件对象，作为excel文件内容的输出文件 File f = new File("test.xlsx"); //输出时通过流的形式对外输出，包装对应的目标文件 OutputStream os = new FileOutputStream(f); //将内存中的workbook数据写入到流中 wb.write(os); wb.close(); os.close(); } }
使用单元测试进行测试！
1.2 POI读Excel文件
创建读Excel的测试方法：testReadByPoi
@Test public void testReadByPoi() throws IOException { //1.获取要读取的文件工作簿对象 Workbook wb = new XSSFWorkbook("test.xlsx"); //2.获取工作表 Sheet s = wb.getSheetAt(0); //3.获取行 Row row = s.getRow(3); //4.获取列 Cell cell = row.getCell(1); //5.根据数据的类型获取数据 // String data = cell.getStringCellValue(); // double data = cell.getNumericCellValue(); boolean data = cell.getBooleanCellValue(); System.out.println(data); wb.close(); }
直接读取第一节创建好的Excel文件
1.3 题目模板表头制作
前两节我们讲了如何去读取及写入Excel数据，操作相对简单，但是实际业务中我们要操作的Excel报表还是比较繁琐的，我们可以从今日课程资料中找到我们最终要导出报表的模板：资料\Excel解析\模板.xlsx
这种形式的我们如何去操作呢？
在测试类中再编写一个测试方法：testProjectPoi
@Test public void testProjectPoi() throws IOException { //1.获取到对应的Excel文件，工作簿文件 Workbook wb = new XSSFWorkbook(); //2.创建工作表 Sheet s = wb.createSheet("题目数据文件"); //制作标题 s.addMergedRegion(new CellRangeAddress(1,1,1,12)); Row row_1 = s.createRow(1); Cell cell_1_1 = row_1.createCell(1); cell_1_1.setCellValue("在线试题导出信息"); //创建一个样式 CellStyle cs_title = wb.createCellStyle(); cs_title.setAlignment(HorizontalAlignment.CENTER); cs_title.setVerticalAlignment(VerticalAlignment.CENTER); cell_1_1.setCellStyle(cs_title); //制作表头 //制作数据区 //创建一个文件对象，作为excel文件内容的输出文件 File f = new File("test.xlsx"); //输出时通过流的形式对外输出，包装对应的目标文件 OutputStream os = new FileOutputStream(f); //将内存中的workbook数据写入到流中 wb.write(os); wb.close(); os.close(); }
1.4 题目模板标题制作
下面我们接着来做Excel的表头
在测试方法testProjectPoi中继续编写代码
@Test public void testProjectPoi() throws IOException { //1.获取到对应的Excel文件，工作簿文件 Workbook wb = new XSSFWorkbook(); //2.创建工作表 Sheet s = wb.createSheet("题目数据文件"); //设置通用配置 // s.setColumnWidth(4,100); //制作标题 s.addMergedRegion(new CellRangeAddress(1,1,1,12)); Row row_1 = s.createRow(1); Cell cell_1_1 = row_1.createCell(1); cell_1_1.setCellValue("在线试题导出信息"); //创建一个样式 CellStyle cs_title = wb.createCellStyle(); cs_title.setAlignment(HorizontalAlignment.CENTER); cs_title.setVerticalAlignment(VerticalAlignment.CENTER); cell_1_1.setCellStyle(cs_title); //制作表头 String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述", "题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"}; Row row_2 = s.createRow(2); for (int i = 0; i < fields.length; i++) { Cell cell_2_temp = row_2.createCell(1 + i); //++ cell_2_temp.setCellValue(fields[i]); //++ CellStyle cs_field = wb.createCellStyle(); cs_field.setAlignment(HorizontalAlignment.CENTER); cell_2_temp.setCellStyle(cs_field); } //制作数据区 //创建一个文件对象，作为excel文件内容的输出文件 File f = new File("test.xlsx"); //输出时通过流的形式对外输出，包装对应的目标文件 OutputStream os = new FileOutputStream(f); //将内存中的workbook数据写入到流中 wb.write(os); wb.close(); os.close(); }
1.5 题目模板数据制作
我们继续来做数据区
@Test public void testProjectPoi() throws IOException { //1.获取到对应的Excel文件，工作簿文件 Workbook wb = new XSSFWorkbook(); //2.创建工作表 Sheet s = wb.createSheet("题目数据文件"); //设置通用配置 // s.setColumnWidth(4,100); CellStyle cs_field = wb.createCellStyle(); cs_field.setAlignment(HorizontalAlignment.CENTER); cs_field.setBorderTop(BorderStyle.THIN); cs_field.setBorderBottom(BorderStyle.THIN); cs_field.setBorderLeft(BorderStyle.THIN); cs_field.setBorderRight(BorderStyle.THIN); //制作标题 s.addMergedRegion(new CellRangeAddress(1,1,1,12)); Row row_1 = s.createRow(1); Cell cell_1_1 = row_1.createCell(1); cell_1_1.setCellValue("在线试题导出信息"); //创建一个样式 CellStyle cs_title = wb.createCellStyle(); cs_title.setAlignment(HorizontalAlignment.CENTER); cs_title.setVerticalAlignment(VerticalAlignment.CENTER); cell_1_1.setCellStyle(cs_title); //制作表头 String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述", "题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"}; Row row_2 = s.createRow(2); for (int i = 0; i < fields.length; i++) { Cell cell_2_temp = row_2.createCell(1 + i); //++ cell_2_temp.setCellValue(fields[i]); //++ cell_2_temp.setCellStyle(cs_field); } //制作数据区 List<Question> questionList = new ArrayList<>(); Question qq = new Question(); qq.setId("1"); qq.setPicture("12"); qq.setReviewStatus("13"); qq.setAnalysis("14"); qq.setCatalogId("15"); qq.setCompanyId("16"); qq.setDifficulty("17"); qq.setIsClassic("18"); qq.setRemark("19"); qq.setState("21"); qq.setSubject("31"); qq.setType("41"); questionList.add(qq); Question qqq = new Question(); qqq.setId("1"); qqq.setPicture("12"); qqq.setReviewStatus("13"); qqq.setAnalysis("14"); qqq.setCatalogId("15"); qqq.setCompanyId("16"); qqq.setDifficulty("17"); qqq.setIsClassic("18"); qqq.setRemark("19"); qqq.setState("21"); qqq.setSubject("31"); qqq.setType("41"); questionList.add(qqq); int row_index = 0; for (Question q : questionList) { int cell_index = 0; Row row_temp = s.createRow(3 + row_index++); Cell cell_data_1 = row_temp.createCell(1 + cell_index++); cell_data_1.setCellValue(q.getId()); //++ cell_data_1.setCellStyle(cs_field); Cell cell_data_2 = row_temp.createCell(1 + cell_index++); cell_data_2.setCellValue(q.getCompanyId()); //++ cell_data_2.setCellStyle(cs_field); Cell cell_data_3 = row_temp.createCell(1 + cell_index++); cell_data_3.setCellValue(q.getCatalogId()); //++ cell_data_3.setCellStyle(cs_field); Cell cell_data_4 = row_temp.createCell(1 + cell_index++); cell_data_4.setCellValue(q.getRemark()); //++ cell_data_4.setCellStyle(cs_field); Cell cell_data_5 = row_temp.createCell(1 + cell_index++); cell_data_5.setCellValue(q.getSubject()); //++ cell_data_5.setCellStyle(cs_field); Cell cell_data_6 = row_temp.createCell(1 + cell_index++); cell_data_6.setCellValue(q.getPicture()); //++ cell_data_6.setCellStyle(cs_field); Cell cell_data_7 = row_temp.createCell(1 + cell_index++); cell_data_7.setCellValue(q.getAnalysis()); //++ cell_data_7.setCellStyle(cs_field); Cell cell_data_8 = row_temp.createCell(1 + cell_index++); cell_data_8.setCellValue(q.getType()); //++ cell_data_8.setCellStyle(cs_field); Cell cell_data_9 = row_temp.createCell(1 + cell_index++); cell_data_9.setCellValue(q.getDifficulty()); //++ cell_data_9.setCellStyle(cs_field); Cell cell_data_10 = row_temp.createCell(1 + cell_index++); cell_data_10.setCellValue(q.getIsClassic()); //++ cell_data_10.setCellStyle(cs_field); Cell cell_data_11 = row_temp.createCell(1 + cell_index++); cell_data_11.setCellValue(q.getState()); //++ cell_data_11.setCellStyle(cs_field); Cell cell_data_12 = row_temp.createCell(1 + cell_index++); cell_data_12.setCellValue(q.getReviewStatus()); //++ cell_data_12.setCellStyle(cs_field); } --------------------------------------------------------------- for (int i = 0; i < questionList.size(); i++) { Question question = questionList.get(i); XSSFRow row = s.createRow(i + 3); XSSFCell cell1 = row.createCell(1); cell1.setCellValue(question.getId()); XSSFCell cell2 = row.createCell(2); cell2.setCellValue(question.getCompanyId()); XSSFCell cell3 = row.createCell(3); cell3.setCellValue(question.getAnalysis()); } 这个方法更好些！ ---------------------------------------------------------------- //创建一个文件对象，作为excel文件内容的输出文件 File f = new File("test.xlsx"); //输出时通过流的形式对外输出，包装对应的目标文件 OutputStream os = new FileOutputStream(f); //将内存中的workbook数据写入到流中 wb.write(os); wb.close(); os.close(); }
测试即可！
1.6 题目报表数据准备
（1）找到/WEB-INF/pages/store/question/list.jsp页面，修改导出题目的链接
<button type="button" class="btn btn-default" title="导出题目" οnclick=location.href="${ctx}/store/question?operation=downloadReport"> <i class="fa fa-download"></i>导出题目</button> 12
（2）在后台servlet中添加对应的方法
// uri:/store/question?operation=list @WebServlet("/store/question") public class QuestionServlet extends BaseServlet { @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String operation = request.getParameter("operation"); if("list".equals(operation)){ this.list(request,response); } //其他的else if判断省略 else if("downloadReport".equals(operation)){ this.downloadReport(request,response); } } private void downloadReport(HttpServletRequest request, HttpServletResponse response) throws IOException { //生成报告的文件，然后传递到前端页面 questionService.getReport(); } }
（3）在业务层QuestionService添加一个方法getReport
public void getReport() throws IOException; 12
（4）在对应的实现类中去实现该方法，把之前在测试类中的测试方法testProjectPoi里面的所有代码拷贝过来，其中数据我们应该是从数据库中查询出来，因此调用dao完成数据的查询
@Override public void getReport() throws IOException{ //获取对应要展示的数据 SqlSession sqlSession = null; List<Question> questionList = null; try{ //1.获取SqlSession sqlSession = MapperFactory.getSqlSession(); //2.获取Dao QuestionDao questionDao = MapperFactory.getMapper(sqlSession,QuestionDao.class); //3.调用Dao层操作 questionList = questionDao.findAll(); }catch (Exception e){ throw new RuntimeException(e); //记录日志 }finally { try { TransactionUtil.close(sqlSession); }catch (Exception e){ e.printStackTrace(); } } //1.获取到对应的Excel文件，工作簿文件 Workbook wb = new XSSFWorkbook(); //2.创建工作表 Sheet s = wb.createSheet("题目数据文件"); //设置通用配置 // s.setColumnWidth(4,100); CellStyle cs_field = wb.createCellStyle(); cs_field.setAlignment(HorizontalAlignment.CENTER); cs_field.setBorderTop(BorderStyle.THIN); cs_field.setBorderBottom(BorderStyle.THIN); cs_field.setBorderLeft(BorderStyle.THIN); cs_field.setBorderRight(BorderStyle.THIN); //制作标题 s.addMergedRegion(new CellRangeAddress(1,1,1,12)); Row row_1 = s.createRow(1); Cell cell_1_1 = row_1.createCell(1); cell_1_1.setCellValue("在线试题导出信息"); //创建一个样式 CellStyle cs_title = wb.createCellStyle(); cs_title.setAlignment(HorizontalAlignment.CENTER); cs_title.setVerticalAlignment(VerticalAlignment.CENTER); cell_1_1.setCellStyle(cs_title); //制作表头 String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述", "题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"}; Row row_2 = s.createRow(2); for (int i = 0; i < fields.length; i++) { Cell cell_2_temp = row_2.createCell(1 + i); //++ cell_2_temp.setCellValue(fields[i]); //++ cell_2_temp.setCellStyle(cs_field); } //制作数据区 int row_index = 0; for (Question q : questionList) { int cell_index = 0; Row row_temp = s.createRow(3 + row_index++); Cell cell_data_1 = row_temp.createCell(1 + cell_index++); cell_data_1.setCellValue(q.getId()); //++ cell_data_1.setCellStyle(cs_field); Cell cell_data_2 = row_temp.createCell(1 + cell_index++); cell_data_2.setCellValue(q.getCompanyId()); //++ cell_data_2.setCellStyle(cs_field); Cell cell_data_3 = row_temp.createCell(1 + cell_index++); cell_data_3.setCellValue(q.getCatalogId()); //++ cell_data_3.setCellStyle(cs_field); Cell cell_data_4 = row_temp.createCell(1 + cell_index++); cell_data_4.setCellValue(q.getRemark()); //++ cell_data_4.setCellStyle(cs_field); Cell cell_data_5 = row_temp.createCell(1 + cell_index++); cell_data_5.setCellValue(q.getSubject()); //++ cell_data_5.setCellStyle(cs_field); Cell cell_data_6 = row_temp.createCell(1 + cell_index++); cell_data_6.setCellValue(q.getPicture()); //++ cell_data_6.setCellStyle(cs_field); Cell cell_data_7 = row_temp.createCell(1 + cell_index++); cell_data_7.setCellValue(q.getAnalysis()); //++ cell_data_7.setCellStyle(cs_field); Cell cell_data_8 = row_temp.createCell(1 + cell_index++); cell_data_8.setCellValue(q.getType()); //++ cell_data_8.setCellStyle(cs_field); Cell cell_data_9 = row_temp.createCell(1 + cell_index++); cell_data_9.setCellValue(q.getDifficulty()); //++ cell_data_9.setCellStyle(cs_field); Cell cell_data_10 = row_temp.createCell(1 + cell_index++); cell_data_10.setCellValue(q.getIsClassic()); //++ cell_data_10.setCellStyle(cs_field); Cell cell_data_11 = row_temp.createCell(1 + cell_index++); cell_data_11.setCellValue(q.getState()); //++ cell_data_11.setCellStyle(cs_field); Cell cell_data_12 = row_temp.createCell(1 + cell_index++); cell_data_12.setCellValue(q.getReviewStatus()); //++ cell_data_12.setCellStyle(cs_field); } //创建一个文件对象，作为excel文件内容的输出文件 File f = new File("test.xlsx"); //输出时通过流的形式对外输出，包装对应的目标文件 OutputStream os = new FileOutputStream(f); //将内存中的workbook数据写入到流中 wb.write(os); wb.close(); os.close(); }
1.7 题目报表业务实现
现在后台已经能够生成Excel文件并且填充了数据，但是真实的业务中我们是需要将这个文件下载到客户端
（1）修改接口方法getReport，添加返回值
/** * 获取包含了数据的流对象 * @return 包含了报表数据的流对象 * @throws IOException */ ByteArrayOutputStream getReport() throws IOException; 123456
（2）在实现类中实现该方法时，将内存中的Excel相关数据写入到ByteArrayOutputStream流中
@Override public ByteArrayOutputStream getReport() throws IOException { //前面的代码无变动 故省略 /** //创建一个文件对象，作为excel文件内容的输出文件 File f = new File("test.xlsx"); //输出时通过流的形式对外输出，包装对应的目标文件 OutputStream os = new FileOutputStream(f); //将内存中的workbook数据写入到流中 wb.write(os); wb.close(); os.close(); */ //将内存中的workbook数据写入到流中 ByteArrayOutputStream os = new ByteArrayOutputStream(); wb.write(os); wb.close(); return os; }
private void downloadReport(HttpServletRequest request, HttpServletResponse response) throws IOException { //返回的数据类型为文件xlsx类型 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); String fileName = new String("测试文件名.xlsx".getBytes(),"iso8859-1"); response.addHeader("Content-Disposition","attachment;fileName="+fileName); //生成报告的文件，然后传递到前端页面 ByteArrayOutputStream os = questionService.getReport(); //获取产生响应的流对象 ServletOutputStream sos = response.getOutputStream(); //将数据从原始的字节流对象中提取出来写入到servlet对应的输出流中 os.writeTo(sos); //将输出流刷新 sos.flush(); os.close(); }
