使用阿里EasyExcel写，下载后在Office打开，提示不可读取内容，是否恢复此工作簿的内容？通过修复或删除不可读取的内容。

 

原因：没有 设置好：category1Name.setRefersToFormula(refers); 

直接上代码：

1、下拉实现类：

public class SelectedCellWriteHandler implements SheetWriteHandler {


    public SelectedCellWriteHandler() {
    }

    public SelectedCellWriteHandler(ITFormCostShareFieldService itFormCostShareFieldService, CostShareQuery type, List<List<String>> maps, List<GroupData> groupData, List<TaxRate> taxRates) {
        this.headList = maps;
        this.groupData = groupData;
        this.taxRates = taxRates;
        this.type = type;
        this.itFormCostShareFieldService = itFormCostShareFieldService;
    }


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Map<Integer, String[]> mapDropDown = new HashMap<>();
        //表头字段下拉
        mapDropDown.put(9, type.getBusinessType1sTxt().toArray(new String[type.getBusinessType1s().size()]));
        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();

        // k 为存在下拉数据集的单元格下表 v为下拉数据集
        mapDropDown.forEach((k, v) -> {
            // 设置下拉单元格的首行 末行 首列 末列
            CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
                        //定义sheet的名称
                        //1.创建一个隐藏的sheet 名称为 hidden + k
                        String sheetName = "hidden" + getExcelLine(k);
                        Workbook workbook = writeWorkbookHolder.getWorkbook();
                        Sheet hiddenSheet = workbook.createSheet(sheetName);
                        for (int i = 0, length = v.length; i < length; i++) {
                            // 开始的行数i，列数k
                            hiddenSheet.createRow(i).createCell(k).setCellValue(v[i]);
                        }
                        Name category1Name = workbook.createName();
                        category1Name.setNameName(sheetName);
                        String excelLine = getExcelLine(k);

                        String refers =  sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.length + 1);
                       category1Name.setRefersToFormula(refers);
                        // 设置存储下拉列值得sheet为隐藏
                        int hiddenIndex = workbook.getSheetIndex(sheetName);
                        if (!workbook.isSheetHidden(hiddenIndex)) {
                            workbook.setSheetHidden(hiddenIndex, true);
                        }
                        // 将刚才设置的sheet引用到你的下拉列表中
                        DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
                        DataValidation dataValidation = helper.createValidation(constraint, rangeList);
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
                        sheet.addValidationData(dataValidation);
        });
    }
    /**
     * 返回excel列标A-Z-AA-ZZ
     *
     * @param num 列数
     * @return java.lang.String
     */
    private String getExcelLine(int num) {
        String line = "";
        int first = num / 26;
        int second = num % 26;
        if (first > 0) {
            line = (char) ('A' + first - 1) + "";
        }
        line += (char) ('A' + second) + "";
        return line;
    }
}

2、controller接口返回

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("导入模板", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
List<Map<String, String>> maps = new ArrayList<>();
EasyExcel.write(response.getOutputStream())
        .head(headFields).sheet("导入模板")
        .registerWriteHandler(new SelectedCellWriteHandler(itFormCostShareFieldService, type, headFields, groupData, taxRates))
        .doWrite(maps);