SpringBoot poi easypoi 记录动态大数量下拉

末蓝、 2022-09-12 12:52 9阅读 0赞

# SpringBoot poi easypoi 记录动态大数量下拉 #

### 原因 ###

因为有一个需求是用户可以下载模版excel，然后上传入库，但是下载的模版需要有一个下拉框，这个下拉框的数据需要根据不角色，下拉内容也不一样

### 先简单用easypoi实现一下下拉 ###

maven：

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-spring-boot-starter</artifactId>
        <version>4.4.0</version>
    </dependency>

目录结构：

*  java
    
     *  com.example.springboot04
        
         *  controller
            
             *  UserExcelController.java
         *  util
            
             *  ExportExcelUtil.java
         *  vo
            
             *  UserExportVo.java

excel对应数据UserExportVo.java：

package com.example.springboot04.vo;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import lombok.Data;
    import org.springframework.beans.factory.annotation.Value;
    
    import java.io.Serializable;
    
    /** * @author zjl */
    @Data
    public class UserExportVo implements Serializable { 
        @Excel(name = "姓名", orderNum = "0", width = 20)
        private String realName;
    
        @Excel(name = "手机号码", orderNum = "1", width = 20)
        private String phone;
    
        /** isColumnHidden 隐藏列 * */
        @Excel(name = "年龄", orderNum = "2", isColumnHidden = true)
        @Value("1")
        private String age;
    
        /** * addressList 下拉 * replace 下拉数据 显示数据_实际数据 */
        @Excel(name = "状态", orderNum = "3", width = 20, replace = { "初始化_3a11", "正常_4b22", "注销_5c33"}, addressList = true)
        private String status;
    }

工具类 ExportExcelUtil.java：

