聊聊Excel解析：如何處理百萬行EXCEL文件

来源:https://www.cnblogs.com/jingdongkeji/archive/2023/07/03/17522063.html
-Advertisement-
Play Games

如何恰當地處理數據量龐大的Excel文件，避免記憶體溢出問題？本文將對比分析業界主流的Excel解析技術，並給出解決方案。 ...


一、引言

Excel表格在後臺管理系統中使用非常廣泛，多用來進行批量配置、數據導出工作。在日常開發中，我們也免不了進行Excel數據處理。

那麼，如何恰當地處理數據量龐大的Excel文件，避免記憶體溢出問題？本文將對比分析業界主流的Excel解析技術，並給出解決方案。

如果這是您第一次接觸Excel解析，建議您從第二章瞭解本文基礎概念；如果您已經對POI有所瞭解，請跳轉第三章閱讀本文重點內容。

二、基礎篇-POI

說到Excel讀寫，就離不開這個圈子的的老大哥——POI。

Apache POI是一款Apache軟體基金會用Java編寫的免費開源的跨平臺的 Java API，全稱Poor Obfuscation Implementation，“簡潔版的模糊實現”。它支持我們用Java語言和包括Word、Excel、PowerPoint、Visio在內的所有Microsoft Office文檔交互，進行數據讀寫和修改操作。

（1）“糟糕”的電子錶格

在POI中，每種文檔都有一個與之對應的文檔格式，如97-2003版本的Excel文件（.xls），文檔格式為HSSF——Horrible SpreadSheet Format，意為“糟糕的電子錶格格式”。雖然Apache幽默而謙虛地將自己的API冠以“糟糕”之名，不過這確實是一款全面而強大的API。

以下是部分“糟糕”的POI文檔格式，包括Excel、Word等：

Office文檔 對應POI格式
Excel (.xls) HSSF (Horrible SpreadSheet Format)
Word (.doc) HWPF (Horrible Word Processor Format)
Visio (.vsd) HDGF (Horrible DiaGram Format)
PowerPoint(.ppt) HSLF（Horrible Slide Layout Format）

（2）OOXML簡介

微軟在Office 2007版本推出了基於XML的技術規範：Office Open XML，簡稱OOXML。不同於老版本的二進位存儲，在新規範下，所有Office文檔都使用了XML格式書寫，並使用ZIP格式進行壓縮存儲，大大提升了規範性，也提高了壓縮率，縮小了文件體積，同時支持向後相容。簡單來說，OOXML定義瞭如何用一系列的XML文件來表示Office文檔。

Xlsx文件的本質是XML

讓我們看看一個採用OOML標準的Xlsx文件的構成。我們右鍵點擊一個Xlsx文件，可以發現它可以被ZIP解壓工具解壓（或直接修改擴展名為.zip後解壓），這說明：Xlsx文件是用ZIP格式壓縮的。解壓後，可以看到如下目錄格式：

打開其中的“/xl”目錄，這是這個Excel的主要結構信息：

其中workbook.xml存儲了整個Excel工作簿的結構，包含了幾張sheet表單，而每張表單結構存儲在/wooksheets文件夾中。styles.xml存放單元格的格式信息，/theme文件夾存放一些預定義的字體、顏色等數據。為了減少壓縮體積，表單中所有的字元數據被統一存放在sharedStrings.xml中。經過分析不難發現，Xlsx文件的主體數據都以XML格式書寫。

XSSF格式

為了支持新標準的Office文檔，POI也推出了一套相容OOXML標準的API，稱作poi-ooxml。如Excel 2007文件（.xlsx）對應的POI文檔格式為XSSF（XML SpreadSheet Format）。

以下是部分OOXML文檔格式：

Office文檔 對應POI格式
Excel (.xlsx) XSSF (XML SpreadSheet Format)
Word (.docx) XWPF (XML Word Processor Format)
Visio (.vsdx) XDGF (XML DiaGram Format)
PowerPoint (.pptx) XSLF (XML Slide Layout Format)

（3）UserModel

在POI中為我們提供了兩種解析Excel的模型，UserModel（用戶模型）和EventModel（事件模型） 。兩種解析模式都可以處理Excel文件，但解析方式、處理效率、記憶體占用量都不盡相同。最簡單和實用的當屬UserModel。

