POI导入导出百万级数据Excel

一、导入pom依赖Evj拜客生活常识网

4.0.0

    org.apache.poi
    poi
    ${poi-version}



    org.apache.poi
    poi-ooxml
    ${poi-version}



    org.apache.poi
    poi-scratchpad
    ${poi-version}



    org.apache.poi
    poi-ooxml-schemas
    ${poi-version}

二、导入Evj拜客生活常识网

1、导入工具类Evj拜客生活常识网

import com.base.infrastructure.exception.ServiceException;
import com.base.infrastructure.utility.MapUtility;
import com.base.infrastructure.utility.ServiceCheckUtility;
import com.base.infrastructure.utility.StringUtility;
import com.base.infrastructure.utility.excel.analysis.ExcelXlsReader;
import com.base.infrastructure.utility.excel.analysis.ExcelXlsxReader;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel2003、2007大数据量解析
 */
public class BigExcelReaderUtil {

    //excel2003扩展名
    private static final String XLS_LOWER_CASE = "xls";

    private static final String XLSX_LOWER_CASE = "xlsx";

    //excel2007扩展名
    private static final String XLS_UPPER_CASE = "XLS";

    private static final String XLSX_UPPER_CASE = "XLSX";

    /**
     * 根据文件路径解析
     *
     * @param path
     * @return
     * @throws Exception
     */
    public static Map readExcelPath(String path, int headerIncex, int dataIndex)
            throws Exception {
        //解析数据返回map
        Map map = new HashMap<>();
        //表头内容
        List header = new ArrayList<>();
        //数据内容
        //List> data = new ArrayList<>();
        Map>> listMap = new HashMap<>();

        //处理excel2003文件
        if (path.endsWith(XLS_LOWER_CASE) || path.endsWith(XLS_UPPER_CASE)) {
            ExcelXlsReader excelXls = new ExcelXlsReader() {
                @Override
                public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {
                    //ServiceCheckUtility.errorCheck(sheetIndex > 1, "请删除或清除多余的sheet页，只保留一个正确的sheet页");
                    header.addAll(cellList);
                }

                @Override
                public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {
                    List tempList = new ArrayList<>();
                    tempList.addAll(cellList);
                    if (listMap.get(sheetIndex) == null) {
                        List> data = new ArrayList<>();
                        data.add(tempList);
                        listMap.put(sheetIndex, data);
                    } else {
                        listMap.get(sheetIndex).add(tempList);
                    }
                }
            };
            excelXls.process(path, headerIncex, dataIndex);
        }
        //处理excel2007文件
        else if (path.endsWith(XLSX_LOWER_CASE) || path.endsWith(XLSX_UPPER_CASE)) {
            ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader() {
                @Override
                public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {
                    //ServiceCheckUtility.errorCheck(sheetIndex > 1, "请删除或清除多余的sheet页，只保留一个正确的sheet页");
                    header.addAll(cellList);
                }

                @Override
                public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {
                    List tempList = new ArrayList<>();
                    tempList.addAll(cellList);
                    if (listMap.get(sheetIndex) == null) {
                        List> data = new ArrayList<>();
                        data.add(tempList);
                        listMap.put(sheetIndex, data);
                    } else {
                        listMap.get(sheetIndex).add(tempList);
                    }
                }
            };
            excelXlsxReader.process(path, headerIncex, dataIndex);
        } else {
            throw new Exception("文件格式错误，上传的excel的扩展名只能是xls或xlsx。");
        }
        map.put("data", listMap);
        map.put("head", header);
        return map;
    }

    /**
     * 根据MultipartFile文件对象解析
     *
     * @param file
     * @return
     * @throws Exception
     */
    public static Map