Streaming
In some cases, when input file is really huge, more advanced techniques required to process data. Streaming is a most commonly used way to lazy load and process by blocks to:
- reduce overall memory usage and
- reduce time of initial loading
Full stream support of xlsx files can be challenging task. More over, in real world only sheets holds huge data to process. Taking this into account, the real world task will be to stream sheets, but not xlsx file entirely.
Limits
Xlsx2Go supports only limited set of features during sheet streaming.
Reading sheet
To stream in sheet, you should open it in Stream mode. After that you can access cells in sheet in a normal way.
N.B.
Sheets that were opened as stream, should be closed to free allocated resources.
sheet := xl.Sheet(0, xlsx.SheetModeStream)
//close sheet to free allocated resources
defer sheet.Close()
Stream Mode
If sheet previously was opened in normal mode, then you can't open it in stream mode. The reason is simple - streaming is for optimization, if information was totally loaded, then nothing to optimize.
Merged cells, Hyperlinks and etc
By default, there is no access to merged cells, hyperlinks and conditional formatting information, to get access you should open sheet in Multi Phase mode. In that case sheet will be processed few times - first one to load later information, such as merged cells, and last phase for actual streaming cells info.
sheet := xl.Sheet(0, xlsx.SheetModeStream, xlsx.SheetModeMultiPhase)
defer sheet.Close()
Example
package code
import (
"fmt"
"github.com/plandem/xlsx"
)
func Example_readStream() {
xl, err := xlsx.Open("./foo.xlsx")
if err != nil {
panic(err)
}
defer xl.Close()
sheet := xl.Sheet(0, xlsx.SheetModeStream)
defer sheet.Close()
totalCols, totalRows := sheet.Dimension()
for rIdx := 0; rIdx < totalRows; rIdx++ {
for cIdx := 0; cIdx < totalCols; cIdx++ {
cell := sheet.Cell(cIdx, rIdx)
fmt.Println(cell.String())
}
}
}
Writing sheet
To stream out sheet, you should create it with Stream mode. After that you can access cells in sheet in a normal way.
sheet := xl.AddSheet("new sheet", xlsx.SheetModeStream)
N.B.
Sheets that were created as stream, will auto close self during save phase, so no need to close it manually.
Example
package code
import (
"github.com/plandem/xlsx"
"github.com/plandem/xlsx/types"
)
func Example_writeStream() {
xl := xlsx.New()
sheet := xl.AddSheet("Sheet1", xlsx.SheetModeStream)
for iRow, iRowMax := 0, 100; iRow < iRowMax; iRow++ {
for iCol, iColMax := 0, 9; iCol < iColMax; iCol++ {
sheet.Cell(iCol, iRow).SetValue(types.CellRefFromIndexes(iCol, iRow))
}
}
xl.SaveAs("./foo.xlsx")
}