UserModel & DOM解析

用戶模型定義瞭如下介面：

  1. Workbook-工作簿，對應一個Excel文檔。根據版本不同，有HSSFWorkbook、XSSFWorkbook等類。

  2. Sheet-表單，一個Excel中的若幹個表單，同樣有HSSFSheet、XSSFSheet等類。

  3. Row-行，一個表單由若幹行組成，同樣有HSSFRow、XSSFRow等類。

  4. Cell-單元格，一個行由若幹單元格組成，同樣有HSSFCell、XSSFCell等類。

用戶模型示意

可以看到，用戶模型十分貼合Excel用戶的習慣，易於理解，就像我們打開一個Excel表格一樣。同時用戶模型提供了豐富的API，可以支持我們完成和Excel中一樣的操作，如創建表單、創建行、獲取表的行數、獲取行的列數、讀寫單元格的值等。

為什麼UserModel支持我們進行如此豐富的操作？因為在UserModel中，Excel中的所有XML節點都被解析成了一棵DOM樹，整棵DOM樹都被載入進記憶體，因此可以進行方便地對每個XML節點進行隨機訪問。

UserModel數據轉換

瞭解了用戶模型，我們就可以直接使用其API進行各種Excel操作。當然，更方便的辦法是使用用戶模型將一個Excel文件轉化成我們想要的Java數據結構，更好地進行數據處理。

我們很容易想到關係型資料庫——因為二者的實質是一樣的。類比資料庫的數據表，我們的思路就有了：

  1. 將一個Sheet看作表頭和數據兩部分，這二者分別包含表的結構和表的數據。

  2. 對錶頭（第一行），校驗表頭信息是否和實體類的定義的屬性匹配。

  3. 對數據（剩餘行），從上向下遍歷每一個Row，將每一行轉化為一個對象，每一列作為該對象的一個屬性，從而得到一個對象列表，該列表包含Excel中的所有數據。

接下來我們就可以按照我們的需求處理我們的數據了，如果想把操作後的數據寫回Excel，也是一樣的邏輯。

使用UserModel

讓我們看看如何使用UserModel讀取Excel文件。此處使用POI 4.0.0版本，首先引入poi和poi-ooxml依賴：

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.0.0</version>
    </dependency>

我們要讀取一個簡單的Sku信息表，內容如下：

如何將UserModel的信息轉化為數據列表？

我們可以通過實現反射+註解的方式定義表頭到數據的映射關係，幫助我們實現UserModel到數據對象的轉換。實現基本思路是： ① 自定義註解，在註解中定義列號，用來標註實體類的每個屬性對應在Excel表頭的第幾列。 ② 在實體類定義中，根據表結構，為每個實體類的屬性加上註解。 ③ 通過反射，獲取實體類的每個屬性對應在Excel的列號，從而到相應的列中取得該屬性的值。

以下是簡單的實現，首先準備自定義註解ExcelCol，其中包含列號和表頭：

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCol {

    /**
     * 當前列數
     */
    int index() default 0;

    /**
     * 當前列的表頭名稱
     */
    String header() default "";
}

接下來，根據Sku欄位定義Sku對象，並添加註解，列號分別為0，1，2，並指定表頭名稱：

import lombok.Data;
import org.shy.xlsx.annotation.ExcelCol;

@Data
public class Sku {

    @ExcelCol(index = 0, header = "sku")
    private Long id;

    @ExcelCol(index = 1, header = "名稱")
    private String name;

    @ExcelCol(index = 2, header = "價格")
    private Double price;
}

然後，用反射獲取表頭的每一個Field，並以列號為索引，存入Map中。從Excel的第二行開始（第一行是表頭），遍歷後面的每一行，對每一行的每個屬性，根據列號拿到對應Cell的值，併為數據對象賦值。根據單元格中值類型的不同，如文本/數字等，進行不同的處理。以下為了簡化邏輯，只對錶頭出現的類型進行了處理，其他情況的處理邏輯類似。全部代碼如下：

import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.shy.domain.pojo.Sku;
import org.shy.xlsx.annotation.ExcelCol;

