easypoi导出EXCEL表格,WPS能打开,OFFICE打不开问题

根据项目需求,需要导出excel表格,选择使用easypoi插件，可是导出的表格,选择wps能打开,office打开报错.于是就被测试提了一个问题单，（呜呜呜。。。）现在把解决方案分享下。

引入相关依赖：
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
特别注意：

        个人原因是使用了HSSFWorkbook创建了后缀名为.xlsx的文件，而HSSFWorkbook只支持.xls的文件

解决方案：HSSFWorkbook换成XSSFWorkbook。

HSSF 提供读写旧版本Excel的功能，而XSSF提供读写新版本Excel格式档案的功能，新版本的兼容旧版的office。
 导出的格式 .xls对应 ExcelType.HSSF   .xlsx对应 ExcelType.XSSF  默认ExportParams.type= ExcelType.HSSF

代码部分截图，仅仅供参考，个人需要根据业务需求做相应改动，最好不要完全照搬过去。

        

 

 /**
     * 导出表格数据
     *
     * @param params
     * @return
     */
    public HttpServletResponse exportWorkPlanData(HttpServletResponse response,RmcpOpDayplanParam params) {
        ResponseResult<List<CommonWorkPlanExportVo>> result= iCommonWorkPlanApi.exportWorkPlanData(params);;
        if (null == result||null == result.getData()) {
            return null;
        }

        ExportParams paramse = new ExportParams();
        paramse.setSheetName("作业计划信息");
        paramse.setStyle(ExcelExportStylerImpl.class);
        //HSSF 提供读写旧版本Excel的功能，而XSSF提供读写新版本Excel格式档案的功能，新版本的兼容旧版的office。
        // 导出的格式 .xls对应 ExcelType.HSSF   .xlsx对应 ExcelType.XSSF  默认ExportParams.type= ExcelType.HSSF
        paramse.setType(ExcelType.XSSF);
        List<CommonWorkPlanExportVo> data = result.getData();
        //创建工作薄对象
        XSSFWorkbook workbook=new XSSFWorkbook();
        //创建工作表对象
//        HSSFSheet sheet=workbook.createSheet();
        XSSFSheet sheet=workbook.createSheet();

        //设置字体样式和边框
//        HSSFCellStyle style = workbook.createCellStyle();
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();

        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
//        HSSFCellStyle style2 = workbook.createCellStyle();
        XSSFCellStyle style2 = workbook.createCellStyle();
        XSSFFont font2 = workbook.createFont();

        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 11);
        font2.setColor(HSSFColor.GREEN.index);

        modalStyle(style, font);
        modalStyle(style2, font2);

        sheet.setColumnWidth(1, 17 * 256);
        sheet.setColumnWidth(2, 18 * 256);
        sheet.setColumnWidth(3, 11 * 256);
        sheet.setColumnWidth(4, 30 * 256);
        sheet.setColumnWidth(5, 15 * 256);
        sheet.setColumnWidth(6, 35 * 256);
        sheet.setColumnWidth(7, 15 * 256);
        sheet.setColumnWidth(8, 15 * 256);
        sheet.setColumnWidth(9, 15 * 256);
        sheet.setColumnWidth(10, 10 * 256);

        //创建工作表的行
        //设置第一行&#xff0c;从零开始
//        HSSFRow row = sheet.createRow(0);
        XSSFRow row = sheet.createRow(0);
        //表头单元格
