问题

当我们通过POI导出Excel文件后，发现文件出错打不开，如下所示

原因

这是因为我们代码中   这三个的应用应该和我们的office表格的版本有关系。

    HSSFWorkBook：适用于Excel03版本的，文件扩展名只能为.xls；
    XSSFWorkBook：适用于Excel07版本及以后，文件扩展名为.xlsx或者.xls这两个都行；
    SXSSFWorkBook：XSSFWorkBook升级；

所以 ，我们可以通过查看我们电脑的版本来选择，当我们office版本为03时，我们就用HSSFWorkBook创建，
当我们的Office版本为07以后的话我们就用XSSFWorkBook来创建对象。

• 2003（在导出时均需使用HSSF相关方法（后缀名仅可为.xls, 如果设置为".xlsx"，会提示文件已损坏无法打开））

@Override
    public Workbook exportExcel() {
        // 创建新的Excel 工作簿
        Workbook workbook = new  HSSFWorkBook();

        // 在Excel工作簿中建一工作表，其名为缺省值 Sheet0
        //Sheet sheet = workbook.createSheet();

        // 创建工作表
        Sheet sheet = workbook.createSheet("题库");

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("题目标题");
        row.createCell(1).setCellValue("题目解答");
        row.createCell(2).setCellValue("题目难度等级");
        row.createCell(3).setCellValue("排序");
        row.createCell(4).setCellValue("副标题");
        row.createCell(5).setCellValue("题目类型");
        row.createCell(6).setCellValue("是否显示");
        List<QuestionEntity> list = this.list();
        for (int i = 0; i < list.size(); i++) {
            Row row2 = sheet.createRow(i + 1);
            //创建表格
            row2.createCell(0).setCellValue(list.get(i).getTitle());
            row2.createCell(1).setCellValue(list.get(i).getAnswer());
            row2.createCell(2).setCellValue(list.get(i).getLevel());
            row2.createCell(3).setCellValue(list.get(i).getDisplayOrder());
            row2.createCell(4).setCellValue(list.get(i).getSubTitle());
            row2.createCell(5).setCellValue(list.get(i).getType());
            row2.createCell(6).setCellValue(list.get(i).getEnable());

        }

        return workbook;
    }

 //导出excel
    @GetMapping("exportExcel")
    public void export(String tableName, HttpServletResponse response){
        System.out.println("导出excele");
        Workbook workbook = questionService.exportExcel();

        if (workbook != null) {
            String fileName = "uxue_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx";
//            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName  );
//            response.setContentType("application/octet-stream;charset=GB2312");
            response.setContentType("application/binary;charset=ISO8859_1");
            response.setCharacterEncoding("GB2312");
            OutputStream outputStream;
            try {
                outputStream = response.getOutputStream();
                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            try {
                response.getWriter().print("error");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


    }

• 2007（在导出时使用XSSF相关方法（后缀名为.xls和.xlsx均可））

@Override
    public Workbook exportExcel() {
        // 创建新的Excel 工作簿
        Workbook workbook = new  XSSFWorkBook();

        // 在Excel工作簿中建一工作表，其名为缺省值 Sheet0
        //Sheet sheet = workbook.createSheet();

        // 创建工作表
        Sheet sheet = workbook.createSheet("题库");

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("题目标题");
        row.createCell(1).setCellValue("题目解答");
        row.createCell(2).setCellValue("题目难度等级");
        row.createCell(3).setCellValue("排序");
        row.createCell(4).setCellValue("副标题");
        row.createCell(5).setCellValue("题目类型");
        row.createCell(6).setCellValue("是否显示");
        List<QuestionEntity> list = this.list();
        for (int i = 0; i < list.size(); i++) {
            Row row2 = sheet.createRow(i + 1);
            //创建表格
            row2.createCell(0).setCellValue(list.get(i).getTitle());
            row2.createCell(1).setCellValue(list.get(i).getAnswer());
            row2.createCell(2).setCellValue(list.get(i).getLevel());
            row2.createCell(3).setCellValue(list.get(i).getDisplayOrder());
            row2.createCell(4).setCellValue(list.get(i).getSubTitle());
            row2.createCell(5).setCellValue(list.get(i).getType());
            row2.createCell(6).setCellValue(list.get(i).getEnable());

        }

        return workbook;
    }
 

 //导出excel
    @GetMapping("exportExcel")
    public void export(String tableName, HttpServletResponse response){
        System.out.println("导出excele");
        Workbook workbook = questionService.exportExcel();

        if (workbook != null) {
            String fileName = "uxue_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx";
//这里我们的.xls或者xlsx都可以
//            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName  );
//            response.setContentType("application/octet-stream;charset=GB2312");
            response.setContentType("application/binary;charset=ISO8859_1");
            response.setCharacterEncoding("GB2312");
            OutputStream outputStream;
            try {
                outputStream = response.getOutputStream();
                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            try {
                response.getWriter().print("error");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


    }

就这样我们的问题成功解决了！！！！