阿里easyexcel做导入导出

1.导出的公共类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.ccp.common.entity.AttachFile;
import com.ccp.common.entity.Base;
import com.ccp.common.entity.OrderExcelEntity;
import com.ccp.common.entity.User;
import com.ccp.common.util.LoginUtil;
import com.ccp.provider.common.mapper.AttachMapper;
import com.ccp.provider.common.mapper.ExcelMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.bag.SynchronizedSortedBag;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.hssf.record.PageBreakRecord;
import org.apache.poi.ss.usermodel.*;
import org.apache.shiro.util.CollectionUtils;
import org.jsoup.Connection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Future;
import java.util.concurrent.atomic.AtomicInteger;

import com.google.common.util.concurrent.ThreadFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.concurrent.*;
import java.util.logging.Logger;

/**
 * @author zhoufangyuan
 */
@Component
@Slf4j
public class MultiThreadExcelExport<T> {

    public static final String MOZILLA = "Mozilla";
    public static final String USER_AGENT = "USER-AGENT";

    protected static FastDateFormat fastDateFormat= FastDateFormat.getInstance("yyyyMMddHH:mm:ss");

    /**
     * 导出
     * @param name 导出名字
     * @param response  导出流
     * @param excelMapper 导出的顶级父类
     * @param base 查询的顶级父类
     * @param head 导出的实体类
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public void exportExcel(String name,HttpServletResponse response,ExcelMapper excelMapper, Base base,Class head) throws Exception{
        String fileName = name+fastDateFormat.format(new Date());
        ExcelWriter writer = EasyExcel.write(getOutputStream(fileName,response),head).registerWriteHandler(myHorizontalCellStyleStrategy()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
        // 根据数据读写速度来调整，一般来说读的逻辑复杂，比较慢，如果读比写快，这里设为1
        Long total=excelMapper.excelListCount(base);
        // 分页大小可以适当调整
        int pageSize = 5000;
        long leng = 3;
        Long pageCount = total % pageSize == 0 ? (total / pageSize) : (total / pageSize + 1);
        int BlockingQueueSize=pageCount.intValue();
        // 大小设置为2就可以，作为缓冲
        BlockingQueue<List<T>> queue = new ArrayBlockingQueue<>(BlockingQueueSize);
        AtomicInteger start = new AtomicInteger(0);
        AtomicInteger writerCount = new AtomicInteger(0);
        ThreadFactory threadFactory = new ThreadFactoryBuilder().setNameFormat("excel-pool-%d").build();
        //线程池
        ExecutorService executorService=new ThreadPoolExecutor(30 , 50 ,
                1, TimeUnit.MINUTES, new LinkedBlockingQueue<>(100), threadFactory);



        LinkedList linkedList=new LinkedList<>();
        //开启多个线程分页查数据
            executorService.submit(() -> {
                while (start.get()<=total) {
                    //自增
                    int pageNum = start.getAndAdd(pageSize);
                    try {
                        List<T> list = findPage(pageNum, pageSize,excelMapper,base);
                        System.out.println(list.size());
                        if (CollectionUtils.isEmpty(list)) {
                            //读到没数据也要放入空集合
                            queue.add(linkedList);
                            break;
                        }
                        queue.add(list);
                    } catch (Exception e) {
                        //异常情况也要放入空集合，防止写线程无法退出循环
                        queue.add(linkedList);
                    }
                }
            });
        


        Future<?> submit = executorService.submit(() -> {
            while (writerCount.get()<total) {
                List<T> list = null;
                try{
                    queue.element();
                    list = queue.remove();
                    writerCount.getAndAdd(list.size());
                    writer.write(list, writeSheet);
                }catch (NoSuchElementException e){

                }

            }
            try{
                writer.finish();
            }catch (Exception e) {
                log.error("刷新出错",e);
            }finally {
                executorService.shutdown();
            }

        });

        try {
            // 阻塞等待完成，异步处理也可以去掉这段代码
            submit.get();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }



    private List<T> findPage(int pageNum, int pageSize, ExcelMapper excelMapper,Base base) {
        // todo 实现分页查询
        base.setPage(pageNum);
        base.setPageSize(pageSize);
        List<T> list=excelMapper.excelList(base);
        return list;
    }


    /**
     * 导出文件时为Writer生成OutputStream
     *
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        try {
            HttpServletRequest request=LoginUtil.getRequest();
            String userAgent = request.getHeader(USER_AGENT);
            if (StringUtils.contains(userAgent, MOZILLA)) {
                //google,火狐浏览器
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } else {
                //其他浏览器
                fileName = URLEncoder.encode(fileName, "UTF8");
            }
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

    /*public <T> void writeExcel( int exifInfoCount, HttpServletResponse response) throws Exception{
        String fileName = fastDateFormat.format(new Date());
        //每个sheet保存的数据量
        int num = 5000;
        ExcelWriter excelWriter = null;
        int corePoolSize=exifInfoCount / num;
        int maximumPoolSize=1000;
        //用线程池管理多线程
        ThreadPoolExecutor exector = (ThreadPoolExecutor) Executors.newFixedThreadPool(corePoolSize);
        exector.setCorePoolSize(corePoolSize);
        exector.setMaximumPoolSize(maximumPoolSize);
        List<Callable<List<T>>> tasks = new ArrayList<Callable<List<T>>>();
        excelWriter = EasyExcel.write(getOutputStream(fileName,response), AttachEntity.class).build();

        int pageCount = exifInfoCount % num == 0 ? (exifInfoCount / num) : (exifInfoCount / num + 1);
        for (int i = 0; i < pageCount; i++) {
            ReadExifInfoThread readExifInfoThread = new ReadExifInfoThread( attachMapper, i, num);
            tasks.add(readExifInfoThread);
        }
        try {
            List<Future<List<T>>> futures = exector.invokeAll(tasks);
            for (int i = 0; i < pageCount; i++) {
                List<T> exifInfoList = futures.get(i).get();
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "xxxx信息表" + (i + 1)).build();
                System.out.println("写人数据"+i);
                excelWriter.write(exifInfoList, writeSheet);
            }
        } catch (Exception e) {
            //Constant.bLog.error("写入excel数据失败",e);
        }
        exector.shutdown();
        excelWriter.finish();
    }*/


