多线程批量读取Excel 2007行数据

参考链接 

https://blog.csdn.net/zhangpan_soft/article/details/82698817

https://blog.csdn.net/lichunericli/article/details/82832067

https://blog.csdn.net/zhangpan_soft/article/details/52415238

https://www.oschina.net/news/88797/apache-poi-3-17

https://blog.csdn.net/redarmy_chen/article/details/12951649

 

话不多说直接上代码，带详细注释

测试类中测试方法如下

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/xerces/xercesImpl -->
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.9.1</version>
        </dependency>

 @Test
    public void testThreadPool() throws InterruptedException {

        ArrayBlockingQueue<Map<String,String>> queue = new ArrayBlockingQueue<>(100);
        new Thread(new Runnable() {
            @Override
            public void run() {
                ExcelUtil.readFirst("/Users/allin/Documents/新建XLSX 工作表.xlsx", new Callback() {
                    //每行数据存储为一个map,并记录对应行号和有效个数
                    @Override
                    public void callback(Map<String, String> map, int currentRowNumber, int availabledRows) {
                        try {
                            //把 map 加到 BlockingQueue 里，如果 BlockQueue 没有空间，则调用此方法的线程被阻断直到 BlockingQueue 里面有空间再继续
                            queue.put(map);
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                    }
                });
            }
        }).start();
        SimpleThreadPool pool = new SimpleThreadPool(5);
        boolean flag = true;
        int m = 0;
        while (flag){
            //取走 BlockingQueue 里排在首位的对象，若不能立即取出，则可以等 time 参数规定的时间,取不到时返回 null
            Map<String, String> map = queue.poll();
            if (map==null||map.isEmpty()){
                m++;
                if (m>100) flag=false;
                else
                    Thread.currentThread().sleep(10);
            }else {
                m = 0;
                A a = ExcelUtil.resultToObj(map, A.class);
                pool.execute(new BaseThreadPool.Execute() {
                    @Override
                    public void execute() {
                        mongoTemplate.save(a);
                    }
                });
            }
        }
    }

package com.allinmd.dossier.common.utils.excel;

import java.util.Map;
public interface Callback {
    /**
     *
     * @param result   <列索引,值> 数据值
     * @param currentRowNumber 当前数据所在行号
     * @param availabledRows 有效行数
     */
    void callback(Map<String,String> result,int currentRowNumber,int availabledRows);
}

package com.allinmd.dossier.common.utils.excel;


import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Workbook {
    String cell();

    String format() default "";
}

package com.allinmd.dossier.common.utils.excel;

import com.alibaba.fastjson.annotation.JSONField;

import java.math.BigDecimal;
import java.util.Date;

public class A {
    @Workbook(cell = "A")
    private Integer a;
    @Workbook(cell = "B")
    private Long b;
    @Workbook(cell = "C")
    private Double c;
    @Workbook(cell = "D")
    private Boolean d;
    @Workbook(cell = "E")
    private String e;
    @Workbook(cell = "F",format = "yyyy-MM-dd")
    @JSONField(format = "yyyy-MM-dd")
    private Date f;
    @Workbook(cell = "G")
    private BigDecimal g;
    @Workbook(cell = "H",format = "yyyy-MM-dd HH:mm:ss")
    @JSONField(format = "yyyy-MM-dd HH:mm:ss")
    private Date h;

    public Date getH() {
        return h;
    }

    public void setH(Date h) {
        this.h = h;
    }

    public Integer getA() {
        return a;
    }

    public void setA(Integer a) {
        this.a = a;
    }

    public Long getB() {
        return b;
    }

    public void setB(Long b) {
        this.b = b;
    }

    public Double getC() {
        return c;
    }

    public void setC(Double c) {
        this.c = c;
    }

    public Boolean getD() {
        return d;
    }

    public void setD(Boolean d) {
        this.d = d;
    }

    public String getE() {
        return e;
    }

    public void setE(String e) {
        this.e = e;
    }

    public Date getF() {
        return f;
    }

    public void setF(Date f) {
        this.f = f;
    }

    public BigDecimal getG() {
        return g;
    }

    public void setG(BigDecimal g) {
        this.g = g;
    }
}

 

package com.allinmd.dossier.common.utils.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.ZipPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Locale;
import java.util.Map;

public class ExcelUtil {

    /**
     * 只拿取第一个sheet
     * @param callback
     * @param file ,
     */
    public static void readFirst(File file,Callback callback)  {
        //获取xml文件
        XSSFReader reader = getXSSFReader(file);
        //获取xml处理器
        XMLReader parser = getXMLReader(callback,reader);
        //以迭代器形式获取xml中不同的表
        Iterator<InputStream> sheetsData = getSheetsData(reader);
        //解析所有sheet表数据
        parseFirst(sheetsData,parser);
    }


    public static void readFirst(String path,Callback callback){
        readFirst(new File(path),callback);
    }


    public static void readAll(File file,Callback callback){
        XSSFReader reader = getXSSFReader(file);
        XMLReader parser = getXMLReader(callback, reader);
        Iterator<InputStream> sheetsData = getSheetsData(reader);
        parseAll(sheetsData,parser);
    }

    public static void readAll(String path,Callback callback){
        readAll(new File(path),callback);
    }

    public static <T> T resultToObj(Map<String,String> result,Class<T> clazz) {
        try {
            T t = clazz.newInstance();
            Field[] fields = clazz.getDeclaredFields();
            for (Field field:fields){
                field.setAccessible(true);
                Workbook workbook = field.getDeclaredAnnotation(Workbook.class);
                if (workbook!=null){
                    String cell = workbook.cell();
                    String value = result.get(cell);
                    if (!StringUtils.isEmpty(value)){
                        value=value.trim();
                        if (field.getType()==String.class){
                            field.set(t,value);
                        }else if (field.getType()==Byte.class){
                            field.set(t,Byte.parseByte(value));
                        }else if (field.getType()==Short.class){
                            field.set(t,Short.parseShort(value));
                        }else if (field.getType()==Integer.class){
                            field.set(t,Integer.parseInt(value));
                        }else if (field.getType()==Long.class){
                            field.set(t,Long.parseLong(value));
                        }else if (field.getType()==Float.class){
                            field.set(t,Float.parseFloat(value));
                        }else if (field.getType()==Double.class){
                            field.set(t,Double.parseDouble(value));
                        }else if (field.getType()==Boolean.class){
                            field.set(t,value.equalsIgnoreCase("0")?false:true);
                        }else if (field.getType()==Character.class){
                            field.set(t,value.charAt(0));
                        }else if (field.getType()== BigDecimal.class){
                            field.set(t,new BigDecimal(value));
                        }else if (field.getType()== Date.class){
                            try {
                                double v = Double.parseDouble(value);
                                long m = (long) (v*24*60*60*1000);
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                long n = Math.abs(sdf.parse("1900-00-30").getTime());
                                field.set(t,new Date(m-n));
                            } catch (Exception e) {
                                String format = workbook.format();
                                try {
                                    field.set(t,new SimpleDateFormat(format).parse(value));
                                } catch (ParseException e1) {
                                    e1.printStackTrace();
                                    throw new RuntimeException(e1);
                                }
                            }
                        }
                    }
                }
                field.setAccessible(false);
            }
            return t;
        } catch (InstantiationException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    private static void parseAll(Iterator<InputStream> sheetsData, XMLReader parser) {
        while (sheetsData.hasNext()){
            parse(sheetsData,parser);
        }
    }

    private static void parse(Iterator<InputStream> sheetsData, XMLReader parser){
        try(InputStream inputStream = sheetsData.next()) {
            //解析指定的xml(即对应的sheet表)，具体解析方法参考解析器ExcelXlsxHandle 中的
            parser.parse(new InputSource(inputStream));
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    private static void parseFirst(Iterator<InputStream> sheetsData,XMLReader parser){
        if (sheetsData.hasNext()){
            parse(sheetsData,parser);
        }
    }

    private static XSSFReader getXSSFReader(File file){
        if (!file.getName().endsWith(".xlsx")) throw new RuntimeException("请使用word 2007的Excel格式,即xlsx格式");
        XSSFReader reader = null;
        try {
            //ZipPackage.open(file) 获取excel的xml压缩包，并进行解压
            reader = new XSSFReader(ZipPackage.open(file));
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (OpenXML4JException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return reader;
    }

    private static XMLReader getXMLReader(Callback callback, XSSFReader reader){
        XMLReader parser = null;
        try {
            //设置事件处理器对象
            parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        } catch (SAXException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        //设置事件处理器对象 为ExcelXlsxHandle ，用于处理reader中的xml文件
        parser.setContentHandler(new ExcelXlsxHandle(callback,reader));
        return parser;
    }

    private static Iterator<InputStream> getSheetsData(XSSFReader reader){
        try {
            return reader.getSheetsData();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.ROOT);
        Date parse = sdf.parse("1970-1-1 00:00:00");
        System.out.println(parse.getTime());
    }
}

package com.allinmd.dossier.common.utils.excel;


import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

public class ExcelXlsxHandle extends DefaultHandler {

    private CellDataType nextDataType=CellDataType.SSTINDEX;;
    private int formatIndex;
    private String formatString;
    private SharedStringsTable sst;

    /**
     * 单元格中的数据可能的数据类型
     */
    enum CellDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
    }


    private boolean isAvailabledOfRow = false;// 是否是有效行
    private int availabledRows = 0;
    private int totalRows = 0;
    private int currentRowNum = 0;
    private Map<String, String> cellMap = null;
    private String key;
    private String lastIndex;
    private Callback callback;


    public ExcelXlsxHandle(Callback callback, XSSFReader reader) {
        this.callback = callback;
        try {
            //将xml的字符串索引load到内存中；经过调试发现此时产生了OOM(内存溢出)情况
            sst=reader.getSharedStringsTable();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     *
     *  解析器在 XML 文档中的每个元素的开始调用此方法；对于每个 startElement 事件都将有相应的 endElement 事件（即使该元素为空时）。所有元素的内容都将在相应的 endElement 事件之前顺序地报告。
     * @param uri  名称空间 URI，如果元素没有名称空间 URI，或者未执行名称空间处理，则为空字符串
     * @param localName 本地名称（不带前缀），如果未执行名称空间处理，则为空字符串
     * @param qName 限定名（带有前缀），如果限定名不可用，则为空字符串
     * @param attributes 连接到元素上的属性。如果没有属性，则它将是空 Attributes 对象。在 startElement 返回后，此对象的值是未定义的
     * @throws SAXException
     */
    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if ("row".equalsIgnoreCase(qName)) {// 如果是行元素
            // 总行数+1
            totalRows++;
            // 获取行号
            String r = attributes.getValue("r");
            currentRowNum = Integer.parseInt(r);
            cellMap= new HashMap<>();
            isAvailabledOfRow=false;
        } else if ("c".equalsIgnoreCase(qName)) {// 如果是单元格
            // 获取键值
            key = getKey(attributes);
            // 先放入map,单此时值为null
            cellMap.put(key, null);
            this.setNextDataType(attributes);
        }
    }

    private String getKey(Attributes attributes){
        return attributes.getValue("r").replaceAll("\\d*","");
    }

    /**
     *接收字符数据的通知,可以通过new String(ch,start,length)构造器，创建解析出来的字符串文本.
     * @param ch  来自 XML 文档的字符
     * @param start 数组中的开始位置
     * @param length 从数组中读取的字符的个数
     * @throws SAXException
     */
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        //      super.characters(ch, start, length);
        lastIndex = new String(ch, start, length);
    }

    /**
     *
     * SAX 解析器会在 XML 文档中每个元素的末尾调用此方法；对于每个 endElement 事件都将有相应的 startElement 事件（即使该元素为空时）
     * @param uri 名称空间 URI，如果元素没有名称空间 URI，或者未执行名称空间处理，则为空字符串
     * @param localName  本地名称（不带前缀），如果未执行名称空间处理，则为空字符串
     * @param qName 限定的 XML 名称（带前缀），如果限定名不可用，则为空字符串
     * @throws SAXException
     */
    @Override
    public void endElement(String uri, String localName, String qName) throws SAXException {
        //super.endElement(uri, localName, qName);
        if ("v".equalsIgnoreCase(qName)) {// 如果是值标签
            String value = this.getDataValue(lastIndex.trim());
            if (!StringUtils.isEmpty(value)) {
                isAvailabledOfRow=true;
            }
            // 重设值
            cellMap.put(key, value);
        } else if ("c".equalsIgnoreCase(qName)) {
            // key置位null
            key = null;
            // lastIndex置位null
            lastIndex = null;

        } else if ("row".equalsIgnoreCase(qName)) {// 如果row是结束标签,说明一行结束

            if (isAvailabledOfRow) {
                // 如果是有效行
                // 是有效行则有效行数+1
                availabledRows++;
                // 回调,将结果输送给客户端,让客户端处理
                callback.callback(cellMap,currentRowNum,availabledRows);
            }
        }
    }

    /**
     * 处理数据类型
     *
     * @param attributes
     */
    public void setNextDataType(Attributes attributes) {
        //cellType为空，则表示该单元格类型为数字
        nextDataType = CellDataType.NUMBER;
        formatIndex = -1;
        formatString = null;
        //单元格类型
        String cellType = attributes.getValue("t");
        //
        String cellStyleStr = attributes.getValue("s");
        //获取单元格的位置，如A1,B1
        String columnData = attributes.getValue("r");

        if ("b".equals(cellType)) {
            //处理布尔值
            nextDataType = CellDataType.BOOL;
        } else if ("e".equals(cellType)) {
            //处理错误
            nextDataType = CellDataType.ERROR;
        } else if ("inlineStr".equals(cellType)) {
            nextDataType = CellDataType.INLINESTR;
        } else if ("s".equals(cellType)) {
            //处理字符串
            nextDataType = CellDataType.SSTINDEX;
        } else if ("str".equals(cellType)) {
            nextDataType = CellDataType.FORMULA;
        }
    }

    /**
     * 对解析出来的数据进行类型处理
     * @param value   单元格的值，
     *                value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
     *                SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
     * @return
     */
    @SuppressWarnings("deprecation")
    public String getDataValue(String value) {
        String thisStr = null;
        switch (nextDataType) {
            // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
            case BOOL: //布尔值
                thisStr=value;
                break;
            case ERROR: //错误
                thisStr = "\"ERROR:" + value.toString() + '"';
                break;
            case FORMULA: //公式
                thisStr = '"' + value.toString() + '"';
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                thisStr = rtsi.toString();
                rtsi = null;
                break;
            case SSTINDEX: //字符串
                String sstIndex = value.toString();
                try {
                    int idx = Integer.parseInt(sstIndex);
                    XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
                    thisStr = rtss.toString();
                    rtss = null;
                } catch (NumberFormatException ex) {
                    thisStr = value.toString();
                }
                break;
            case NUMBER: //数字
                thisStr=value;
                thisStr = thisStr.replace("_", "").trim();
                break;
            case DATE: //日期
                thisStr=value;
                break;
            default:
                thisStr = value;
                break;
        }
        return thisStr;
    }

    public static void main(String[] args){
        System.out.println("AA123".replaceAll("\\d*",""));
    }
}

package com.allinmd.dossier.common.utils.excel;


import java.util.concurrent.Semaphore;

/**
 * 封装线程池
 */
public abstract class BaseThreadPool {
    private Semaphore semaphore;
    //非静态代码块 在类的加载时进行加载，早于构造方法
    {
        init();
    }

    /**
     * 初始化方法,此方法会在构造方法之前,属性之后执行
     */
    protected abstract void init();

    /**
     * 构造方法执行
     */
    public BaseThreadPool(){
        this(5);
    }

    /**
     * 构造方法执行
     * @param permits 并发数
     */
    public BaseThreadPool(int permits){
        if (permits<1) {
            throw new RuntimeException("并发数至少为1");
        }
        // 这里我们需要用到它的代参构造Semaphore semaphore = new Semaphore(permits);
        // 参数permits的意思是同时可以存在多少个信号，或者说是同时可以并发多少个信号
        // 比如说：我有100个线程，同时并发只并发5个，则设置permits为5
        // 说到这里又涉及到一个问题就是，线程是越多越好么？
        // 答案自然是否定的，这里Google官方给出的最佳并发数是当前服务器内核数+1
        // 也就是说，pertits = cpu(内核数) + 1;假如是4核的则推荐最佳并发数是5
        // 因为我的电脑是4核的，所以这里就举例并发为5，这里不再进行代码的封装，
        // 只是举一个简单的例子
        //Semaphore semaphore = new Semaphore(5);// 设置并发信号量为5




        semaphore = new Semaphore(permits);
        afterConstructor(permits);
    }

    /**
     * 此为核心执行方法,
     * @param execute 回调接口,此为用户实现其核心执行内容
     */
    public void execute(Execute execute){
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    afterInitThread();
                    // 第一个：semaphore.acquire(); 获取信号或者说获取一把锁
                    semaphore.acquire();
                    beforeExecute();
                    execute.execute();
                    afterExecute();
                }catch (Exception e){
                    e.printStackTrace();
                    exeception(e);
                }finally {
                    // 第二个：semaphore.release(); 释放信号或者说释放一把锁
                    semaphore.release();
                    finallz();
                }
            }
        }).start();
    }

    /**
     * 在构造方法执行之后执行此方法
     * @param permits
     */
    protected abstract void afterConstructor(int permits);

    /**
     * 当线程初始化完成,但是还没来得及获取线程锁的时候,执行此方法
     */
    protected abstract void afterInitThread();

    /**
     * 在业务代码执行之前执行此方法
     */
    protected abstract void beforeExecute();

    /**
     * 在实际业务代码执行之后,执行此方法
     */
    protected abstract void afterExecute();

    /**
     * 当出异常时执行此方法
     */
    protected abstract void exeception(Exception e);

    /**
     * 当整个执行业务结束,不论是否出异常,都会执行此方法
     */
    protected abstract void finallz();

    public interface Execute{
        void execute();
    }
}

package com.allinmd.dossier.common.utils.excel;

import com.allinmd.dossier.common.utils.IdUtils;
import lombok.extern.slf4j.Slf4j;

import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.atomic.AtomicInteger;
@Slf4j
public class SimpleThreadPool extends BaseThreadPool {

    private int permins;
    private ArrayBlockingQueue<String> queue;
    // 这里我想对线程进行计数，但是是多线程并发，所以不能直接用i++来计数，
    // 因此我们需要进行原子操作，为此，我们再次介绍一个工具包，“原子”，Atomic
    // 这里我们使用整形的AtomicInteger，因为需要在内部类中使用，所以声明为成员变量，设置初始化为0
    private AtomicInteger total;// 线程总数
    private AtomicInteger core;// 核心池中的线程数
    private AtomicInteger wait;// 等待数

    public SimpleThreadPool(int permits){
        super(permits);
    }

    @Override
    protected void afterConstructor(int permits) {
        this.permins=permits;
    }


    @Override
    protected void init() {
        queue = new ArrayBlockingQueue<String>(100);
        total = new AtomicInteger(0);
        core = new AtomicInteger(0);
        wait = new AtomicInteger(0);
    }

    @Override
    protected void afterInitThread() {
        total.addAndGet(1);
        wait.addAndGet(1);
        String threadId = IdUtils.uuid();
        Thread.currentThread().setName(threadId);
        log.debug("线程["+threadId+"]初始化完成");
    }

    @Override
    protected void beforeExecute() {
        String name = Thread.currentThread().getName();
        log.debug("线程["+name+"]进入核心池...");
        wait.addAndGet(-1);
        core.addAndGet(1);
        String uuid = IdUtils.uuid();
        try {
            queue.put(uuid);
        } catch (InterruptedException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    @Override
    protected void afterExecute() {
        core.addAndGet(-1);
    }

    @Override
    protected void exeception(Exception e) {
        throw new RuntimeException(e);
    }

    @Override
    protected void finallz() {
        String poll = queue.poll();
        String threadId = Thread.currentThread().getName();
        log.debug("线程["+threadId+"]出去了");
    }

    public void callback(Callback callback){
        callback.callback(total.get(),core.get(),wait.get());
    }

    /**
     * 获取总数,调用此方法,程序会进入500毫秒等待,然后判定是否有等待线程,如果有则递归,如果没有则返回
     * @return
     */
    public int getTotal() throws InterruptedException {
        Thread.currentThread().sleep(500);
        if (queue.isEmpty()) {return total.get();}
        else {return getTotal();}
    }

    public interface Callback{
        void callback(int total,int core,int wait);
    }
}