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

代码层级结构

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@Aspectpublic 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;@RestControllerpublic class SalariesController {@Resourceprivate ExportService exportService;@Resourceprivate 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;@Componentpublic class SalariesListener extends ServiceImpl implements ReadListener, IService {private static final Log logger = LogFactory.getLog(SalariesListener.class);// 创建一个固定线程数的线程池进行解析excelprivate ExecutorService executorService = Executors.newFixedThreadPool(20);//使用ThreadLocal保证线程并发安全private ThreadLocal> salariesList = ThreadLocal.withInitial(ArrayList::new);private static AtomicInteger count = new AtomicInteger(1);//一次批量导入的条数private static final int batchSize = 10000;@Resourceprivate 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 = (ArrayList) 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 salariesList;private SalariesListener salariesListener;public SaveTask(List salariesList, SalariesListener salariesListener) {this.salariesList = salariesList;this.salariesListener = salariesListener;}@Overridepublic 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;@Mapperpublic interface SalariesMapper extends BaseMapper {}

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;@Servicepublic class ExportService {public static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";@Resourceprivate SalariesMapper salariesMapper;public void exportExcel1(HttpServletResponse response) throws IOException {setExportHeader(response);List salaries = salariesMapper.selectList(null);EasyExcel.write(response.getOutputStream(), Salaries.class).sheet().doWrite(salaries);}public void exportExcel2(HttpServletResponse response) throws IOException {setExportHeader(response);List 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 data1 = salaries.subList(0, salaries.size() / 3);List data2 = salaries.subList(salaries.size() / 3, salaries.size() * 2 / 3);List 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 page = new Page();page.setCurrent(i + 1);page.setSize(size);Page 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);//多少个sheetInteger pages = 20;// 每个sheet的条数Long size = count / pages;// 创建固定的线程数（多少个sheet就创建多少个线程）ExecutorService executorService = Executors.newFixedThreadPool(pages);CountDownLatch countDownLatch = new CountDownLatch(pages);Map<Integer, Page> pageMap = new HashMap();for (int i = 0; i < pages; i++) {int finalI = i;executorService.submit(new Runnable() {@Overridepublic void run() {Page page = new Page();page.setCurrent(finalI + 1);page.setSize(size);Page 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> entry : pageMap.entrySet()) {Integer num = entry.getKey();Page 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.*;@Servicepublic class ImportService {@Resourceprivate SalariesListener salariesListener;// 创建一个固定线程数的线程池进行解析excelprivate ExecutorService executorService = Executors.newFixedThreadPool(20);//方案一：导入excel，单线程进行解析，单线程进行导入excelpublic 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