Excel 解析工具类实现Demo，通过XSSFSheetXMLHandler使用实现

文章目录

一、功能概述
二、BigExcelAnalysisUtil类
三、SheetRuleUtil 类
其他
- SheetContentsHandler 使用讲解

一、功能概述

可以校验表头
以sheet维度，读取数据
可以根据反射，自动把excel中的数据封装到bean
主要使用了OPCPackage、XSSFReader、XSSFSheetXMLHandler、XMLReader 读取数据
具体的执行demo，下载绑定的代码资源即可

二、BigExcelAnalysisUtil类

excel数据的解析过程

package org.example.ljj.util;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.example.ljj.util.model.*;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

public class BigExcelAnalysisUtil {

    private OPCPackage xlsxPackage;

    /**
     * 私有内部类
     */
    private class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

        HashMap<String, String> rowData = null;
        List<HashMap<String, String>> datas = null;

        public SimpleSheetContentsHandler(List<HashMap<String, String>> datas) {
            this.datas = datas;
        }

        /**
         * 当数据和行号对不上的时候，补null
         *
         * @param rowNum 当前行号
         */
        @Override
        public void startRow(int rowNum) {
            if (datas.size() < rowNum) {
                for (int i = 0; i < rowNum; i++) {
                    datas.add(null);
                }
            }
            rowData = new HashMap<String, String>(16);
        }

        /**
         * 当前行中的，cellReference列数据处理
         *
         * @param cellReference，列号，A1，B1，C1...
         * @param formattedValue，该单元格的值
         * @param comment
         */
        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {

            // 读取的列号，cellReference为“A1 A2 A3...”的时候，thisCol为0
            int thisCol = (new CellReference(cellReference)).getCol();
            if (!StringUtils.isEmpty(cellReference)) {
                // formattedValue，为该单元格的值
                formattedValue = formattedValue.trim();
            }
            rowData.put(String.valueOf(thisCol), formattedValue);
        }

        /**
         * 全部列读完之后，做行处理
         *
         * @param rowNum 行号
         */
        @Override
        public void endRow(int rowNum) {
            datas.add(rowData);
        }

