Optimize for reading large file?

[yqylovy]

I have a 30MB size excel，it contains 50000+ rows。
After it unziped, the xl/worksheets/sheet1.xml is about 330MB, so while reading it, a lot of memory are used.It's any idea to optimize it?

[xuri]

The column and row iterator read data as streaming, it will use lower memory than GetRows.

[yqylovy]

In my case，just call OpenFile and Cols method using 700MB.I found file.XLSX hold the raw data in memory, so it take space when reading.
But read data as streaming is better than GetRows,thanks.

[ywtGGG]

老哥，我也遇到了一样的问题，我一个170m，大约200w行的数据，读取的时候居然占用我接近2g的内存，能优化下不，用那个rows迭代器读的
Showing nodes accounting for 1916.15MB, 99.58% of 1924.18MB total
Dropped 30 nodes (cum <= 9.62MB)
Showing top 10 nodes out of 20
flat flat% sum% cum cum%
1336.30MB 69.45% 69.45% 1336.30MB 69.45% github.com/360EntSecGroup-Skylar/excelize/v2.readFile
346.17MB 17.99% 87.44% 515.17MB 26.77% github.com/360EntSecGroup-Skylar/excelize/v2.rowXMLHandler
145.50MB 7.56% 95.00% 145.50MB 7.56% encoding/xml.copyValue

[xuri]

Hi @yqylovy, @ywtGGG , I have improved the streaming reader performance for the rows iterator, memory usage decrease by about 93.7%. Please upgrade to the master branch code. This is a breaking change, the user should be close the stream after using the row's iterator, and close the spreadsheet after opening an existing spreadsheet like this:

package main

import (
    "fmt"

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

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    rows, err := f.Rows("Sheet1")
    if err != nil {
        fmt.Println(err)
        return
    }
    for rows.Next() {
        row, err := rows.Columns()
        if err != nil {
            fmt.Println(err)
        }
        for _, colCell := range row {
            fmt.Print(colCell, "\t")
        }
        fmt.Println()
    }
    // Close the stream
    if err = rows.Close(); err != nil {
        fmt.Println(err)
    }
    // Close the workbook
    if err := f.Close(); err != nil {
        fmt.Println(err)
    }
}

[yqylovy]

@xuri hi，请教下，我发现在 Cols 方法里面，还有

colIterator.cols.sheetXML = f.readBytes(name)
decoder := f.xmlNewDecoder(bytes.NewReader(colIterator.cols.sheetXML))

这样的解压调用，使得调用Cols时内存会暴涨，请问是有什么原因这块需要保留原始bytes吗？

[xuri]

The cell data was stored row by row in the worksheet so that the columns iterator with backtracking required was different from the rows iterator.

[yqylovy]

看到也是构建decoder来迭代，具体不同体现在哪呢？如果改成 f.xmlDecoder(name) 是仅仅影响性能，还是会有功能方面的影响？