Apache Poi实现导出excel时生成下拉框

最近项目上要实现文件的导入与导出，前面的文章 已经实现了excel文章的导入与导出，但是考虑到用户在使用的时候体验感不好，于是在网上搜索下，有没有实现excel文件导出的时候带下拉框，然后还真有，之后结合别人的代码写出来如下的代码，废话不说，上代码

Maven

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

因为导入功能在前面的文章已经实现，所以我这边就不写导入的代码了，直接看我之前的文章（Spring Boot上Excel的导入）注意的是这时候选择的版本不能为4.0.1以上的，要不然进行导入的时候会报错

Controller

import com.wlb.file.service.CityService;
import com.wlb.file.util.PoiUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;

import java.util.*;

@Slf4j
@Controller
@RequestMapping("/city")
@SuppressWarnings("all")
public class CityController {

    @Autowired
    private CityService cityService;

    @GetMapping("/exportFile")
    @ResponseBody
    public String exportFile() {
        String desktopPath = System.getProperty("user.home") + "/Desktop/";
        String filePath = desktopPath + "test.xls";
        List<String> headers = Arrays.asList("省","市", "区","","你","我","他");

        List<String> mapOneList = new ArrayList<String>();

        // 查询数据库以获取所有省份
        List<String> cityProvince = cityService.getProvince();

        Map<String, List<String>> map = new HashMap<>();

        // 遍历省份
        for (String province : cityProvince) {
            // 添加省份到mapOneList
            mapOneList.add(province);

            // 查询数据库以获取当前省份的城市数据
            List<String> cityList = cityService.getProvinceAndCity(province);

            if (!CollectionUtils.isEmpty(cityList)) {
                // 将城市列表添加到map，省份作为键
                map.put(province, cityList);
            }

            // 遍历城市
            for (String city : cityList) {
                // 查询数据库以获取当前城市的区域数据
                List<String> areaList = cityService.getCityAndArea(city);

                if (!CollectionUtils.isEmpty(areaList)) {
                    // 将区域列表添加到map，城市作为键
                    map.put(city, areaList);
                }
            }
        }

        // 导出数据
        PoiUtil.export(filePath, headers, mapOneList, map);
        return "导出成功";
    }
}

Service & ServiceImpl

//获取一级数据省份
List<String> getProvince();
public List<String> getProvince() {
    return cityMapper.getProvince();
}

//获取一级省份所对应的市级数据
List<String> getProvinceAndCity(String province);
//获取省级数据对应的市级数据
public List<String> getProvinceAndCity(String province) {
    return cityMapper.getProvinceAndCity(province);
}

//根据市级进行查询区的数据
List<String> getCityAndArea(String city);
public List<String> getCityAndArea(String city) {
    return cityMapper.getCityAndArea(city);
}

