考试管理系统-POI操作Excel文件

---

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;
}

（3）修改后台servlet的downloadReport方法

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();
}