import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyUserModel {

    public static void main(String[] args) throws Exception {
        List<Sku> skus = parseSkus("D:\sunhaoyu8\Documents\Files\skus.xlsx");
        System.out.println(JSON.toJSONString(skus));
    }

    public static List<Sku> parseSkus(String filePath) throws Exception {
        FileInputStream in = new FileInputStream(filePath);
        Workbook wk = new XSSFWorkbook(in);
        Sheet sheet = wk.getSheetAt(0);
        // 轉換成的數據列表
        List<Sku> skus = new ArrayList<>();

        // 獲取Sku的註解信息
        Map<Integer, Field> fieldMap = new HashMap<>(16);
        for (Field field : Sku.class.getDeclaredFields()) {
            ExcelCol col = field.getAnnotation(ExcelCol.class);
            if (col == null) {
                continue;
            }
            field.setAccessible(true);
            fieldMap.put(col.index(), field);
        }

        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row r = sheet.getRow(rowNum);
            Sku sku = new Sku();
            for (int cellNum = 0; cellNum < fieldMap.size(); cellNum++) {
                Cell c = r.getCell(cellNum);
                if (c != null) {
                    setFieldValue(fieldMap.get(cellNum), getCellValue(c), sku);
                }
            }
            skus.add(sku);
        }
        return skus;
    }

    public static void setFieldValue(Field field, String value, Sku sku) throws Exception {
        if (field == null) {
            return;
        }
        //得到此屬性的類型
        String type = field.getType().toString();
        if (StringUtils.isBlank(value)) {
            field.set(sku, null);
        } else if (type.endsWith("String")) {
            field.set(sku, value);
        } else if (type.endsWith("long") || type.endsWith("Long")) {
            field.set(sku, Long.parseLong(value));
        } else if (type.endsWith("double") || type.endsWith("Double")) {
            field.set(sku, Double.parseDouble(value));
        } else {
            field.set(sku, value);
        }
    }

    public static String getCellValue(Cell cell) {
        DecimalFormat df = new DecimalFormat("#.##");
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                return df.format(cell.getNumericCellValue());
            case STRING:
                    return cell.getStringCellValue().trim();
            case BLANK:
                return null;
        }
        return "";
    }

最後，將轉換完成的數據列表列印出來。運行結果如下：

[{"id":345000,"name":"電腦A","price":5999.0},{"id":345001,"name":"手機C","price":4599.0}]

Tips：如果您的程式出現“NoClassDefFoundError”，請引入ooxml-schemas依賴：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.4</version>
</dependency>

版本選擇見下表，如POI 4.0.0對應ooxml-schemas 1.4版本：

UserModel的局限

以上處理邏輯對於大部分的Excel文件都很適用，但最大的缺點是記憶體開銷大，因為所有的數據都被載入入記憶體。實測，以上3列的Excel文件在7萬行左右就會出現OOM，而XLS文件最大行數為65535行，XLSX更是達到了1048576行，如果將幾萬甚至百萬級別的數據全部讀入記憶體，記憶體溢出風險極高。

那麼，該如何解決傳統UserModel無法處理大批量Excel的問題呢？開發者們給出了許多精彩的解決方案，請看下一章。

三、進階篇-記憶體優化的探索

接下來介紹本文重點內容，同時解決本文所提出的問題：如何進行Excel解析的記憶體優化，從而處理百萬行Excel文件？

（1）EventModel

前面我們提到，除了UserModel外，POI還提供了另一種解析Excel的模型：EventModel事件模型。不同於用戶模型的DOM解析，事件模型採用了SAX的方式去解析Excel。

EventModel & SAX解析

SAX的全稱是Simple API for XML，是一種基於事件驅動的XML解析方法。不同於DOM一次性讀入XML，SAX會採用邊讀取邊處理的方式進行XML操作。簡單來講，SAX解析器會逐行地去掃描XML文檔，當遇到標簽時會觸發解析處理器，從而觸發相應的事件Handler。我們要做的就是繼承DefaultHandler類，重寫一系列事件處理方法，即可對Excel文件進行相應的處理。

下麵是一個簡單的SAX解析的示例，這是要解析的XML文件：一個sku表，其中包含兩個sku節點，每個節點有一個id屬性和三個子節點。

