easypoi导出带动态下拉框

标签：return excel 导出 new list import easypoi 下拉框

easypoi导出带动态下拉框。

实体注解：

package com.springbooteasypoi.entity.excel.select;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

/**
 * Created by Administrator on 2022/12/6.
 */
@Data
public class Student {
    @Excel(name = "姓名",width = 20)
    private String name;
    
    // 动态下拉框字段：dict,addressList = true
    @Excel(name = "学生类型",dict = "level",addressList = true,width = 20)
    private String type;
}

handler：

package com.springbooteasypoi.handler.excel.select;

import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2022/12/6.
 */
@Component
public class ExcelDicHandler 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", "严重瞌睡");
        list.add(dictMap);
        dictMap = new HashMap<>();
        dictMap.put("dictKey", "1");
        dictMap.put("dictValue", "小B");
        list.add(dictMap);
        dictMap = new HashMap<>();
        dictMap.put("dictKey", "2");
        dictMap.put("dictValue", "深度富有");
        list.add(dictMap);
        return list;
    }

    // 导出用到
    @Override
    public String toName(String dict, Object obj, String name, Object value) {
        if ("level".equals(dict)) {
            int level = Integer.parseInt(value.toString());
            switch (level) {
                case 1:
                    return "小B";
                case 0:
                    return "严重瞌睡";
                case 2:
                    return "深度富有";
            }
        }
        return null;
    }

    // 导入用到
    @Override
    public String toValue(String dict, Object obj, String name, Object value) {
        if ("level".equals(dict)) {
            String valueStr = String.valueOf(value);
            switch (valueStr) {
                case "小B":
                    return "1";
                case "严重瞌睡":
                    return "0";
                case "深度富有":
                    return "2";
            }
        }
        return null;
    }
}

导入导出：

package com.springbooteasypoi.controller.excel.select;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.springbooteasypoi.entity.excel.select.Student;
import com.springbooteasypoi.entity.response.ResponseObjectResult;
import com.springbooteasypoi.entity.response.ResponseStatus;
import com.springbooteasypoi.entity.response.ResultCode;
import com.springbooteasypoi.handler.excel.select.ExcelDicHandler;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
* @description: 动态下拉框
* @author liuyachao
* @date 2022/12/6 12:34
*/
@RestController
@RequestMapping("/select")
public class SelectController {
    // 无数据导出
    @PostMapping("/exportDynamicSelect")
    public void exportTest(HttpServletResponse resp){
        try {
            ExportParams exportParams = new ExportParams("测试", "测试");
            exportParams.setDictHandler(new ExcelDicHandler());
            exportParams.setCreateHeadRows(true);
            List<Student> list = new ArrayList<>();
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);
            File filePath = new File("D:/easypoi");
            if(!filePath.exists()){
                filePath.mkdirs();
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            File localFile = new File("D:/easypoi" + File.separator + "test" + sdf.format(new Date()) + ".xls");
            localFile.setReadable(true, false);
            OutputStream os = new FileOutputStream(localFile);
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    // 有数据导出
    @PostMapping("/exportDynamicSelect2")
    public void exportTest2(HttpServletResponse resp){
        try {
            ExportParams exportParams = new ExportParams("测试", "测试");
            exportParams.setDictHandler(new ExcelDicHandler());
            exportParams.setCreateHeadRows(true);
            List<Student> list = new ArrayList<>();
            Student s1 = new Student();
            s1.setName("xiao");
            s1.setType("0");
            Student s2 = new Student();
            s2.setName("da");
            s2.setType("1");
            list.add(s1);
            list.add(s2);
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);
            File filePath = new File("D:/easypoi");
            if(!filePath.exists()){
                filePath.mkdirs();
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            File localFile = new File("D:/easypoi" + File.separator + "test" + sdf.format(new Date()) + ".xls");
            localFile.setReadable(true, false);
            OutputStream os = new FileOutputStream(localFile);
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    // 导入
    @PostMapping("/importDynamicSelect")
    public ResponseObjectResult importDynamicSelect(@RequestPart(value = "file") MultipartFile file,
                                    HttpServletResponse resp){
        try {
            //判断文件是否存在
            if (file == null) {
                return new ResponseObjectResult(new ResponseStatus(ResultCode.ILLEGALINPUT));
            }
            //获得文件名
            String fileName = file.getOriginalFilename();

            //判断文件是否是excel文件
            if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
                return new ResponseObjectResult(new ResponseStatus(10001, "不是excel文件", false));
            }

            ImportParams params = new ImportParams();
            //设置标题占行
            params.setTitleRows(1);
            //设置表头占行
            params.setHeadRows(1);
            //解决空行问题 选择一个不为空的列
            params.setKeyIndex(1);
            //sheet位置 从0开始
//        params.setStartSheetIndex(0);
//        params.setStartRows(2);
            //代表导入这里是需要验证的（根据字段上的注解校验）
            params.setNeedVerify(true);
            params.setDictHandler(new ExcelDicHandler());

            //获取excel文件的io流
            List<Student> list = null;
            try {
                InputStream is = file.getInputStream();
                ExcelImportResult<Student> result = ExcelImportUtil.importExcelMore(is, Student.class, params);
                list = result.getList();
                //失败结果集
                List<Student> failList = result.getFailList();
                //拿到导出失败的工作簿
                Workbook failWorkbook = result.getFailWorkbook();
//            Workbook workbook = result.getWorkbook();
                //验证是否有失败的数据
                if (result.isVerifyFail()) {
                    ServletOutputStream fos = resp.getOutputStream();
                    //mime类型
                    resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                    resp.setHeader("Content-disposition", "attachment;filename=error.xlsx");
                    result.getFailWorkbook().write(fos);
//                workbook.write(fos);
//                failWorkbook.write(fos);
                    fos.close();
                    // java.lang.IllegalStateException: Cannot call sendError() after the response has been committed
                    //return new ResponseObjectResult(new ResponseStatus(ResultCode.FAIL),failList);
                    return null;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return new ResponseObjectResult(new ResponseStatus(ResultCode.SUCCESS));
        } catch (Exception e) {
            e.printStackTrace();
            return new ResponseObjectResult(new ResponseStatus(ResultCode.FAIL));
        }
    }
}

效果图：

导出空数据模板：

 

 导出：

 

 导入：

 

 注意：

如果注解没有addressList，需升级easypoi版本，我的是由4.1.3升级到了4.3.0就有了。

<!--使用easypoi,可以导出大数量的文件-->
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.3.0</version> <!--4.1.3-->
        </dependency>

 easpoi的api文档：

http://doc.wupaas.com/docs/easypoi/easypoi-1c2cp5rf3hnqv

老版本：

http://easypoi.mydoc.io/#text_202975

新版本才有下拉框相关api。

 

标签：return,excel,导出,new,list,import,easypoi,下拉框
From： https://www.cnblogs.com/super-chao/p/16955065.html