使用多線程的導出海量資料到Excel

实现的功能点

对于每个报表都相同的操作，我们很自然的会抽离出来，这个很简单。而最重要的是：如何把那些每个报表不相同的操作进行良好的封装，尽可能的提高复用性；针对以上的原则，主要实现了一下关键功能点：

• 导出任意类型的数据
• 自由设置表头
• 自由设置字段的导出格式

使用实例

上面说到了本工具类实现了三个功能点，自然在使用的时候设置好这三个要点即可：

• 设置数据列表
• 设置表头
• 设置字段格式

下面的export函数可以直接向客户端返回一个excel数据，其中​

​productInfoPos​

​为待导出的数据列表，​

​ExcelHeaderInfo​

​用来保存表头信息，包括表头名称，表头的首列，尾列，首行，尾行。

因为默认导出的数据格式都是字符串型，所以还需要一个Map参数用来指定某个字段的格式化类型（例如数字类型，小数类型、日期类型）。这里大家知道个大概怎么使用就好了，下面会对这些参数进行详细解释

代码实现​

1、pom.xml

<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>

 <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.4.0</version>
        </dependency>

<!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>      

2、导出类

public class ExcelUtils {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    public static final int ROW_ACCESS_WINDOW_SIZE = 100;
    public static final int SHEET_MAX_ROW = 100000;

    private List list;
    private List<ExcelHeaderInfo> excelHeaderInfos;
    private Map<String, ExcelFormat> formatInfo;

    public ExcelUtils(List list, List<ExcelHeaderInfo> excelHeaderInfos) {
        this.list = list;
        this.excelHeaderInfos = excelHeaderInfos;
    }

    public ExcelUtils(List list, List<ExcelHeaderInfo> excelHeaderInfos, Map<String, ExcelFormat> formatInfo) {
        this.list = list;
        this.excelHeaderInfos = excelHeaderInfos;
        this.formatInfo = formatInfo;
    }

    public Workbook getWorkbook() {
        Workbook workbook = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE);
        String[][] datas = transformData();
        Field[] fields = list.get(0).getClass().getDeclaredFields();
        CellStyle style = setCellStyle(workbook);
        int pageRowNum = 0;
        Sheet sheet = null;

        long startTime = System.currentTimeMillis();
        LOGGER.info("开始处理excel文件。。。");