<?xml version="1.0" encoding="UTF-8"?>
<skus>
    <sku id="345000">
        <name>電腦A</name>
        <price>5999.0</price>
   </sku>
    <sku id="345001">
        <name>手機C</name>
        <price>4599.0</price>
   </sku>
</skus>

對照XML結構，創建Java實體類：

import lombok.Data;

@Data
public class Sku {
    private Long id;
    private String name;
    private Double price;
}

自定義事件處理類SkuHandler：

import com.alibaba.fastjson.JSON;
import org.shy.domain.pojo.Sku;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

public class SkuHandler extends DefaultHandler {
    /**
     * 當前正在處理的sku
     */
    private Sku sku;
    /**
     * 當前正在處理的節點名稱
     */
    private String tagName;

    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if ("sku".equals(qName)) {
            sku = new Sku();
            sku.setId(Long.valueOf((attributes.getValue("id"))));
        }
        tagName = qName;
    }

    @Override
    public void endElement(String uri, String localName, String qName) throws SAXException {
        if ("sku".equals(qName)) {
            System.out.println(JSON.toJSONString(sku));
            // 處理業務邏輯
            // ...
        }
        tagName = null;
    }

    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        if ("name".equals(tagName)) {
            sku.setName(new String(ch, start, length));
        }
        if ("price".equals(tagName)) {
            sku.setPrice(Double.valueOf(new String(ch, start, length)));
        }
    }
}

其中，SkuHandler重寫了三個事件響應方法：

startElement（）——每當掃描到新XML元素時，調用此方法，傳入XML標簽名稱qName，XML屬性列表attributes；

characters（）——每當掃描到未在XML標簽中的字元串時，調用此方法，傳入字元數組、起始下標和長度；

endElement（）——每當掃描到XML元素的結束標簽時，調用此方法，傳入XML標簽名稱qName。

我們用一個變數tagName存儲當前掃描到的節點信息，每次掃描節點發送變化時，更新tagName；

用一個Sku實例維護當前讀入記憶體的Sku信息，每當該Sku讀取完成時，我們列印該Sku信息，並執行相應業務邏輯。這樣，就可以做到一次讀取一條Sku信息，邊解析邊處理。由於每行Sku結構相同，因此，只需要在記憶體維護一條Sku信息即可，避免了一次性把所有信息讀入記憶體。

調用SAX解析器時，使用SAXParserFactory創建解析器實例，解析輸入流即可，Main方法如下：

import org.shy.xlsx.sax.handler.SkuHandler;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.InputStream;

public class MySax {
    public static void main(String[] args) throws Exception {
        parseSku();
    }

    public static void parseSku() throws Exception {
        SAXParserFactory saxParserFactory = SAXParserFactory.newInstance();
        SAXParser saxParser = saxParserFactory.newSAXParser();
        InputStream inputStream = ClassLoader.getSystemResourceAsStream("skus.xml");
        saxParser.parse(inputStream, new SkuHandler());
    }
}

輸出結果如下：

{"id":345000,"name":"電腦A","price":5999.0}
{"id":345001,"name":"手機C","price":4599.0}

以上演示了SAX解析的基礎原理。EventModel的API更複雜，同樣通過重寫Event handler，實現SAX解析。有興趣的讀者，請參見POI官網的示例代碼： https://poi.apache.org/components/spreadsheet/how-to.html

EventModel的局限

POI官方提供的EventModel API雖然使用SAX方式解決了DOM解析的問題，但是存在一些局限性：

① 屬於low level API，抽象級別低，相對比較複雜，學習使用成本高。

② 對於HSSF和XSSF類型的處理方式不同，代碼需要根據不同類型分別做相容。

③ 未能完美解決記憶體溢出問題，記憶體開銷仍有優化空間。

④ 僅用於Excel解析，不支持Excel寫入。

因此，筆者不建議使用POI原生的EventModel，至於有哪些更推薦的工具，請看下文。

（2）SXSSF

SXSSF簡介

SXSSF，全稱Streaming XML SpreadSheet Format，是POI 3.8-beta3版本後推出的低記憶體占用的流式Excel API，旨在解決Excel寫入時的記憶體問題。它是XSSF的擴展，當需要將大批量數據寫入Excel中時，只需要用SXSSF替換XSSF即可。SXSSF的原理是滑動視窗——在記憶體中保存一定數量的行，其餘行存儲在磁碟。這麼做的好處是記憶體優化，代價是失去了隨機訪問的能力。SXSSF可以相容XSSF的絕大多數API，非常適合瞭解UserModel的開發者。