package com.example.springboot04.util;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.List;
    
    public class ExportExcelUtil { 
        /** * 导出excel * * @param list 数据集合 * @param pojoClass 数据类型 * @param fileName 文件名称 * @param response 响应对象 */
        public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException { 
    // ExportParams exportParams = new ExportParams(null, null);
            ExportParams exportParams = new ExportParams();
            // 自定义字典查询规则
    // exportParams.setDictHandler(new IExcelDictHandlerImpl());
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
            if (workbook != null) { 
                try { 
                    response.setCharacterEncoding("UTF-8");
                    response.setHeader("content-Type", "application/vnd.ms-excel");
                    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                    workbook.write(response.getOutputStream());
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
        }
    
    }

请求类 UserExcelController.java ：

package com.example.springboot04.controller;
    
    import cn.afterturn.easypoi.excel.ExcelImportUtil;
    import cn.afterturn.easypoi.excel.entity.ImportParams;
    import cn.hutool.json.JSONUtil;
    import com.example.springboot04.util.ExportExcelUtil;
    import com.example.springboot04.vo.UserExportVo;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    
    @RestController
    @RequestMapping("/test")
    public class UserExcelController { 
    
        @GetMapping("/")
        public String test(){ 
            return "hello";
        }
    
        @GetMapping(value = "/export")
        public void exportXls(HttpServletResponse response) throws IOException { 
            // 查询数据，这是一个模版所以没数据
            List<UserExportVo> list = new ArrayList<>();
            UserExportVo userExportVo = new UserExportVo();
            userExportVo.setRealName("");
            userExportVo.setPhone("");
            userExportVo.setAge("");
            userExportVo.setStatus("");
            list.add(userExportVo);
            // 导出数据，数据，数据类型，文件名称，响应对象
            ExportExcelUtil.exportExcel(list, UserExportVo.class, "用户信息表", response);
        }
        @PostMapping("/upload")
        public Boolean upload(@RequestParam("file") MultipartFile multipartFile, HttpServletResponse response) throws Exception { 
            ImportParams params = new ImportParams();
            params.setHeadRows(1);
            // params.setTitleRows(0);
            List<UserExportVo> result = ExcelImportUtil.importExcel(multipartFile.getInputStream(),
                    UserExportVo.class, params);
            System.out.println(JSONUtil.toJsonStr(result));
            return true;
        }
    }

浏览器输入下面url，就能下载模版  
[http://127.0.0.1:8080/test/export][http_127.0.0.1_8080_test_export]

easypoi文档  
[http://doc.wupaas.com/docs/easypoi/easypoi-1c2cp5rf3hnqv][http_doc.wupaas.com_docs_easypoi_easypoi-1c2cp5rf3hnqv]

不过上面的代码有个问题，@Excel注解中使用replace的话，没法动态生成数据，所以需要使用dict

在util目录下新增ExcelDiceAddressListHandlerImpl类

在UserExportVo.java中添加地址字段：

package com.example.springboot04.vo;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import lombok.Data;
    import org.springframework.beans.factory.annotation.Value;
    import java.io.Serializable;
    
    @Data
    public class UserExportVo implements Serializable { 
        @Excel(name = "姓名", orderNum = "0", width = 20)
        private String realName;
    
        @Excel(name = "手机号码", orderNum = "1", width = 20)
        private String phone;
    
        /** isColumnHidden 隐藏列 * */
        @Excel(name = "年龄", orderNum = "2", isColumnHidden = true)
        @Value("1")
        private String age;
    
        /** * addressList 下拉 * replace 下拉数据 显示数据_实际数据 */
        @Excel(name = "状态", orderNum = "3", width = 20, replace = { "初始化_3a11", "正常_4b22", "注销_5c33"}, addressList = true)
        private String status;
    
        // 新增一个下拉，使用dict
        @Excel(name = "地址", orderNum = "4", width = 20, dict = "address", addressList = true)
        private String address;
    }

新增的ExcelDiceAddressListHandlerImpl.java

package com.example.springboot04.util;
    
    import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
    import cn.hutool.core.util.ObjectUtil;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    public class ExcelDiceAddressListHandlerImpl implements IExcelDictHandler { 
        @Override
        public List<Map> getList(String dict) { 
            // 这里可以动态加入数据
            List<Map> list = new ArrayList<>();
            Map<String, String> dictMap = new HashMap<>();
            dictMap.put("dictKey", "0");
            dictMap.put("dictValue", "xx省xx市xx区xx小区");
            list.add(dictMap);
            dictMap = new HashMap<>();
            dictMap.put("dictKey", "1");
            dictMap.put("dictValue", "yy省yy市yy区yy小区");
            list.add(dictMap);
            dictMap = new HashMap<>();
            dictMap.put("dictKey", "2");
            dictMap.put("dictValue", "zz省zz市zz区zz小区");
            list.add(dictMap);
            return list;
        }
    
        @Override
        public String toName(String s, Object o, String s1, Object o1) { 
            if ("address".equals(s) && ObjectUtil.isNotNull(o1)) { 
                int level = Integer.parseInt(o1.toString());
                switch (level) { 
                    case 0:
                        return "xx省xx市xx区xx小区";
                    case 1:
                        return "yy省yy市yy区yy小区";
                    case 2:
                        return "zz省zz市zz区zz小区";
                }
            }
            return null;
        }
    
        @Override
        public String toValue(String s, Object o, String s1, Object o1) { 
            if ("address".equals(s) && ObjectUtil.isNotNull(o1)) { 
                int level = Integer.parseInt(o1.toString());
                switch (level) { 
                    case 0:
                        return "xx省xx市xx区xx小区";
                    case 1:
                        return "yy省yy市yy区yy小区";
                    case 2:
                        return "zz省zz市zz区zz小区";
                }
            }
            return null;
        }
    }

ExportExcelUtil.java中添加dict数据

package com.example.springboot04.util;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import org.apache.poi.ss.usermodel.Workbook;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.List;
    
    public class ExportExcelUtil { 
        /** * 导出excel * * @param list 数据集合 * @param pojoClass 数据类型 * @param fileName 文件名称 * @param response 响应对象 */
        public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException { 
    // ExportParams exportParams = new ExportParams(null, null);
            ExportParams exportParams = new ExportParams();
            // 添加dict数据
            exportParams.setDictHandler(new ExcelDiceAddressListHandlerImpl());
            // 自定义字典查询规则
    // exportParams.setDictHandler(new IExcelDictHandlerImpl());
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
            if (workbook != null) { 
                try { 
                    response.setCharacterEncoding("UTF-8");
                    response.setHeader("content-Type", "application/vnd.ms-excel");
                    response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                    workbook.write(response.getOutputStream());
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
        }
    
    }

UserExcelController.java中增加地址字段：

package com.example.springboot04.controller;
    
    import cn.afterturn.easypoi.excel.ExcelImportUtil;
    import cn.afterturn.easypoi.excel.entity.ImportParams;
    import cn.hutool.json.JSONUtil;
    import com.example.springboot04.util.ExportExcelUtil;
    import com.example.springboot04.vo.UserExportVo;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    
    @RestController
    @RequestMapping("/test")
    public class UserExcelController { 
    
        @GetMapping("/")
        public String test(){ 
            return "hello";
        }
    
        @GetMapping(value = "/export")
        public void exportXls(HttpServletResponse response) throws IOException { 
            // 查询数据，这是一个模版所以没数据
            List<UserExportVo> list = new ArrayList<>();
            UserExportVo userExportVo = new UserExportVo();
            userExportVo.setRealName("");
            userExportVo.setPhone("");
            userExportVo.setAge("");
            userExportVo.setStatus("");
            // 新增下拉
            userExportVo.setAddress(null);
            list.add(userExportVo);
            // 导出数据，数据，数据类型，文件名称，响应对象
            ExportExcelUtil.exportExcel(list, UserExportVo.class, "用户信息表", response);
        }
        @PostMapping("/upload")
        public Boolean upload(@RequestParam("file") MultipartFile multipartFile, HttpServletResponse response) throws Exception { 
            ImportParams params = new ImportParams();
            params.setHeadRows(1);
            // params.setTitleRows(0);
            List<UserExportVo> result = ExcelImportUtil.importExcel(multipartFile.getInputStream(),
                    UserExportVo.class, params);
            System.out.println(JSONUtil.toJsonStr(result));
            return true;
        }
    }

浏览器输入下面url，就能下载模版  
[http://127.0.0.1:8080/test/export][http_127.0.0.1_8080_test_export]

本来以为这样就可以了，但是，因为下拉数据最多有一千多条数据，而用dict我发现到几十条就不显示了，也不知道是不是我没有弄对，因为文档上说能支持几万条的

于是我只能又在网上找方法

util目录下新建一个ExportExcelUtilNew.java

package com.example.springboot04.util;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Name;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.net.URLEncoder;
    
    public class ExportExcelUtilNew { 
        /** * 导出excel * * @param fileName 文件名称 * @param response 响应对象 */
        public static void exportExcel(String fileName, HttpServletResponse response) { 
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeight((short)(30*12));
    
            // 设置表头字体样式
            HSSFFont columnHeadFont = wb.createFont();
            columnHeadFont.setFontName("宋体");
            // 列头的样式
            HSSFCellStyle columnHeadStyle = wb.createCellStyle();
            columnHeadStyle.setFont(columnHeadFont);
    
            //创建Excel工作表第一行
            HSSFRow row0 = sheet.createRow(0);
            // 设置行高
            row0.setHeight((short) 750);
    
            HSSFCell cell = row0.createCell(0);
            cell.setCellValue(new HSSFRichTextString("分类"));
            cell.setCellStyle(columnHeadStyle);
    
            cell = row0.createCell(1);
            cell.setCellValue(new HSSFRichTextString("名称"));
            cell.setCellStyle(columnHeadStyle);
    
    
            cell = row0.createCell(2);
            cell.setCellValue(new HSSFRichTextString("地址"));
            cell.setCellStyle(columnHeadStyle);
    
            // 将下拉数据写入新的sheet中，这样就可以大数据量，亲测一千多条没问题，而且这个数组也可以根据自己需求动态生成想要的下拉数据
            String[] a = { "面", "馄饨", "饺子", "炒饭"};
            String[] x = { "xx省xx市xx区xx小区", "yy省yy市yy区yy小区", "zz省zz市zz区zz小区", "aa省aa市aa区aa小区"};
    
            // 参数：从第一行开始到65535行都用下拉，从第几列到第几列使用下拉，下拉数据，需要隐藏的sheet，sheet名
            selectList(wb, 1, 65535, 0, 0, a, 1, "state");
            selectList(wb, 1, 65535, 2, 2, x, 2, "address");
            try { 
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                //response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
    
                // 解决下载文件名中文乱码问题
                String fileNameURL = URLEncoder.encode(fileName+".xls", "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
                wb.write(response.getOutputStream());
            } catch (IOException e) { 
                e.printStackTrace();
            }
        }
    
        /** * firstRow 开始行号(下标0开始) * lastRow 结束行号，最大65535 * firstCol 区域中第一个单元格的列号 (下标0开始) * lastCol 区域中最后一个单元格的列号 * dataArray 下拉内容 * sheetHidden 影藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个 * */
        public static void selectList(HSSFWorkbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, String[] dataArray, int sheetHidden, String hiddenName){ 
            HSSFSheet sheet = workbook.getSheetAt(0);
            HSSFSheet hidden = workbook.createSheet(hiddenName);
    
            Cell cell = null;
            for (int i = 0, length = dataArray.length; i < length; i++)
            { 
                String name = dataArray[i];
                Row row = hidden.createRow(i);
                cell = row.createCell(0);
                cell.setCellValue(name);
            }
    
            Name namedCell = workbook.createName();
            namedCell.setNameName(hiddenName);
            namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + dataArray.length);
            //加载数据,将名称为hidden的
            DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenName);
    
            // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    
    // // 将sheet设置为隐藏
            workbook.setSheetHidden(sheetHidden, true);
            sheet.addValidationData(validation);
        }
    }

controller目录下新建UserExcelControllerNew.java

package com.example.springboot04.controller;
    
    import com.example.springboot04.util.ExportExcelUtilNew;
    import com.example.springboot04.vo.UserExportVo;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    @RestController
    @RequestMapping("/testnew")
    public class UserExcelControllerNew { 
    
        @GetMapping("/")
        public String test(){ 
            return "hello";
        }
    
        @GetMapping(value = "/export")
        public void exportXls(HttpServletResponse response) throws IOException { 
            ExportExcelUtilNew.exportExcel("信息表",response);
        }
        @PostMapping("/upload")
        public List upload(@RequestParam("file") MultipartFile multipartFile) throws Exception { 
            InputStream inputStream = multipartFile.getInputStream();
            String fileName = multipartFile.getOriginalFilename();
    
            Workbook workbook = null;
            if (fileName.endsWith(".xls")) { 
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) { 
                workbook = new XSSFWorkbook(inputStream);
            }else{ 
                workbook = new HSSFWorkbook(inputStream);
            }
            //获取文件中的表格
            Sheet sheetAt = workbook.getSheetAt(0);
            //获取文件中的长度从0开始
            int lastRowNum = sheetAt.getLastRowNum();
    
            List<Map<String, String>> list = new ArrayList<>();
    
            // 从1开始，因为0是表头
            for (int i = 1; i <= lastRowNum; i++) { 
                Row row = sheetAt.getRow(i);
    
                Map<String, String> map = new HashMap<>();
    
                Cell cell = row.getCell(0);
                cell.setCellType(CellType.STRING);
                String category = cell.getStringCellValue();
                System.out.println("分类:"+category);
                map.put("category",category);
    
                Cell cell1 = row.getCell(1);
                cell1.setCellType(CellType.STRING);
                String name = cell1.getStringCellValue();
                System.out.println("名称:"+name);
                map.put("name",name);
    
                Cell cell2 = row.getCell(2);
                cell2.setCellType(CellType.STRING);
                String address = cell2.getStringCellValue();
                System.out.println("地址:"+address);
                map.put("address",address);
    
                list.add(map);
            }
            return list;
        }
    }

输入下面url，下载模版  
[http://127.0.0.1:8080/testnew/export][http_127.0.0.1_8080_testnew_export]

使用这种形式就可以在下拉中显示上千条数据了

导入地址：  
[http://127.0.0.1:8080/testnew/upload][http_127.0.0.1_8080_testnew_upload]

[http_127.0.0.1_8080_test_export]: http://127.0.0.1:8080/test/export
[http_doc.wupaas.com_docs_easypoi_easypoi-1c2cp5rf3hnqv]: http://doc.wupaas.com/docs/easypoi/easypoi-1c2cp5rf3hnqv
[http_127.0.0.1_8080_testnew_export]: http://127.0.0.1:8080/testnew/export
[http_127.0.0.1_8080_testnew_upload]: http://127.0.0.1:8080/testnew/upload