使用多线程+poi导出excel

最新推荐文章于 2024-08-22 16:39:00 发布

qq_42151769

最新推荐文章于 2024-08-22 16:39:00 发布

阅读量3.3k

点赞数 2

分类专栏： Java 文章标签：使用多线程+poi导出excel

转载请注明原创

本文链接：https://blog.csdn.net/qq_42151769/article/details/100674862

版权

本文介绍了一种使用多线程和Apache POI库高效导出Excel的方法。通过创建线程池和自定义注解，利用反射工具处理数据。在ExcelUtil工具类中，使用ReentrantLock避免并发问题，确保数据安全地填充到Excel表格中。在多线程环境中，为避免内存中的对象数据不一致，需每次新建对象实例。此方案显著提高了导出大型Excel文件的性能。

摘要由CSDN通过智能技术生成

引入poi依赖:

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

excel导出工具类:

package com.t3.ts.driver.resume.utils.excel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.util.concurrent.ThreadFactoryBuilder;
import com.t3.ts.driver.resume.constant.ValidateConstant;
import com.t3.ts.driver.resume.utils.CommonUtil;
import com.t3.ts.driver.resume.utils.StringUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.*;
import java.util.*;
import java.util.concurrent.*;
import java.util.stream.Collectors;

/**
 * @Description: 用于导出数据
 * @Author wm_yu
 * @Date 2018年11月26日09:18:44
 */
public class ExcelUtil {

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

    public static final int DEFAULT_COLOUMN_WIDTH = 17;

    public static final String X_AUTH_SESSIONID = "SESSIONID";

    /**
     * EXCEL的行数最大100行(去掉标题行)
     */
    public static final Integer EXCEL_ROW_MAX = 101;