記憶體優化會難以避免地帶來一定限制：

① 在某個時間點只能訪問有限數量的行，因為其餘行並未被載入入記憶體。

② 不支持需要隨機訪問的XSSF API，如刪除/移動行、克隆sheet、公式計算等。

③ 不支持Excel讀取操作。

④ 正因為它是XSSF的擴展，所以不支持寫入Xls文件。

UserModel、EventModel、SXSSF對比

到這裡就介紹完了所有的POI Excel API，下表是所有這些API的功能對比，來自POI官網：

可以看到，UserModel基於DOM解析，功能是最齊全的，支持隨機訪問，唯一缺點是CPU和記憶體效率不穩定；

EventModel是POI提供的流式讀取方案，基於SAX解析，僅支持向前訪問，其餘API不支持；

SXSSF是POI提供的流式寫入方案，同樣僅能向前訪問，支持部分XSSF API。

（3）EasyExcel

EasyExcel簡介

為瞭解決POI原生的SAX解析的問題，阿裡基於POI二次開發了EasyExcel。下麵是引用自EasyExcel官網的介紹：

Java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個嚴重的問題就是非常的耗記憶體，poi有一套SAX模式的API可以一定程度的解決一些記憶體溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓後存儲都是在記憶體中完成的，記憶體消耗依然很大。 easyexcel重寫了poi對07版Excel的解析，一個3M的excel用POI sax解析依然需要100M左右記憶體，改用easyexcel可以降低到幾M，並且再大的excel也不會出現記憶體溢出；03版依賴POI的sax模式，在上層做了模型轉換的封裝，讓使用者更加簡單方便。

如介紹所言，EasyExcel同樣採用SAX方式解析，但由於重寫了xlsx的SAX解析，優化了記憶體開銷；對xls文件，在上層進一步進行了封裝，降低了使用成本。API上，採用註解的方式去定義Excel實體類，使用方便；通過事件監聽器的方式做Excel讀取，相比於原生EventModel，API大大簡化；寫入數據時，EasyExcel對大批數據，通過重覆多次寫入的方式從而降低記憶體開銷。

EasyExcel最大的優勢是使用簡便，十分鐘可以上手。由於對POI的API都做了高級封裝，所以適合不想瞭解POI基礎API的開發者。總之，EasyExcel是一款值得一試的API。

使用EasyExcel

引入easyexcel依賴：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.3</version>
</dependency>

首先，用註解定義Excel實體類：

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class Sku {
    @ExcelProperty(index = 0)
    private Long id;

    @ExcelProperty(index = 1)
    private String name;

    @ExcelProperty(index = 2)
    private Double price;
}

接下來，重寫AnalysisEventListener中的invoke和doAfterAllAnalysed方法，這兩個方法分別在監聽到單行解析完成的事件時和全部解析完成的事件時調用。每次單行解析完成時，我們列印解析結果，代碼如下：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.shy.domain.pojo.easyexcel.Sku;

public class MyEasyExcel {
    public static void main(String[] args) {
        parseSku();
    }

    public static void parseSku() {
        //讀取文件路徑
        String fileName = "D:\sunhaoyu8\Documents\Files\excel.xlsx";
        //讀取excel
        EasyExcel.read(fileName, Sku.class, new AnalysisEventListener<Sku>() {
            @Override
            public void invoke(Sku sku, AnalysisContext analysisContext) {
                System.out.println("第" + analysisContext.getCurrentRowNum() + "行：" + JSON.toJSONString(sku));
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("全部解析完成");
            }
        }).sheet().doRead();
    }
}

測驗一下，用它解析一個十萬行的excel，該文件用UserModel讀取會OOM，如下：

運行結果：

（4）Xlsx-streamer

Xlsx-streamer簡介

Xlsx-streamer是一款用於流式讀取Excel的工具，同樣基於POI二次開發。雖然EasyExcel可以很好地解決Excel讀取的問題，但解析方式為SAX，需要通過實現監聽器以事件驅動的方式進行解析。有沒有其他的解析方式呢？Xlsx-streamer給出了答案。

