如何使用Golang建立與讀取Excel檔案

2022-07-08 14:06:22

摘要

本文提出一種使用 Golang 進行 Excel 檔案建立和讀取的方案。首先對問題進行分析，引出方案的基本架構；然後分章節描述了 Excelize 基礎庫的基本用法，以及 Excel 資料在 Golang 中的表示和解析方式，並進一步提出了應對大規模資料寫入場景的優化方法；最後，指出了一些可能遇到的問題和對策。

引言

飛書是業界領先的下一代企業共同作業與管理平臺，集合了很多細分領域的優秀 ToB 產品。作者所在的部門，負責員工持股計劃（Employee Stock Ownership Plan，ESOP）相關係統的研發，主要的後端開發語言為 Golang 。系統管理員，需要定期對公司 ESOP 的參與人資訊，以及股權激勵的授予、歸屬、取消等資訊，以Excel表格的形式進行彙總，為相關決策提供參考和依據；必要時，也需要通過修改 Excel 資料表，上傳系統，實現參與人、授予等資訊的批次修改。

總而言之，隨著 Golang 的廣泛應用，如何使用 Golang 進行 Excel 資料表的讀取和建立，是一個值得探討的問題。本文將描述一套完整的 Excel 檔案建立和讀取的方案，方案力圖做到靈活通用，和具體的業務資料型別無關，同時能夠兼顧大規模資料匯出的效率。最後，分享了一些研發過程中遇到的問題，希望能夠避免讀者再次踩坑。

正文

架構

在開始具體深入地描述我們的方案之前，不妨先思考一下，實現這樣一個方案，需要解決的問題都有哪些？資料的處理大致應該是一個什麼樣的流程？下圖是ESOP系統中，涉及到Excel檔案建立和讀取的部分業務場景。

檔案物件

很自然地，我們需要引入一個 Excel 檔案物件，此物件應該包含一個 Excel 工作簿的所有資訊：有哪些工作表，每個工作表都有哪些列，每一行資料是什麼，單元格和文字的格式是什麼樣的，某一列是否包含列舉值，等等。我們對檔案的任何操作，無論是資料的增刪改，還是格式樣式的變更，亦或是檔案的開啟和儲存，都應該將這個檔案物件作為切入點。

資料的表示

一個 Excel 工作表，可以很自然地和 Golang 結構體聯絡起來，工作表的每一列對應結構體中的一個欄位。然而，只定義一個基本的結構體還不夠，至少還應該想辦法儲存每個欄位對應的 Excel 列名、資料驗證等等。

資料的解析

用 Golang 結構體表示了一個 Excel 工作表，自然還需要一種方法來解釋結構體中記錄的各種 Excel 資料資訊，這樣，程式程式碼才知道如何將結構體資料正確地寫入檔案物件，以及反過來，如何讀取檔案物件中的資料，來還原 Golang 結構體。資料表示和解析的整體思路，如下圖所示。

實際架構

幸運的是，上面幾個問題，我們都可以找到成熟且有力的工具來解決。方案基本的架構如下圖所示。

檔案物件的建立和各種操作，我們通過 Excelize 基礎庫來實現，後面會對該基礎庫進行簡要介紹。

Excel 資料的表示，我們使用包含 tag 的 Golang 結構體實現，資料值以外的其他資訊，用某種格式記錄在 tag 中。

Excel 資料的解析，我們利用 Golang 的反射機制實現。通過反射，我們可以解析出結構體每個欄位的值以及 tag 中儲存的其他有用資訊。

Excelize 基礎庫

Excelize 是一個使用 Golang 編寫的，用於操作 Office Excel 檔案的基礎庫，支援 Golang 1.15 及以上版本。下面對其基本用法進行介紹，涉及到的各 API 的具體用法，可檢視文章末尾給出的Excelize Doc連結。

檔案

檔案物件是本文大部分 Excel 檔案操作的入口。使用 NewFile 函數，可以建立一個空白的檔案物件。如果需要用已有的 Excel 檔案資料建立檔案物件，可以使用 OpenReader 或者 OpenFile 函數。結束檔案操作之後，通常需要將檔案儲存在本地，或者將檔案輸出為位元組陣列，返回給前端供使用者下載，使用檔案物件的 SaveAs 和 WriteToBuffer 方法，可以達到目的。

座標

在使用更多功能之前，必須搞清楚如何定位一個單元格或一個區域。

