【easypoi】实现excel导入导出（多sheet）

太过爱你忘了你带给我的痛 2021-11-29 05:00 1882阅读 0赞

首先导入jar包：

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.0.3</version>
        <exclusions>
            <exclusion>
                <artifactId>guava</artifactId>
                <groupId>com.google.guava</groupId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>3.0.3</version> </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>3.0.3</version>
    </dependency>

封装实体类信息：

UserExcel：

import cn.afterturn.easypoi.excel.annotation.Excel;
    import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.io.Serializable;
    import java.util.Date;
    
    /**
     * @author gourd
     */
    @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：

import cn.afterturn.easypoi.excel.annotation.Excel;
    import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
    import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.io.Serializable;
    import java.util.List;
    
    /**
     * @author gourd
     */
    @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导入导出工具类：

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 lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang3.StringUtils;
    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.beans.PropertyDescriptor;
    import java.io.File;
    import java.io.IOException;
    import java.io.InputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.net.URLEncoder;
    import java.util.List;
    import java.util.Map;
    import java.util.NoSuchElementException;
    
    /**
     * @author gourd
     */
    @Slf4j
    public class ExcelUtil {
    
    
        public static boolean isExcel2003(String filePath)
        {
            return filePath.matches("^.+\\.(?i)(xls)$");
        }
    
        public static boolean isExcel2007(String filePath)
        {
            return filePath.matches("^.+\\.(?i)(xlsx)$");
        }
    
        /**
         * 得到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;
        }
    
        /**
         * 得到错误信息
         * @param sb
         * @param list
         * @param i
         * @param obj
         * @param name  用哪个属性名去表明不和规定的数据
         * @param msg
         * @throws Exception
         */
        public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throws Exception{
            Class clazz=obj.getClass();
            Object str=null;
            //得到属性名数组
            Field[] fields = clazz.getDeclaredFields();
            for(Field f : fields){
                if(f.getName().equals(name)){
                    //用来得到属性的get和set方法
                    PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
                    //得到get方法
                    Method getMethod=pd.getReadMethod();
                    str = getMethod.invoke(obj);
                }
            }
            if(i==0) {
                sb.append(msg + str + ";");
            }
            else if(i==(list.size()-1)) {
                sb.append(str + "</br>");
            }
            else {
                sb.append(str + ";");
            }
        }
    
    
        public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
            ExportParams exportParams = new ExportParams(title, sheetName);
            exportParams.setCreateHeadRows(isCreateHeader);
            defaultExport(list, pojoClass, fileName, response, exportParams);
    
        }
        public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
            defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
        }
        public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
            defaultExport(list, fileName, response);
        }
    
        private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
            if (workbook != null){
                downLoadExcel(fileName, response, workbook);
            }
    
        }
    
        public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
            try {
                // 告诉浏览器用什么软件可以打开此文件
                response.setHeader("content-Type", "application/vnd.ms-excel");
                //设置浏览器响应头对应的Content-disposition
                response.setHeader("Content-Disposition",
                        "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                //编码
                response.setCharacterEncoding("UTF-8");
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
            Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
            if (workbook != null){
                downLoadExcel(fileName, response, workbook);
            }
    
        }
    
        public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
            if (StringUtils.isBlank(filePath)){
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
            }catch (NoSuchElementException e){
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
            return list;
        }
        public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
            if (file == null){
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            }catch (NoSuchElementException e){
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
            return list;
        }
    
    }

excel导入导出service：

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.result.ExcelImportResult;
    import com.gourd.common.data.BaseResponse;
    import com.gourd.common.rbac.entity.RbacDepart;
    import com.gourd.common.rbac.entity.RbacUser;
    import com.gourd.common.rbac.service.RbacUserService;
    import com.gourd.common.utils.CollectionCopyUtil;
    import com.gourd.common.utils.DateUtil;
    import com.gourd.common.utils.ExcelUtil;
    import com.gourd.file.excel.entity.DeptExcel;
    import com.gourd.file.excel.entity.UserExcel;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.*;
    
    /**
     * @author gourd
     */
    @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<UserExcel> result = ExcelImportUtil.importExcelMore(file.getInputStream(), UserExcel.class,
                        importParams);
                List<UserExcel> successList = result.getList();
                for (UserExceldemoExcel : 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<UserExcel> result =ExcelImportUtil.importExcelMore(file.getInputStream(),
                                UserExcel.class, params);
                        // 校验是否合格
                        if(result.isVerfiyFail()){
                            // 不合格的数据
                            List<UserExcel> errorList = result.getList();
                            // 拼凑错误信息,自定义
                            for(int i=0;i<errorList.size();i++){
                                ExcelUtil.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "用户信息不合法");
                            }
                        }
                        // 合格的数据
                        List<UserExcel > successList = result.getList();
                        // 业务逻辑
    
                    }else if(numSheet==1){
                        ExcelImportResult<DeptExcel> result =ExcelImportUtil.importExcelMore(file.getInputStream(),
                                DeptExcel.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<DeptExcel> successList = result.getList();
                        // 业务逻辑
                    }
    
                }
            } catch (Exception e) {
                log.error("导入失败:{}",e);
                return BaseResponse.failure("导入失败！请检查导入文档的格式是否正确");
            }
            return BaseResponse.ok("导入成功！");
        }
    
    }

