Excelize: Hopefully A Last Straw To VBA

1. Introduction

Recently I notice a Go library called Excelize. It supplies many excel manipulations and it's similar to Microsoft's VBA. I have interest in it but during research online I found very few people had talked about it yet. Excelize has already 5.9k stars on Github(Apr, 2020), frequently updated, and I have reasons to believe it is a highly promising library to learn.

Although it's not designed for data wrangling, but it is pure Go and maybe a good start to replace VBA. In this article I will present excelize in a normal daily workflow: read in data, do some changes, and make a data summarize(pivot table).

 

2. Manually install

On excelize Github's page(here), it is very simple using go's command "go get github.com/360EntSecGroup-Skylar/excelize".

But if for some reasons we can't use this commond, we can manually install excelize with only github as follow:

2.1 Install excelize main body

On excelize's github page(here), we click "Clone or download" button. Unzip it into "Your-Go-Path\src\github.com\360EntSecGroup-Skylar\excelize"

Run an example code to check if we are ready.

package main

import (
    "fmt"

    "github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
    f := excelize.NewFile()
    // Create a new sheet.
    index := f.NewSheet("Sheet2")
    // Set value of a cell.
    f.SetCellValue("Sheet2", "A2", "Hello world.")
    f.SetCellValue("Sheet1", "B2", 100)
    // Set active sheet of the workbook.
    f.SetActiveSheet(index)
    // Save xlsx file by the given path.
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

2.2 Install dependancy packages excelize requries

With following wrong message, it means we need "deepcopy" package.

We can go to github.com/mohae/deepcopy, download and unzip it into "Your-Go-Path\src\github.com\mohae\deepcopy".

D:\Libraries\Go\src\github.com\360EntSecGroup-Skylar\excelize\col.go:17:2: cannot find package "github.com/mohae/deepcopy"

With following wrong message, it means we need "net" and "text" packages.

We can go to github.com/golang, find "net" package and "text" package and download it. Download and unzip it into "Your-Go-Paht\golang.org\x\net" and  "Your-Go-Paht\golang.org\x\text".

D:\Libraries\Go\src\github.com\360EntSecGroup-Skylar\excelize\excelize.go:26:2: cannot find package "golang.org/x/net/html/charset"

D:\Libraries\Go\src\golang.org\x\net\html\charset\charset.go:20:2: cannot find package "golang.org/x/text/encoding" 

D:\Libraries\Go\src\golang.org\x\net\html\charset\charset.go:20:2: cannot find package "golang.org/x/text/encoding" 

D:\Libraries\Go\src\golang.org\x\net\html\charset\charset.go:21:2: cannot find package "golang.org/x/text/encoding/charmap"

D:\Libraries\Go\src\golang.org\x\net\html\charset\charset.go:22:2: cannot find package "golang.org/x/text/encoding/htmlindex" 

 

3. Read Data

We will focus on xlsx files. For now excelize has no function to read csv files. That might be designer's purpose, because read in a csv file is not so hard in Go. Besides, at the least situation, we can manually change a csv file into xlsx file.

As an example, we will use stocks.xlsx file as below:

package main

import (
	"fmt"

	"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
	file, err := excelize.OpenFile("stocks.xlsx")
	if err != nil {
		panic(err)
	}
	//Print data cell by cell
	rows, err := file.GetRows("stocks")
	if err != nil {
		panic(err)
	}
	for _, row := range rows {
		for _, colCell := range row {
			fmt.Print(colCell, "\t")
		}
		fmt.Println()
	}
}

On the console we can see the output as below, which is decent enough for now. 

　 

 

3. Do some changes

We want to add a column to the data, which classify those companies. For example, we want to label AAPL and MSFT as Target, CSCO as Untarget. 

In Python pandas it is as easy as we filter the data and label them. Now what we can do in excelize?

Excelize has a function called AutoFilter(). I believe it is named after VBA's AutoFilter function. 

package main

import (
	"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
	file, err := excelize.OpenFile("stocks.xlsx")
	if err != nil {
		panic(err)
	}
	// AutoFilter not working
	err = file.AutoFilter("stocks", "A1", "F11",
		`{"column":"D","expression":"Symbol == AAPL or Symbol == MSFT"}`)
	if err != nil {
		panic(err)
	}
	file.SaveAs("stocks_filter.xlsx")
}

 

Good part of this function is it doesn't need the precise range of data. In our example, data range is "A1:D10", but I use "A1:F11" and it can work fine.

However, this function seeming not actually filter the data as we need. In Excelize's Document, it is decribed as:

It isn't sufficient to just specify the filter condition. You must also hide any rows that don't match the filter condition. Rows are hidden using the SetRowVisible() method.

Normally we cannot know which row should be hidden, which makes this function somehow strange to me.

In addition, this AutoFilter funcion has bad thing as in VBA's AutoFilter: They have only two filter conditions. And Excelize has no VBA's AdvancedFilter function to filter more than two conditions.

We can use some for-loop to achieve our filter purpose. Maybe it's low level hard-coded but it actually works fine for my case.

package main

import (
	"strconv"
	"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
	file, err := excelize.OpenFile("stocks.xlsx")
	if err != nil {
		panic(err)
	}
	file.SetSheetRow("stocks", "E1", &[]string{"Cat"})
	rows, err := file.GetRows("stocks")
	if err != nil {
		panic(err)
	}
	for i := 0; i < len(rows); i++ {
		for j := 0; j < len(rows[i]); j++ {
			if rows[i][j] == "AAPL" || rows[i][j] == "MSFT" {
				file.SetCellValue("stocks", "E"+strconv.Itoa(i+1), "Target")
			}
			if rows[i][j] == "CSCO" {
				file.SetCellValue("stocks", "E"+strconv.Itoa(i+1), "Untarget")
			}
		}
	}
	file.SaveAs("stocks_filter2.xlsx")
}

　　

 

 4. Data Summarize(Pivot Table)

 In the AddPivotTable function, the DataRange has to be precise range, but the PivotTableRange can be casual but bigger than one cell.

package main

import (
	"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
	file, err := excelize.OpenFile("stocks.xlsx")
	if err != nil {
		panic(err)
	}
	err = file.AddPivotTable(&excelize.PivotTableOption{
		DataRange:"stocks!$A1:$D10",
		PivotTableRange: "stocks!$G1:G2",
		Rows: []excelize.PivotTableField{{Data: "Symbol"}, {Data: "Date"}},
		Data: []excelize.PivotTableField{{Data: "Close", Subtotal: "Average"},
	}})
	if err != nil {
		panic(err)
	}
	file.SaveAs("stocks_pivot.xlsx")
}

　　

 

Summay

Compare Excelize with VBA is not so fair actually. VBA has been developed nearly 30 years by big compaies, but this open library is still young and promising.

For an easy test in this article, it expose some weakness. For example the AutoFilter function has no reason to be as bad as VBA.

I think time will prove it's value in a near future. If you are interesting in it, author say it's open for contribution.

More infomation can be found on it's document(HERE) and github(HERE).