Excel 中使用形如“A1”這樣的座標來定位單元格。相應的，在 Excelize 基礎庫中，可以通過 CoordinatesToCellName 函數，將行號和列號這個二元組，轉換為一個形如“A1”的座標。一些操作，需要通過兩個座標來確定生效區域，此時，兩個相同的座標表示對一個單元格生效，兩個不同的座標表示對一個矩形區域生效，分別指向區域左上角和右下角的單元格。

樣式

樣式包含字型、文字大小、粗細、位置、顏色等屬性。Excelize 中，樣式可以通過 NewStyle 方法建立，返回一個整數索引，唯一標識這個樣式。通過檔案物件的 SetCellStyle 方法，指定座標和樣式索引，可以為一個區域設定統一的樣式。

單元格操作

單元格的常用操作有設定高度和寬度、合併單元格、設定單元格資料等。

我們針對一行設定高度，針對一列設定寬度，分別使用檔案物件中的 SetRowHeight 和 SetColWidth 方法進行。

在“座標”部分，我們講解了如何確定一個區域，合併單元格的操作，就是一個例子，我們可以使用檔案物件中的 MergeCell 方法來完成。

一般情況下，資料的寫入操作，在單元格層面進行。使用檔案物件中的 SetCellValue 方法，指定一個座標，可以將 Golang 中常用的資料型別（包括無符號整數、有符號整數、浮點數、位元組切片、字串、時間、布林型別等等）的值寫入對應的單元格。

資料驗證

資料驗證功能，可以為某一列資料定義列舉值，使用者可以使用下拉選單，為該列中某一行選擇要填入的值。

使用 NewDataValidation 函數，可以建立一個資料驗證物件，不妨命名為 vd 。為了指定 vd 的生效範圍，需要為 vd 設定一個 Sqref 屬性，格式形如“A1:A10”，表示此 vd 對第 1 列中第 1 行到第 10 行的資料生效。然後，使用 vd 的 SetDropList 方法，設定下拉選單的內容。最後，使用檔案物件中的 AddDataValidation 方法，將此 vd 寫入檔案。

資料的表示和解析

表示

根據“架構”部分的設想，我們可以定義一個帶有 tag 的結構體，來表示一個 Excel 工作表。

Golang 結構體的 tag ，是以鍵值對的形式表示的。為了和其他用途的 tag 進行區分，我們將本方案的 tag 資訊，用一個名為 ex 的鍵來表示，而 ex 的值，仍然沿用鍵值對的形式，如下列程式碼所示：

type PeopleInfo struct {
    PeopleNo          string `ex:"head:工號;type:string;required;color:#0070C0"`
    PeopleName        string `ex:"head:姓名;type:string;required"`
    BirthDate         string `ex:"head:出生日期;type:date;omitempty"`
    EmploymentStatus  string `ex:"head:在職狀態;type:string;select:在職,離職"`
}

我們可以為ex設計下列屬性：

head，指定了此結構體欄位對應的 Excel 列名。
type，表示在使用反射進行資料解析時，會將此結構體欄位的值作為指定的型別處理。
select，表示此欄位所在的列，包含一個下拉選單，列表中的列舉值由 select 後面的值指定。
required，表示此欄位必須包含非零值，否則在寫入 Excel 時會報錯。
omitempty，表示此欄位如果是零值，則對應的單元格留空。
color，指定了列名所在單元格的顏色，通過這個欄位，可以為不同的列名設定不同的底色，賦予一些含義，例如，可以將必填的列和選填的列，設定不同的底色。可以通過 Excel 的 RGB 顏色設定視窗，檢視不同顏色對應的色號，作為 color 屬性的值。

此外，我們還要定義一個結構體，儲存 ex 的解析結果，結構體不妨命名為 Setting ：

type Setting struct {
    Head      string
    Type      string
    Select    []string
    Required  bool
    OmitEmpty bool
    Color     string
}

解析

使用 Golang 的反射機制，對類似於 PeopleInfo 這樣的結構體，我們可以抽取每個欄位的ex值，進行字串處理後，組裝成Setting物件。範例程式碼如下：

import reflect

// 解析第idx個欄位的ex
func ParseEx(idx int, data interface{}) *Setting {
    tp := reflect.ValueOf(data).Type().Elem().Elem() // 獲得結構體的反射Type
    field := tp.Field(idx)
    exStr := field.Tag.Get("ex") // 獲得tag中ex的值
    setting := &Setting{}
    // 下面可通過對exStr字串進行切分，來組裝Setting物件，較為簡單，省略
    ...
    return setting
}