譯自官方文檔的描述：

如果您過去曾使用 Apache POI 讀取 Excel 文件，您可能會註意到它的記憶體效率不是很高。 閱讀整個工作簿會導致嚴重的記憶體使用高峰，這會對伺服器造成嚴重破壞。 Apache 必須讀取整個工作簿的原因有很多，但其中大部分與該庫允許您使用隨機地址進行讀寫有關。 如果（且僅當）您只想以快速且記憶體高效的方式讀取 Excel 文件的內容，您可能不需要此功能。 不幸的是，POI 庫中唯一用於讀取流式工作簿的東西要求您的代碼使用類似 SAX 的解析器。 該 API 中缺少所有友好的類，如 Row 和 Cell。 該庫充當該流式 API 的包裝器，同時保留標準 POI API 的語法。 繼續閱讀，看看它是否適合您。 註意：這個庫只支持讀取 XLSX 文件。

如介紹所言，Xlsx-streamer最大的便利之處是相容了用戶使用POI UserModel的習慣，它對所有的UserModel介面都給出了自己的流式實現，如StreamingSheet、StreamingRow等，對於熟悉UserModel的開發者來說，幾乎沒有學習門檻，可以直接使用UserModel訪問Excel。

Xlsx-streamer的實現原理和SXSSF相同，都是滑動視窗——限定讀入記憶體中的數據大小，將正在解析的數據讀到記憶體緩衝區中，形成一個臨時文件，以防止大量使用記憶體。緩衝區的內容會隨著解析的過程不斷變化，當流關閉後，臨時文件也將被刪除。由於記憶體緩衝區的存在，整個流不會被完整地讀入記憶體，從而防止了記憶體溢出。

與SXSSF一樣，因為記憶體中僅載入入部分行，故犧牲了隨機訪問的能力，僅能通過遍歷順序訪問整表，這是不可避免的局限。換言之，如果調用StreamingSheet.getRow(int rownum)方法，該方法會獲取sheet的指定行，會拋出“不支持該操作”的異常。

Xlsx-streamer最大的優勢是相容UserModel，尤其適合那些熟悉UserModel又不想使用繁瑣的EventModel的開發者。它和SXSSF一樣，都通過實現UserModel介面的方式給出解決記憶體問題的方案，很好地填補了SXSSF不支持讀取的空白，可以說它是“讀取版”的SXSSF。

使用Xlsx-streamer

引入pom依賴：

    <dependency>
        <groupId>com.monitorjbl</groupId>
        <artifactId>xlsx-streamer</artifactId>
        <version>2.1.0</version>
    </dependency>

下麵是一個使用xlsx-streamer的demo：

import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;

public class MyXlsxStreamer {
    public static void main(String[] args) throws Exception {
        parseSku();
    }

    public static void parseSku() throws Exception {
        FileInputStream in = new FileInputStream("D:\sunhaoyu8\Documents\Files\excel.xlsx");
        Workbook wk = StreamingReader.builder()
                //緩存到記憶體中的行數，預設是10
                .rowCacheSize(100)
                //讀取資源時，緩存到記憶體的位元組大小，預設是1024
                .bufferSize(4096)
                //打開資源，必須，可以是InputStream或者是File
                .open(in);
        Sheet sheet = wk.getSheetAt(0);

        for (Row r : sheet) {
            System.out.print("第" + r.getRowNum() + "行：");
            for (Cell c : r) {
                if (c != null) {
                    System.out.print(c.getStringCellValue() + " ");
                }
            }
            System.out.println();
        }
    }
}

如代碼所示，Xlsx-streamer的使用方法為：使用StreamingReader進行參數配置和流式讀取，我們可以手動配置固定的滑動視窗大小，有兩個指標，分別是緩存在記憶體中的最大行數和緩存在記憶體的最大位元組數，這兩個指標會同時限制該滑動視窗的上限。接下來，我們可以使用UserModel的API去遍歷訪問讀到的表格。

使用十萬行量級的excel文件實測一下，運行結果：

StAX解析