        /**
         * 文件表头和表尾处理，这里无需做任何处理
         *
         * @param text
         * @param isHeader
         * @param tagName
         */
        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {

        }

    }

    /**
     * 解析文件，返回excel的数据
     *
     * @param filePath
     * @param sheetrules
     * @return
     * @throws Exception
     */
    public WorkBookDataResult process(String filePath, List<SheetRule> sheetrules) throws Exception {
        WorkBookDataResult workBookData = new WorkBookDataResult();
        workBookData.setSuccess(true);
        this.xlsxPackage = OPCPackage.open(filePath, PackageAccess.READ);
        // 只读字符表
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
        // Xssf读取
        XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
        // 样式表
        StylesTable styles = xssfReader.getStylesTable();
        //读取文件数据，生成List<InputStream>
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        //xml文件解析器
        XMLReader parser = SAXHelper.newXMLReader();
        int sheetIndex = 0;
        List<SheetDataResult> sheetDatas = new ArrayList<>();
        //一个个sheet分开读取，分开处理
        while (sheets.hasNext()) {
            //获取当前sheet的数据
            InputStream sheet = sheets.next();
            //获取当前sheet的校验规则
            SheetRule sheetRule = this.getSheetRule(sheetrules, sheetIndex);
            //如果不为空才获取数据
            if (sheetRule != null) {
                //获取当前sheet名
                String sheetName = sheets.getSheetName();
                //保存当前sheet的所有数据
                List<HashMap<String, String>> datas = new ArrayList<>();
                //通过把 datas 放入控制器中，通过控制器读取excel的数据，同时把数据放入datas中，这里的datas只是初始化
                XSSFSheetXMLHandler.SheetContentsHandler sheetHandler = new SimpleSheetContentsHandler(datas);
                //解析sheet数据，这里执行结束，datas将有数据
                this.processSheet(parser, styles, strings, sheetHandler, sheet);
                //数据已经读取，释放资源
                sheet.close();
                System.out.println(sheetName + " rows=" + datas.size());
                //验证+组装数据
                String sheetAnalysisResult = this.processData(sheetDatas, sheetName, datas, sheetRule);
                if (!"SUCCESS".equals(sheetAnalysisResult)) {
                    workBookData.setErrMsg(sheetAnalysisResult);
                    workBookData.setSuccess(false);
                    sheetDatas.clear();
                    break;
                }
            }
            sheetIndex++;
        }
        workBookData.setWorkDatas(sheetDatas);
        return workBookData;
    }


    /**
     * 解析文件中sheet数据
     *
     * @param parser
     * @param styles
     * @param strings
     * @param sheetHandler
     * @param sheetInputStream
     * @throws IOException
     * @throws SAXException
     */
    private void processSheet(XMLReader parser, StylesTable styles, ReadOnlySharedStringsTable strings,
                              XSSFSheetXMLHandler.SheetContentsHandler sheetHandler, InputStream sheetInputStream) throws IOException, SAXException {
        ContentHandler handler = new XSSFSheetXMLHandler(styles, strings, sheetHandler, false);
        InputSource sheetSource = new InputSource(sheetInputStream);
        //设置内容格式
        parser.setContentHandler(handler);
        //解析XMl文件数据
        parser.parse(sheetSource);
    }


    /**
     * 验证+组装数据
     *
     * @param sheetDatas
     * @param sheetName
     * @param datas      excel中单个sheet的数据：含表头数据和普通数据
     * @param sheetRule
     * @return
     * @throws Exception
     */
    private String processData(List<SheetDataResult> sheetDatas, String sheetName, List<HashMap<String, String>> datas, SheetRule sheetRule) throws Exception {
        List<CellRule> cellRules = sheetRule.getCellRules();

        //解析校验表头
        if (!checkSheetTitles(sheetRule, datas)) {
            return "导入文件的表头和模板的表头不一致!";
        }
        //校验数据
        int startRow = sheetRule.getStartRow();
        String mapBeanName = sheetRule.getMapBean();
        boolean mapBean = StringUtils.isEmpty(mapBeanName) ? false : true;
        //获取数据转换字典
        HashMap<String, HashMap<String, String>> dictionary = sheetRule.getDictionary();
        List<Map<String, Object>> sheetMapDatas = new ArrayList<Map<String, Object>>();
        List<Object> sheetBeanDatas = new ArrayList<Object>();
        //解析行数据
        for (int rowIndex = 0 + startRow; rowIndex < datas.size(); rowIndex++) {
            // 过滤空行
            if (datas.get(rowIndex) == null || datas.get(rowIndex).isEmpty()) {
                continue;
            }
            HashMap<String, Object> mapData = null;
            Object beanData = null;
            Class beanClass = null;
            if (!mapBean) {
                mapData = new HashMap<>(16);
            } else {
                //根据 mapBeanName 创建对应的对象
                beanClass = Class.forName(mapBeanName);
                beanData = beanClass.newInstance();
            }
            //获取单元格数据，按照模板的顺序遍历，cellRule对象里面的columnIndex存储了数据在文件的具体位置（列）
            for (int ls = 0; ls < cellRules.size(); ls++) {
                CellRule cellRule = cellRules.get(ls);
                //获取单个单元格的值
                String cellData = datas.get(rowIndex).get(String.valueOf(cellRule.getColumnIndex()));
                //校验该单元格的数据是否合法
                CheckCellInfo checkCellInfo = checkCelldata(cellRule, cellData);
                if (!checkCellInfo.isSuccess()) {
                    return sheetName + "第" + (rowIndex + 1) + "行" + checkCellInfo.getMeg();
                } else {
                    //查询是否有字典名
                    String transformDicName = cellRule.getTransformDicName();
                    //如果有字典，做转换
                    if (!StringUtils.isEmpty(transformDicName)) {
                        cellData = dictionary.get(transformDicName).get(cellData);
                    }
                    if (!mapBean && mapData != null) {
                        String key = cellRule.getMapColumn();
                        mapData.put(key, cellData);
                    } else if (beanClass != null) {
                        String beanField = cellRule.getBeanFiled();
                        Field field = beanClass.getDeclaredField(beanField);
                        //设置访问权限为可访问（如果需要访问私有字段）
                        field.setAccessible(true);
                        //通过字段反射类field，给beanData对象赋值
                        field.set(beanData, transFiledData(field, cellData));
                    }
                }
            }
            //把一行的数据存储起来
            if (!mapBean) {
                sheetMapDatas.add(mapData);
            } else {
                sheetBeanDatas.add(beanData);
            }
        }
        //把sheet的数据封装到sheetDataResult中
        SheetDataResult sheetDataResult = new SheetDataResult();
        sheetDataResult.setSheetIndex(sheetRule.getSheetIndex());
        sheetDataResult.setSheetName(sheetName);
        //把sheet的数据放入sheetDataResult中
        if (!mapBean) {
            sheetDataResult.setSheetDatas(sheetMapDatas);
            sheetDataResult.setRowNum(sheetMapDatas.size());
        } else {
            sheetDataResult.setSheetBeanDatas(sheetBeanDatas);
            sheetDataResult.setRowNum(sheetBeanDatas.size());
        }
        sheetDatas.add(sheetDataResult);
        return "SUCCESS";
    }


    /**
     * 解析校验表头
     *
     * @param sheetRule 当前sheet的表头规则
     * @param datas     文件数据
     * @return
     */
    private boolean checkSheetTitles(SheetRule sheetRule, List<HashMap<String, String>> datas) {
        List<List<SheetTitle>> sheetTitles = sheetRule.getSheetTitles();
        List<CellRule> cellRules = sheetRule.getCellRules();
        if (!CollectionUtils.isEmpty(sheetTitles)) {
            //根据文件的表头更新，数据所在列，用于后续的数据组装
            List<CellRule> dynamicCellRule = new ArrayList<>();
            //遍历模板表头的第一行
            for (int cl = 0; cl < sheetTitles.get(0).size(); cl++) {
                //单个空格的title值
                SheetTitle sheetTitle = sheetTitles.get(0).get(cl);
                //文件中的数据
                HashMap<String, String> titleRow = datas.get(0);
                boolean findTitle = false;
                int columnIndex = 0;
                if (titleRow != null) {
                    //文件中的数据
                    Iterator<String> titleKye = titleRow.keySet().iterator();
                    //通过遍历文件中的表头寻找title的值
                    while (titleKye.hasNext()) {
                        String titleColumn = titleKye.next();
                        String titleValue = titleRow.get(titleColumn);
                        //找到文件中第一行中的表头
                        if (sheetTitle.getTitleValue().equals(titleValue)) {
                            sheetTitle.setColumnIndex(Integer.parseInt(titleColumn));
                            findTitle = true;
                            columnIndex = Integer.parseInt(titleColumn);
                            //循环比较同一列的所有表头（其它行）
                            for (int i = 1; i < sheetTitles.size(); i++) {
                                if (!sheetTitles.get(i).get(cl).getTitleValue().equals(datas.get(i).get(titleColumn))) {
                                    return false;
                                }
                            }
                            break;
                        }
                    }
                }
                //当这个表头是必须的，同时不存在，则返回表头检验不合格
                if (sheetTitle.isRequire() && !findTitle) {
                    return false;
                }
                //从文件中找到表头，做标记处理
                if (findTitle) {
                    //获取和标题对应的解析列规则,重新修改列坐标,加个判定防止数组越界错误
                    if (cl < cellRules.size()) {
                        CellRule cellRule = cellRules.get(cl);
                        cellRule.setColumnIndex(columnIndex);
                        dynamicCellRule.add(cellRule);
                    }
                }
            }
            //把dynamicCellRule中的值赋给cellRules，用于后续的数据组装
            cellRules.clear();
            cellRules.addAll(dynamicCellRule);
        }
        return true;
    }

    /**
     * 校验该单元格的数据是否合法
     *
     * @param cellRule 校验规则
     * @param value    值
     * @return
     */
    private static CheckCellInfo checkCelldata(CellRule cellRule, String value) {
        CheckCellInfo checkCellInfo = null;
        if (cellRule != null) {
            checkCellInfo = cellRule.checkData(value);
        } else {
            checkCellInfo = new CheckCellInfo();
            checkCellInfo.setSuccess(true);
        }
        return checkCellInfo;
    }

    /**
     * 把数据转化为bean中的字段的数据类型
     *
     * @param field bean中的字段
     * @param data  数据
     * @return
     */
    private static Object transFiledData(Field field, Object data) {
        Object value = null;
        String fileType = field.getType().getName();
        fileType = fileType.substring(fileType.lastIndexOf(".") + 1);
        if (data != null && !StringUtils.isEmpty(String.valueOf(data))) {
            try {
                switch (fileType) {
                    case "String":
                        value = String.valueOf(data);
                        break;
                    case "int":
                        value = Integer.parseInt(String.valueOf(data));
                        break;
                    case "Short":
                        value = Short.parseShort(String.valueOf(data));
                        break;
                    case "Integer":
                        value = Integer.parseInt(String.valueOf(data));
                        break;
                    case "double":
                        value = Double.parseDouble(String.valueOf(data));
                        break;
                    case "float":
                        value = Float.parseFloat(String.valueOf(data));
                        break;
                    case "Date":
                        String format = "yyyy-MM-dd HH:mm:ss";
                        if (!String.valueOf(data).contains(":")) {
                            if (String.valueOf(data).contains("/")) {
                                format = "yyyy/MM/dd";
                            } else {
                                format = "yyyy-MM-dd";
                            }
                        } else {
                            if (String.valueOf(data).contains("/")) {
                                format = "yyyy/MM/dd HH:mm:ss";
                            }
                        }
                        SimpleDateFormat sdf = new SimpleDateFormat(format);
                        ;
                        value = sdf.parse(String.valueOf(data));
                        break;
                    case "boolean":
                        value = Boolean.parseBoolean(String.valueOf(data));
                        break;
                    case "char":
                        value = String.valueOf(data).charAt(0);
                        break;
                    case "long":
                        value = Long.parseLong(String.valueOf(data));
                        break;
                    case "Long":
                        value = Long.parseLong(String.valueOf(data));
                        break;
                    default:
                        value = data;
                        break;
                }
            } catch (Exception e) {
                System.out.println("数据转换异常!");
                e.printStackTrace();
            }
        }
        return value;
    }

    /**
     * 获取当前sheet的校验规则
     *
     * @param sheetrules
     * @param sheetIndex
     * @return
     */
    private SheetRule getSheetRule(List<SheetRule> sheetrules, int sheetIndex) {
        if (sheetrules != null && sheetrules.size() > 0) {
            for (SheetRule sheetRule : sheetrules) {
                if (sheetRule.getSheetIndex() == sheetIndex) {
                    return sheetRule;
                }
            }
        }
        return null;
    }

}

