EasyPoi工具使用

EasyPoi实现Excel导入导出（多sheet）

首先导入jar包：

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>19.0</version>
        </dependency>

封装实体类信息：

UserExcel：

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("userExcel")
public class UserExcel implements Serializable {
 
    /**
     * 名称
     */
    @Excel(name = "姓名",orderNum = "1")
    private String name;
    /**
     * 年龄
     */
    @Excel(name = "年龄",orderNum = "2",groupName = "基本信息")
    private Integer age;
 
    /**
     * 性别
     */
    @Excel(name = "性别",orderNum = "2",replace = {"男_M", "女_F","未知_X"},groupName = "基本信息")
    private String sex;
 
 
    /**
     * 手机
     */
    @Excel(name = "手机",orderNum = "3",width = 20,groupName = "联系方式")
    private String mobilePhone;
 
    /**
     * 邮箱
     */
    @Excel(name = "邮箱",orderNum = "4",width = 20,groupName = "联系方式", isWrap=true)
    private String email;
 
    /**
     * 昵称
     */
    @Excel(name = "昵称",orderNum = "5",groupName = "其他信息")
    private String nickName;
 
    @Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "6",groupName = "其他信息")
    private Date birth;
}

DeptExcel：

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("deptExcel")
public class DeptExcel implements Serializable {
 
    /**
     * 部门代码
     */
    @Excel(name = "部门代码", orderNum = "2", width = 20, needMerge = true)
    private String code;
    /**
     * 部门名称
     */
    @Excel(name = "部门名称", orderNum = "1", width = 20, needMerge = true)
    private String name;
 
    @ExcelCollection(name = "员工信息", orderNum = "3")
    private List<UserExcel> users;
 
 
}

excel导入导出工具类:

package com.glodon.single.domain.service.utils;

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.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import com.glodon.single.domain.dto.table.impl.ConstructionContentTableDTO;
import com.glodon.single.infrastructure.utils.IndexStringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author zhangbh-b
 * @create 2020-03-09 22:28
 * @description excel导入导出工具
 */
public class ExcelUtils {

