Intro
In this time, I will try reading MS Excel files by excelize.
Opening and saving files
Opening from a file path and saving
main.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
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
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
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
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
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)
}
Search by defined names
Get sheet names from defined names
...
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
# 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 個人用月次収支
Top comments (0)