三、SheetRuleUtil 类

解析xml配置文件，获取Excel的解析规则

package org.example.ljj.util;

import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.example.ljj.util.model.*;
import org.example.ljj.util.enums.DataType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.*;

/**
 * @ClassName: AnaylisisXml
 * @Description: 解析xml配置文件
 * @author: ljj
 * @date: 2018-9-5 上午8:50:07
 */

public class SheetRuleUtil {
    private static final Logger logger = LoggerFactory.getLogger(SheetRuleUtil.class);

    /**
     * 读取xml文件得到配置规则
     * @param in 文件输入流
     * @return
     */
    public static List<SheetRule> analysiXml(InputStream in) {
        // 解析xml生成对应的bean
        SAXReader saxReader = new SAXReader();
        List<SheetRule> sheetRules = new ArrayList<SheetRule>();
        try {
            if (in != null) {
                Document document = saxReader.read(in);
                Element rootElement = document.getRootElement();
                Iterator<Element> sheets = rootElement.element("sheets").elements().iterator();
                while (sheets.hasNext()) {
                    Element sheet = sheets.next();
                    //解析sheet规则
                    SheetRule sheetRule = analysisSheetRule(sheet);
                    int startColumn = sheetRule.getStartColumn();
                    //解析sheet数据规则
                    List<CellRule> cellRules = analysisCellRules(sheet, startColumn);
                    sheetRule.setCellRules(cellRules);
                    //解析字典表
                    HashMap<String, HashMap<String, String>> dictionary = analysisDictionary(sheet);
                    sheetRule.setDictionary(dictionary);
                    //解析表头标题数据
                    List<List<SheetTitle>> sheetTitles = analysisSheetTitle(sheet);
                    sheetRule.setSheetTitles(sheetTitles);
                    sheetRules.add(sheetRule);
                }
            }
        } catch (Exception e) {
            logger.error("解析xml配置异常", e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return sheetRules;
    }

    /**
     * @param sheet
     * @return
     * @return SheetRule
     * @Title: analysisSheetRule
     * @Description: 解析sheet规则
     */
    private static SheetRule analysisSheetRule(Element sheet) {
        Boolean isCommon = Boolean.valueOf(sheet.attributeValue("isCommon"));
        int sheetIndex = Integer.parseInt(sheet.attributeValue("sheetIndex"));
        int startRow = Integer.parseInt(sheet.attributeValue("startRow"));
        int startColumn = Integer.parseInt(sheet.attributeValue("startColumn"));
        int columnSize = Integer.parseInt(sheet.attributeValue("columnSize"));
        String sheetName = sheet.attributeValue("sheetName");
        String mapBean = sheet.attributeValue("mapBean");
        SheetRule sheetRule = new SheetRule(sheetIndex, startRow, startColumn, columnSize, null, isCommon);
        sheetRule.setSheetName(sheetName);
        sheetRule.setMapBean(mapBean);
        return sheetRule;
    }

    /**
     * @param sheet
     * @return
     * @return SheetRule
     * @Title: analysisSheetRule
     * @Description: 解析sheet数据规则
     */
    private static List<CellRule> analysisCellRules(Element sheet, int startColumn) {
        Iterator<Element> cells = sheet.element("cells").elements().iterator();
        List<CellRule> cellRules = new ArrayList<CellRule>();
        int colIndex = 0;
        while (cells.hasNext()) {
            Element cell = cells.next();
            String cellName = cell.attributeValue("cellName");
            boolean notNull = "true".equals(cell.attributeValue("notNull")) ? true : false;
            String mapColumn = cell.attributeValue("mapColumn");
            String beanFiled = cell.attributeValue("beanFiled");
            String dataType = cell.attributeValue("dataType");
            String transformDicName = cell.attributeValue("transformDicName");
            boolean isMulti = "true".equals(cell.attributeValue("isMulti")) ? true : false;
            String multiSplit = cell.attributeValue("multiSplit");
            String reJoinSplit = cell.attributeValue("reJoinSplit");
            String labelTypeCode = cell.attributeValue("labelTypeCode");
            CellRule cellRule = null;
            switch (DataType.getCodeValue(dataType)) {
                case 1:
                    try {
                        int sLeg = Integer.parseInt(cell.attributeValue("maxLength"));
                        boolean checkIllegalChar = "false".equals(cell.attributeValue("checkIllegalChar")) ? false : true;
                        cellRule = new StringCellRule(cellName, notNull, mapColumn, beanFiled, sLeg, checkIllegalChar);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    break;
                case 2:
                    int maxLength = Integer.parseInt(cell.attributeValue("maxLength"));
                    cellRule = new IntegerCellRule(cellName, notNull, mapColumn, beanFiled, maxLength);
                    break;
                case 3:
                    int maxLg = Integer.parseInt(cell.attributeValue("maxLength"));
                    int decimalLength = Integer.parseInt(cell.attributeValue("decimalLength"));
                    cellRule = new DoubleCellRule(cellName, notNull, mapColumn, beanFiled, maxLg, decimalLength);
                    break;
                case 4:
                    cellRule = new DateCellRule(cellName, notNull, mapColumn, beanFiled);
                    break;
                case 5:
                    cellRule = new DateTimeCellRule(cellName, notNull, mapColumn, beanFiled);
                    break;
                case 6:
                    String expression = cell.attributeValue("expression");
                    cellRule = new RegularCellRule(cellName, notNull, mapColumn, beanFiled, expression);
                    cellRule.setIsMulti(isMulti);
                    cellRule.setMultiSplit(multiSplit);
                    cellRule.setLabelTypeCode(labelTypeCode);
                    cellRule.setReJoinSplit(reJoinSplit);
                    break;
            }
            ;
            if (cell != null) {
                cellRule.setTransformDicName(transformDicName);
            }
            cellRule.setColumnIndex(startColumn + colIndex);
            cellRules.add(cellRule);
            colIndex++;
        }
        return cellRules;
    }

    /**
     * @param sheet
     * @return
     * @return HashMap<String, HashMap < String, Object>> (这里用一句话描述返回结果说明)
     * @Title: analysisDictionary
     * @Description: 解析字典表
     */
    private static HashMap<String, HashMap<String, String>> analysisDictionary(Element sheet) {
        HashMap<String, HashMap<String, String>> dicsMap = new HashMap<String, HashMap<String, String>>();
        Element dicsElement = sheet.element("dics");
        if (dicsElement != null) {
            Iterator<Element> dics = dicsElement.elements().iterator();
            while (dics.hasNext()) {
                Element dic = dics.next();
                String dicName = dic.attributeValue("name");
                Iterator<Element> kv = dic.elements().iterator();
                HashMap<String, String> dicMap = new HashMap<String, String>();
                while (kv.hasNext()) {
                    Element dicElementData = kv.next();
                    String key = dicElementData.attributeValue("key");
                    String value = dicElementData.getTextTrim();
                    dicMap.put(key, value);
                }
                dicsMap.put(dicName, dicMap);
            }
        }
        return dicsMap;
    }

    /**
     * @param sheet
     * @return
     * @return List<SheetTitle>
     * @Title: analysisSheetTitle
     * @Description: 解析表头标题数据
     */
    private static List<List<SheetTitle>> analysisSheetTitle(Element sheet) {
        //存储多行表头
        List<List<SheetTitle>> sheetTitles = new LinkedList<>();
        Element titlesElements = sheet.element("titles");
        if (titlesElements != null) {
            Iterator<Element> titles = titlesElements.elements().iterator();
            while (titles.hasNext()) {
                //存储单行表头
                List<SheetTitle> list = new LinkedList<>();
                Element title = titles.next();
                //获取行号，从0开始
                int rowIndex = Integer.parseInt(title.attributeValue("rowIndex"));
                Iterator<Element> texts = title.elements().iterator();
                while (texts.hasNext()) {
                    Element value = texts.next();
                    int colIndex = Integer.parseInt(value.attributeValue("columnIndex"));
                    String titleValue = value.getTextTrim();
                    SheetTitle sheetTitle = new SheetTitle(rowIndex, colIndex, titleValue);
                    //默认为true，如果不写require属性
                    boolean require = "false".equals(value.attributeValue("require")) ? false : true;
                    sheetTitle.setRequire(require);
                    list.add(sheetTitle);
                }
                sheetTitles.add(list);
            }
        }
        return sheetTitles;
    }

}