**

2种方式解决此问题**

我在easyexcel通过拦截器拦截 implements SheetWriteHandler
添加样式少的时候是没问题的,有一天线上环境突然不行了

在createrow报错, getrow为null也报错,因为当写入磁盘的内容与现在内容合并导致这个问题

下面出现问题的完整代码
` //获取一个workbook
Sheet sheet = writeSheetHolder.getSheet();
//设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//定义sheet的名称
String hiddenName = “hidden”;
//1.创建一个隐藏的sheet 名称为 hidden
Workbook workbook= writeWorkbookHolder.getWorkbook();
Sheet hidden = workbook.createSheet(hiddenName);
Name category1Name = workbook.createName();
category1Name.setNameName(hiddenName);

    for (Map.Entry<Integer,ExcelSelectedResolve> entry : map.entrySet()) {
        //下拉框的起始行,结束行,起始列,结束列
        CellRangeAddressList addressList = new CellRangeAddressList(1, 2000, entry.getKey(), entry.getKey());
        //获取excel列名
        String excelLine = getExcelLine(entry.getKey());
        //2.循环赋值
        String[] values = entry.getValue().getSource();
        for (int i = 0, length = values.length; i < length; i++) {
            // 3:表示你开始的行数  3表示 你开始的列数
            Row row = hidden.getRow(i);
            if (row == null) {
                row = hidden.createRow(i);
            }
            row.createCell(entry.getKey()).setCellValue(values[i]);
        }
        //4.  =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
        String refers = "="+hiddenName + "!$"+excelLine+
                "$1:$"+excelLine +"$"+ (values.length);
        //5 将刚才设置的sheet引用到你的下拉列表中
        DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        // 阻止输入非下拉选项的值
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.createErrorBox("提示", "请输入下拉选项中的内容");
        writeSheetHolder.getSheet().addValidationData(dataValidation);
    }
    //设置列为隐藏
    int hiddenIndex = workbook.getSheetIndex("hidden");
    if (!workbook.isSheetHidden(hiddenIndex)) {
        workbook.setSheetHidden(hiddenIndex, true);
    }

`

废话不多说开始吧
方式一

//获取一个workbook
      Sheet sheet = writeSheetHolder.getSheet();
      //设置下拉框
      DataValidationHelper helper = sheet.getDataValidationHelper();

      for (Map.Entry<Integer,ExcelSelectedResolve> entry : map.entrySet()) {
          //定义sheet的名称
          String hiddenName = "hidden"+entry.getKey();
          //1.创建一个隐藏的sheet 名称为 hidden
          Workbook workbook= writeWorkbookHolder.getWorkbook();
          Sheet hidden = workbook.createSheet(hiddenName);
          Name category1Name = workbook.createName();
          category1Name.setNameName(hiddenName);
          //下拉框的起始行,结束行,起始列,结束列
          CellRangeAddressList addressList = new CellRangeAddressList(1, 2000, entry.getKey(), entry.getKey());
          //获取excel列名
          String excelLine = getExcelLine(entry.getKey());
          //2.循环赋值
          String[] values = entry.getValue().getSource();
          for (int i = 0, length = values.length; i < length; i++) {
              // 3:表示你开始的行数  3表示 你开始的列数
              Row row = hidden.getRow(i);
              if (row == null) {
                  row = hidden.createRow(i);
              }
              row.createCell(entry.getKey()).setCellValue(values[i]);
          }
          //4.  =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
          String refers = "="+hiddenName + "!$"+excelLine+
                  "$1:$"+excelLine +"$"+ (values.length);
          //5 将刚才设置的sheet引用到你的下拉列表中
          DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
          DataValidation dataValidation = helper.createValidation(constraint, addressList);
          // 阻止输入非下拉选项的值
          dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
          dataValidation.setShowErrorBox(true);
          dataValidation.setSuppressDropDownArrow(true);
          dataValidation.createErrorBox("提示", "请输入下拉选项中的内容");
          writeSheetHolder.getSheet().addValidationData(dataValidation);
          //设置列为隐藏
          int hiddenIndex = workbook.getSheetIndex(hiddenName);
          if (!workbook.isSheetHidden(hiddenIndex)) {
              workbook.setSheetHidden(hiddenIndex, true);
          }
      }

在区别在于 之前讲所有下拉内容放入一个sheet内
导致内存与磁盘合并出现问题
修改成每一个下拉框新建一个隐藏sheet即可

方式二

通过poi不实用easyexcel 直接上代码废话不多说

 XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 100, false, false);
        SXSSFSheet sheet = workbook.createSheet("sheet");
        SXSSFRow row = sheet.createRow(0);
        List<String> titleString = excelTemplateServiceImpl.listadd();
        //设置标题
        for (int i = 0; i < titleString.size(); i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellValue(titleString.get(i));
        }
        Map<Integer, String[]> buffer = getBuffer();
        for (Map.Entry<Integer, String[]> integerEntry : buffer.entrySet()) {
            //创建下拉框
            setLongHSSFValidation(workbook,integerEntry.getValue(), sheet, 1, 10000, integerEntry.getKey());
        }
        try {
            try {
                //文件名中文乱码问题
                fileName = new String(fileName.getBytes("iso8859-1"), "utf-8");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            //编码格式为UTF-8
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }



/**
     * 设置下拉cell。下拉数据放在隐藏的sheet中。
     * 解决下拉框过长不显示问题
     *
     * @param workbook HSSFWorkbook 和SXSSWorkbook 都可以
     * @param dataList 下拉数据数组
     * @param sheet    设置下拉框的sheet
     * @param firstRow 开始行  为空的话默认数值是1
     * @param endRow   结束行
     * @param cellNum  下拉框所在的列
     */
    public static void setLongHSSFValidation(Workbook workbook, String[] dataList, Sheet sheet, Integer firstRow, Integer endRow, Integer cellNum) {
        String hiddenName = "hidden" + (cellNum + 1);
        //1.创建隐藏的sheet页。存放下拉数据
        Sheet hidden = workbook.createSheet(hiddenName);
        //2.循环赋值
        for (int i = 0, length = dataList.length; i < length; i++) {
            hidden.createRow(i).createCell(cellNum).setCellValue(dataList[i]);
        }
        Name category1Name = workbook.createName();
        category1Name.setNameName(hiddenName);
        category1Name.setRefersToFormula(hiddenName + "!A$1:A$" + dataList.length);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenName);
        CellRangeAddressList addressList = new CellRangeAddressList(null == firstRow ? 1 : firstRow, endRow, cellNum, cellNum);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
        // 阻止输入非下拉选项的值
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.createErrorBox("提示", "请输入下拉选项中的内容");
        // 设置hiddenSheet隐藏
        workbook.setSheetHidden(workbook.getSheetIndex(hidden), true);
    }

解决问题