    /**
     * 得到workbook对象
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //这样写  excel 能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is = file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }

    /**
     * excel 导出
     *
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     * @param isCreateHeader 是否创建表头
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list      数据
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  文件名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * 导出多张excel
     *
     * @param list
     * @param title
     * @param sheetName
     * @param pojoClass
     * @param fileName
     * @param response
     * @throws IOException
     */
    public static Workbook getWorkBook(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, sheetName, ExcelType.XSSF), pojoClass, list);
        return workbook;
    }

    /**
     * excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * @param columnList  表头
     * @param formContent 表单内容
     * @param fileName    文件名
     * @param response
     */
    public static void exportExcelList(List<ExcelExportEntity> columnList, List<Map<String, Object>> formContent, String title, String sheetName, String fileName, HttpServletResponse response) throws IOException {
        ExportParams exportParams =new ExportParams(title, sheetName, ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, columnList, formContent);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            response.setHeader("filename", URLEncoder.encode(fileName, "utf-8") + "." + ExcelTypeEnum.XLSX.getValue());
            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (IndexStringUtil.isEmpty(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param file       excel文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerfiy 是否检验excel内容
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerfiy  是否检验excel内容
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        params.setNeedVerfiy(needVerfiy);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }
}

示例

excel导入导出service：

@Service
@Slf4j
public class ExcelService {
 
 
    @Autowired
    private RbacUserService userService;
 
    /**
     * 单sheet导出
     *
     * @param response
     */
    public void export(HttpServletResponse response) {
        // 模拟从数据库获取需要导出的数据
        List<RbacUser> personList = userService.findAll();
        List<UserExcel> userExcelList =  CollectionCopyUtil.copyList(personList,UserExcel.class);
        //导出操作
        ExcelUtil.exportExcel(userExcelList,"用户列表","用户", UserExcel.class,"用户信息"+ DateUtil.date2Str(new Date(),"YYYY-MM-dd-HH-mm") +".xls",response);
 
    }
 
    /**
     * 多sheet导出示例
     *
     * @return
     */
    public void exportSheets(HttpServletResponse response) {
        // 准备测试数据
        List<UserExcel> userExcels = new ArrayList<>();
        UserExcel userExcel = new UserExcel();
        userExcel.setName("gourd");
        userExcel.setAge(23);
        userExcel.setSex("M");
        userExcel.setMobilePhone("10086");
        userExcel.setEmail("163.com");
        userExcel.setNickName("gourd");
        userExcel.setBirth(new Date());
        userExcels.add(userExcel);
        UserExcel userExcel2 = new UserExcel();
        userExcel2.setName("葫芦");
        userExcel2.setAge(23);
        userExcel2.setSex("X");
        userExcel2.setMobilePhone("110");
        userExcel2.setEmail("136@163.com");
        userExcel2.setNickName("gourd");
        userExcel2.setBirth(new Date());
        userExcels.add(userExcel2);
        List<DeptExcel> deptExcels = new ArrayList<>();
        DeptExcel deptExcel = new DeptExcel();
        deptExcel.setCode("T-01");
        deptExcel.setName("技术一部");
        deptExcel.setUsers(userExcels);
        deptExcels.add(deptExcel);
        DeptExcel deptExcel2 = new DeptExcel();
        deptExcel2.setCode("T-02");
        deptExcel2.setName("技术二部");
        deptExcel2.setUsers(userExcels);
        deptExcels.add(deptExcel2);
 
        // 创建参数对象
        ExportParams exportParams1 = new ExportParams();
        // 设置sheet得名称
        exportParams1.setSheetName("部门人员信息");
        ExportParams exportParams2 = new ExportParams();
        exportParams2.setSheetName("用户信息");
 
        // 创建sheet1使用得map
        Map<String, Object> deptDataMap = new HashMap<>(4);
        // title的参数为ExportParams类型
        deptDataMap.put("title", exportParams1);
        // 模版导出对应得实体类型
        deptDataMap.put("entity", DeptExcel.class);
        // sheet中要填充得数据
        deptDataMap.put("data", deptExcels);
 
        // 创建sheet2使用得map
        Map<String, Object> userDataMap = new HashMap<>(4);
        userDataMap.put("title", exportParams2);
        userDataMap.put("entity", UserExcel.class);
        userDataMap.put("data", userExcels);
        // 将sheet1和sheet2使用得map进行包装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(deptDataMap);
        sheetsList.add(userDataMap);
        // 执行方法
        Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
        ExcelUtil.downLoadExcel("用户信息"+ DateUtil.date2Str(new Date(),"YYYY-MM-dd-HH-mm") +".xls",response,workbook);
    }
 
    /**
     * 单sheet导入
     *
     * @param file
     */
    public BaseResponse importExcel(MultipartFile file) {
        ImportParams importParams = new ImportParams();
        // 距离表头中间有几行不要的数据
        importParams.setHeadRows(1);
        // 表头在第几行
        importParams.setTitleRows(1);
        // 需要验证
        importParams.setNeedVerfiy(true);
        try {
            ExcelImportResult<RbacUser> result = ExcelImportUtil.importExcelMore(file.getInputStream(), RbacUser.class,
                    importParams);
            List<RbacUser> successList = result.getList();
            for (RbacUser demoExcel : successList) {
                System.out.println(demoExcel);
            }
        }catch (Exception e) {
            log.error("导入失败:{}",e);
            return BaseResponse.failure("导入失败！请检查导入文档的格式是否正确");
        }
        return BaseResponse.ok("导入成功");
    }
 
 
    /**
     * 多sheet导入
     *
     * @param file
     * @return
     * @throws IOException
     */
    public BaseResponse importSheets(MultipartFile file){
        try {
            // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
            Workbook workBook = ExcelUtil.getWorkBook(file);
            StringBuilder sb=new StringBuilder();
            ImportParams params = new ImportParams();
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                // 表头在第几行
                params.setTitleRows(0);
                // 距离表头中间有几行不要的数据
                params.setStartRows(1);
                // 第几个sheet页
                params.setStartSheetIndex(numSheet);
                // 验证数据
                params.setNeedVerfiy(true);
                if(numSheet==0){
                    ExcelImportResult<RbacUser> result =ExcelImportUtil.importExcelMore(file.getInputStream(),
                            RbacUser.class, params);
                    // 校验是否合格
                    if(result.isVerfiyFail()){
                        // 不合格的数据
                        List<RbacUser> errorList = result.getList();
                        // 拼凑错误信息,自定义
                        for(int i=0;i<errorList.size();i++){
                            ExcelUtil.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "用户信息不合法");
                        }
                    }
                    // 合格的数据
                    List<RbacUser> successList = result.getList();
                    // 业务逻辑
 
                }else if(numSheet==1){
                    ExcelImportResult<RbacDepart> result =ExcelImportUtil.importExcelMore(file.getInputStream(),
                            RbacDepart.class, params);
                    // 校验是否合格
                    if(result.isVerfiyFail()){
                        // 不合格的数据
                        List<RbacDepart> errorList = result.getList();
                        // 拼凑错误信息,自定义
                        for(int i=0;i<errorList.size();i++){
                            ExcelUtil.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "部门信息不合法");
                        }
                    }
                    // 校验合格的数据
                    List<RbacDepart> successList = result.getList();
                    // 业务逻辑
                }
 
            }
        } catch (Exception e) {
            log.error("导入失败:{}",e);
            return BaseResponse.failure("导入失败！请检查导入文档的格式是否正确");
        }
        return BaseResponse.ok("导入成功！");
    }
 
}

