EasyPoi实现查询数据库并导出到Excel中，包含大批量导出

前言：因公司需要导出大批量数据，普通导出的话，一直内存溢出，所以记录下自己用EasyPoi导出Excel代码，导出效率贼高，主要要优化sql的查询。

实测导出14M的Excel不超过30秒

实现代码：

1.首先导入easypoi依赖jar包：

 <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>4.0.0</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-web</artifactId>
      <version>4.0.0</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
      <version>4.0.0</version>
    </dependency>

2.编写实体类

public class Test{

    @Excel(name = "姓名", orderNum = "0")
    private String name;

    @Excel(name = "性别", replace = {"男_1", "女_0"}, orderNum = "1")
    private String sex;


    
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

}

3.编写接口方法 

继承IExcelExportServer接口，也可以在实现类中直接继承

public interface ExcelExportService extend IExcelExportServer{


}

4.接口实现类 ：重点关照

public class ExcelExportServiceImpl implements ExcelExportService{

       @Override
	public List<Object> selectListForExcelExport(Object obj, int page) {

		//传参处理，支持转换
		Test test= (Test) obj;
         null;
        //此处可以写dao层分页查询的实现方法，page为当前第几页，分批次循环查询导入数据至excel中
        //obj:查询条件，page：当前第几页，pageSize：每页条数
        List<Test> testList = dao.xxx(obj,page,pageSize)
        //dao层结束
        List<Object> list = = new ArrayList<Object>();
        Test p = null;
        //循环读取数据并赋值
        for (int i = 0; i < testList; i++) {
            p = new Test ();
            p.setName("姓名[" + i + "]");
            p.setSex(String.valueOf(i % 2));
            list.add(p);
            p = null;
        }
        return list;
	}

}

5.controller调用

@Controller
@RequestMapping("file")
public class DemoController {
    @Resource(type = ExceEportService.class)
    private IExcelExportServer exceEportService;

    //普通数据量导出
    @RequestMapping("export2")
    public void export(ModelMap map, HttpServletRequest request, HttpServletResponse response){
        
       

        //自定义标题、sheet名、文件名
        FileUtil.exportExcel("这是标题","我是sheet名","最终导出的Excel名字，不带后缀",查询数据,Test.class,request,response);
    }
    //导入
    @RequestMapping("import")
    public void importExcel(MultipartFile file, HttpServletResponse response){

        //默认导入excel，注意：Person需要有空的构造函数
        List<?> Test= FileUtil.importExcel(file,Test.class,response);
        System.out.println(people.size());
    }


    
    /***大批量导出***/
    @RequestMapping(value = "/big-export")
    public void bigExport(HttpServletRequest request, HttpServletResponse response) {
        //查询条件
        Test test= new test("姓名","1");

        FileUtil.defaultExportBigExcelWithParams("人员测试",Test.class,exceEportService,test,request,response);
    }


}

6.工具类FileUtil

 

import cn.afterturn.easypoi.entity.vo.BigExcelConstants;
import cn.afterturn.easypoi.entity.vo.NormalExcelConstants;
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 cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import cn.afterturn.easypoi.view.PoiBaseView;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.ui.ModelMap;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;

public class FileUtil {

    static Logger logger = LoggerFactory.getLogger(FileUtil.class);

    public static final String DEFAULT_SHEET_NAME = "Sheet1";
    public static final String DEFAULT_FILE_NAME = "新建文件";

