[Go] Try excelize

Intro

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

• qax-os/excelize - GitHub

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)
}

• Content-Disposition - MDN
• MIME Type settings - Cloud edition Garoon Help

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 個人用月次収支