func main() {
    ParseEx(0, []*PeopleInfo{{}})
}

由於反射機制較為抽象，這裡不再贅述，對反射不熟悉的讀者，可以檢視文章末尾給出的 Golang reflect 連結。

組裝了 Setting 之後，我們可以繼續通過反射，來獲取結構體中各欄位的值，然後使用前面介紹過的一些 API ，將這些資訊寫入 Excel 檔案。

下面給出建立 Excel 檔案的範例程式碼，程式碼對 omitempty 和 type 屬性進行了處理，並將部分資料寫入檔案物件。其他 ex 屬性的處理，因篇幅有限，不再演示，讀者有興趣可以自己嘗試實現。

import reflect

import "github.com/xuri/excelize/v2"

// 寫入第1行資料的第idx個欄位
func WriteFirstRow(ef *excelize.File, idx int, data interface{}) error {
    firstRow := reflect.ValueOf(data).Index(0).Elem() // 第1個資料的反射Value
    v := firstRow.Field(idx) // 第idx個欄位的反射Value
    setting := ParseEx(idx, data) // 第idx個欄位解析出來的ex資訊
    
    // 處理omitempty
    if setting.OmitEmpty && v.IsZero() {
       return nil
    }
    
    var val interface{}
    // 處理type
    switch setting.Type {
    case "string":
        val = v.String()
    case ...
    }
    
    // Excel列號從1開始，所以列號是idx+1；行號從2開始，因為第1行要顯示列名
    axis, err := excelize.CoordinatesToCellName(idx+1, 2)
    if err != nil {
        return err
    }
    
    // 將資料寫入預設工作表Sheet1中axis座標處的單元格
    return ef.SetCellValue("Sheet1", axis, val)
}

func main() {
    ef := excelize.NewFile()
    WriteFirstRow(ef, 0, []*PeopleInfo{{PeopleNo: "test"}})
    ef.SaveAs("people_info.xlsx")
}

上面給出的是建立 Excel 檔案的範例。讀取 Excel 檔案的過程是類似的，首先從二進位制資料建立出檔案物件，然後根據檔案物件中的每一列資料，生成對應的結構體物件。範例程式碼如下。

import reflect

import "github.com/xuri/excelize/v2"

// 讀取第1行資料的第idx列，假定idx從0開始，只有一個預設工作表Sheet1，資料從第2行開始
func ReadFirstRow(ef *excelize.File, idx int, holder interface{}) error {
    rows, err := ef.GetRows("Sheet1") // 所有行
    if err != nil {
       return err
    }
    row := rows[1]

    tp := reflect.TypeOf(holder).Elem().Elem().Elem() // 結構體的型別
    val := reflect.New(tp)                            // 建立一個新的結構體物件

    field := val.Elem().Field(idx) // 第idx個欄位的反射Value
    cellValue := row[idx]          // 第idx個欄位對應的Excel資料
    field.SetString(cellValue)     // 將Excel資料儲存到結構體物件的對應欄位中

    listV := reflect.ValueOf(holder)
    listV.Elem().Set(reflect.Append(listV.Elem(), val)) // 將結構體物件新增到holder中

    return nil
}

func main() {
    ef, _ := excelize.OpenFile("people_info.xlsx")
    holder := make([]*PeopleInfo, 0, 10)
    ReadFirstRow(ef, 0, &holder)
}

本節描述瞭如何使用 Golang 來表示和解析 Excel 資料，以及在此基礎上如何建立和讀取 Excel 檔案。範例程式碼中對 Excel 檔案的寫入和讀取操作函數，使用 interface 型別的引數作為資料提供方或接收方，和具體的業務資料型別無關，因此該方案具備通用性。

大規模資料的寫入

之前演示的 Excel 檔案寫入方式，是在單元格層面進行的，在大規模資料寫入的場景下，耗時長，體驗差。Excelize 提供了一套流式寫入 API，以行為單位寫入 Excel 資料，能夠顯著提高大規模資料的寫入效率。

使用流式 API 寫入 Excel 資料，首先需要使用檔案物件的 NewStreamWriter 方法，建立一個流式寫入器。寫入一行資料時，需要構造一個切片，表示這一行資料，切片中每個元素表示一個單元格資訊，包含單元格的值和樣式。單元格元素，可以使用 Excelize 中提供的 Cell 資料型別來表示。之後，就可以通過流式寫入器的 SetRow 方法，將行資料寫入 Excel 檔案。行的高度，可以在寫入時指定。範例程式碼如下：