controller调用：

@RestController
@RequestMapping("/excel")
@Api(tags = "excel", description = "excel控制器")
@Slf4j
public class ExcelController {
 
 
    @Autowired
    private ExcelService excelService;
 
    @GetMapping("/single")
    @ApiOperation(value="导出")
    public void export(HttpServletResponse response) {
        excelService.export(response);
    }
 
    @GetMapping("/sheets")
    @ApiOperation(value="导出多个sheet")
    public void exportSheets(HttpServletResponse response) {
        excelService.exportSheets(response);
    }
 
    @PostMapping("/single")
    @ApiOperation(value="导入")
    public BaseResponse importExcel(@RequestParam("file") MultipartFile file) {
        return excelService.importExcel(file);
    }
 
 
    @PostMapping(value = "/sheets")
    @ApiOperation(value="多sheet导入")
    public BaseResponse importSheets(MultipartFile file){
        return excelService.importSheets(file);
    }
 
}

如果你使用的POI请参考：blog.csdn.net/HXNLYW/arti…

@Excel注解属性解释：

更多请参考：easypoi.mydoc.io/

EasyPoi动态隐藏导出列-反射实现

我们使用的注解版导出的@Excel，官方文档中说明很详细，默认大家都会用；

举个例子：

@Data
@ExcelTarget("TestExcle")
public class TestExcle implements Serializable {
    private static final long serialVersionUID = 4152437113488964399L;

    @Excel(name = "名称")
    private String name;
    @Excel(name = "年龄", isColumnHidden = true))
    private String age;
    @Excel(name = "学校")
    private String school;
    @Excel(name = "状态", replace = { "毕业_1", "在校_2" })
    private Integer status;
}

我们需要用到的是EasyPoi官方提供的这个属性isColumnHidden

// 点击@Excel注解进去看到源码
/**
 *  是否需要隐藏该列
 * @return
 */
public boolean isColumnHidden() default  false;

可以看到，isColumnHidden中提供的默认值是false，也就是默认全部导出，不隐藏。加入我的权限是学生（ST），在登陆教务系统时，导出班级学生信息时，不想让学生看到各班同学的年龄情况，可以把他设置为true；这样所有导出都没有学生列，但是如果教师（TC）导出时是可以看到年龄信息的。

思路：

JAVA反射机制是在运行状态中，对于任意一个实体类，都能够知道这个类的所以属性和方法；对于任意一个对象，都能够调用它的任意方法和属性；这种动态获取信息以及动态调用对象方法的功能成为java语言的反射姬智。

根据反射我们可以拿到一个类的所有属性和方法，同理，注解也是一个类，也是可以拿到它的属性和方法，拿到之后就好办了，直接修改它的默认值，然后根据每个角色调整，就可以达到一个类实现动态导出的目的。

以上面为例，动态方法如下：

/**
     * 修改指定列isColumnHidden属性
     * @param columnName
     * @param target
     * @param pojoClass
     * @param <T>
     * @throws Exception
     */
    public static <T> void hideColumn(String columnName, Boolean target, Class<T> pojoClass) throws Exception {
        if (pojoClass == null) {
            throw new RuntimeException("目标类未找到");
        }
        if (IndexStringUtil.isEmpty(columnName)) {
            throw new RuntimeException("字段不能为空");
        }
        if (target == null) {
            target = true;
        }
        //获取目标对象的属性值
        Field field = pojoClass.getDeclaredField(columnName);
        //获取注解反射对象
        Excel excelAnnotation = field.getAnnotation(Excel.class);
        //获取代理
        InvocationHandler invocationHandler = Proxy.getInvocationHandler(excelAnnotation);
        Field excelField = invocationHandler.getClass().getDeclaredField("memberValues");
        excelField.setAccessible(true);
        Map memberValues = (Map) excelField.get(invocationHandler);
        memberValues.put("isColumnHidden", target);
    }

**注意：**这里导出隐藏仅仅是隐藏而已，拖动单元格还是会显示出来的，如果想彻底不导出的话还是定义另外的对象吧；