SpringBoot下使用easypoi导出数据到Excel中下拉实现

情景：导出时会遇到某个字段是字典表中数据，为了防止用户乱填，需要返回下拉，用户只需要选择下拉即可

实现方法如下：

第一步：需要导入poi包

        <!-- excel --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.2.0</version></dependency>

第二步：easypoi中未封装进去下拉，需要创建一个生成下拉的方法

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;public final class ExcelUtil {/*** firstRow 开始行号(下标0开始)* lastRow  结束行号，最大65535* firstCol 区域中第一个单元格的列号 (下标0开始)* lastCol 区域中最后一个单元格的列号* dataArray 下拉内容* sheetHidden 影藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个* */public static void selectList(Workbook workbook,int firstCol,int lastCol,String[] strings ){Sheet sheet = workbook.getSheetAt(0);//  生成下拉列表//  只对(x，x)单元格有效CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);//  生成下拉框内容DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strings);HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);//  对sheet页生效sheet.addValidationData(dataValidation);}
}

第三步:把查询字典列表封装一个方法中：

    private void commonChange(Workbook workbook) {//String[] list1 = new String[]{"南山区", "福田区", "宝安区", "罗湖区", "龙岗区"};//String[] list2 = new String[]{"商场", "医院", "住宅", "学校", "港口", "酒店","其他"};SysDictMaintenance sysDictMaintenance = new SysDictMaintenance();//设备使用区域列表List<String> sbsyqyList = new ArrayList<>();List<SysArea> list3 = sysAreaService.getAreaList();for (SysArea dict : list3) {sbsyqyList.add(dict.getName());}//查询设备所属场所列表List<String> sbcsList = new ArrayList<>();sysDictMaintenance.setType("use_place");List<SysDictMaintenance> list4 = sysDictMaintenanceService.queryList(sysDictMaintenance);for (SysDictMaintenance dict : list4) {sbcsList.add(dict.getLabel());}//设置Excel下拉列表ExcelUtil.selectList(workbook, 33, 33, sbsyqyList.toArray(new String[sbsyqyList.size()]));ExcelUtil.selectList(workbook, 34, 34, sbcsList.toArray(new String[sbcsList.size()]));}

注意： ExcelUtil.selectList(workbook, 33, 33, sbsyqyList.toArray(new String[sbsyqyList.size()]));  下标要与实体对象中相关字段，一一对应，不然显示对不上的。还有要把数据格式数组数组对象转换成数组

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class WbDeviceParameters {private static final long serialVersionUID = 1L;/*** 特种设备编号*/@TableIdprivate String id;/*** 设备注册代码*/@Excel(name = "*设备注册代码", orderNum = "1", width = 20)private String sbzcdm;.../*** 设备使用区域*/@Excel(name = "*设备使用区域", orderNum = "34", width = 20)private String deviceUseArea;/*** 设备所属场所*/@Excel(name = "*设备所属场所", orderNum = "35", width = 20)private String devicePlace;}

第四步：调用封装相关字典查询列表方法：

 /*** 下载导入数据模板*/@RequestMapping(value = "downLoad")public void downLoad(HttpServletResponse response) throws IOException {ServletOutputStream sos = null;try {String title = "设备信息";//表格标题String fileName = "设备信息导入模板.xls";DeviceParameters deviceParameters = new DeviceParameters();deviceParameters.setSbzcdm("3110441xxx2012060002");List<DeviceParameters> list = deviceParametersService.findList(deviceParameters);//查询设备列表commonChange(workbook);//生成下拉调用ExportParams exportParams = new ExportParams(title, title);exportParams.setDictHandler(new ExcelDictHandlerImpl());Workbook workbook = ExcelExportUtil.exportExcel(exportParams, DeviceParameters.class, list);response.setContentType("application/vnd.ms-excel;charset=UTF-8");response.setCharacterEncoding("UTF-8");response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(fileName));sos = response.getOutputStream();workbook.write(sos);} catch (Exception e) {LoggerUtils.error(logger, e);}}

效果图如下：

OK  ^ _ ^这次就到这里啦