public static SXSSFWorkbook createExportInterviewExcel(List<Operator> data, String sheetName) {

        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100);

        //标题栏设置

        CellStyle style = sxssfWorkbook.createCellStyle();

        style.setWrapText(true);//自动换行

        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中

        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居左

        Font font = sxssfWorkbook.createFont();

        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //字体增粗

        style.setFont(font);

        DataFormat formats = sxssfWorkbook.createDataFormat();

        style.setDataFormat(formats.getFormat("@"));//设置文本格式

        //正常格式设置

        CellStyle cellStyle = sxssfWorkbook.createCellStyle();

        //cellStyle.setWrapText(true);//自动换行

        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中

        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居左

        DataFormat format = sxssfWorkbook.createDataFormat();

        cellStyle.setDataFormat(format.getFormat("@"));//设置文本格式

        Sheet taskInfoSheet = sxssfWorkbook.createSheet(sheetName);

        DataValidationHelper helper = taskInfoSheet.getDataValidationHelper();//设置下拉框xlsx格式

//设置列宽

        taskInfoSheet.setColumnWidth(5, 256 * 21);

        taskInfoSheet.setColumnWidth(6, 256 * 15);

        taskInfoSheet.setColumnWidth(7, 256 * 15);

        taskInfoSheet.setColumnWidth(8, 256 * 20);

        taskInfoSheet.setColumnWidth(18, 256 * 20);

        // 第一行标题

        Row row_tital = taskInfoSheet.createRow(0);

        String[] keyWord = {"姓名","性别","民族", "年龄", "学历", "身份证号", "手机号",

                "在我司任职过", "岗位名", "广告名", "部门","工作地", "推荐人","推荐人电话","推荐人部门","推荐人工号", "面试安排时间","是否录用", "淘汰类型", "淘汰原因"};

        for (int i = 0; i < keyWord.length; i++) {

            Cell cell_tital_index = row_tital.createCell(i);

            cell_tital_index.setCellValue(keyWord[i]);

            cell_tital_index.setCellStyle(style);

        }

        //异常情况处理

        if (CollectionUtils.isEmpty(data) ||(CollectionUtils.isNotEmpty(data) && StringUtils.isBlank(data.get(0).getName()))) {

            return sxssfWorkbook;

        }

        // 数据行

        for (int i = 0; i < data.size(); i++) {  // 导出详情

            Row row_data = taskInfoSheet.createRow(i + 1);

            Cell cell_data_name = row_data.createCell(0);

            cell_data_name.setCellValue(data.get(i).getName());

            cell_data_name.setCellStyle(cellStyle);

            Cell cell_data_nation = row_data.createCell(1);

            cell_data_nation.setCellValue(data.get(i).getNational());

            cell_data_nation.setCellStyle(cellStyle);

            Cell cell_data_age = row_data.createCell(2);

            Integer age= null;

            if(data.get(i).getAge()!= null){

                 age = data.get(i).getAge();

            }else{

                age=1;

            }

            cell_data_age.setCellValue(age);

            cell_data_age.setCellStyle(cellStyle);

            //添加工作地

            Cell cell_data_location =row_data.createCell(3);

            cell_data_location.setCellValue(data.get(i).getLocation());

            cell_data_location.setCellStyle(cellStyle);

            Cell cell_data_referrer = row_data.createCell(4);

            cell_data_referrer.setCellValue(data.get(i).getReferrer());

            cell_data_referrer.setCellStyle(cellStyle);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            String interviewTime = "";

            if (null != data.get(i).getInterviewTime()) {

                interviewTime = sdf.format(data.get(i).getInterviewTime());

            }

            Cell cell_data_interviewTime = row_data.createCell(5);

            cell_data_interviewTime.setCellValue(interviewTime);

            cell_data_interviewTime.setCellStyle(cellStyle);

            String[] yesOrNo = {"是", "否"};

            creatDropDownList(taskInfoSheet,helper,yesOrNo,1,200,6,6);

            String[] list = {"不符合返聘要求", "不符合公司规则", "综合素质", "其他"};

            creatDropDownList(taskInfoSheet, helper, list, 1, 200, 7, 7);

        }

        return sxssfWorkbook;

    }

    //创建下拉框

    private static void creatDropDownList(Sheet taskInfoSheet, DataValidationHelper helper, String[] list,

                                     Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

        //设置下拉框数据

        DataValidationConstraint constraint = helper.createExplicitListConstraint(list);

        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        //处理Excel兼容性问题

        if (dataValidation instanceof XSSFDataValidation) {

            dataValidation.setSuppressDropDownArrow(true);

            dataValidation.setShowErrorBox(true);

        } else {

            dataValidation.setSuppressDropDownArrow(false);

        }

        taskInfoSheet.addValidationData(dataValidation);

    }