PoiUtil 导出工具类（核心）

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class PoiUtil {

   private static final String MAIN_SHEET = "template";

   private static final String SHEET_MAP = "parentCity";
   private static final String SHEET_MAP2 = "hidden";
   private static final String SHEET_MAP3 = "省份";


   /**
    * 计算formula
    *
    * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
    * @param rowId    第几行
    * @param colCount 一共多少列
    * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
    */
   private static String getRange(int offset, int rowId, int colCount) {
       char start = (char) ('A' + offset);
       if (colCount <= 25) {
           char end = (char) (start + colCount - 1);
           return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
       } else {
           char endPrefix = 'A';
           char endSuffix = 'A';
           if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
               if ((colCount - 25) % 26 == 0) {// 边界值
                   endSuffix = (char) ('A' + 25);
               } else {
                   endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
               }
           } else {// 51以上
               if ((colCount - 25) % 26 == 0) {
                   endSuffix = (char) ('A' + 25);
                   endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
               } else {
                   endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                   endPrefix = (char) (endPrefix + (colCount - 25) / 26);
               }
           }
           return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
       }
   }


   /**
    * 导出三级联动and单个下拉框的excel
    *
    * @param filePath   文件输出地址
    * @param headers    表头
    * @param mapOneList 一级所有内容
    * @param map        三级联动对应内容
    */
   public static void export(String filePath, List<String> headers, List<String> mapOneList, Map<String, List<String>> map) {
       List<String> list = new ArrayList<>();
       // 创建workbook
       HSSFWorkbook hssfWorkBook = new HSSFWorkbook();
       // 创建sheet
       HSSFSheet mainSheet = hssfWorkBook.createSheet(MAIN_SHEET);// 主sheet
       // 创建表头
       initHeaders(hssfWorkBook, mainSheet, headers);
       //导出数据到主sheet
       setMainSheet(mainSheet, list);

       //三级联动 sheet
       HSSFSheet mapSheet = hssfWorkBook.createSheet(SHEET_MAP);


       //省市区关系sheet，设置sheet是否隐藏 true:是，false:否
       hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(mapSheet), true);


       // 3.写入数据 将数据写入隐藏的sheet中并做好关联关系
       writeData(hssfWorkBook, mapSheet, mapOneList, map);

       // 4.设置数据有效性
       setDataValid(hssfWorkBook, mainSheet, mapOneList, map);

       // 隐藏 SHEET_MAP2
       int sheetIndex2 = hssfWorkBook.getSheetIndex(SHEET_MAP2);
       hssfWorkBook.setSheetHidden(sheetIndex2, true);

       // 隐藏 SHEET_MAP3
       int sheetIndex3 = hssfWorkBook.getSheetIndex(SHEET_MAP3);
       hssfWorkBook.setSheetHidden(sheetIndex3, true);

       try {
           String exisname = filePath.substring(0, filePath.lastIndexOf("/"));
           File f = new File(exisname);
           if (!f.exists()) {
               f.mkdirs();//创建目录
           }
           // 创建文件
           File file = new File(filePath);
           if (!file.exists()) {
               file.createNewFile();
           } else {
               file.delete();
               file.createNewFile();
           }
       } catch (Exception e) {
           e.printStackTrace();
       }
       // 数据写出文件
       try (FileOutputStream fileOutputStream = new FileOutputStream(filePath)) {
           hssfWorkBook.write(fileOutputStream);
           System.out.println("导出成功！");
       } catch (IOException e) {
           e.printStackTrace();
       }
   }

   /**
    * 生成主页面表头
    *
    * @param wb
    * @param mainSheet
    * @param headers
    */
   private static void initHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {
       //表头样式
       HSSFCellStyle style = wb.createCellStyle();
       style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
       //字体样式
       HSSFFont fontStyle = wb.createFont();
       fontStyle.setFontName("微软雅黑");
       fontStyle.setFontHeightInPoints((short) 12);
       fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
       style.setFont(fontStyle);
       //生成主内容
       HSSFRow rowFirst = mainSheet.createRow(0);//第一个sheet的第一行为标题
       mainSheet.createFreezePane(0, 1, 0, 1); //冻结第一行
       //写标题
       for (int i = 0; i < headers.size(); i++) {
           HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
           mainSheet.setColumnWidth(i, 4000); //设置每列的列宽
           cell.setCellStyle(style); //加样式
           cell.setCellValue(headers.get(i)); //往单元格里写数据
       }
   }


   private static void setMainSheet(HSSFSheet mainSheet, List<String> list) {
       for (int j = 0; j < list.size(); j++) {
           //行
           Row row = mainSheet.createRow(j + 1);
           Object obj = list.get(j);
           if (obj != null) {
               Field[] fields = obj.getClass().getDeclaredFields();
               try {
                   for (int i = 0; i < fields.length; i++) {
                       if (i == 0) {
                           //excel 第一列 为序号
                           Cell cell = row.createCell(i);
                           cell.setCellValue(j + 1);
                       } else {
                           Field f = fields[i];
                           f.setAccessible(true);
                           Object value = f.get(obj);
                           if (value == null) {
                               value = "";
                           }
                           //列
                           Cell cell = row.createCell(i);
                           cell.setCellValue(value.toString());
                       }
                   }
               } catch (IllegalAccessException e) {
                   e.printStackTrace();
               }
           }
       }
   }

   // 修改 setDataValid 方法以修复问题
   private static void setDataValid(HSSFWorkbook HSSFWorkBook, HSSFSheet mainSheet, List<String> provinceList, Map<String, List<String>> siteMap) {
       HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet) mainSheet);
       String[] dataArray = provinceList.toArray(new String[0]);

       // 创建一个隐藏工作表用于省份数据
       HSSFSheet hidden = HSSFWorkBook.createSheet("hidden");
       HSSFCell cell = null;
       for (int i = 0, length = dataArray.length; i < length; i++) {
           String name = dataArray[i];
           HSSFRow row = hidden.createRow(i);
           cell = row.createCell(0);
           cell.setCellValue(name);
       }

       // 设置数据验证公式的条件
       DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden!$A$1:$A$" + dataArray.length);

       // 设置第一列（A列）的数据验证
       CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(1, 10, 0, 0); // 第一列 (A列)
       DataValidation provinceDataValidation = dvHelper.createValidation(constraint, provinceRangeAddressList);
       provinceDataValidation.createErrorBox("error", "请选择正确");
       provinceDataValidation.setShowErrorBox(true);
       mainSheet.addValidationData(provinceDataValidation);

       // 设置市（B列）和区（C列）的数据验证
       for (int i = 0; i <= 10; i++) {
           // 使用 setDataValidation 方法设置市（B列）和区（C列）的数据验证
           setDataValidation('A', 'B', mainSheet, i + 1, 1, 2);
       }

       // 为省份创建一个隐藏工作表
       HSSFSheet provinceSheet = HSSFWorkBook.createSheet("省份");
       HSSFCell provinceCell;
       for (int i = 0; i < dataArray.length; i++) {
           String name = dataArray[i];
           HSSFRow row = provinceSheet.createRow(i);
           provinceCell = row.createCell(0);
           provinceCell.setCellValue(name);
       }

       // 为省份列（E列）设置数据验证
       DVConstraint provinceConstraint = DVConstraint.createFormulaListConstraint("省份!$A$1:$A$" + dataArray.length);
       CellRangeAddressList provinceRangeList = new CellRangeAddressList(1, 10, 4, 4); // E列
       DataValidation provinceDataValidation1 = dvHelper.createValidation(provinceConstraint, provinceRangeList);
       mainSheet.addValidationData(provinceDataValidation1);

       // 为城市列（F列）设置数据验证
       for (int rowNum = 1; rowNum <= 10; rowNum++) {
           setDataValidation('E', 'F', mainSheet, rowNum, 5, 6);
       }

       // 为区列（G列）设置数据验证
       for (int rowNum = 1; rowNum <= 10; rowNum++) {
           setDataValidation('F', 'G', mainSheet, rowNum, 6, 7);
       }
   }

   // 修改 setDataValidation 方法以修复问题
   private static void setDataValidation(char offset1, char offset2, HSSFSheet sheet, int rowNum, int colNum1, int colNum2) {
       HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
       DataValidation dataValidationList1;
       DataValidation dataValidationList2;

       // 数据验证公式条件
       String formulaString1 = "INDIRECT($" + offset1 + (rowNum) + ")";
       String formulaString2 = "INDIRECT($" + offset2 + (rowNum) + ")";

       dataValidationList1 = getDataValidationByFormula(formulaString1, rowNum, colNum1, dvHelper);
       dataValidationList2 = getDataValidationByFormula(formulaString2, rowNum, colNum2, dvHelper);

       // 将数据验证添加到工作表
       sheet.addValidationData(dataValidationList1);
       sheet.addValidationData(dataValidationList2);
   }

   private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, HSSFDataValidationHelper dvHelper) {
       DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
       CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex, 65535, naturalColumnIndex, naturalColumnIndex);
       HSSFDataValidation data_validation_list = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
       data_validation_list.setEmptyCellAllowed(false);
       if (data_validation_list instanceof HSSFDataValidation) {
           data_validation_list.setShowErrorBox(true);
       }
       // 设置输入信息提示信息
       //data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
       return data_validation_list;
   }


   private static void writeData(HSSFWorkbook hssfWorkBook, HSSFSheet mapSheet, List<String> provinceList, Map<String, List<String>> siteMap) {
       //循环将父数据写入siteSheet的第1行中
       int siteRowId = 0;
       HSSFRow provinceRow = mapSheet.createRow(siteRowId++);
       provinceRow.createCell(0).setCellValue("父列表");
       for (int i = 0; i < provinceList.size(); i++) {
           provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));
       }

       // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
       Iterator<String> keyIterator = siteMap.keySet().iterator();
       while (keyIterator.hasNext()) {
           String key = keyIterator.next();
           List<String> son = siteMap.get(key);
           HSSFRow siteRow = mapSheet.createRow(siteRowId++);
           siteRow.createCell(0).setCellValue(key);
           for (int i = 0; i < son.size(); i++) {
               siteRow.createCell(i + 1).setCellValue(son.get(i));
           }

           // 添加名称管理器
           String range = getRange(1, siteRowId, son.size());
           Name name = hssfWorkBook.createName();
           name.setNameName(key);
           String formula = mapSheet.getSheetName() + "!" + range;
           name.setRefersToFormula(formula);
       }
   }
}

缺点

上面的代码即可实现导出下拉框，不过当前代码的缺点是，不能通过浏览器进行excel导出下载，只能固定的导出到你指定好的路径上；另外还有当用户进行下拉框选择的时候，下面的子节点不能够实现自动填充内容。本人能力有限，上面两大遗憾未能实现，希望后续可以实现那样的操作。另外进行导出的时候没有进行考虑当前导出的数据量过大，一般的只要不超过百万量都可以使用。上面的下拉框本人只设置了2000行，如若需要可自行进行调。如有错误，望指正，谢谢！！！