easyExcel大数据导出

前段时间需要做一个导出功能，而且是大数据的，在网上看下一些大神的做法，自己也写了一个，话不多说，直接上代码，后面有多线程导出的写法，欢迎大神指教

现创建一个控制类

public ResponseEntity<LuinResult<String>> readExcel(HttpServletResponse response) throws Exception {
        int totalCount=healthOrderMapper.selectCount(new EntityWrapper<>());
        excelExportUtil.exportExcel(response,totalCount, ExportExcelVO.class,healthOrderService.getClass());
        return ResponseEntity.ok().body(LuinResult.success("success"));
    }

创建文件导出类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

@Component
public class ExcelExportUtil {

    /**
     *
     * @param response 响应
     * @param totalCount 总记录条数
     * @param clazz 导出的Excel对象
     * @throws Exception
     */
    public void exportExcel( HttpServletResponse response, int totalCount,
                             Class<?> clazz,Class<?> clazzImpl) throws Exception{
        //文件名
        String fileName =String.valueOf(System.currentTimeMillis());

        OutputStream outputStream =null;
        try {
            //每一个Sheet存放1w条数据
            Integer sheetDataRows = 10000;
            //每次写入的数据量5000,每页查询5000
            Integer writeDataRows = 5000;
            //计算需要的Sheet数量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

            outputStream = response.getOutputStream();

            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

            List<List<String>> dataList = new ArrayList<>();

            //开始分批查询分次写入 sheetNum
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName("Sheet"+i);
                sheet.setSheetNo(i);
                //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    // 清空集合
                    dataList.clear();
                    // 反射调用方法
                    Method method = clazzImpl.getMethod("queryPageExcel",Integer.class,Integer.class);
                    Object obj = method.invoke(clazzImpl,j + 1 + oneSheetWriteCount * i, writeDataRows);

                    dataList = (List<List<String>>) obj;

                    // 分sheet保存数据
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(clazz)
                            .registerWriteHandler(new LongestMatchColum nWidthStyleStrategy()).build();

                    excelWriter.write(dataList,writeSheet);

                }
            }
            // 下载EXCEL，返回给前端stream流
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }

}

反射调用类

public static List<List<String>> queryPageExcel(Integer idx, Integer pageSize) {
        List<List<String>> dataList = new ArrayList<>();
        //分页查询一次
        Page<HealthOrder> page = new Page<>(idx, pageSize);
        //分页查询数据
        EntityWrapper<HealthOrder> wrapper = new EntityWrapper<>();
        wrapper.setSqlSelect("order_id","real_name","id_card");

        List<HealthOrder> list = SpringUtil.getBean(HealthOrderMapper.class).selectPage(page,wrapper);

        if(!ObjectUtil.isNullOrEmpty(list)){
            list.forEach(item->{
                dataList.add(Arrays.asList(item.getOrderId().toString(),item.getRealName(),item.getIdCard()));
            });
        }
        return dataList;
    }

导出实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExportExcelVo implements Serializable{
    @ExcelProperty(value="主键",index=0)
    private String orderId;
    @ExcelProperty(value="姓名",index=1)
    private String realName;
    @ExcelProperty(value="证件号",index=2)
    private String idCard;

}

这个导出是通用的，唯一的缺点就是所有要导出的都要写queryPageExcel这个方法，因为反射调用的就是这个方法

下面是多线程的写法

核心导出方法

import cn.com.cgnpc.aep.bizcenter.common.service.impl.FileExportContext;
import cn.com.cgnpc.aep.bizcenter.common.service.impl.FileExportService;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.CountDownLatch;

@Component
public class ExcelExportUtil {

    @Resource
    @Qualifier("excelThreadPool")
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;

