easypoi excel导入导出（多sheet）

一、导入jar包


    cn.afterturn
    easypoi-base
    3.0.3
    
        
            guava
            com.google.guava
        
    


    cn.afterturn
    easypoi-web
    3.0.3 

    cn.afterturn
    easypoi-annotation
    3.0.3

二、封装实体类 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;
 

@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;
 

@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 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;
 

@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)$");
    }
 
    
    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;
    }
 
    
    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 + "
");
        }
        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> 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> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null){
            downLoadExcel(fileName, response, workbook);
        }
 
    }
 
    public static  List importExcel(String filePath,Integer titleRows,Integer headerRows, Class pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        importParams params = new importParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List 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  List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass){
        if (file == null){
            return null;
        }
        importParams params = new importParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List 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.*;
 

@Service
@Slf4j
public class ExcelService {
 
 
    @Autowired
    private RbacUserService userService;
 
    
    public void export(HttpServletResponse response) {
        // 模拟从数据库获取需要导出的数据
        List personList = userService.findAll();
        List userExcelList =  CollectionCopyUtil.copyList(personList,UserExcel.class);
        //导出操作
        ExcelUtil.exportExcel(userExcelList,"用户列表","用户", UserExcel.class,"用户信息"+ DateUtil.date2Str(new Date(),"YYYY-MM-dd-HH-mm") +".xls",response);
 
    }
 
    
    public void exportSheets(HttpServletResponse response) {
        // 准备测试数据
        List 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 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 deptDataMap = new HashMap<>(4);
        // title的参数为ExportParams类型
        deptDataMap.put("title", exportParams1);
        // 模版导出对应得实体类型
        deptDataMap.put("entity", DeptExcel.class);
        // sheet中要填充得数据
        deptDataMap.put("data", deptExcels);
 
        // 创建sheet2使用得map
        Map userDataMap = new HashMap<>(4);
        userDataMap.put("title", exportParams2);
        userDataMap.put("entity", UserExcel.class);
        userDataMap.put("data", userExcels);
        // 将sheet1和sheet2使用得map进行包装
        List> 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);
    }
 
    
    public baseResponse importExcel(MultipartFile file) {
        importParams importParams = new importParams();
        // 距离表头中间有几行不要的数据
        importParams.setHeadRows(1);
        // 表头在第几行
        importParams.setTitleRows(1);
        // 需要验证
        importParams.setNeedVerfiy(true);
        try {
            ExcelimportResult result = ExcelimportUtil.importExcelMore(file.getInputStream(), UserExcel.class,
                    importParams);
            List successList = result.getList();
            for (UserExceldemoExcel : successList) {
                System.out.println(demoExcel);
            }
        }catch (Exception e) {
            log.error("导入失败:{}",e);
            return baseResponse.failure("导入失败！请检查导入文档的格式是否正确");
        }
        return baseResponse.ok("导入成功");
    }
 
 
    
    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 result =ExcelimportUtil.importExcelMore(file.getInputStream(),
                            UserExcel.class, params);
                    // 校验是否合格
                    if(result.isVerfiyFail()){
                        // 不合格的数据
                        List errorList = result.getList();
                        // 拼凑错误信息,自定义
                        for(int i=0;i successList = result.getList();
                    // 业务逻辑
 
                }else if(numSheet==1){
                    ExcelimportResult result =ExcelimportUtil.importExcelMore(file.getInputStream(),
                            DeptExcel.class, params);
                    // 校验是否合格
                    if(result.isVerfiyFail()){
                        // 不合格的数据
                        List errorList = result.getList();
                        // 拼凑错误信息,自定义
                        for(int i=0;i 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;
 

@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接口导出结果截图：

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

easypoi excel导入导出（多sheet）

Java相关栏目本月热门文章