EasyPoi导入导出案例

POM文件引入

 
        
            cn.afterturn
            easypoi-base
            4.3.0
        
        
            cn.afterturn
            easypoi-web
            4.3.0
        
        
            cn.afterturn
            easypoi-annotation
            4.3.0
        
        
            com.alibaba
            easyexcel
            2.1.3
        
        

创建导入导出VO

package com.wl.ltx.dealer.sql.vo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.alibaba.fastjson.annotation.JSONField;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.util.Date;


@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="ExportVo对象", description="ExportVo对象")
public class ExportVo implements Serializable {
    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "ID")
    private String id;

   
    @ApiModelProperty(value = "银行联号")
    @Excel(name = "银行联号",width = 20)
    private String affiliated;

    @ApiModelProperty(value = "公私标识")
    @Excel(name = "公私标识",replace = { "私户_1", "公户_2"},width = 20)
    private String pubPriSign;

    @ApiModelProperty("有效起始日期")
    @Excel(name = "有效起始日期",format = "yyyy-MM-dd",width = 20)
    private Date bkValidFrom;

    @ApiModelProperty("有效结束日期")
    @Excel(name = "有效结束日期",format = "yyyy-MM-dd",width = 20)
    private Date bkValidTo;

}

@ApiOperation(value = "导入", notes = "导入", httpMethod = "POST")
    @RequestMapping(value = "/import", method = {RequestMethod.POST})
    @Transactional(rollbackFor = Exception.class)
    public ResponseEntity import(@RequestParam(value = "file",required = false) MultipartFile file){
        ImportParams importParams = new ImportParams();
        // 数据处理
        importParams.setHeadRows(1);
        importParams.setTitleRows(1);
        try {
          
            List list = ExcelImportUtil.importExcel(file.getInputStream(), ExportVo.class, importParams);
            if(list.size()==0){
                return RestResponse.createFailRes("上传0条请按照模板格式上传");
            }else {
               
               //自行处理导入的数据做操作.....
            }
            return RestResponse.createSuccessRes("导入成功");
        } catch (Exception e) {
            e.printStackTrace();
            return RestResponse.createFailRes(e.getMessage());
        }
    }

导出 

 @ApiOperation(value = "导出", notes = "导出")
    @RequestMapping(value = "/export", method = {RequestMethod.POST})
    public void export(@RequestBody ExportVo param, HttpServletResponse response) {
        
        try {
            List exportDealerBankVoList = infoService.queryExport(param);//查询
            EaspoiExcelUtils.exportExcel(exportDealerBankVoList,"导出","导出",ExportVo.class,"导出", response);
        } catch (Exception e) {
            e.printStackTrace();
            LoggerFactory.getLogger(this.getClass()).error(e.getMessage());
        }
    }

附一个工具类

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 org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


public class EaspoiExcelUtils {
    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.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName+".xls", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                response.getOutputStream().flush();
                response.getOutputStream().close();
            } catch (IOException var14) {
                var14.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){
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        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){
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }
    public static void downLoadZip(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setContentType("application/octet-stream ");
            response.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
            response.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));
            response.setCharacterEncoding("UTF-8");

            OutputStream out = response.getOutputStream();
            ZipOutputStream zos = new ZipOutputStream(out);
            //导出的文件名称
            String entryName = fileName + ".xlsx";
            ZipEntry entry = new ZipEntry(entryName);
            //编写新的zip条目，并将流定位到条目数据的开头
            zos.putNextEntry(entry);
            //workBook.write会指定关闭数据流，直接用workbook.write(zos)，下次就会抛出zos已被关闭的异常，所以用ByteArrayOutputStream来拷贝一下。
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //workbook写入bos
            workbook.write(bos);
            //bos写入zos
            bos.writeTo(zos);
            zos.closeEntry();

            if(zos != null) {
                zos.flush();
                zos.close();
            }
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                response.getOutputStream().flush();
                response.getOutputStream().close();
            } catch (IOException var14) {
                var14.printStackTrace();
            }

        }
    }

}

多shell 工具类

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.Workbook;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class WorkBookUtils {
    
    public static Workbook mutiSheet(List> mapListList){
        Workbook workbook = null;
        workbook = ExcelExportUtil.exportExcel(mapListList,ExcelType.HSSF);
        return workbook;
    }
    public static Map createOneSheet(ExportParams exportParams,Class clazz,List data){
        Map map = new HashMap<>();
        map.put("title",exportParams);//new ExportParams("title"+i, "sheetName"+i, ExcelType.XSSF)
        map.put("entity", clazz);
        map.put("data",data);
        return map;
    }
    
    public static Map createOneSheet(String sheetName,String title,Class clazz,List data){
        ExportParams exportParams = new ExportParams(title,sheetName, ExcelType.HSSF);
        return createOneSheet(exportParams,clazz,data);
    }


    
    public static Workbook mutiSheetXSSF(List> mapListList){
        Workbook workbook = null;
        workbook = ExcelExportUtil.exportExcel(mapListList,ExcelType.XSSF);
        return workbook;
    }
    
    public static Map createOneSheetXSSF(String sheetName,String title,Class clazz,List data){
        ExportParams exportParams = new ExportParams(title,sheetName, ExcelType.XSSF);
        return createOneSheet(exportParams,clazz,data);
    }
}

多shell 导出

@ApiOperation(value = "多shell导出", notes = "多shell导出")
    @RequestMapping(value = "/exportShell", method = {RequestMethod.POST})
    public void exportShell(@RequestBody ExportVo param, HttpServletResponse response) {
        
        try {
           
            List> lists = new ArrayList<>();
            List vo1List = infoService.query1Export(param);
            List vo2List = infoService.query2Export(param);

            lists.add(WorkBookUtils.createOneSheet("shell1导出", "shell1导出", ExportShell1Vo.class, vo1List ));
            lists.add(WorkBookUtils.createOneSheet("shell2导出", "shell2导出", ExportShell2Vo.class, vo2List));
            Workbook workbook = WorkBookUtils.mutiSheet(lists);
            if (ObjectUtils.isNotEmpty(workbook)){
                EaspoiExcelUtils.downLoadExcel("导出", response, workbook);
            }
        } catch (Exception e) {
            e.printStackTrace();
            LoggerFactory.getLogger(this.getClass()).error(e.getMessage());
        }
    }