Springboot+poi上传并处理百万级数据EXCEL

时间：2025-03-29 07:15:52

1 Excel上传

针对Excel的上传，采用的是比较常规的方法，其实和文件上传是相同的。具体源码如下：


    @PostMapping(value = "", consumes = "multipart/*", headers = "content-type=multipart/form-data")
    public Map<String, Object> addBlacklist(
            @RequestParam("file") MultipartFile multipartFile, HttpServletRequest request
    ) {
        //判断上传内容是否符合要求
        String fileName = ();
        if (!("^.+\\.(?i)(xls)$") && !("^.+\\.(?i)(xlsx)$")) {
            return returnError(0,"上传的文件格式不正确");
        }

        String file = saveFile(multipartFile, request);
        int result = 0;
        try {
            result = (file);
        } catch (Exception e) {
            ();
        }
        return returnData(result);
    }

    private String saveFile(MultipartFile multipartFile, HttpServletRequest request) {
        String path;
        String fileName = ();
        // 判断文件类型
        String realPath = ().getServletContext().getRealPath("/");
        String trueFileName = fileName;
        // 设置存放Excel文件的路径
        path = realPath + trueFileName;
        File file = new File(path);
        if (() && ()) {
            ();
        }
        try {
            (new File(path));
        } catch (IOException e) {
            ();
        }

        return path;
    }

上面的源码我们可以看见有一个saveFile方法，这个方法是将文件存在服务器本地，这样方便后续文件内容的读取，用不着一次读取所有的内容从而导致消耗大量的内存。当然这里大家如果有更好的方法希望能留言告知哈。

2 Excel处理工具源码

import .;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;

import ;
import ;
import .*;

/**
 * XSSF and SAX (Event API)
 */
public abstract class XxlsAbstract extends DefaultHandler {
    private SharedStringsTable sst;
    private String lastContents;
    private int sheetIndex = -1;
    private List<String> rowlist = new ArrayList<>();
    public List<Map<String, Object>> dataMap = new LinkedList<>();  //即将进行批量插入的数据
    public int willSaveAmount;  //将要插入的数据量
    public int totalSavedAmount; //总共插入了多少数据
    private int curRow = 0;        //当前行
    private int curCol = 0;        //当前列索引
    private int preCol = 0;        //上一列列索引
    private int titleRow = 0;    //标题行，一般情况下为0
    public int rowsize = 0;    //列数

    //excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型
    public abstract void optRows(int sheetIndex, int curRow, List<String> rowlist) throws SQLException;

    //只遍历一个sheet，其中sheetId为要遍历的sheet索引，从1开始，1-3

    /**
     * @param filename
     * @param sheetId  sheetId为要遍历的sheet索引，从1开始，1-3
     * @throws Exception
     */
    public void processOneSheet(String filename, int sheetId) throws Exception {
        OPCPackage pkg = (filename);
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = ();

        XMLReader parser = fetchSheetParser(sst);

        // rId2 found by processing the Workbook
        // 根据 rId# 或 rSheet# 查找sheet
        InputStream sheet2 = ("rId" + sheetId);
        sheetIndex++;
        InputSource sheetSource = new InputSource(sheet2);
        (sheetSource);
        ();
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst)
            throws SAXException {
        XMLReader parser = ();
         = sst;
        (this);
        return parser;
    }

    public void endElement(String uri, String localName, String name) {
        // 根据SST的索引值的到单元格的真正要存储的字符串
        try {
            int idx = (lastContents);
            lastContents = new XSSFRichTextString((idx))
                    .toString();
        } catch (Exception e) {

        }

        // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
        // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
        if (("v")) {
            String value = ();
            value = ("") ? " " : value;
            int cols = curCol - preCol;
            if (cols > 1) {
                for (int i = 0; i < cols - 1; i++) {
                    (preCol, "");
                }
            }
            preCol = curCol;
            (curCol - 1, value);
        } else {
            //如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
            if (("row")) {
                int tmpCols = ();
                if (curRow >  && tmpCols < ) {
                    for (int i = 0; i <  - tmpCols; i++) {
                        ((), "");
                    }
                }
                try {
                    optRows(sheetIndex, curRow, rowlist);
                } catch (SQLException e) {
                    ();
                }
                if (curRow == ) {
                     = ();
                }
                ();
                curRow++;
                curCol = 0;
                preCol = 0;
            }
        }
    }
}

3 解析成功后的数据处理

首先我们将源码展示出来，然后再具体说明

public int addBlackLists(String file) throws ExecutionException, InterruptedException {
        ArrayList<Future<Integer>> resultList = new ArrayList<>();
        XxlsAbstract xxlsAbstract = new XxlsAbstract() {

            //针对数据的具体处理
            @Override
            public void optRows(int sheetIndex, int curRow, List<String> rowlist) {

                /**
                 * 判断即将插入的数据是否已经到达8000，如果到达8000，
                 * 进行数据插入
                 */
                if ( == 5000) {

                    //插入数据
                    List<Map<String, Object>> list = new LinkedList<>();
                    Callable<Integer> callable = () -> {
                        int count = (list);
                        (list);
                        return count;
                    };
                     = 0;
                     = new LinkedList<>();
                    Future<Integer> future = (callable);
                    (future);
                }

                //汇总数据
                Map<String, Object> map = new HashMap<>();
                ("uid", (0));
                ("createTime", (1));
                ("regGame", (2));
                    ("banGame", (2));
                (map);
                ++;
                ++;
            }
        };
        try {
            (file, 1);
        } catch (Exception e) {
            ();
        }

        //针对没有存入的数据进行处理
        if( != 0){
            List<Map<String, Object>> list = new LinkedList<>();
            Callable<Integer> callable = () -> {
                int count = (list);
                (list);
                return count;
            };
            Future<Integer> future = (callable);
            (future);
        }

        ();
        int total = 0;
        for (Future<Integer> future : resultList) {
            while (true) {
                if (() && !()) {
                    int sum = ();
                    total += sum;
                    break;
                } else {
                    (100);
                }
            }
        }
        return total;
    }

针对上面的源码，我们可以发现，我们需要将读取到的EXCEL数据插入到数据库中，这里为了减小数据库的IO和提高插入的效率，我们采用5000一批的批量插入（注意：如果数据量过大会导致组成的SQL语句无法执行）。

这里需要获取到一个最终执行成功的插入结果，并且插入执行很慢。所有采用了Java多线程的Future模式，采用异步的方式最终来获取J执行结果。

通过上面的实现，楼主测试得到最终一百万条数据需要四分钟左右的时间就可以搞定。如果大家有更好的方法，欢迎留言。

相关文章