【Java】Excel百万级别数据的导入和导出【详细代码】

2024 年 8 月 26 日 9点热度 0人点赞 0条评论

代码层级结构

DurationAspect



import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.boot.SpringApplication;
import org.springframework.stereotype.Component;

import java.time.Duration;


@Component
@Aspect
public class DurationAspect {

    private static final Log logger = LogFactory.getLog(DurationAspect.class);

    @Around("execution(public void com.zhouyu.controller.SalariesController.exportExcel*(..))")
    public void exportExcel(ProceedingJoinPoint joinPoint) {
        long startTime = System.nanoTime();
        logger.info("开始导出：" + joinPoint.getSignature().getName());
        try {
            joinPoint.proceed();
        } catch (Throwable e) {
            throw new RuntimeException(e);
        } finally {
            Duration time = Duration.ofNanos(System.nanoTime() - startTime);
            logger.info("导出结束，消耗了：" + time.getSeconds() + "s");
        }

    }

    @Around("execution(public void com.zhouyu.controller.SalariesController.importExcel*(..))")
    public void importExcel(ProceedingJoinPoint joinPoint) {
        long startTime = System.nanoTime();
        logger.info("开始导入：" + joinPoint.getSignature().getName());
        try {
            joinPoint.proceed();
        } catch (Throwable e) {
            throw new RuntimeException(e);
        } finally {
            Duration time = Duration.ofNanos(System.nanoTime() - startTime);
            logger.info("导入结束，消耗了：" + time.getSeconds() + "s");
        }

    }
}

SalariesController



import com.zhouyu.service.ExportService;
import com.zhouyu.service.ImportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;



@RestController
public class SalariesController {

    @Resource
    private ExportService exportService;

    @Resource
    private ImportService importService;

    //方案一：查全表，写入一个sheet
    @GetMapping("export1")
    public void exportExcel1(HttpServletResponse response) throws IOException {
        exportService.exportExcel1(response);
    }

    //方案二：查全部，写入多个sheet
    @GetMapping("export2")
    public void exportExcel2(HttpServletResponse response) throws IOException {
        exportService.exportExcel2(response);
    }

    //方案三：分页查询，每页数据写入每个sheet
    @GetMapping("export3")
    public void exportExcel3(HttpServletResponse response) throws IOException {
        exportService.exportExcel3(response);
    }

    //方案四：多线程分页查询，每页数据写入多个sheet
    @GetMapping("export4")
    public void exportExcel4(HttpServletResponse response) throws IOException, InterruptedException {
        exportService.exportExcel4(response);
    }

    //excel的导入
    @PostMapping("import")
    public void importExcel(MultipartFile file) throws IOException {
        importService.importExcel(file);
        importService.importExcelAsync(file);
    }


}

Salaries



import org.springframework.beans.factory.InitializingBean;

import javax.annotation.PostConstruct;
import java.util.Date;

/**
 * salaries实体类
 * */
public class Salaries {

    private Integer empNo;
    private Integer salary;
    private Date fromDate;
    private Date toDate;

    public Integer getEmpNo() {
        return empNo;
    }

    public void setEmpNo(Integer empNo) {
        this.empNo = empNo;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }

    public Date getFromDate() {
        return fromDate;
    }

    public void setFromDate(Date fromDate) {
        this.fromDate = fromDate;
    }

    public Date getToDate() {
        return toDate;
    }

    public void setToDate(Date toDate) {
        this.toDate = toDate;
    }
}

SalariesListener



import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zhouyu.domain.Salaries;
import com.zhouyu.mapper.SalariesMapper;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger;



@Component
public class SalariesListener extends ServiceImpl<SalariesMapper, Salaries> implements ReadListener<Salaries>, IService<Salaries> {

    private static final Log logger = LogFactory.getLog(SalariesListener.class);
    // 创建一个固定线程数的线程池进行解析excel
    private ExecutorService executorService = Executors.newFixedThreadPool(20);

    //使用ThreadLocal保证线程并发安全
    private ThreadLocal<ArrayList<Salaries>> salariesList = ThreadLocal.withInitial(ArrayList::new);
    private static AtomicInteger count = new AtomicInteger(1);
    //一次批量导入的条数
    private static final int batchSize = 10000;

