java多线程导入excel(poi)

导入excel util

/**
 * @Description: excel导入工具类

 * @Author: hg

 * @CreateDate: 2019/6/4 11:58

 */
@SuppressWarnings("all")
public class POIUtil<T> {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    private Workbook wb;

    private String[] property;

    private Class<T> c;

    private String filePath;

    private int startRow;
    
    private int startColumn;

    //是否需要hash
    private boolean isHash = false;

    //存放hash集合
    private HashSet<Integer> hashSet;
　　
    private CopyOnWriteArrayList beanList;

    public CopyOnWriteArrayList getBeanList() {
        return beanList;
    }

    public POIUtil(String[] property, Class<T> c, String filePath, int startRow, int startColumn) {
        this.property = property;
        this.c = c;
        this.filePath = filePath;
        this.wb = getWorkbook(filePath);
        this.startRow = startRow;
        this.startColumn = startColumn;
        this.beanList = new CopyOnWriteArrayList();
    }

    /**
     * @Description:
     * @Param: [property 类的属性字符串数组, c class对下你给, filePath excel地址,
     * startRow 开始行, startColumn 开始列, hashSet 不需要必填，hash校验]
     * @return:
     * @Author: hg
     * @Date: 2019/6/4
     */
    public POIUtil(String[] property, Class<T> c, String filePath, int startRow, int startColumn, HashSet<Integer> hashSet) {
        this.property = property;
        this.c = c;
        this.filePath = filePath;
        this.wb = getWorkbook(filePath);
        this.startRow = startRow;
        this.startColumn = startColumn;
        this.hashSet = hashSet;
        this.isHash = true;
        this.beanList = new CopyOnWriteArrayList();
    }

    /**
     * @Description:
     * @Param: [startSheet sheet开始, endSheet sheet结束]
     * @return: void
     * @Author: hg
     * @Date: 2019/6/4
     */
    public void getBeanList(int startSheet, int endSheet) throws IllegalAccessException, InstantiationException, InvocationTargetException {
        HashMap<String, String> valueMap = new HashMap<>();
        for (int sheetNum = startSheet; sheetNum < endSheet; sheetNum++) {
            Sheet sheet = wb.getSheetAt(sheetNum);
            for (int rowNum = startRow; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                for (int i = 0, columnNum = startColumn; i < property.length; i++, columnNum++) {
                    Cell cell = row.getCell(columnNum);
                    valueMap.put(property[i], getCellValue(cell, cell.getCellType()));
                }
                //这里使用clone效率更高
                T t = c.newInstance();
                BeanUtils.populate(t, valueMap);

                //校验非空值
                try {
                    ValidatorUtils.validateEntity(t);
                } catch (WLHSEException e) {
                    continue;
                }

                //hash校验
                if (isHash) {
                    synchronized (hashSet) {
                        if (hashSet.contains(t.hashCode()))
                            continue;
                        hashSet.add(t.hashCode());
                    }
                }
                beanList.add(t);
            }
        }
    }

    private Workbook getWorkbook(String filePath) {
        Workbook wb = null;
        try {
            if (null != filePath) {
                FileInputStream fis = new FileInputStream(filePath);
                if (filePath.endsWith(".xls")) {
                    wb = new HSSFWorkbook(fis);
                } else if (filePath.endsWith(".xlsx")) {
                    wb = new XSSFWorkbook(fis);
                }
                return wb;
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return null;
    }

    private String getCellValue(Cell rowCell, int rowCellType) {
        String value = "";
        switch (rowCellType) {
            case Cell.CELL_TYPE_STRING:
                value = rowCell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                String dataFormat = rowCell.getCellStyle().getDataFormatString();
                AtomicReference<Boolean> isDate = new AtomicReference<>(false);
                if (DateUtil.isCellDateFormatted(rowCell)) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue()));
                } else if (DateUtil.isCellInternalDateFormatted(rowCell)) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue()));
                } else if (isDate.get()) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(rowCell.getDateCellValue());
                } else if (dataFormat == null) {
                    value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue()));
                } else {
                    if (dataFormat != null) {
                        value = String.valueOf(rowCell.getNumericCellValue());
                    } else {
                        if (rowCell.getCellStyle().getDataFormatString().contains("$")) {
                            value = "$" + rowCell.getNumericCellValue();
                        } else if (rowCell.getCellStyle().getDataFormatString().contains("￥")) {
                            value = "￥" + rowCell.getNumericCellValue();
                        } else if (rowCell.getCellStyle().getDataFormatString().contains("¥")) {
                            value = "¥" + rowCell.getNumericCellValue();
                        } else if (rowCell.getCellStyle().getDataFormatString().contains("€")) {
                            value = "€" + String.valueOf(rowCell.getNumericCellValue());
                        } else {
                            value = String.valueOf(rowCell.getNumericCellValue());
                        }
                    }
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(rowCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                value = ErrorEval.getText(rowCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = rowCell.getCellFormula();
                break;
        }
        return value;
    }

    public Workbook getWb() {
        return wb;
    }
}

导入excel线程

public class POIThread implements Runnable {
    private int startSheet;
    private int endSheet;
    private POIUtil util;
    private CountDownLatch countDownLatch;


    POIThread(int startSheet, int endSheet, POIUtil util, CountDownLatch countDownLatch) {
        this.startSheet = startSheet;
        this.endSheet = endSheet;
        this.util = util;
        this.countDownLatch = countDownLatch;
    }

    @Override
    public void run() {
        try {
            util.getBeanList(startSheet, endSheet);
        } catch (IllegalAccessException | InstantiationException | InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            countDownLatch.countDown();
        }
    }
}

执行线程

public class POIThreadHelper {
    public void work(POIUtil util) {
        //一个sheet分配一个线程
        int runSize = util.getWb().getNumberOfSheets();

        ExecutorService executor = Executors.newFixedThreadPool(runSize);
        CountDownLatch countDownLatch = new CountDownLatch(runSize);

        for (int i = 0; i < runSize; i++) {
            executor.execute(new POIThread(i, i + 1, util,countDownLatch));
        }

        try {
            countDownLatch.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        executor.shutdown();
    }
}

测试

@Test
    public void test1() throws IllegalAccessException, InvocationTargetException, InstantiationException {
        String path = "C:\logs\test.xls";
        String[] test = {"name", "sex"};
        POIUtil<Student> poiUtil = new POIUtil<Student>(test, Student.class, path, 1, 0);
        POIThreadHelper helper = new POIThreadHelper();
        helper.work(poiUtil);
        for (Object o : poiUtil.getBeanList()) {
            System.out.println(o.toString());
        }
    }

 

结果