    /**
     * @param totalCount 总记录条数
     * @param clazz 导出的Excel对象
     * @param service 具体实现查询数据的服务类
     * @param map 查询参数
     */
    public void exportExcel(HttpServletResponse response,int totalCount, Class<?> clazz, FileExportService service, Map<String,Object> map) throws Exception{

        FileExportContext context = new FileExportContext(service);

        //文件名
        String fileName = String.valueOf(System.currentTimeMillis());

        //ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        OutputStream outputStream = null;
        try {
            //每一个Sheet存放1w条数据
            Integer sheetDataRows = 100;
            //每次写入的数据量5000,每页查询5000
            Integer writeDataRows = 50;
            //计算需要的Sheet数量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : totalCount % sheetDataRows % writeDataRows == 0 ? totalCount % sheetDataRows / writeDataRows : (totalCount % sheetDataRows / writeDataRows)+1;

            outputStream = response.getOutputStream();

            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

            Map<Integer,List<List<String>>> pageMap = new ConcurrentHashMap<>(Math.toIntExact(sheetNum));

            CountDownLatch countDownLatch = new CountDownLatch(Math.toIntExact(sheetNum));
            // 多线程查询参数Map
            Map<Integer,Map<String,Object>> queryMap = new ConcurrentHashMap<>();

            //开始分批查询分次写入 sheetNum
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName("Sheet"+i);
                sheet.setSheetNo(i);
                int finalNum = i;
                threadPoolTaskExecutor.submit(()->{
                    ConcurrentHashMap<String,List<List<String>>> dataListMap = new ConcurrentHashMap<>();
                    //循环写入次数, j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                    for (int j = 0; j < (finalNum != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                        int finalJ = j;
                        queryMap.put(finalNum,new HashMap<String,Object>(){
                        {
                put("page",finalNum * sheetDataRows + finalJ * writeDataRows);
                put("pageSize",writeDataRows);
                putAll(map);
    }
});
                        // 策略模式调用查询
                    List<List<String>> dataList = Optional.ofNullable(pageMap.get(finalNum)).orElse(new ArrayList<>());
                    dataList.addAll(context.queryPageExcel(queryMap.get(finalNum)));
                    pageMap.put(finalNum,dataList);    
                    }
                    
                    countDownLatch.countDown();
                });
            }
            try{
                countDownLatch.await();
            }catch (Exception e){
                e.printStackTrace();
            }
            // 关闭线程
            threadPoolTaskExecutor.shutdown();

            pageMap.forEach((k,v)->{
                // 分sheet保存数据
                WriteSheet writeSheet = EasyExcel.writerSheet(k, "Sheet" + (k + 1)).head(clazz)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                excelWriter.write(v,writeSheet);
                pageMap.remove(k);
            });

            // 下载EXCEL，返回给前端stream流
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
        //return new ByteArrayInputStream(outputStream.toByteArray());
    }

}

多线程配置

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.ThreadPoolExecutor;

@Configuration
public class ConcurrentThreadGlobalConfig {

    @Bean("excelThreadPool")
    public ThreadPoolTaskExecutor defaultThreadPool(){
        // 创建线程池
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        // 核心线程数
        executor.setCorePoolSize(Runtime.getRuntime().availableProcessors()+1);
        // 最大线程数
        executor.setMaxPoolSize(Runtime.getRuntime().availableProcessors()+1);
        // 队列中的最大数目
        executor.setQueueCapacity(600);
        // 线程名称的前缀
        executor.setThreadNamePrefix("defaultThreadPool_");
        // 线程拒绝策略，由调用者决定
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        // 空闲线程时间
        executor.setKeepAliveSeconds(60);
        // 加载线程
        executor.initialize();
        return executor;
    }

}

策略上下文

import java.util.List;
import java.util.Map;
// 策略上下文，类似工厂，通过上下文决定调用那个实现类的queryPageExcel方法
public class FileExportContext {

    private FileExportService fileExportService;

    public FileExportContext(FileExportService fileExportService){
        this.fileExportService = fileExportService;
    }

    public List<List<String>> queryPageExcel(Map<String,Object> map){
        return fileExportService.queryPageExcel(map);
    }

}

策略接口类

import java.util.List;
import java.util.Map;
// 接口方法
public interface FileExportService {

    List<List<String>> queryPageExcel(Map<String,Object> map);

}

策略接口实现类

@Service
@Slf4j
// 具体实现策略的服务类
public class HealthOrderServiceImpl implements FileExportService {


	public List<List<String>> queryPageExcel(Map<String,Object> map) {
        List<List<String>> dataList = new ArrayList<>();
        //分页查询一次
        Page<HealthOrder> page = new Page<>((Integer) map.get("page"), (Integer) map.get("pageSize"));
        //分页查询数据
        EntityWrapper<HealthOrder> wrapper = new EntityWrapper<>();
        wrapper.setSqlSelect("order_id","real_name","id_card");

        List<HealthOrder> list = SpringUtil.getBean(HealthOrderMapper.class).selectPage(page,wrapper);

        if(!ObjectUtil.isNullOrEmpty(list)){
            list.forEach(item->{
                dataList.add(Arrays.asList(item.getOrderId().toString(),item.getRealName(),item.getIdCard()));
            });
        }
        return dataList;
    }

}