1：测试数据量两百二十多万，Excel2007，一个Excel多个sheet

2：导入maven依赖包

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beta5</version>
</dependency>

3：实体类添加注解。注意事项：1：必须继承BaseRowModel。2：@ExcelProperty必须写，其中value是要导出到Excel的表头名字自己起，index：代表某一列从0开始排序，由于实体类字段比较多，所以在导出时会自动忽略没有加注解的字段

public class TaskStoreItemDTO extends BaseRowModel{
	
@ExcelProperty(value = "id",index = 0)
private String id;
	
@ExcelProperty(value = "replenish_item_store_id",index = 1)
private String replenishItemStoreId;
	
@ExcelProperty(value = "tab_nbr",index = 2)
private String tabNbr;

}

4：业务层书写。1：aa：代表要导出的路径：例：D:/test/.....随意。2：writer.write(）方法可以改变，因为我的是两百万的数据所以这么写，你要是少可以把代码改一下把百万级中分sheet的代码删除掉。下边给大家截图修改那些。10000：这个是一个sheet要展示多少条数据

//查询数据库返回的数据集合
List<TaskStoreItemDTO> exportList = mapper.export(taskId);

//导出开始，生成EXCEL并指定输出路径
OutputStream out=null;
try {
	String aa=serviceConfig.getExportFeedbackPath()+"任务信息详情表.xlsx";
	out = new FileOutputStream(aa);
	ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
	//测试导出200万数据
	//List<TaskStoreItemDTO> exportList = mapper.test();
	List<List<TaskStoreItemDTO>> taskStoreItemList = Lists.partition(exportList, 1000000);
	//一个sheet一百万调数据，自动分页
	for(int i=0;i<taskStoreItemList.size();i++) {
		// 设置SHEET
		Sheet sheet = new Sheet((i+1), 0,TaskStoreItemDTO.class,"sheet"+(i+1),null);
		writer.write(taskStoreItemList.get(i), sheet);
		}
	    writer.finish();
	} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				out.flush();
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

5：可以修改的数据

第一张截图是在修改第二张截图后的操作：三个参数：1一个sheet，从0行开始，继承BaseRowModel的实体类，必须写不然找不到要导出的表头。下边是设置这个sheet的名字。在修改第二张截图后writer.write(）方法可以改变，自己writer.的时候能用的方法就会展示。自己选择适合自己的就可以了。





6：导出结果，数据就不展示了



7：由于easyExcel是阿里巴巴的所以还可去查看官方给的例子。给大家粘贴几个写的比较好的导出地址，如有问题欢迎大家留言交流。

https://www.cnblogs.com/technologykai/articles/10984878.html

https://blog.csdn.net/Java_Mrsun/article/details/85678028

 

本博客只供大家学习交流，禁止使用在其他用途。对于使用在其他用途的自行承担责任与本博客无关，最终解释权归本人所有

Maven依赖如下：

       <dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.1.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>

Controller：

package com.excel.demo.controller;


import com.alibaba.excel.EasyExcel;
import com.excel.demo.vo.LedgerreRealFeeVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author :Hj
 * @date 2021/2/24 10:50
 */
@RestController
@RequestMapping("/person")
public class PersonControl {
    @Autowired
    private LedgerRealFeeService feeService;

    @RequestMapping(value = "/getData")
    public String test(){
        //获取数据
        List<LedgerreRealFeeVo> realFeeVos= feeService.getDataVo();
        String filename = "D:\\Data\\Data.xlsx";
        long start = System.currentTimeMillis();
        //关键代码
        EasyExcel.write(filename,LedgerreRealFeeVo.class).sheet("数据").doWrite(realFeeVos);
        long finish = System.currentTimeMillis();
        long timeElapsed=finish-start;
        System.out.print(timeElapsed);

        return "sucess";
    }

}


VO:

package com.excel.demo.vo;

import com.alibaba.excel.annotation.ExcelProperty;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @author :Hj
 * @date 2021/2/24 10:47
 */
public class LedgerreRealFeeVo {
    @ExcelProperty(value = "业务号")
    private Long sno;
    @ExcelProperty(value = "任务号")
    private String taskId;
    @ExcelProperty(value = "类型")
    private String certiType;
    @ExcelProperty(value = "人员代码")
    private String agentCode;
    @ExcelProperty(value = "机构")
    private String comCode;
    @ExcelProperty(value = "业务性质")
    private String businessNature;
    @ExcelProperty(value = "团队代码")
     private String teamCode;
    @ExcelProperty(value = "团队名称")
     private String teamName;
    @ExcelProperty(value = "日期")
    private Date realPayDate;
    @ExcelProperty(value = "afee")
    private BigDecimal afee;
    @ExcelProperty(value = "bfee")
    private BigDecimal bfee;
    @ExcelProperty(value = "cfee")
    private BigDecimal cfee;

    public Long getSno() {
        return sno;
    }

    public void setSno(Long sno) {
        this.sno = sno;
    }

    public String getTaskId() {
        return taskId;
    }

    public void setTaskId(String taskId) {
        this.taskId = taskId;
    }

    public String getCertiType() {
        return certiType;
    }

    public void setCertiType(String certiType) {
        this.certiType = certiType;
    }

    public String getAgentCode() {
        return agentCode;
    }

    public void setAgentCode(String agentCode) {
        this.agentCode = agentCode;
    }

    public String getComCode() {
        return comCode;
    }

    public void setComCode(String comCode) {
        this.comCode = comCode;
    }

    public String getBusinessNature() {
        return businessNature;
    }

    public void setBusinessNature(String businessNature) {
        this.businessNature = businessNature;
    }

    public String getTeamCode() {
        return teamCode;
    }

    public void setTeamCode(String teamCode) {
        this.teamCode = teamCode;
    }

    public String getTeamName() {
        return teamName;
    }

    public void setTeamName(String teamName) {
        this.teamName = teamName;
    }

    public Date getRealPayDate() {
        return realPayDate;
    }

    public void setRealPayDate(Date realPayDate) {
        this.realPayDate = realPayDate;
    }

    public BigDecimal getAfee() {
        return afee;
    }

    public void setAfee(BigDecimal afee) {
        this.afee = afee;
    }

    public BigDecimal getBfee() {
        return bfee;
    }

    public void setBfee(BigDecimal bfee) {
        this.bfee = bfee;
    }

    public BigDecimal getCfee() {
        return cfee;
    }

    public void setCfee(BigDecimal cfee) {
        this.cfee = cfee;
    }
}


1000960行数据耗时：





 

先看sql语句：查询全部数据时间14秒



分页查询10万数据时间1秒左右


连接点，分组都加了索引，没加索引可能要10来分钟，

sql详情：



对于百万级的数据如果一下查出来是会内存溢出的，所以需要分页查出来，分页放到excel表格中
直接上我写的代码：excel表头类就不沾出来了，每个人的都不一样
/**
     * 导出excel
     * */
    @RequestMapping("/export")
    @RequiresPermissions("b2b:contract:export")
    public void export(HttpServletResponse response) throws IOException {
        OutputStream outputStream = response.getOutputStream();
        String time = new SimpleDateFormat("yyyy-MM-dd-hh-mm-ss").format(new Date());
        //添加响应头信息
        response.setHeader("Content-disposition", "attachment; filename=" + "contract"+time+".xlsx");
        //设置类型
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        //设置头
        response.setHeader("Pragma", "No-cache");
        //设置头
        response.setHeader("Cache-Control", "no-cache");
        //设置日期头
        response.setDateHeader("Expires", 0);

        //获取总数据量
        Integer count = contractService.getCount(null);
        //如果总数据量多余10万，分页导出
        if(count>100000){
            //每页多少个
            int max=100000;
            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter=EasyExcel.write(outputStream).build();;
            for (int i=0;i<(count/max)+1;i++){
                List<ContractAndCompanyExcel> exportList = contractService.export(i *max,max);
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "合同" + (i + 1)).head(ContractAndCompanyExcel.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                excelWriter.write(exportList,writeSheet);
            }

            //刷新流
            excelWriter.finish();
        }else {
            List<ContractAndCompanyExcel> exportList = contractService.export(null,null);
            EasyExcel.write(outputStream,ContractAndCompanyExcel.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("合同").doWrite(exportList);
        }

        outputStream.flush();
        response.getOutputStream().close();

    }

实测：导出用时105秒左右



每个表10万条


我这个肯定不是最优的方法，不过能用，如果您有更好的办法，或是我有什么错误，欢迎大牛指出或评论区分享出来，供大家交流学习谢谢！

说明

由于某些原因系统jvm内存最大只能给到512，但是要导出百万数据该如何实现呢？传统的一次性导出肯定是不行的

优化
Excel导出基于  springboot , easyexcel

依赖：
<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.2.6</version>
		</dependency>

导出优化
基于自增id滚动式查询写入
@GetMapping("/standard-product-ext/export")
    public void productExtExport(HttpServletResponse response, StandardProductExtQuery query) throws Exception{

        String today = DateUtil.today();
        String fileName = "标准商品标签导入模板" + today;
        String fileNameCode = URLEncoder.encode(fileName, "UTF-8");

        response.setContentType("application/force-download");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileNameCode + ".xlsx");
        ExcelWriter writer = new ExcelWriterBuilder()
                .autoCloseStream(true)
                .file(response.getOutputStream())
                .head(DscStandardProductExtVO.class)
                .build();
        Integer lastBatchMaxId = 0;
        query.setLastBatchMaxId(lastBatchMaxId);
        WriteSheet writeSheet = new WriteSheet();
        writeSheet.setSheetName("标准商品标签导入模板" + today);
        List<DscStandardProductExtVO> productExt
        for (; ; ) {
            productExt = dictionaryService.getProductExt(query);
            if (productExt.isEmpty()) {
                writer.write(productExt, writeSheet);
                writer.finish();
                break;
            } else {
                lastBatchMaxId = productExt.stream().map(DscStandardProductExtVO::getId).max(Integer::compareTo).orElse(Integer.MAX_VALUE);
                query.setLastBatchMaxId(lastBatchMaxId);
                writer.write(productExt, writeSheet);
            }


        }
    }


dictionaryService.getProductExt(query) 的xml

SELECT
       *
        FROM
        base_drug
        where
        id > #{lastBatchMaxId}

导入优化

使用 easyexcel 监听器导出

@PostMapping("/standard-product-ext/import")
    public ResponseResult productExtImport(MultipartFile file) throws IOException {

        File localFile = new File(appSettings.getTempdir() + RandomStringUtils.randomAlphanumeric(12) + file.getOriginalFilename());
        // 将上传文件 写入到 localFile  本地文件，后续对 localFile 操作读取
        FileUtils.multipartFileToFile(file, localFile);
        try {
            ExcelUploadResult excelUploadResult = dictionaryService.updateBatchProductExtByFile(localFile);
            return ResponseUtils.success(excelUploadResult);
        } catch (DaoException e) {
            ResponseUtils.fail(ErrorCodeConstants.FAIL, "导入标准商品标签失败");
        }
        return null;

    }



dictionaryService.updateBatchProductExtByFile(localFile) 方法

public ExcelUploadResult updateBatchProductExtByFile(File excelFile) throws DaoException{
        StandardProductExtListener listener = new StandardProductExtListener(clearSearchDao,
                dscStandardProductExtDao, cosUtils, appSettings);
        try {
            EasyExcel.read(excelFile, DscStandardProductExtVO.class, listener).sheet().doRead();
        } catch (Exception e) {
            log.error("[标签导入]读取excel出错", e);
            throw new ServiceException("excel导入失败");
        } finally {
            if (excelFile.exists()) {
                excelFile.delete();
            }
        }
        int successTotal = listener.getSuccessTotal();
        int total = listener.getTotal();
        int error = listener.getError();
        ExcelUploadResult result =  error > 0 ? new ExcelUploadResult(total, successTotal,error, listener.getErrorFileUrl())
        : new ExcelUploadResult(successTotal, total, 0, null);
        return result;
    }


StandardProductExtListener


需要注意的是 StandardProductExtListener 不能被spirng管理，需要手动new，依赖spring的类通过构造方法注入，这里是官方说明的，如果给spirng管理会有什么问题暂时没有尝试

public class StandardProductExtListener extends AnalysisEventListener<DscStandardProductExtVO> {


    int successTotal = 0;
    int total = 0;
    int error = 0;
    String errorFileUrl;

    List<DscStandardProductExtVO> list = new ArrayList<>();

    ClearSearchDao clearSearchDao;

    DscStandardProductExtDao dscStandardProductExtDao;

    CosUtils cosUtils;

    AppSettings appSettings;

    List<DscStandardProductExtVO> errorList = Lists.newArrayList();

    public StandardProductExtListener(ClearSearchDao clearSearchDao, DscStandardProductExtDao dscStandardProductExtDao,
                                      CosUtils cosUtils, AppSettings appSettings) {
        this.clearSearchDao = clearSearchDao;
        this.dscStandardProductExtDao = dscStandardProductExtDao;
        this.cosUtils = cosUtils;
        this.appSettings = appSettings;
    }




    /**
     * 每条数据的解析
     * @param vo
     * @param analysisContext
     */
    @Override
    public void invoke(DscStandardProductExtVO vo, AnalysisContext analysisContext) {
        list.add(vo);
        if (list.size() > 3000) {
            try {
                doFile(list);
                total += list.size();
                list.clear();
            } catch (DaoException e) {
                e.printStackTrace();
            }
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        try {
            total += list.size();
            doFile(list);
            log.info("处理最后数据{}", total);
            errorFileUrl = updateErrorFile();
        } catch (DaoException e) {
            e.printStackTrace();
        }
    }

    public void doFile(List<DscStandardProductExtVO> readExcels) throws DaoException{
       // 业务逻辑处理
       }


}

总结
经过测试 导出百万数据 使用这两种优化没什么问题，只是导入速度可能有点慢，但是不会OOM，其次可能系统full gc会相对严重。