    /**
     * 样式设置
     * @return
     */
    private HorizontalCellStyleStrategy myHorizontalCellStyleStrategy(){
        //表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景设置淡蓝色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short)14);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short)11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置水平靠左
        //contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        //设置边框样式
        setBorderStyle(contentWriteCellStyle);
        //内容风格可以定义多个。
        List<WriteCellStyle>   listCntWritCellSty =  new ArrayList<>();
        listCntWritCellSty.add(contentWriteCellStyle);
        WriteCellStyle contentWriteCellStyle2 = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色。
        // 头默认了 FillPatternType所以可以不指定。
        contentWriteCellStyle2.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        //contentWriteCellStyle2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        //设置垂直居中
        contentWriteCellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框样式
        setBorderStyle(contentWriteCellStyle2);
        //listCntWritCellSty.add(contentWriteCellStyle2);
        // 水平单元格风格综合策略(表头 + 内容)
        // return  new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return  new HorizontalCellStyleStrategy(headWriteCellStyle, listCntWritCellSty);
    }

    private void setBorderStyle(WriteCellStyle contentWriteCellStyle){
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        // contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex()); //颜色
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
    }

    private List<List<String>> head(List<String> head) {
        List<List<String>> list = new ArrayList<List<String>>();
        for(String string:head){
            List<String> headList = new ArrayList<String>();
            headList.add(string);
            list.add(headList);
        }
        return list;
    }
}

2.导出的对象

@Data
public class OrderExcelEntity implements Serializable {
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String name;

    @ExcelProperty("父订单编号")
    private String orderId;


    @ExcelProperty("子订单编号")
    private Long orderItemId;
}

3.pom.xml

       <poi.version>3.17</poi.version>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>