    @Resource
    private SalariesListener salariesListener;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void invoke(Salaries data, AnalysisContext context) {
        // 方案一：单线程逐行解析，单线程单行数据插入
        // 解析一行，导入数据库一行
//        saveOne(data);

        // 方案二，单线程逐行解析，单线程批量插入
        // 将解析的Salaries对象添加到集合当中进行批量导入
        salariesList.get().add(data);
        if (salariesList.get().size() >= batchSize) {
            //方案三：单线程插入数据库
//            saveData();
            //方案四：多线程插入数据库
            asyncSaveData();
        }
    }

    public void saveOne(Salaries data){
        save(data);
        logger.info("第" + count.getAndAdd(1) + "次插入1条数据");
    }

    //单线程插入数据库
    public void saveData() {
        if (!salariesList.get().isEmpty()) {
            // 批量写入
            saveBatch(salariesList.get(), salariesList.get().size());
            logger.info("第" + count.getAndAdd(1) + "次插入" + salariesList.get().size() + "条数据");
            //将List中的数据进行清空，重新对excel中的数据进行解析
            salariesList.get().clear();
        }

    }
    //多线程插入数据库
    public void asyncSaveData() {
        if (!salariesList.get().isEmpty()) {
            //将集合clone转换成Salaries的集合对象
            ArrayList<Salaries> salaries = (ArrayList<Salaries>) salariesList.get().clone();
            //将集合对象传递到线程当中去
            executorService.execute(new SaveTask(salaries, salariesListener));
            salariesList.get().clear();
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void doAfterAllAnalysed(AnalysisContext context) {
        logger.info("一个Sheet全部处理完");
        if (salariesList.get().size() >= batchSize) {
            saveData();
        }
    }

    /**
     * 创建线程的方式
     * 实现Runnable接口重写run方法
     * */
    static class SaveTask implements Runnable {

        private List<Salaries> salariesList;
        private SalariesListener salariesListener;

        public SaveTask(List<Salaries> salariesList, SalariesListener salariesListener) {
            this.salariesList = salariesList;
            this.salariesListener = salariesListener;
        }

        @Override
        public void run() {
            //监听器进行批量导入
            salariesListener.saveBatch(salariesList);
            logger.info("第" + count.getAndAdd(1) + "次插入" + salariesList.size() + "条数据");
        }
    }
}

SalariesMapper



import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zhouyu.domain.Salaries;
import org.apache.ibatis.annotations.Mapper;


@Mapper
public interface SalariesMapper extends BaseMapper<Salaries> {

}

ExportService



import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zhouyu.domain.Salaries;
import com.zhouyu.mapper.SalariesMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


@Service
public class ExportService {
    public static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    @Resource
    private SalariesMapper salariesMapper;

    public void exportExcel1(HttpServletResponse response) throws IOException {

        setExportHeader(response);

        List<Salaries> salaries = salariesMapper.selectList(null);

        EasyExcel.write(response.getOutputStream(), Salaries.class).sheet().doWrite(salaries);
    }


    public void exportExcel2(HttpServletResponse response) throws IOException {

        setExportHeader(response);

        List<Salaries> salaries = salariesMapper.selectList(null);

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), Salaries.class).build()) {
            WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "模板1").build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "模板2").build();
            WriteSheet writeSheet3 = EasyExcel.writerSheet(3, "模板3").build();

            List<Salaries> data1 = salaries.subList(0, salaries.size() / 3);
            List<Salaries> data2 = salaries.subList(salaries.size() / 3, salaries.size() * 2 / 3);
            List<Salaries> data3 = salaries.subList(salaries.size() * 2 / 3, salaries.size());


            excelWriter.write(data1, writeSheet1);
            excelWriter.write(data2, writeSheet2);
            excelWriter.write(data3, writeSheet3);
        }
    }


    public void exportExcel3(HttpServletResponse response) throws IOException {

        setExportHeader(response);

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), Salaries.class).build()) {

            Long count = salariesMapper.selectCount(null);
            Integer pages = 10;
            Long size = count / pages;

            for (int i = 0; i < pages; i++) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();

                Page<Salaries> page = new Page<>();
                page.setCurrent(i + 1);
                page.setSize(size);
                Page<Salaries> selectPage = salariesMapper.selectPage(page, null);

                excelWriter.write(selectPage.getRecords(), writeSheet);
            }
        }
    }

    /**
     * 多线程分页查询，每页数据写入多个sheet
     */
    public void exportExcel4(HttpServletResponse response) throws IOException, InterruptedException {

        setExportHeader(response);
        //查询数据库的总条数
        Long count = salariesMapper.selectCount(null);

        //多少个sheet
        Integer pages = 20;
        // 每个sheet的条数
        Long size = count / pages;
        // 创建固定的线程数（多少个sheet就创建多少个线程）
        ExecutorService executorService = Executors.newFixedThreadPool(pages);
        CountDownLatch countDownLatch = new CountDownLatch(pages);

        Map<Integer, Page<Salaries>> pageMap = new HashMap<>();
        for (int i = 0; i < pages; i++) {
            int finalI = i;
            executorService.submit(new Runnable() {
                @Override
                public void run() {
                    Page<Salaries> page = new Page<>();
                    page.setCurrent(finalI + 1);
                    page.setSize(size);
                    Page<Salaries> selectPage = salariesMapper.selectPage(page, null);

                    pageMap.put(finalI, selectPage);
                    countDownLatch.countDown();
                }
            });
        }

        countDownLatch.await();

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), Salaries.class).build()) {
            for (Map.Entry<Integer, Page<Salaries>> entry : pageMap.entrySet()) {
                Integer num = entry.getKey();
                Page<Salaries> salariesPage = entry.getValue();
                WriteSheet writeSheet = EasyExcel.writerSheet(num, "模板" + num).build();
                excelWriter.write(salariesPage.getRecords(), writeSheet);
            }
        }

        // https://github.com/alibaba/easyexcel/issues/1040
        // easyexcel不同的sheet页,支持并发写入操作吗 ,不支持
    }

    private static void setExportHeader(HttpServletResponse response) {
        response.setContentType(CONTENT_TYPE);
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "zhouyu.xlsx");
    }
}