Xlsx-streamer底層採用的解析方式，被稱作StAX解析。StAX於2004年3月在JSR 173規範中引入，是JDK 6.0推出的新特性。它的全稱是Streaming API for XML，流式XML解析。更準確地講，稱作“流式拉分析”。之所以稱作拉分析，是因為它和“流式推分析”——SAX解析相對。

之前我們提到，SAX解析是一種事件驅動的解析模型，每當解析到標簽時都會觸發相應的事件Handler，將事件“推”給響應器。在這樣的推模型中，解析器是主動，響應器是被動，我們不能選擇想要響應哪些事件，因此這樣的解析比較不靈活。

為瞭解決SAX解析的問題，StAX解析採用了“拉”的方式——由解析器遍歷流時，原來的響應器變成了驅動者，主動遍歷事件解析器（迭代器），從中拉取一個個事件並處理。在解析過程中，StAX支持使用peek()方法來"偷看"下一個事件，從而決定是否有必要分析下一個事件，而不必從流中讀取事件。這樣可以有效提高靈活性和效率。

下麵用StAX的方式再解析一下相同的XML：

<?xml version="1.0" encoding="UTF-8"?>
<skus>
    <sku id="345000">
        <name>電腦A</name>
        <price>5999.0</price>
   </sku>
    <sku id="345001">
        <name>手機C</name>
        <price>4599.0</price>
   </sku>
</skus>

這次我們不需要監聽器，把所有處理的邏輯集成在一個方法中：

import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.shy.domain.pojo.Sku;

import javax.xml.stream.XMLEventReader;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.events.Attribute;
import javax.xml.stream.events.StartElement;
import javax.xml.stream.events.XMLEvent;
import java.io.InputStream;
import java.util.Iterator;


public class MyStax {

    /**
     * 當前正在處理的sku
     */
    private static Sku sku;
    /**
     * 當前正在處理的節點名稱
     */
    private static String tagName;

    public static void main(String[] args) throws Exception {
        parseSku();
    }
    
    public static void parseSku() throws Exception {
        XMLInputFactory inputFactory = XMLInputFactory.newInstance();
        InputStream inputStream = ClassLoader.getSystemResourceAsStream("skus.xml");
        XMLEventReader xmlEventReader = inputFactory.createXMLEventReader(inputStream);
        while (xmlEventReader.hasNext()) {
            XMLEvent event = xmlEventReader.nextEvent();
            // 開始節點
            if (event.isStartElement()) {
                StartElement startElement = event.asStartElement();
                String name = startElement.getName().toString();
                if ("sku".equals(name)) {
                    sku = new Sku();
                    Iterator iterator = startElement.getAttributes();
                    while (iterator.hasNext()) {
                        Attribute attribute = (Attribute) iterator.next();
                        if ("id".equals(attribute.getName().toString())) {
                            sku.setId(Long.valueOf(attribute.getValue()));
                        }
                    }
                }
                tagName = name;
            }
            // 字元
            if (event.isCharacters()) {
                String data = event.asCharacters().getData().trim();
                if (StringUtils.isNotEmpty(data)) {
                    if ("name".equals(tagName)) {
                        sku.setName(data);
                    }
                    if ("price".equals(tagName)) {
                        sku.setPrice(Double.valueOf(data));
                    }
                }
            }
            // 結束節點
            if (event.isEndElement()) {
                String name = event.asEndElement().getName().toString();
                if ("sku".equals(name)) {
                    System.out.println(JSON.toJSONString(sku));
                    // 處理業務邏輯
                    // ...
                }
            }
        }
    }
}

以上代碼與SAX解析的邏輯是等價的，用XMLEventReader作為迭代器從流中讀取事件，迴圈遍歷事件迭代器，再根據事件類型做分類處理。有興趣的小伙伴可以自己動手嘗試一下，探索更多StAX解析的細節。

四、結論

EventModel、SXSSF、EasyExcel和Xlsx-streamer分別針對UserModel的記憶體占用問題給出了各自的解決方案，下麵是對所有本文提到的Excel API的對比：

UserModel EventModel SXSSF EasyExcel Xlsx-streamer
記憶體占用量 高 較低 低 低 低
全表隨機訪問 是 否 否 否 否
讀Excel 是 是 否 是 是
讀取方式 DOM SAX -- SAX StAX
寫Excel 是 是 是 是 否

