[Go] Try excelize
Tags: go, excelize
Published: 2023-05-24 16:35:34

## Intro
In this time, I will try reading MS Excel files by excelize.

* [qax-os/excelize - GitHub](https://github.com/qax-os/excelize)

## Opening and saving files
### Opening from a file path and saving
#### main.go
```go
package main

import (
	"fmt"
	"log"
	"os"
)
func main() {
    // Get file from current directory
	cur, _ := os.Getwd()
	filePath := fmt.Sprintf("%s/files/%s", cur, "sample.xlsm")
	err := SaveFileFromPath(filePath, fmt.Sprintf("%s/files/%s", cur, "sample2.xlsm"))
	if err != nil {
		log.Println(err.Error())
	}
}
```

#### xlsWriter.go
```go
package main

import (
	"bytes"
	"fmt"
	"log"

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

func SaveFileFromPath(filePath string, saveFilePath string) error {
    // open xlsm file from file path
	xlFile, err := excelize.OpenFile(filePath)
	if err != nil {
		fmt.Println(err)
		return err
	}
	defer func() {
		// Close the spreadsheet.
		if err := xlFile.Close(); err != nil {
			fmt.Println(err)
		}
	}()
    // Write the text in A1 cell of the first sheet
	xlFile.SetCellStr(xlFile.GetSheetName(0), "A1", "Hello")
	if len(saveFilePath) <= 0 {
		err = xlFile.Save()
	} else {
		err = xlFile.SaveAs(saveFilePath)
	}
	return err
}
```
### Opening from byte array and returning byte array
#### main.go
```go
package main

import (
	"fmt"
	"log"
	"os"
)

func main() {
...
	fileData, err := os.ReadFile(filePath)
	if err != nil {
		log.Println(err.Error())
		return
	}
	result, err := WriteFromByteData(fileData)
	if err != nil {
		log.Println(err.Error())
		return
	}
	// file path, byte[], permission
	err = os.WriteFile(fmt.Sprintf("%s/files/%s", cur, "sample2.xlsm"), result, 0664)
	if err != nil {
		log.Println(err.Error())
		return
	}
}
```

#### xlsWriter.go
```go
func WriteFromByteData(fileData []byte) ([]byte, error) {
    // Create bytes.Reader(io.Reader)
	reader := bytes.NewReader(fileData)
	xlFile, err := excelize.OpenReader(reader)
	if err != nil {
		fmt.Println(err)
		return nil, err
	}
	defer func() {
		// Close the spreadsheet.
		if err := xlFile.Close(); err != nil {
			fmt.Println(err)
		}
	}()
    // Get sheet name list
	slist := xlFile.GetSheetList()
	xlFile.SetCellStr(slist[0], "A1", "Hello")
    // save as bytes.Buffer
	buf, err := xlFile.WriteToBuffer()
	if err != nil {
		fmt.Println(err)
		return nil, err
	}
    // Get byte array
	result := buf.Bytes()

	return result, nil
}
```
### Returning as HTTP response
#### main.go
```go
package main

import (
	"fmt"
	"net/http"
	"os"
)

func main() {
	http.HandleFunc("/file", func(w http.ResponseWriter, r *http.Request) {
		cur, _ := os.Getwd()
		filePath := fmt.Sprintf("%s/files/%s", cur, "sample.xlsm")
		WriteResponse(filePath, w)
	})
	log.Fatal(http.ListenAndServe("localhost:8085", nil))
}
```

#### xlsWriter.go
```go
package main

import (
	"bytes"
	"fmt"
	"log"
	"net/http"

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

func WriteResponse(filePath string, w http.ResponseWriter) {
	xlFile, err := excelize.OpenFile(filePath)
	if err != nil {
		fmt.Println(err)
		w.WriteHeader(500)
		return
	}
	defer func() {
		// Close the spreadsheet.
		if err := xlFile.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	// Set file name
	w.Header().Set("Content-Disposition", "attachment; filename=sample.xlsm")
	// Set Content-Type
	w.Header().Set("Content-Type", "application/vnd.ms-excel.sheet.macroEnabled.12")
	// DO NOT set before setting the file name and the Content-Type
	w.WriteHeader(200)
	// Directly writing into http.ResponseWriter
	xlFile.WriteTo(w)
}
```

* [Content-Disposition - MDN](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Disposition)
* [MIME Type settings - Cloud edition Garoon Help](https://jp.cybozu.help/g/en/admin/system/files/type.html)

## Search by defined names
### Get sheet names from defined names
```go
...
func SaveFileFromPath(filePath string, saveFilePath string) error {
...
	// Get defined names
	for _, name := range xlFile.GetDefinedName() {
		log.Printf("Name: %s Refer: %s Scope: %s", name.Name, name.RefersTo, name.Scope)
		splittedRefs := strings.Split(name.RefersTo, "!")
		// The name set for the shape does not have a sheet name
		if len(splittedRefs) > 1 {
			log.Printf("SheetName %s", splittedRefs[0])
		}
	}
...
}
```

#### Result
```ini
# a name given to the book
2023/05/25 01:17:16 Name: MonthSheet Refer: 個人用月次収支!$B$4 Scope: 個人用月次収支
2023/05/25 01:17:16 SheetName 個人用月次収支
# a name given to a shape
2023/05/25 01:17:16 Name: ShapeSheet Refer: "二等辺三角形 2" Scope: 個人用月次収支
# a name given to the sheet
2023/05/25 01:17:16 Name: TitleBook Refer: 個人用月次収支!$B$2 Scope: Workbook
2023/05/25 01:17:16 SheetName 個人用月次収支
```