import reflect

import "github.com/xuri/excelize/v2"

// 寫入第1行資料
func StreamWriteFirstRow(sw *excelize.StreamWriter, data interface{}) error {
    firstRow := reflect.ValueOf(data).Index(0).Elem() // 第1個資料的反射Value
    v := firstRow.Field(0) // 首個欄位的反射Value
    style := &excelize.Style{
        Alignment: &excelize.Alignment{
            Horizontal: "left",
            Vertical:   "center",
        },
    }
    styleID, err := sw.File.NewStyle(style) // 建立樣式
    if err != nil {
        return err
    }
    
    length := firstRow.NumField() // 結構體欄位數量
    
    row := make([]interface{}, length) // 建立一個切片，表示一行資料
    row[0] = excelize.Cell{
       Value: v.String(),
       StyleID: styleID,
    } // 這裡只寫入首個欄位
    
    // 每一行都是從列號1開始；行號從2開始，因為假定第1行要顯示列名
    axis, err := excelize.CoordinatesToCellName(1, 2)
    if err != nil {
        return err
    }
    
    // 流式寫入行，並指定高度
    return sw.SetRow(axis, row, excelize.RowOpts{Height: 16})
}

func main() {
    ef := excelize.NewFile()
    sw, _ := ef.NewStreamWriter("Sheet1")
    StreamWriteFirstRow(sw, []*PeopleInfo{{PeopleNo: "test"}})
    sw.Flush()
    ef.SaveAs("stream_people_info.xlsx")
}

需要關注的問題

大量列舉值的設定

在“資料驗證”部分，我們提到使用 SetDropList 方法可以設定下拉選單。然而，這樣設定的下拉選單是有侷限性的：每個列舉值使用逗號拼接後得到的字串，其總長度不得超過 255 個字元。

如果超過了這個限制，我們需要建立一個工作表（假設名稱為 enum ），將列舉值儲存在 enum 工作表的某一列中（假設儲存在 A 列，第 2 行到第 10 行），然後通過 vd 的 SetSqrefDropList 方法設定下拉選單，此方法通過一個形如“enum!A2:A10”的字串來指定列舉值的儲存位置，即 enum 工作表 A 列的第 2 行到第 10 行。

大工作表的讀取

讀取 Excel 檔案時，我們會基於已有的物理檔案來建立檔案物件，如果其中有一個很大的工作表，那麼當我們將這個檔案物件另存為一個新的物理檔案時，可能會發現檔案變小了，且無法正常開啟。

Excelize 庫通過一些引數，來限制開啟和讀取工作薄時的記憶體使用。其中，WorksheetUnzipMemLimit 引數限制了 unzip 一個工作表時允許使用的最大記憶體，預設為 16 MB 。當一個工作表大小超過這一預設值時，此工作表的資料會 unzip 到作業系統的臨時檔案中。然而，當我們進行另存為操作時，這些臨時檔案的資料卻被 Excelize 的相關函數忽略了。

這可能是 Excelize 庫的一個 bug ，但是我們可以通過增大 WorksheetUnzipMemLimit 引數來規避。這一引數的值，可根據具體業務場景來設定，最大可以設定為和 UnzipSizeLimit 引數相同，後者是開啟整個工作簿時總的記憶體使用限制，預設為 16 GB 。

流式寫入的注意事項

流式操作有自己的一套 API ，用於資料寫入、合併單元格、設定列寬等操作。流式 API 不能和普通的非流式 API 混用，否則可能無法正確寫入資料或設定格式。使用流式 API 設定列寬，需要在寫入資料之前進行。流式寫入完成之後，需要呼叫流式寫入器的 Flush 方法來結束寫入，否則儲存檔案時可能會丟失資料。

結語

本文對 Golang 中建立和讀取 Excel 檔案所涉及的各方面問題，進行了總結歸納，並提出了一套完整的方案。此方案使用 Golang 結構體的 tag ，以及 Golang 反射機制，對 Excel 資料進行定義和解釋，實現了 Golang 結構體和 Excel 資料的雙向對映，同時使用成熟強大的 Excelize 基礎庫，對 Excel 檔案進行建立、寫入、讀取等操作。

希望讀者能有所收穫，為解決實際的問題提供思路，也歡迎大家對方案中的不足之處提出改進意見。