建議您根據自己的使用場景選擇適合的API：

  1. 處理大批量Excel文件的需求，推薦選擇POI UserModel、EasyExcel；

  2. 讀取大批量Excel文件，推薦選擇EasyExcel、Xlsx-streamer；

  3. 寫入大批量Excel文件，推薦選擇SXSSF、EasyExcel。

使用以上API，一定可以滿足關於Excel開發的需求。當然Excel API不止這些，還有許多同類型的API，歡迎大家多多探索和創新。

頁面鏈接：

POI官網： https://poi.apache.org/

EasyExcel官網：https://easyexcel.opensource.alibaba.com

Xlsx-streamer Github： https://github.com/monitorjbl/excel-streaming-reader

作者：京東保險 孫昊宇

來源：京東雲開發者社區


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • sql server 資料庫自動備份
    一丶打開客戶端: 對象資源管理器->管理->維護計劃(右鍵點擊)->維護計劃嚮導 二丶打開後點擊下一步, 填寫名稱與說明並更改備份計劃 三丶點下一步, 選擇維護任務 四丶點擊下一步, 選擇需要備份的資料庫, 和備份文件路徑 五丶點擊下一步, 選擇報告文件保存路徑 六丶點擊下一步, 查看維護計劃, 沒 ...
  • Mysql基礎篇（四）之事務
    # 一. 事務簡介 **事務是一組操作的集合，它是一個不可分隔的工作單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要麼同時成功，要麼同時失敗。** **就比如：張三給李四轉賬1000塊錢，張三銀行賬戶的錢減少了1000，而李四銀行賬戶的錢要增加1000。這一組操作就必須 ...
  • 完全相容DynamoDB協議！GaussDB(for Cassandra)為NoSQL註入新活力
    摘要：DynamoDB是一款托管式的NoSQL資料庫服務，支持多種數據模型，廣泛應用於電商、社交媒體、游戲、IoT等場景。 本文分享自華為雲社區《完全相容DynamoDB協議！GaussDB(for Cassandra)為NoSQL註入新活力》，作者：GaussDB 資料庫 。 DynamoDB是一 ...
  • SQL Server中的NULL值處理：判斷與解決方案
    摘要： 在SQL Server資料庫中，NULL是表示缺少數據或未知值的特殊標記。處理NULL值是SQL開發人員經常遇到的問題之一。本文將介紹SQL Server中判斷和處理NULL值的不同方法，以及一些解決方案，幫助您更好地處理資料庫中的NULL值情況。 文章內容： 引言： 在資料庫開發中，經常會 ...
  • Mysql基礎篇（三）之多表查詢
    # 一. 多表關係 - **一對多（多對一）** - **多對一** - **一對一** ## 1. 一對多 ### (1). 案例：部門與員工的關係 ### (2). 關係：一個部門對應多個員工，一個員工對應一個部門 ### (3). 實現：在多的一方建立外建，指向一的一方的主鍵 ![](http ...
  • Mysql基礎篇（二）之函數和約束
    # 一. 函數 **Mysql中的函數主要分為四類：字元串函數、數值函數、日期函數、流程函數** ## 1. 字元串函數 **常用函數如下：** | 函數 | 功能 | | | | | CONCAT(S1, S2, ......Sn) | 字元串拼接，將S1，S2，.....Sn拼接成一個字元串 | ...
  • 移動應用加固：政務APP如何保障安全？
    作為一款服務國土調查和自然資源管理工作的一款手機App，是自然資源部自然資源調查監測司組織中國國土勘測規劃院應用互聯網+、雲計算等技術，依托“三調”和年度國土變更調查工程開發的平臺。分為管理版和專業版兩個版本，其中，管理版面向自然資源系統內人員，專業版面向系統外專業技術隊伍。 “國土調查雲”具有土地 ...
  • 記錄--多行標簽超出展開摺疊功能
    這裡給大家分享我在網上總結出來的一些知識，希望對大家有所幫助 前言 記錄分享每一個日常開發項目中的實用小知識，不整那些虛頭巴腦的框架理論與原理，之前分享過抽獎功能、簽字功能等，有興趣的可以看看本人以前的分享。 今天要分享的實用小知識是最近項目中遇到的標簽相關的功能，我不知道叫啥，姑且稱之為【多行標簽 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類