Controller掉用：

import com.gourd.common.data.BaseResponse;
    import com.gourd.file.excel.service.ExcelService;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * @author gourd
     */
    @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);
        }
    
    }

调用export-sheets接口导出结果截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70 1][]

如果你使用的POI请参考：[https://blog.csdn.net/HXNLYW/article/details/96830248][https_blog.csdn.net_HXNLYW_article_details_96830248]

@Excel注解属性解释：

<table> 
 <thead> 
  <tr> 
   <th>属性</th> 
   <th>类型</th> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>name</td> 
   <td>String</td> 
   <td>null</td> 
   <td>列名</td> 
  </tr> 
  <tr> 
   <td>needMerge</td> 
   <td>boolean</td> 
   <td>fasle</td> 
   <td>纵向合并单元格</td> 
  </tr> 
  <tr> 
   <td>orderNum</td> 
   <td>String</td> 
   <td>"0"</td> 
   <td>列的排序,支持name_id</td> 
  </tr> 
  <tr> 
   <td>replace</td> 
   <td>String[]</td> 
   <td>{}</td> 
   <td>值得替换 导出是{a_id,b_id} 导入反过来</td> 
  </tr> 
  <tr> 
   <td>savePath</td> 
   <td>String</td> 
   <td>"upload"</td> 
   <td>导入文件保存路径</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>int</td> 
   <td>1</td> 
   <td>导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本</td> 
  </tr> 
  <tr> 
   <td>width</td> 
   <td>double</td> 
   <td>10</td> 
   <td>列宽</td> 
  </tr> 
  <tr> 
   <td>height</td> 
   <td>double</td> 
   <td>10</td> 
   <td>列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意</td> 
  </tr> 
  <tr> 
   <td>isStatistics</td> 
   <td>boolean</td> 
   <td>fasle</td> 
   <td>自动统计数据,在追加一行统计,把所有数据都和输出这个处理会吞没异常,请注意这一点</td> 
  </tr> 
  <tr> 
   <td>isHyperlink</td> 
   <td>boolean</td> 
   <td>false</td> 
   <td>超链接,如果是需要实现接口返回对象</td> 
  </tr> 
  <tr> 
   <td>isImportField</td> 
   <td>boolean</td> 
   <td>true</td> 
   <td>校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id</td> 
  </tr> 
  <tr> 
   <td>exportFormat</td> 
   <td>String</td> 
   <td>""</td> 
   <td>导出的时间格式,以这个是否为空来判断是否需要格式化日期</td> 
  </tr> 
  <tr> 
   <td>importFormat</td> 
   <td>String</td> 
   <td>""</td> 
   <td>导入的时间格式,以这个是否为空来判断是否需要格式化日期</td> 
  </tr> 
  <tr> 
   <td>format</td> 
   <td>String</td> 
   <td>""</td> 
   <td>时间格式,相当于同时设置了exportFormat 和 importFormat</td> 
  </tr> 
  <tr> 
   <td>databaseFormat</td> 
   <td>String</td> 
   <td>"yyyyMMddHHmmss"</td> 
   <td>导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出</td> 
  </tr> 
  <tr> 
   <td>numFormat</td> 
   <td>String</td> 
   <td>""</td> 
   <td>数字格式化,参数是Pattern,使用的对象是DecimalFormat</td> 
  </tr> 
  <tr> 
   <td>imageType</td> 
   <td>int</td> 
   <td>1</td> 
   <td>导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的</td> 
  </tr> 
  <tr> 
   <td>suffix</td> 
   <td>String</td> 
   <td>""</td> 
   <td>文字后缀,如% 90 变成90%</td> 
  </tr> 
  <tr> 
   <td>isWrap</td> 
   <td>boolean</td> 
   <td>true</td> 
   <td>是否换行 即支持\n</td> 
  </tr> 
  <tr> 
   <td>mergeRely</td> 
   <td>int[]</td> 
   <td>{}</td> 
   <td>合并单元格依赖关系,比如第二列合并是基于第一列 则{1}就可以了</td> 
  </tr> 
  <tr> 
   <td>mergeVertical</td> 
   <td>boolean</td> 
   <td>fasle</td> 
   <td>纵向合并内容相同的单元格</td> 
  </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70]: /images/20211129/6dc8e3f35fb7452aaaac13b6ff27077b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70 1]: /images/20211129/a9c20828c7194db28557b1743d81f4fe.png
[https_blog.csdn.net_HXNLYW_article_details_96830248]: https://blog.csdn.net/HXNLYW/article/details/96830248