    /**
     * 批量录入司机履约保证金最大限制500条
     */
    public static final Integer PERFORMANCE_EXCEL_ROW_MAX = 501;



    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title            标题行
     * @param headerList       属性-列头
     * @param sheetDataListArr 数据集
     * @param colWidth         列宽 默认 至少17个字节
     * @param out              输出流
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     */
    public static void exportExcel(String title, List<String> headerList, HashMap<String, String> linkMap, List<?> sheetDataListArr,
                                   int colWidth, OutputStream out)
            throws IllegalArgumentException, IllegalAccessException {
        SXSSFWorkbook workbook = null;
        try {

            // 声明一个工作薄缓存
            workbook = new SXSSFWorkbook(1000);
            workbook.setCompressTempFiles(true);
            CreationHelper createHelper = workbook.getCreationHelper();
            // 表头样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 700);
            titleStyle.setFont(titleFont);
            // 列头样式
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            // 单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(cellFont);
            // 生成一个(带标题)表格
            Sheet sheet = workbook.createSheet();
            //不刷新
            ((SXSSFSheet) sheet).setRandomAccessWindowSize(-1);
            // 设置列宽 至少字节数
            int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;
            if (sheetDataListArr != null && !sheetDataListArr.isEmpty()) {
                Class<?> sheetClass = sheetDataListArr.get(0).getClass();
                Map<String, String> headMap = new LinkedHashMap<>();
                Field[] allFields = FieldReflectionUtil.getAllFields(sheetClass);
                if (allFields != null && allFields.length > 0) {
                    for (Field field : allFields) {
                        if (field.isAnnotationPresent(ExcelField.class)) {
                            if (headerList != null && headerList.size() != 0) {
                                for (String header : headerList) {
                                    if (field.getName().equals(header)) {
                                        headMap.put(field.getName(), field.getAnnotation(ExcelField.class).name());
                                    }
                                }
                            } else {
                                headMap.put(field.getName(), field.getAnnotation(ExcelField.class).name());
                            }
                        }
                    }
                }
                int[] arrColWidth = new int[headMap.size()];
                // 产生表格标题行,以及设置列宽
                String[] headers = new String[headMap.size()];
                List<Field> fields = new ArrayList<Field>();

                int ii = 0;
                for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
                    String fieldName = iter.next();
                    if (allFields != null && allFields.length > 0) {
                        for (Field field : allFields) {
                            if (Modifier.isStatic(field.getModifiers()) || Modifier.isAbstract(field.getModifiers()) || Modifier.isNative(field.getModifiers())
                                    || Modifier.isFinal(field.getModifiers())) {
                                continue;
                            }
                            if (field.getName().equals(fieldName)) {
                                fields.add(field);
                            }
                        }
                    }
                    headers[ii] = headMap.get(fieldName);

                    int bytes = fieldName.getBytes().length;
                    arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
                    sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
                    ii++;
                }
                int rowIndex = 0;

                for (Object obj : sheetDataListArr) {
                    if (rowIndex == 100000 || rowIndex == 0) {
                        if (rowIndex != 0) {
                            // 如果数据超过了，则在第二页显示
                            sheet = workbook.createSheet();
                        }
                        // 表头 rowIndex=0
                        Row titleRow = sheet.createRow(0);
                        titleRow.createCell(0).setCellValue(title);
                        titleRow.getCell(0).setCellStyle(titleStyle);
                        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                        // 列头 rowIndex =1
                        Row headerRow = sheet.createRow(1);
                        for (int i = 0; i < headers.length; i++) {
                            headerRow.createCell(i).setCellValue(headers[i]);
                            headerRow.getCell(i).setCellStyle(headerStyle);

                        }
                        // 数据内容从 rowIndex=2开始
                        rowIndex = 2;
                    }
                }
                List<? extends List<?>> list = CommonUtil.splitList(sheetDataListArr, ValidateConstant.EXCEL_EXPORT_DEFAULT_SIZE);
                //线程计数
                CountDownLatch latch = new CountDownLatch(list.size());
                for (int i = 1; i <= list.size() ; i++) {
                    threadPoolExecutor.submit(new PoiExcelTask(sheet,workbook,list.get(i-1),fields,rowIndex,createHelper,linkMap,cellStyle,latch));
                    rowIndex = incrRowNum(rowIndex,ValidateConstant.EXCEL_EXPORT_DEFAULT_SIZE,list);
                }
                try {
                    //阻塞(最多10秒)，直到计数器的值为0
                    latch.await(THREAD_THREAD_OUT,TimeUnit.SECONDS);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
            // 自动调整宽度
            /*
             * for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }
             */
            try {
                workbook.write(out);
                out.close();
                workbook.dispose();
            } catch (IOException e) {
                logger.warn("导出Excel异常：{}", e.getLocalizedMessage());
            }
        } finally {
            if (null != workbook) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    logger.warn("导出Excel,关闭workbook异常：{}", e.getLocalizedMessage());
                }
            }
        }

    }

    /**
     * excel行数增加
     * @param rowIndex
     * @param num
     * @return
     */
    private static synchronized int incrRowNum(int rowIndex,Integer num,List<? extends List<?>> list){
        if(list.size() == 1){
            rowIndex += list.get(0).size();
        }else{
           rowIndex += num;
        }
        return rowIndex;
    }

    private static final Long THREAD_THREAD_OUT = 60L;

    private static BlockingQueue queue = new ArrayBlockingQueue(1024);

    private static ThreadPoolExecutor threadPoolExecutor;
    static {
        threadPoolExecutor = new ThreadPoolExecutor(30
                , 40
                , 60L
                , TimeUnit.MILLISECONDS
                , queue
                , (new ThreadFactoryBuilder()).build(), new ThreadPoolExecutor.CallerRunsPolicy());
    }

    /**
     * Web 导出excel
     */
    public static void downloadExcelFile(String title, List<String> headMap, List<?> sheetDataListArr,
                                         HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcel(title, headMap, null, sheetDataListArr, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * cros
     * Web 导出excel
     */
    public static void downloadExcelFile(String title, List<String> headMap, List<?> sheetDataListArr,
                                         HttpServletResponse response, HttpServletRequest request) {
        downloadExcelFile(title, headMap, null, sheetDataListArr, response, request);
    }

    /**
     * cros
     * Web 导出excel,带超链接
     */
    public static void downloadExcelFile(String title, List<String> headMap, HashMap<String, String> linkMap, List<?> sheetDataListArr,
                                         HttpServletResponse response, HttpServletRequest request) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcel(title, headMap, linkMap, sheetDataListArr, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            //            response.setHeader("Access-Control-Allow-Methods", "POST,GET,OPTIONS,DELETE,PUT");
            response.setHeader("Access-Control-Max-Age", "3600");
            String originHeader = request.getHeader("Origin");

            //response.setHeader("Access-Control-Allow-Origin", "*");
            //            response.setHeader("Access-Control-Allow-Credentials", "true");
            //response.setHeader("Access-Control-Allow-Headers", "Content-Type, Access-Control-Allow-Headers, Authorization," + X_AUTH_SESSIONID);
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List getData(MultipartFile file) throws IOException {
        if (file == null) {
            return new ArrayList();
        }
        String filename = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
        return doGetData(inputStream, filename);
    }


    public static List doGetData(InputStream inputStream, String fileName) throws IOException {

        Workbook workbook = null;
        //判断excel的两种格式xls,xlsx
        if (fileName.toLowerCase().e