解决POI读取Excel百万级内存溢出问题

素颜马尾好姑娘i 2023-06-28 06:15 47阅读 0赞

使用传统poi来操作大数据量的excel会出现内存溢出的问题，根据各种资源，亲试了一个可用工具类，附代码如下：

### 一、基于eventusermodel的excel解析工具类 ###

package com.taikang.task.service.excel;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;  
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;  
import org.apache.poi.openxml4j.opc.OPCPackage;  
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.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;  
import org.apache.poi.xssf.model.StylesTable;  
import org.apache.poi.xssf.usermodel.XSSFComment;  
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
import org.springframework.stereotype.Component;  
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.FileInputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.util.ArrayList;  
import java.util.List;

/\*\*  
 \* 解析大数据量Excel工具类  
 \* @author   
 \*  
 \*/  
@Component  
public class ExcelParser \{  
    private static final Logger logger = LoggerFactory.getLogger(ExcelParser.class);  
    /\*\*  
     \* 表格默认处理器  
     \*/  
    private ISheetContentHandler contentHandler = new DefaultSheetHandler();  
    /\*\*  
     \* 读取数据  
     \*/  
    private List<String\[\]> datas = new ArrayList<String\[\]>();

/\*\*  
     \* 转换表格,默认为转换第一个表格  
     \* @param stream  
     \* @return  
     \* @throws InvalidFormatException  
     \* @throws IOException  
     \* @throws ParseException  
     \*/  
    public ExcelParser parse(InputStream stream)  
            throws InvalidFormatException, IOException, ParseException \{  
        return parse(stream, 1);  
    \}

/\*\*  
     \*  
     \* @param stream  
     \* @param sheetId:为要遍历的sheet索引，从1开始  
     \* @return  
     \* @throws InvalidFormatException  
     \* @throws IOException  
     \* @throws ParseException  
     \*/  
    public synchronized ExcelParser parse(InputStream stream, int sheetId)  
            throws InvalidFormatException, IOException, ParseException \{  
        // 每次转换前都清空数据  
        datas.clear();  
        // 打开表格文件输入流  
        OPCPackage pkg = OPCPackage.open(stream);  
        try \{  
            // 创建表阅读器  
            XSSFReader reader;  
            try \{  
                reader = new XSSFReader(pkg);  
            \} catch (OpenXML4JException e) \{  
                logger.error("读取表格出错");  
                throw new ParseException(e.fillInStackTrace());  
            \}

// 转换指定单元表  
            InputStream shellStream = reader.getSheet("rId" + sheetId);  
            try \{  
                InputSource sheetSource = new InputSource(shellStream);  
                StylesTable styles = reader.getStylesTable();  
                ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);  
                getContentHandler().init(datas);// 设置读取出的数据  
                // 获取转换器  
                XMLReader parser = getSheetParser(styles, strings);  
                parser.parse(sheetSource);  
            \} catch (SAXException e) \{  
                logger.error("读取表格出错");  
                throw new ParseException(e.fillInStackTrace());  
            \} finally \{  
                shellStream.close();  
            \}  
        \} finally \{  
            pkg.close();

\}  
        return this;

/\*\*  
     \* 获取表格读取数据,获取数据前，需要先转换数据<br>  
     \* 此方法不会获取第一行数据  
     \*  
     \* @return 表格读取数据  
     \*/  
    public List<String\[\]> getDatas() \{  
        return getDatas(true);

/\*\*  
     \* 获取表格读取数据,获取数据前，需要先转换数据  
     \*  
     \* @param dropFirstRow  
     \*            删除第一行表头记录  
     \* @return 表格读取数据  
     \*/  
    public List<String\[\]> getDatas(boolean dropFirstRow) \{  
        if (dropFirstRow && datas.size() > 0) \{  
            datas.remove(0);// 删除表头  
        \}  
        return datas;

/\*\*  
     \* 获取读取表格的转换器  
     \*  
     \* @return 读取表格的转换器  
     \* @throws SAXException  
     \*             SAX错误  
     \*/  
    protected XMLReader getSheetParser(StylesTable styles, ReadOnlySharedStringsTable strings) throws SAXException \{  
        XMLReader parser = XMLReaderFactory.createXMLReader();  
        parser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, getContentHandler(), false));  
        return parser;  
    \}

public ISheetContentHandler getContentHandler() \{  
        return contentHandler;  
    \}

public void setContentHandler(ISheetContentHandler contentHandler) \{  
        this.contentHandler = contentHandler;  
    \}

/\*\*  
     \* 表格转换错误  
     \*/  
    public class ParseException extends Exception \{  
        private static final long serialVersionUID = -2451526411018517607L;

public ParseException(Throwable t) \{  
            super("表格转换错误", t);  
        \}

public interface ISheetContentHandler extends SheetContentsHandler \{

/\*\*  
         \* 设置转换后的数据集，用于存放转换结果  
         \*  
         \* @param datas  
         \*            转换结果  
         \*/  
        void init(List<String\[\]> datas);  
    \}

/\*\*  
     \* 默认表格解析handder  
     \*/  
    class DefaultSheetHandler implements ISheetContentHandler \{  
        /\*\*  
         \* 读取数据  
         \*/  
        private List<String\[\]> datas;  
        private int columsLength;  
        // 读取行信息  
        private String\[\] readRow;  
        private ArrayList<String> fristRow = new ArrayList<String>();

@Override  
        public void init(List<String\[\]> datas) \{  
            this.datas = datas;  
//          this.columsLength = columsLength;  
        \}

@Override  
        public void startRow(int rowNum) \{  
            if (rowNum != 0) \{  
                readRow = new String\[columsLength\];  
            \}  
        \}

@Override  
        public void endRow(int rowNum) \{  
            //将Excel第一行表头的列数当做数组的长度，要保证后续的行的列数不能超过这个长度，这是个约定。  
            if (rowNum == 0) \{  
                columsLength = fristRow.size();  
                readRow = fristRow.toArray(new String\[fristRow.size()\]);  
            \}else \{  
                readRow = fristRow.toArray(new String\[columsLength\]);  
            \}  
            datas.add(readRow.clone());  
            readRow = null;  
            fristRow.clear();  
        \}

@Override  
        public void cell(String cellReference, String formattedValue, XSSFComment comment) \{  
            int index = getCellIndex(cellReference);//转换A1,B1,C1等表格位置为真实索引位置  
            try \{  
                fristRow.set(index, formattedValue);  
            \} catch (IndexOutOfBoundsException e) \{  
                int size = fristRow.size();  
                for (int i = index - size+1;i>0;i--)\{  
                    fristRow.add(null);  
                \}  
                fristRow.set(index,formattedValue);  
            \}  
        \}

@Override  
        public void headerFooter(String text, boolean isHeader, String tagName) \{  
        \}

/\*\*  
         \* 转换表格引用为列编号  
         \*  
         \* @param cellReference  
         \*            列引用  
         \* @return 表格列位置，从0开始算  
         \*/  
        public int getCellIndex(String cellReference) \{  
            String ref = cellReference.replaceAll("\\\\d+", "");  
            int num = 0;  
            int result = 0;  
            for (int i = 0; i < ref.length(); i++) \{  
                char ch = cellReference.charAt(ref.length() - i - 1);  
                num = (int) (ch - 'A' + 1);  
                num \*= Math.pow(26, i);  
                result += num;  
            \}  
            return result - 1;  
        \}  
    \}

以下为读取excel测试main方法

public static void main(String\[\] args) throws IOException, ParseException, InvalidFormatException \{  
        File file = new File("E:\\\\tmp\\\\weibaozj.xlsx");  
        FileInputStream inputStream = new FileInputStream(file);  
        ExcelParser excelParser = new ExcelParser();  
        ExcelParser parse = excelParser.parse(inputStream);  
        List<String\[\]> datas = parse.getDatas();  
        String\[\] array5 = datas.get(0);  
        for (int i=0;i<array5.length;i++)\{

System.out.println("下标："+i+"--对应value :"+array5\[i\]);  
        \}  
        //System.out.println(datas);  
    \}  
\}

### 二、实现项目中根据excel的数据批量写入数据库中 ###

/\*\*  
 \* 请求数据导入功能  
 \*  
 \* @return  
 \*/  
@Transactional  
public ReturnDTO<String> importBatchRequest() \{  
    log.info("导入轻松筹入参数据开始");  
    int rowCount = 0;  
    String userName = "";  
    try \{  
        //读取服务器路径  
        //String filePath = "D:\\\\MyDocuments\\\\itw\_denghj\\\\工作簿1.xlsx";  
        String filePath = dataPropertiesUtil.getBatchFilePath();  
        File file = new File(filePath);  
        log.info("需要读取的文件fileName:\{\}", file.getName());  
        FileInputStream inputStream = new FileInputStream(file);  
        log.info("------解析excel文件开始-----------");  
        ExcelParser parse = excelParser.parse(inputStream);  
        List<String\[\]> dataList = parse.getDatas();  
        log.info("------解析excel文件结束，获取数据size:\{\}-----------",dataList.size());  
        List<HdBatchInsuraRequest> batchInsuraRequestList = new ArrayList<>();  
       /\* InputStream inputStream = new FileInputStream(file);  
        Workbook workBook = ExcelUtils.getWorkBook(inputStream, file.getName());  
        List<List<Object>> sheetContentList = ExcelUtils.getCurrentSheetContentList(workBook.getSheetAt(0), 13);\*/  
        for (int i = 0; i < dataList.size(); i++) \{//遍历每一行  
            rowCount = i;  
            String\[\] rowList = dataList.get(i);  
            if (rowList.length !=12)\{  
                log.info("微保重疾rowlist内容长度不符合要求:\{\}",JSONObject.toJSONString(rowList));  
                continue;  
            \}  
            HdBatchInsuraRequest hdBatchInsuraRequest = new HdBatchInsuraRequest();  
            //姓名  
            userName = rowList\[0\];  
            //身份证唯一标识  
            String uniqueIdentify =  rowList\[1\];  
            //身份证号  
            String userId = rowList\[2\];  
            //投保时间  
            String proposalTime = rowList\[3\];  
            //保单号  
            String policyNo = rowList\[4\];  
            //保额  
            String sumInsured = rowList\[5\];  
            //保单有效期  
            String policyValid = rowList\[6\];  
            //是否发生理赔  
            String isClaim = rowList\[7\];  
            //理赔时间  
            String claimTime = rowList\[8\];  
            //是否购买医疗险  
            String buyMedicalInsurance = rowList\[9\];  
            //首次购买医疗险时间  
            String firstBuyTime = rowList\[10\];  
            //地址  
            String location = rowList\[11\];  
            hdBatchInsuraRequest.setUserId(AesUtil.encrypt(userId, dataPropertiesUtil.getCidAesKey()));  
            hdBatchInsuraRequest.setUniqueIdentify(uniqueIdentify);  
            hdBatchInsuraRequest.setBuyMedicalInsurance(buyMedicalInsurance);  
            hdBatchInsuraRequest.setCallStatus(0);  
            hdBatchInsuraRequest.setClaimTime(claimTime);  
            hdBatchInsuraRequest.setCreateTime(new Date());  
            hdBatchInsuraRequest.setFirstBuyTime(firstBuyTime);  
            hdBatchInsuraRequest.setSumInsured(sumInsured);  
            hdBatchInsuraRequest.setIsClaim(isClaim);  
            hdBatchInsuraRequest.setLocation(location);  
            hdBatchInsuraRequest.setBatchCode("weibao");  
            hdBatchInsuraRequest.setPolicyNo(policyNo);  
            hdBatchInsuraRequest.setPolicyValid(Integer.valueOf(policyValid));  
            hdBatchInsuraRequest.setUserName(userName);  
            hdBatchInsuraRequest.setProposalTime(proposalTime);  
            batchInsuraRequestList.add(hdBatchInsuraRequest);  
        \}  
        log.info("------转换数据结束，数据batchInsuraRequestList.size:\{\}-----------",batchInsuraRequestList.size());  
        List<List<HdBatchInsuraRequest>> lists = subList(batchInsuraRequestList, 5000);  
        log.info("---------分批保存数据的集合大小为：\{\}",lists.size());  
        for (List<HdBatchInsuraRequest> requestList : lists)\{  
            int insertInsuraRequestList = hdBatchInsuraRequestExtMapper.insertInsuraRequestList(requestList);  
        \}  
        log.info("导入微保入参数据成功");  
    \} catch (Exception e) \{  
        log.info("批量文件导入DB异常,异常行数：\{\},用户名：\{\}", rowCount, userName);  
        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();  
        e.printStackTrace();  
        return ReturnDTO.FAIL();  
    \}  
    return ReturnDTO.SUCCESS();  
\}

### 三、List集合拆分通用工具方法 ###

/\*\*  
 \* 拆分集合  
 \* @param tList  
 \* @param subNum  
 \* @return  
 \*/  
public static List<List<HdBatchInsuraRequest>> subList(List<HdBatchInsuraRequest> tList, Integer subNum) \{  
    // 新的截取到的list集合  
    List<List<HdBatchInsuraRequest>> tNewList = new ArrayList<List<HdBatchInsuraRequest>>();  
    // 要截取的下标上限  
    Integer priIndex = 0;  
    // 要截取的下标下限  
    Integer lastIndex = 0;  
    // 每次插入list的数量  
    // Integer subNum = 500;  
    // 查询出来list的总数目  
    Integer totalNum = tList.size();  
    // 总共需要插入的次数  
    Integer insertTimes = totalNum / subNum;  
    List<HdBatchInsuraRequest> subNewList = new ArrayList<HdBatchInsuraRequest>();  
    for (int i = 0; i <= insertTimes; i++) \{  
        // \[0--20) \[20 --40) \[40---60) \[60---80) \[80---100)  
        priIndex = subNum \* i;  
        lastIndex = priIndex + subNum;  
        // 判断是否是最后一次  
        if (i == insertTimes) \{  
            log.info("最后一次截取："+priIndex + "," + lastIndex);  
            subNewList = tList.subList(priIndex, tList.size());  
        \} else \{  
            // 非最后一次  
            subNewList = tList.subList(priIndex, lastIndex);

\}  
        if (subNewList.size() > 0) \{  
            //logger.info("开始将截取的list放入新的list中");  
            tNewList.add(subNewList);  
        \}

return tNewList;