Step by Step Guide to Building an Excel API Integration in Go

Aug 3, 2024 • 7 minute read

Introduction

Hey there, fellow Go enthusiast! Ready to dive into the world of Excel manipulation with Go? We're going to build an awesome Excel API integration using the powerful excelize package. Buckle up, because by the end of this guide, you'll be slicing and dicing Excel files like a pro!

Prerequisites

Before we jump in, make sure you've got:

Go installed on your machine (if not, head over to golang.org and get it set up)
The excelize package (install it with go get github.com/360EntSecGroup-Skylar/excelize/v2)

Setting up the project

Let's get our project off the ground:

mkdir excel-api
cd excel-api
go mod init excel-api

Creating a basic Excel file

Time to write some Go! Let's start by creating a simple Excel file:

package main

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

func main() {
    f := excelize.NewFile()
    
    // Add a new sheet
    index := f.NewSheet("Sheet1")
    
    // Set cell value
    f.SetCellValue("Sheet1", "A1", "Hello Excel from Go!")
    
    // Set the active sheet
    f.SetActiveSheet(index)
    
    // Save the file
    if err := f.SaveAs("hello.xlsx"); err != nil {
        fmt.Println(err)
    }
}

Run this, and boom! You've got your first Excel file created with Go.

Reading data from an existing Excel file

Now, let's read some data:

f, err := excelize.OpenFile("hello.xlsx")
if err != nil {
    fmt.Println(err)
    return
}

cell, err := f.GetCellValue("Sheet1", "A1")
if err != nil {
    fmt.Println(err)
    return
}
fmt.Println(cell)

Modifying Excel files

Updating is a breeze:

f.SetCellValue("Sheet1", "B1", "I'm a new value!")
f.InsertRow("Sheet1", 2)
style, _ := f.NewStyle(`{"font":{"bold":true}}`)
f.SetCellStyle("Sheet1", "A1", "B1", style)

Working with formulas

Let's add some math to our spreadsheet:

f.SetCellValue("Sheet1", "A2", 10)
f.SetCellValue("Sheet1", "B2", 20)
f.SetCellFormula("Sheet1", "C2", "=A2+B2")

Creating charts

Spice up your data with a chart:

if err := f.AddChart("Sheet1", "E1", `{
    "type": "col",
    "series": [
        {
            "name": "Sheet1!$A$2",
            "categories": "Sheet1!$A$1:$B$1",
            "values": "Sheet1!$A$2:$B$2"
        }
    ],
    "title":
    {
        "name": "Fruit Chart"
    }
}`); err != nil {
    fmt.Println(err)
}

Handling multiple sheets

Working with multiple sheets is straightforward:

f.NewSheet("Sheet2")
f.SetCellValue("Sheet2", "A1", "This is Sheet2!")
f.CopySheet(1, 2)

Error handling and best practices

Always check for errors and consider performance:

if err := f.SetCellValue("Sheet1", "A1", "Value"); err != nil {
    log.Fatal(err)
}

// For large files, use streaming mode
stream, err := f.NewStreamWriter("Sheet1")
if err != nil {
    log.Fatal(err)
}

Building a simple API

Let's wrap our Excel operations in a basic HTTP server:

package main

import (
    "encoding/json"
    "net/http"
    "github.com/360EntSecGroup-Skylar/excelize/v2"
)

func main() {
    http.HandleFunc("/create", createExcel)
    http.ListenAndServe(":8080", nil)
}

func createExcel(w http.ResponseWriter, r *http.Request) {
    f := excelize.NewFile()
    f.SetCellValue("Sheet1", "A1", "Hello API!")
    if err := f.SaveAs("api_generated.xlsx"); err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    json.NewEncoder(w).Encode(map[string]string{"message": "Excel file created"})
}

Testing the API

Test your new API with curl:

curl http://localhost:8080/create

Or write some Go tests:

func TestCreateExcel(t *testing.T) {
    req, _ := http.NewRequest("GET", "/create", nil)
    rr := httptest.NewRecorder()
    handler := http.HandlerFunc(createExcel)
    handler.ServeHTTP(rr, req)
    if status := rr.Code; status != http.StatusOK {
        t.Errorf("handler returned wrong status code: got %v want %v", status, http.StatusOK)
    }
}

Conclusion

And there you have it! You've just built a fully functional Excel API integration in Go. From creating and modifying Excel files to wrapping it all up in an HTTP server, you're now equipped to handle Excel operations with ease.

Remember, this is just scratching the surface. The excelize package has tons more features to explore, so don't be afraid to dive deeper!

Resources

Now go forth and conquer those spreadsheets with your newfound Go powers!