说起easypoi相信大家经常会用，因为在数据导入导出时，常用的也就是.xls文件进行上传下载，一般需要下载一个模板，然后再上传。在一次开发中，甲方提到一个需求，需要带下拉框的excel。于是我摸索了下，做个简单的笔记；如下
import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; /** * @ClassName test * @Description TODO * @Author zhanggesheng * @Date 2020/10/20 17:38 * @Version 1.0 */ @Data public class juice { @Excel(name = "可乐名称", width=30, orderNum = "0",replace = {"可口可乐_coco","百事可乐_baishi"}) private String name; @Excel(name = "热爱程度", width=30, orderNum = "1",replace = {"中度热爱_2","深度热爱_1"}) private String level; }首先是导入导出的实体类对象，就是@Excel这个注解进行注入，那么不常见的应该就是后面的replace,这是字符转换，下拉框和显示的都是中文，实际传到后台解析的数据为下划线后面的字母和数字，那么说到这一切都很简单了。
然后开始封装工具类ExcelUtiles：
/*调用方法*/ public static void exportBatchSelectExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response,Map<Integer,String[]> map) { selectBatchExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName), map); } /*多个导出下拉框具体实现*/ private static void selectBatchExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams,Map<Integer,String[]> map) { exportParams.setType(ExcelType.XSSF); Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list); List keyset = Arrays.asList(map.keySet().toArray()); for (Object i:keyset) { createXssfSelected(workbook,map.get(i),(Integer)i); //createXssfSelected(workbook,strings,col); } Sheet sheet = workbook.getSheet(exportParams.getSheetName()); sheet.getRow(0).setHeight((short) (50 * 20)); sheet.getRow(1).setHeight((short) (30 * 20)); downLoadExcel(fileName, response, workbook); } private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); workbook.write(response.getOutputStream()); } catch (IOException e) { } }然后就是调用这个方法了
/** * 测试 */ @GetMapping("/export") public void export(HttpServletResponse response) { List<juice> list = new ArrayList<>(); Map<Integer,String[]> selectMap = new HashMap<>(); selectMap.put(0,new String[]{"可口可乐","百事可乐"}); selectMap.put(1,new String[]{"中度热爱","轻度热爱"}); ExcelUtiles.exportBatchSelectExcel(list, "title", "sheet0", juice.class, "测试导入模板.xls", response,selectMap); }可以看到封装里面创建下拉选择框的需要对应的string[] 和col,来实现哪一列插入哪些数据选项。createXssfSelected(workbook,strings,col); 所以将需要的多个下拉选项定义为Map<Integer,String[]>就很容易实现这个效果了。
最后导出的excel效果为
