导出excel

@GetMapping("/{hid}")
    public void userLoginExport(HttpServletResponse response,@PathVariable("hid") Long hid) {
        // 查询需要导出的数据
        List<Map<String,String>> list = efileService.getExportDataByhid(hid);
        if(list == null || list.size() <= 0){
            throw new SelectNoFindException("导出失败,数据为空！",null);
        }
        // 创建工作薄
        Properties prop = System.getProperties();
        String os = prop.getProperty("os.name");
        String filePath = "";
        if(os.startsWith("Win")) {
            filePath = ContentConstant.EFILE_WINDOWS;
        }else{
            filePath = ContentConstant.EFILE_LINUX;
        }
        // 在这里是直接读取的Excel模板,不用再创建表头
        File file = new File(filePath  + EfileConstant.EFILE_MODEL_NAME);
        InputStream io = null;
        HSSFWorkbook workbook = null;
        try {
            io = new FileInputStream(file);
            workbook = new HSSFWorkbook(io);
        }catch (Exception e){
            e.printStackTrace();
            throw new SelectNoFindException("导出失败，找不到默认模板！",e.getMessage());
        }
        workbook.setSheetName(0,EfileConstant.EXPORT_NAME);
        // 读取excel中的内容
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFRow row = null;
        HSSFCell cell = null;
        // 设置模板样式，锁定内容
        HSSFCellStyle locked = createStyle(workbook);
        int rowNumber = 2;
        String value = null;
        for(Map<String,String> m : list){
            if(m != null){
                row = sheet.getRow(rowNumber++);
                for(int i = 0,n = 1; i < EfileConstant.EXPORT_DEFAULT_PROPERTY.length;i++,n++){
                    value = null;
                    cell = row.createCell(n);
                    cell.setCellStyle(locked);
                    value = String.valueOf(m.get(EfileConstant.EXPORT_DEFAULT_PROPERTY[i]));
                    if(value != null && !value.equals("null")){
                        // 当等于性别时
                        if(EfileConstant.EXPORT_DEFAULT_PROPERTY[i].equals("sex")){
                            if(Integer.valueOf(value) == UserConstant.SEX_MAN ){
                                cell.setCellValue(UserConstant.SEX_MAN_ALIAS);
                            }else{
                                cell.setCellValue(UserConstant.SEX_WOMAN_ALIAS);
                            }
                        }else{
                            cell.setCellValue(value);
                        }
                    }
                    cell = null;
                }
            }
            row = null;
        }
        //设置返回header
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="+EfileConstant.EXPORT_NAME + System.currentTimeMillis() +".xls");
        try{
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }     

创建Excel模板 // 创建工作薄

        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建工作表sheet
        HSSFSheet sheet = workbook.createSheet(efileModel.getName());
        // 创建单元格样式
        HSSFCellStyle style =  workbook.createCellStyle();
        // 加粗
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 单元格数据类型为字符串
        style.setDataFormat(workbook.createDataFormat().getFormat("@"));
        style.setFont(font);
        // 文字水平居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 文字垂直居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 设置未锁定
        style.setLocked(false);
        sheet.setDefaultColumnStyle(0, style);
        // 单元格宽度自适应
        //sheet.autoSizeColumn(1, true);
        // 合并单元格cellRangAddress四个参数，第一个起始行，第二终止行，第三个起始列，第四个终止列
        for(int z = 0; z < CELL_DEFAULT_LENTH ; z++){
            // 设置指定列度
            sheet.setColumnWidth(z, 256*15);
　　　　　　　// 将该列的第一行和第二行单元格合并
            sheet.addMergedRegion(new CellRangeAddress(0, 1, z, z));
        }// 默认表头长度
        int number = 4;
        // 有两行表头，第一行需要将某两列的单元格合并，而第二行不需要合并
        if(list_twoCell.size() > 0){
            for(int y = 0; y < list_twoCell.size() ; y++){
                sheet.addMergedRegion(new CellRangeAddress(0, 0, (number+y*2), (number+y*2+1)));
            }
        }// 创建表头（表头的高度）
        HSSFRow row1 = sheet.createRow(0);
        row1.setHeight((short) 750);
        HSSFRow row2 = sheet.createRow(1);
        row1.setHeight((short) 600);
        // 单元格
        HSSFCell cell = null;
        HSSFCell cell2 = null;
　　　　 // 笨方法，写固定的表头
        cell = row1.createCell(0);
        cell.setCellValue("ID");
        cell = row1.createCell(1);
        cell.setCellValue("姓名");
        cell.setCellStyle(style);
        cell = row1.createCell(2);
        cell.setCellValue("性别");
        cell.setCellStyle(style);cell = row1.createCell(3);
        cell.setCellValue("家长手机号");
        cell.setCellStyle(style);
        cell = row1.createCell(4);
        cell.setCellValue("籍贯");
        cell.setCellStyle(style);// 如果存在手机号，则记录手机号的表头索引
        int phone_index = 0;// 如果存在籍贯，则记录籍贯的表头索引
        int nativaPlace_index = 0;
　　　　　// 动态设置某两列中第一行合并了两个单元格，而第二行未合并的表头
for(int n = 0,size = 0; n < twoCellLens; n = n+2,size++){
            cell = row1.createCell(n + number);
            cell.setCellValue(list_twoCell.get(size));
            cell.setCellStyle(style);
            cell2 = row2.createCell(n + number);
            cell2.setCellValue("右眼(OD)");
            cell2.setCellStyle(style);
            cell2 = row2.createCell(n + 1 + number);
            cell2.setCellValue("左眼(OS)");
            cell2.setCellStyle(style);
        }// 数据校验(手机号长度)
        lenthValidate(sheet,3,3,"11");//加载下拉列表内容
        String[] textList = null;
        // 性别
        setList(sheet, new String[]{UserConstant.SEX_MAN_ALIAS,UserConstant.SEX_WOMAN_ALIAS}, 2,2);// 籍贯
        setList(sheet, RaceConstant.provinces, 4, 4);// 设置单元格数据格式为字符串
        for (int i = 2; i < 10000; i++) {
            row1 = sheet.createRow(i);
            // 设置行高
            row1.setHeightInPoints(25);
            for(int m = 0;m <number;m++){
                cell = row1.createCell(m);
                cell.setCellStyle(style);
            }
        }
        // 隐藏第一列
        sheet.setColumnHidden((short)0,true);
        // 创建文件
        Properties prop = System.getProperties();
        String os = prop.getProperty("os.name");
        String filePath = "";
        if(os.startsWith("Win")) {
            filePath = ContentConstant.EFILE_WINDOWS;
        }else{
            filePath = ContentConstant.EFILE_LINUX;
        }
        String fileName = System.currentTimeMillis() +".xls";
        File f = new File(filePath + fileName);
        FileOutputStream fout = null;
        try {
            f.createNewFile();
            fout = new FileOutputStream(f);
            workbook.write(fout);
        }catch(IOException e){
            e.printStackTrace();
            throw new SelectNoFindException("生成筛查模板失败！",e.getMessage());
        }finally {
            try {
                if(fout != null){
                    fout.close();
                }
                if(workbook != null) {
                    workbook.close();
                }
            }catch(IOException e){
                    e.printStackTrace();
                    throw new SelectNoFindException("生成筛查模板失败！",e.getMessage());
                }
        }

添加校验

/**
      * @Author holley
      * @Description 加载下拉列表内容
      * @Date 2018/11/2 16:27
      * @Param [sheet, textList, index]
      * @return void
      */
    private void setList(HSSFSheet sheet, String[] textList, int firstCol,int lastCol) {
        //下拉列表
        HSSFDataValidationHelper helper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint dvConstraint1 = helper.createExplicitListConstraint(textList);
        dvConstraint1.setExplicitListValues(textList);
        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList1 = new CellRangeAddressList( 2,  10000, firstCol,lastCol);
        //数据有效性对象
        DataValidation dataValidation1 = helper.createValidation(dvConstraint1, addressList1);
        sheet.addValidationData(dataValidation1);
    }

    private void lenthValidate(HSSFSheet sheet,int firstCol,int lastCol,String limitLenth) {
        //DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("");
        DVConstraint constraint = DVConstraint.createNumericConstraint(
                DataValidationConstraint.ValidationType.TEXT_LENGTH,
                DataValidationConstraint.OperatorType.BETWEEN, "0", limitLenth);
        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList( 2,  10000, firstCol,lastCol);
        //数据有效性对象
        DataValidation validation = new HSSFDataValidation(regions,constraint);
        validation.createErrorBox("超出限制长度！", "格式错误，长度不能超过"+ limitLenth +"位");
        sheet.addValidationData(validation);
    }