    public static void exportExcel(String title, String sheetName, String fileName,
                                   List<?> data, Class<?> pojoClass,
                                   HttpServletRequest request, HttpServletResponse response) {
        ModelMap modelMap = new ModelMap();
        sheetName = StringUtils.isBlank(sheetName) ? DEFAULT_SHEET_NAME : sheetName;
        fileName = StringUtils.isBlank(fileName) ? DEFAULT_FILE_NAME : fileName;
        ExportParams params = new ExportParams(title, sheetName, ExcelType.XSSF);
        // params.setFreezeCol(2);//冻结
        // 数据集合(导出的数据)
        modelMap.put(NormalExcelConstants.DATA_LIST, data);
        //导出实体
        modelMap.put(NormalExcelConstants.CLASS, pojoClass);
        //参数
        modelMap.put(NormalExcelConstants.PARAMS, params);
        //文件名称
        modelMap.put(NormalExcelConstants.FILE_NAME, fileName);

        PoiBaseView.render(modelMap, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
    }

    public static void defaultExportExcel(List<?> data, Class<?> pojoClass,
                                          HttpServletRequest request, HttpServletResponse response) {
        exportExcel(null, null, null, data, pojoClass, request, response);
    }

    public static <T> List<T> importExcel(MultipartFile empFile, Class<T> pojoClass, HttpServletResponse response) {

        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setNeedVerfiy(true);
        ExcelImportResult<T> result = null;
        try {
            result = ExcelImportUtil.importExcelMore(
                    empFile.getInputStream(),
                    pojoClass, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //获取正确导入的集合
        if (result == null) {
            return null;
        }
        return result.getList();

    }

    public static void exportBigExcel(String title, String sheetName, String fileName,
                                      Class<?> pojoClass, IExcelExportServer excelExportServer,
                                      HttpServletRequest request, HttpServletResponse response) {
        sheetName = StringUtils.isBlank(sheetName) ? DEFAULT_SHEET_NAME : sheetName;
        fileName = StringUtils.isBlank(fileName) ? DEFAULT_FILE_NAME : fileName;
        ModelMap modelMap = getModelMap(title, sheetName, fileName, pojoClass, excelExportServer, null);
        PoiBaseView.render(modelMap, request, response, BigExcelConstants.EASYPOI_BIG_EXCEL_VIEW);
    }

    public static void exportBigExcelWithParams(String title, String sheetName, String fileName,
                                      Class<?> pojoClass, IExcelExportServer excelExportServer,Object queryParams,
                                      HttpServletRequest request, HttpServletResponse response) {
        sheetName = StringUtils.isBlank(sheetName) ? DEFAULT_SHEET_NAME : sheetName;
        fileName = StringUtils.isBlank(fileName) ? DEFAULT_FILE_NAME : fileName;
        ModelMap modelMap = getModelMap(title, sheetName, fileName, pojoClass, excelExportServer, queryParams);
        PoiBaseView.render(modelMap, request, response, BigExcelConstants.EASYPOI_BIG_EXCEL_VIEW);
    }

    private static ModelMap getModelMap(String title, String sheetName, String fileName, Class<?> pojoClass, IExcelExportServer excelExportServer, Object queryParams) {
        ModelMap modelMap = new ModelMap();
        ExportParams params = new ExportParams(title, sheetName, ExcelType.XSSF);
//        params.setFreezeCol(2);
        modelMap.put(BigExcelConstants.CLASS, pojoClass);
        modelMap.put(BigExcelConstants.PARAMS, params);
        //就是我们的查询参数,会带到接口中,供接口查询使用
        modelMap.put(BigExcelConstants.DATA_PARAMS, queryParams == null ? Collections.EMPTY_MAP : queryParams);
        modelMap.put(BigExcelConstants.DATA_INTER, excelExportServer);
        modelMap.put(BigExcelConstants.FILE_NAME, fileName);
        return modelMap;
    }

    public static void defaultExportBigExcel(String title, Class<?> pojoClass, IExcelExportServer excelExportServer,
                                             HttpServletRequest request, HttpServletResponse response) {
        exportBigExcel(title, DEFAULT_SHEET_NAME, DEFAULT_FILE_NAME, pojoClass, excelExportServer,request, response);
    }

    public static void defaultExportBigExcelWithParams(String title, Class<?> pojoClass, IExcelExportServer excelExportServer,Object queryParams,
                                             HttpServletRequest request, HttpServletResponse response) {
        exportBigExcelWithParams(title, DEFAULT_SHEET_NAME, DEFAULT_FILE_NAME, pojoClass, excelExportServer, queryParams,request, response);
    }

    public static void main(String[] args) {

    }

}