        for (int i = 0; i < datas.length; i++) {
            // 如果是每个sheet的首行
            if (i % SHEET_MAX_ROW == 0) {
                // 创建新的sheet
                sheet = createSheet(workbook, i);
                pageRowNum = 0; // 行号重置为0
                for (int j = 0; j < getHeaderRowNum(excelHeaderInfos); j++) {
                    sheet.createRow(pageRowNum++);
                }
                createHeader(sheet, style);
            }
            // 创建内容
            Row row = sheet.createRow(pageRowNum++);
            createContent(row, style, datas, i, fields);
        }
        LOGGER.info("处理文本耗时" + (System.currentTimeMillis() - startTime) + "ms");
        return workbook;
    }

    // 创建表头
    private void createHeader(Sheet sheet, CellStyle style) {
        for (ExcelHeaderInfo excelHeaderInfo : excelHeaderInfos) {
            Integer lastRow = excelHeaderInfo.getLastRow();
            Integer firstRow = excelHeaderInfo.getFirstRow();
            Integer lastCol = excelHeaderInfo.getLastCol();
            Integer firstCol = excelHeaderInfo.getFirstCol();

            // 行距或者列距大于0才进行单元格融合
            if ((lastRow - firstRow) != 0 || (lastCol - firstCol) != 0) {
                sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
            }
            // 获取当前表头的首行位置
            Row row = sheet.getRow(firstRow);
            // 在表头的首行与首列位置创建一个新的单元格
            Cell cell = row.createCell(firstCol);
            // 赋值单元格
            cell.setCellValue(excelHeaderInfo.getTitle());
            cell.setCellStyle(style);
            sheet.setColumnWidth(firstCol, sheet.getColumnWidth(firstCol) * 17 / 12);
        }
    }

    // 创建正文
    private void createContent(Row row, CellStyle style, String[][] content, int i, Field[] fields) {
        List<String> columnNames = getBeanProperty(fields);
        for (int j = 0; j < columnNames.size(); j++) {
            // 如果格式化Map为空，默认为字符串格式
            if (formatInfo == null) {
                row.createCell(j).setCellValue(content[i][j]);
                continue;
            }
            if (formatInfo.containsKey(columnNames.get(j))) {
                switch (formatInfo.get(columnNames.get(j)).getValue()) {
                    case "DOUBLE":
                        row.createCell(j).setCellValue(Double.parseDouble(content[i][j]));
                        break;
                    case "INTEGER":
                        row.createCell(j).setCellValue(Integer.parseInt(content[i][j]));
                        break;
                    case "PERCENT":
                        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(style);
                        cell.setCellValue(Double.parseDouble(content[i][j]));
                        break;
                    case "DATE":
                        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd HH:mm:ss"));
                        Cell cell1 = row.createCell(j);
                        cell1.setCellStyle(style);
                        row.createCell(j).setCellValue(this.parseDate(content[i][j]));
                }
            } else {
                row.createCell(j).setCellValue(content[i][j]);
            }
        }
    }

    // 将原始数据转成二维数组
    private String[][] transformData() {
        int dataSize = this.list.size();
        String[][] datas = new String[dataSize][];
        // 获取报表的列数
        Field[] fields = list.get(0).getClass().getDeclaredFields();
        // 获取实体类的字段名称数组
        List<String> columnNames = this.getBeanProperty(fields);
        for (int i = 0; i < dataSize; i++) {
            datas[i] = new String[fields.length];
            for (int j = 0; j < fields.length; j++) {
                try {
                    // 赋值
                    datas[i][j] = BeanUtils.getProperty(list.get(i), columnNames.get(j));
                } catch (Exception e) {
                    LOGGER.error("获取对象属性值失败");
                    e.printStackTrace();
                }
            }
        }
        return datas;
    }

    // 获取实体类的字段名称数组
    private List<String> getBeanProperty(Field[] fields) {
        List<String> columnNames = new ArrayList<>();
        for (Field field : fields) {
            String[] strings = field.toString().split("\\.");
            String columnName = strings[strings.length - 1];
            columnNames.add(columnName);
        }
        return columnNames;
    }

    // 新建表格
    private static Sheet createSheet(Workbook workbook, int i) {
        Integer sheetNum = i / SHEET_MAX_ROW;
        workbook.createSheet("sheet" + sheetNum);
        //动态指定当前的工作表
        return workbook.getSheetAt(sheetNum);
    }

    // 获取标题总行数
    private static Integer getHeaderRowNum(List<ExcelHeaderInfo> headerInfos) {
        Integer maxRowNum = 0;
        for (ExcelHeaderInfo excelHeaderInfo : headerInfos) {
            Integer tmpRowNum = excelHeaderInfo.getLastRow();
            if (tmpRowNum > maxRowNum) {
                maxRowNum = tmpRowNum;
            }
        }
        return maxRowNum + 1;
    }

    // 设置总体表格样式
    private static CellStyle setCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setWrapText(true);
        return style;
    }

    // 字符串转日期
    private Date parseDate(String strDate) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = null;
        try {
            date = dateFormat.parse(strDate);
        } catch (Exception e) {
            LOGGER.error("字符串转日期错误");
            e.printStackTrace();
        }
        return date;
    }

    // 发送响应结果
    public void sendHttpResponse(HttpServletResponse response, String fileName, Workbook workbook) {
        try {
            fileName += System.currentTimeMillis() + ".xlsx";
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
      

3、实体类对象

3.1ExcelHeaderInfo

@Data
@AllArgsConstructor
@Accessors(chain = true)
public class ExcelHeaderInfo {

    //标题的首行坐标
    private int firstRow;
    //标题的末行坐标
    private int lastRow;
    //标题的首列坐标
    private int firstCol;
    //标题的首行坐标
    private int lastCol;
    // 标题
    private String title;

}      

3.2ExcelFormat

public enum ExcelFormat {

    FORMAT_INTEGER("INTEGER"),
    FORMAT_DOUBLE("DOUBLE"),
    FORMAT_PERCENT("PERCENT"),
    FORMAT_DATE("DATE");

    private String value;

    ExcelFormat(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }
}
      

4、业务层方法

ExportService

public interface ExportService<T> {

    List<T> list();

    List<ExcelHeaderInfo> getHeaderInfo();

    Map<String, ExcelFormat> getFormatInfo();

    void export(HttpServletResponse response, String fileName);
}      

ExportServiceImpl

public class ExportServiceImpl<T> implements ExportService<T> {

    private static final Logger logger = LoggerFactory.getLogger(ExportServiceImpl.class);

    @Override
    public List<T> list() {
        return null;
    }

    @Override
    public List<ExcelHeaderInfo> getHeaderInfo() {
        return null;
    }

    @Override
    public Map<String, ExcelFormat> getFormatInfo() {
        return null;
    }

    @Override
    public void export(HttpServletResponse response, String fileName) {
        // 待导出数据
        List<T> list = list();
        ExcelUtils excelUtils = new ExcelUtils(list, getHeaderInfo(), getFormatInfo());
        excelUtils.sendHttpResponse(response, fileName, excelUtils.getWorkbook());
    }
}      

真正要导出的业务层，继承上面的ExportServiceImpl，重写获取数据的list方法和获取表头、数据格式的方法。

TbTaskExportServiceImpl

@Service
public class TbTaskExportServiceImpl extends ExportServiceImpl<TbTask> {

    @Autowired
    private TbTaskMapper tbTaskMapper;

    private static final int THREAD_MAX_ROW = 2000;

    @Autowired
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;

    @Override
    public List<TbTask> list() {
        List<TbTask> result = new ArrayList<>();

        List<Callable<List<TbTask>>> taskList = new ArrayList<>();

        //表总数
        Integer total = tbTaskMapper.selectCount(new QueryWrapper<>());

        //根据表总数确定查询次数
        int selectNum = total / THREAD_MAX_ROW;
        if (total % THREAD_MAX_ROW != 0) {
            selectNum += 1;
        }
        for (int i = 0; i < selectNum; i++) {
            //用多线程查询,先创建callable
            int num = THREAD_MAX_ROW;
            int start = i + 1;
            taskList.add(new TbTaskCallable(start, num));
        }

        long startTime = 0;
        long endTime = 0;

        try {
            startTime = System.currentTimeMillis();
            List<Future<List<TbTask>>> futureList = threadPoolTaskExecutor.getThreadPoolExecutor().invokeAll(taskList);

            if (!CollectionUtils.isEmpty(futureList)) {
                for (Future<List<TbTask>> data : futureList) {
                    result.addAll(data.get());
                }
            }

            endTime = System.currentTimeMillis();

        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("=====查询数据总条数：" + result.size());
        System.out.println("=====多线程查询耗时：" + (endTime - startTime));
        return result;
    }

    @Override
    public List<ExcelHeaderInfo> getHeaderInfo() {
        return Arrays.asList(

                new ExcelHeaderInfo(0, 0, 0, 0, "taskId"),
                new ExcelHeaderInfo(0, 0, 1, 1, "taskName"),
                new ExcelHeaderInfo(0, 0, 2, 2, "taskDesc"),
                new ExcelHeaderInfo(0, 0, 3, 3, "taskExp"),
                new ExcelHeaderInfo(0, 0, 4, 4, "taskStatus"),
                new ExcelHeaderInfo(0, 0, 5, 5, "taskClass"),
                new ExcelHeaderInfo(0, 0, 6, 6, "updateTime"),
                new ExcelHeaderInfo(0, 0, 7, 7, "createTime"),
                new ExcelHeaderInfo(0, 0, 8, 8, "playId"),
                new ExcelHeaderInfo(0, 0, 9, 9, "deviceId")
        );
    }

    @Override
    public Map<String, ExcelFormat> getFormatInfo() {
        Map<String, ExcelFormat> format = new HashMap<>();
        format.put("taskStatus", ExcelFormat.FORMAT_INTEGER);
        format.put("updateTime", ExcelFormat.FORMAT_DATE);
        format.put("createTime", ExcelFormat.FORMAT_DATE);
        return format;
    }

    @Override
    public void export(HttpServletResponse response, String fileName) {
        super.export(response, fileName);
    }

    private class TbTaskCallable implements Callable<List<TbTask>> {
        private int start;
        private int num;
        private List<TbTask> data;

        public TbTaskCallable(int start, int num) {
            this.start = start;
            this.num = num;
        }

        /**
         * Computes a result, or throws an exception if unable to do so.
         *
         * @return computed result
         * @throws Exception if unable to compute a result
         */
        @Override
        public List<TbTask> call() throws Exception {
            Page<TbTask> page = new Page<>(start, num);
            IPage<TbTask> iPage = tbTaskMapper.selectPage(page, new QueryWrapper<>());
            return iPage.getRecords();
        }
    }
}      

5、配置类

5.1线程池配置类：ThreadPoolConfig

@Component
@Configuration
public class ThreadPoolConfig {

    @Value("${threadpool.corePoolSize:5}")
    private int corePoolSize;

    /**
     * 线程池维护线程的最大数量
     * (max(tasks)- queueCapacity)/(1/taskcost)
     */
    @Value("${threadpool.maxPoolSize:8}")
    private int maxPoolSize;

    /**
     * 缓存队列
     * (coreSizePool/taskcost)*responsetime
     */
    @Value("${threadpool.queueCapacity:30}")
    private int queueCapacity;

    /**
     * 允许的空闲时间
     * 默认为60
     */
    @Value("${threadpool.keepAliveSeconds:100}")
    private int keepAlive;

    @Bean
    public ThreadPoolTaskExecutor taskExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        // 设置核心线程数
        executor.setCorePoolSize(corePoolSize);
        // 设置最大线程数
        executor.setMaxPoolSize(maxPoolSize);
        // 设置队列容量
        executor.setQueueCapacity(queueCapacity);
        // 设置允许的空闲时间（秒）
        executor.setKeepAliveSeconds(keepAlive);
        // 设置默认线程名称
        executor.setThreadNamePrefix("thread-taskExecutor-");
        // 设置拒绝策略rejection-policy：当pool已经达到max size的时候，如何处理新任务
        // CALLER_RUNS：不在新线程中执行任务，而是有调用者所在的线程来执行
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        // 等待所有任务结束后再关闭线程池
        executor.setWaitForTasksToCompleteOnShutdown(true);
        return executor;
    }
}      

@MapperScan("com.hikvision.aradc.mapper")
@Configuration
public class MyBatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //向Mybatis过滤器链中添加分页拦截器
        interceptor.addInnerInterceptor(new
                PaginationInnerInterceptor(DbType.POSTGRE_SQL));
        return interceptor;
    }
}
      

结果：

控制层实现导出

@GetMapping("/export")
    public void export(HttpServletResponse response) {
        TbTaskExportServiceImpl.export(response, "商品信息" + new Random().nextInt(1000));
    }