ImportService



import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.cache.Ehcache;
import com.alibaba.excel.cache.MapCache;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zhouyu.domain.Salaries;
import com.zhouyu.listener.SalariesListener;
import com.zhouyu.mapper.SalariesMapper;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;

@Service
public class ImportService {

    @Resource
    private SalariesListener salariesListener;
    // 创建一个固定线程数的线程池进行解析excel
    private ExecutorService executorService = Executors.newFixedThreadPool(20);

    //方案一：导入excel，单线程进行解析，单线程进行导入excel
    public void importExcel(MultipartFile file) throws IOException {

        EasyExcel.read(file.getInputStream(), Salaries.class, salariesListener).doReadAll();
    }


    //方案三
    public void importExcelAsync(MultipartFile file) {
        // 开20个线程分别处理20个sheet,往list里面添加20个任务
        List<Callable<Object>> tasks = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            int num = i;
            // 添加20个线程去解析excel
            tasks.add(() -> {
                EasyExcel.read(file.getInputStream(), Salaries.class, salariesListener)
                        .sheet(num).doRead();
                return null;
            });
        }

        try {
            //唤醒20个线程
            executorService.invokeAll(tasks);
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }

    }


}

SalariesService



import org.springframework.stereotype.Service;


@Service
public class SalariesService {

}

MyApplication



import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import java.util.ArrayList;

@SpringBootApplication
@EnableTransactionManagement
public class MyApplication {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

    public static void main(String[] args) {
        SpringApplication.run(MyApplication.class);
    }
}

application.yml

spring:
  servlet:
    multipart:
      max-request-size: 30MB
      max-file-size: 1024MB
  datasource:
    username: erp   #mysql配置文件需要修改
    password: xxxxxx
    url: jdbc:mysql://127.0.0.1:3306/millionsdataeasyexcel?rewriteBatchedStatements=true&characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=Asia/Shanghai
  main:
    allow-circular-references: true

salaries.sql

/*
Navicat MySQL Data Transfer

Source Server         : Mysql
Source Server Version : 50717
Source Host           : localhost:3306
Source Database       : salaries

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2023-06-24 10:17:18
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `salaries`
-- ----------------------------
DROP TABLE IF EXISTS `salaries`;
CREATE TABLE `salaries` (
  `emp_no` int(20) NOT NULL,
  `salary` int(200) DEFAULT NULL,
  `from_date` date DEFAULT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of salaries
-- ----------------------------

学习视频链接：百万数据快速导入导出，原来这么简单_哔哩哔哩_bilibili

本作品采用知识共享署名 4.0 国际许可协议进行许可