//        HSSFCell cell = row.createCell(0,CellType.BLANK);
        XSSFCell cell = row.createCell(0,CellType.BLANK);
        row.setHeightInPoints(30);
        cell.setCellValue("序号");
        cell = row.createCell(1);
        cell.setCellValue("作业计划名称");
        cell = row.createCell(2);
        cell.setCellValue("作业单位");
        cell = row.createCell(3);
        cell.setCellValue("作业地区");
        cell = row.createCell(4);
        cell.setCellValue("实施单位");
        cell = row.createCell(5);
        cell.setCellValue("作业风险等级");
        cell = row.createCell(6);
        cell.setCellValue("作业内容");
        cell = row.createCell(7);
        cell.setCellValue("作业违章数");
        cell = row.createCell(8);
        cell.setCellValue("电网风险等级");
        cell = row.createCell(9);
        cell.setCellValue("督查情况");
        cell = row.createCell(10);
        cell.setCellValue("作业状态");
        for (int i=0;i<data.size();i++){
            CommonWorkPlanExportVo vo = data.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0, CellType.STRING).setCellValue(i+1);
            row.createCell(1, CellType.STRING).setCellValue(vo.getDayPlanName());
            row.createCell(2, CellType.STRING).setCellValue(vo.getWorkOrg());
            row.createCell(3, CellType.STRING).setCellValue(vo.getCity());
            row.createCell(4, CellType.STRING).setCellValue(vo.getBelongOrgName());
            row.createCell(5, CellType.STRING).setCellValue(WorkriskLevelEnums.getDescByValue(vo.getWorkriskLevel()));
            row.createCell(6, CellType.STRING).setCellValue(vo.getWorkContents());
            row.createCell(7, CellType.STRING).setCellValue(vo.getViolationNum());
            row.createCell(8, CellType.STRING).setCellValue(GridriskLevelEnums.getDescByValue(vo.getGridriskLevel()));
            // 督察情况：2005001-省，2005002-市，2005003-县
            String type = vo.getType();
            String newType = null;
            if(StringUtils.isNotEmpty(type)){
                newType = type.replace("2005001", "省").replace("2005002", "市").replace("2005003", "县")
                        .replace(",", "/").replace(" ", "");
                row.createCell(9, CellType.STRING).setCellValue(newType);
            }
            row.createCell(10, CellType.STRING).setCellValue(WorkStateEnums.getValueByKey(vo.getWorkState()));
            row.setHeightInPoints(20);
        }
        //设置单元格样式
        for (int n = 0; n < sheet.getPhysicalNumberOfRows(); n++) {
            for (int m = 0; m < sheet.getRow(0).getPhysicalNumberOfCells(); m++) {
//                HSSFRow originRow = sheet.getRow(n);
                XSSFRow originRow = sheet.getRow(n);
//                HSSFCell originCell = originRow.getCell(m);
                XSSFCell originCell = originRow.getCell(m);

                if (originCell == null) {
                    originCell = originRow.createCell(m);
                    originCell.setCellStyle(style);
                } else if (m == 9 && n >=1) {
                    originCell.setCellStyle(style2);
                } else {
                    originCell.setCellStyle(style);
                }
            }
        }

        //方法响应
        Calendar rightNow = Calendar.getInstance();
        Integer year = rightNow.get(Calendar.YEAR);
        Integer mouth = rightNow.get(Calendar.MONTH);
        Integer day = rightNow.get(Calendar.DAY_OF_MONTH);
        String fileName = "作业计划_" + year.toString() + "_" + mouth.toString() + "_" + day.toString();
        ServletOutputStream outputStream = null;
        try {

            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode("作业计划列表导出文件".concat("_").concat(DateUtil.current()),"UTF-8")+".xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Pargam", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setCharacterEncoding("UTF-8");
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                }
            }
        }
        return response;
    }

    private void modalStyle(XSSFCellStyle style, XSSFFont font) {
        style.setFont(font);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setFillBackgroundColor(IndexedColors.GREEN.index);
    }

   

如果还没有解决office打不开，wps能打开这个问题，也有可能是jar包冲突导致。比如，是fop依赖的xalan和poi冲突了。于是，排除xalan，问题解决。

最后祝福大家好好努力，说不定通过自己的拼搏，三四轮面试，有一天就能进大厂美团或者饿了么。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。送外卖。（哈哈哈）