//需求：导入excel表格，如果excel有错误，将错误的地方标红，在把数据以excel的形式写出，供用户下载

//解决方案：1.以实体类的方式接收excel并解析（创建两个集合一个接收正常的数据一个接收错误的数据）

//2.错误集合无论正确错误数据都要存储，并记录是否有误（错误数据拼接特殊字符作为标记，然后记录写入集合）

//3.如果发现记录有错误记录，就要使用错误数据集合生成excel（对错误的数据对特殊字符截取）

//4.将错误的excel生成到工程的相对的路径下（下次下载前要记得清理记录），返回前端一个地址，前端点击链接进行下载

//这个方法就是清除之前的文件夹

 private String getExportResult(SXSSFWorkbook sxssfWorkbook) {

        //删除之前文件夹下的文件

        String targetUrl = System.getProperty("export.error.result");

        File targetFile = new File(targetUrl);

        ContentUtil.deletAllFiles(targetFile);

        String fileName = null;

        try {

            fileName = System.getProperty("export.error.result") + UUID.randomUUID().toString() + ".xlsx";

            File file = new File(fileName);

            if (!file.exists()) {

                file.getParentFile().mkdirs();

            }

            FileOutputStream outputStream = new FileOutputStream(fileName);

            sxssfWorkbook.write(outputStream);

            outputStream.close();

        } catch (IOException e) {

            logger.error("OperatorInfoImportAuditResultServiceImpl下载导入错误excel表格异常", e);

            e.printStackTrace();

        }

        return fileName;

    }

 public static void deletAllFiles(File file) {

        if (file == null) {

            return;

        }

        if (file.exists()) {

            if (file.isFile()) {

                file.delete();

            }else if (file.isDirectory()) {

                File[] listFiles = file.listFiles();

                if (listFiles == null) {

                    return;

                }

                for (File file2 : listFiles) {

                    deletAllFiles(file2);

                }

               /* //递归跳出来的时候删除空文件夹

                file.delete();*/

            }

        }

    }

@RequestMapping(value = "/exportErrorImportResult", method = RequestMethod.GET)

    public void exportErrorImportResult(@RequestParam(value = "paramUrl") String paramUrl, HttpServletResponse response, HttpServletRequest request) {

        //生成错误的文档

        try {

            XSSFWorkbook workbook = null;

            File file = new File(paramUrl);

            if (file.exists()) {

                FileInputStream in = new FileInputStream(file);

                workbook = new XSSFWorkbook(in);

            } else {

                logger.info("初级员工内推导出错误的excel文件,文件不存在");

            }

            OutputStream ouputStream = response.getOutputStream();

            String fileName = "导出错误";

            fileName = URLEncoder.encode(fileName, "UTF-8");

            response.reset();

            response.setContentType("application/vnd.ms-excel");

            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

            workbook.write(ouputStream);

            ouputStream.flush();

            ouputStream.close();

        } catch (Exception e) {

            logger.error("导出错误的excel文件,OperatorPostInfoController 异常:", e);

        }

    }