Excelize

The StreamWriter is designed for generating or reading worksheets with massive amounts of data. It uses a streaming approach to reduce memory consumption, utilizing temporary files on disk when in-memory chunks exceed 16MB.

Critical Constraints:

• Order of Operations: You must call configuration methods (like SetColVisible, SetColWidth, SetColStyle, SetPanes) before calling SetRow.
• Ascending Rows: When using SetRow, you must ensure that the row numbers are provided in ascending order.
• No Mixing: You cannot mix normal mode functions (standard SetCellValue etc.) and stream mode functions on the same worksheet.
• Finalization: You must call Flush() to end the streaming process and ensure all data is written to the file.
• Read Access: You cannot retrieve cell values from a worksheet while using a StreamWriter.

f := excelize.NewFile()
// ... handle error
sw, err := f.NewStreamWriter("Sheet1")
// ... handle error

// 1. Set column properties BEFORE rows
sw.SetColWidth(1, 1, 20)

// 2. Write rows in ascending order
err = sw.SetRow("A1", []interface{}{"Data", 123})

// 3. Finalize
err = sw.Flush()

// 4. Save file
f.SaveAs("Book1.xlsx")

The PivotTableShowValuesAs struct allows you to perform calculations on data fields instead of simple aggregation.

Supported Calculation Types (PivotTableShowValuesAsType):

• PivotTableShowValuesAsNoCalculation: No special calculation.
• PivotTableShowValuesAsPercentOfGrandTotal: % of Grand Total.
• PivotTableShowValuesAsPercentOfColumnTotal: % of Column Total.
• PivotTableShowValuesAsPercentOfRowTotal: % of Row Total.
• PivotTableShowValuesAsPercentOf: % of a specific base field/item.
• PivotTableShowValuesAsPercentOfParentRowTotal: % of Parent Row Total.
• PivotTableShowValuesAsPercentOfParentColumnTotal: % of Parent Column Total.
• PivotTableShowValuesAsPercentOfParentTotal: % of Parent Total.
• PivotTableShowValuesAsDifferenceFrom: Difference from a base field/item.
• PivotTableShowValuesAsPercentDifferenceFrom: % Difference from a base field/item.
• PivotTableShowValuesAsRunningTotalIn: Running total.
• PivotTableShowValuesAsPercentRunningTotalIn: % Running total.
• PivotTableShowValuesAsRankSmallestToLargest: Rank (Ascending).
• PivotTableShowValuesAsRankLargestToSmallest: Rank (Descending).
• PivotTableShowValuesAsIndex: Index.

Requirements for Base Fields/Items: Some calculation types require BaseField and BaseItem to be set:

• Requires BaseField: PivotTableShowValuesAsPercentOf, PivotTableShowValuesAsPercentOfParentTotal, PivotTableShowValuesAsDifferenceFrom, PivotTableShowValuesAsPercentDifferenceFrom, PivotTableShowValuesAsRunningTotalIn, PivotTableShowValuesAsPercentRunningTotalIn, PivotTableShowValuesAsRankSmallestToLargest, PivotTableShowValuesAsRankLargestToSmallest.
• Requires BaseItem: PivotTableShowValuesAsPercentOf, PivotTableShowValuesAsDifferenceFrom, PivotTableShowValuesAsPercentDifferenceFrom.

type PivotTableShowValuesAs struct {
	Type      PivotTableShowValuesAsType
	BaseField string
	BaseItem  string
}

You can insert images into a worksheet using AddPicture (from a file path) or AddPictureFromBytes (from raw bytes).

Important Limitations:

• Currently, these methods only support adding pictures that are placed over the cells.
• They do not support adding pictures placed in cells or creating Kingsoft WPS Office embedded image cells.
• Supported image types: BMP, EMF, EMZ, GIF, ICO, JPEG, JPG, PNG, SVG, TIF, TIFF, WMF, and WMZ.

To customize the image, use the GraphicOptions struct to control scaling, offsets, hyperlinks, and positioning.

package main

import (
    "fmt"
    "_" // Import image decoders for supported formats
    "image/gif"
    "image/jpeg"
    "image/png"
    "github.com/xuri/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()

    // 1. Basic insertion from file
    if err := f.AddPicture("Sheet1", "A2", "image.jpg", nil); err != nil {
        fmt.Println(err)
        return
    }

    // 2. Insertion with scaling and internal hyperlink
    enable := true
    if err := f.AddPicture("Sheet1", "D2", "image.png", 
        &excelize.GraphicOptions{
            ScaleX:        0.5,
            ScaleY:        0.5,
            Hyperlink:     "#Sheet2!D8",
            HyperlinkType: "Location",
        }, 
    ); err != nil {
        fmt.Println(err)
        return
    }

    // 3. Insertion with offsets, external hyperlink, and positioning
    if err := f.AddPicture("Sheet1", "H2", "image.gif", 
        &excelize.GraphicOptions{
            PrintObject:     &enable,
            LockAspectRatio: false,
            OffsetX:         15,
            OffsetY:         10,
            Hyperlink:       "https://github.com/xuri/excelize",
            HyperlinkType:   "External",
            Positioning:     "oneCell",
        }, 
    ); err != nil {
        fmt.Println(err)
        return
    }

    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

Excelize allows you to generate charts based on existing worksheet data using the AddChart method. You provide a *excelize.Chart configuration object which includes:

• Type: The chart type (e.g., excelize.Col3DClustered).
• Series: A slice of excelize.ChartSeries defining the data ranges (Categories and Values) and names.
• Title: A excelize.ChartTitle containing RichTextRun elements for the chart title.

You can also use excelize.CoordinatesToCellName(col, row) to convert numeric coordinates into Excel cell names (e.g., A1).

package main

import (
    "fmt"
    "github.com/xuri/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for idx, row := range [][]interface{}{
        {nil, "Apple", "Orange", "Pear"}, {"Small", 2, 3, 3},
        {"Normal", 5, 2, 4}, {"Large", 6, 7, 8},
    } {
        cell, err := excelize.CoordinatesToCellName(1, idx+1)
        if err != nil {
            fmt.Println(err)
            return
        }
        f.SetSheetRow("Sheet1", cell, &row)
    }
    if err := f.AddChart("Sheet1", "E1", &excelize.Chart{
        Type: excelize.Col3DClustered,
        Series: []excelize.ChartSeries{
            {
                Name:       "Sheet1!$A$2",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$2:$D$2",
            },
            {
                Name:       "Sheet1!$A$3",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$3:$D$3",
            },
            {
                Name:       "Sheet1!$A$4",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$4:$D$4",
            }},
        Title: excelize.ChartTitle{
            Paragraph: []excelize.RichTextRun{
                {
                    Text: "Fruit 3D Clustered Column Chart",
                },
            },
        },
    }); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

To read an existing spreadsheet, use excelize.OpenFile(). You can retrieve specific cell values using GetCellValue() or iterate through all rows in a worksheet using GetRows().

package main

import (
    "fmt"
    "github.com/xuri/excelize/v2"
)

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Get value from cell by given worksheet name and cell reference.
    cell, err := f.GetCellValue("Sheet1", "B2")
    if err != nil {
        fmt.Println(err)
        return
    }
    fmt.Println(cell)
    // Get all the rows in the Sheet1.
    rows, err := f.GetRows("Sheet1")
    if err != nil {
        fmt.Println(err)
        return
    }
    for _, row := range rows {
        for _, colCell := range row {
            fmt.Print(colCell, "\t")
        }
        fmt.Println()
    }
}

Excelize allows you to generate charts (like 3D clustered column charts) based on worksheet data. Use AddChart() and provide an excelize.Chart configuration object specifying the Type, Series (with Name, Categories, and Values as cell ranges), and a Title.

package main

import (
    "fmt"
    "github.com/xuri/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for idx, row := range [][]interface{}{
        {nil, "Apple", "Orange", "Pear"}, {"Small", 2, 3, 3},
        {"Normal", 5, 2, 4}, {"Large", 6, 7, 8},
    } {
        cell, err := excelize.CoordinatesToCellName(1, idx+1)
        if err != nil {
            fmt.Println(err)
            return
        }
        f.SetSheetRow("Sheet1", cell, &row)
    }
    if err := f.AddChart("Sheet1", "E1", &excelize.Chart{
        Type: excelize.Col3DClustered,
        Series: []excelize.ChartSeries{
            {
                Name:       "Sheet1!$A$2",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$2:$D$2",
            },
            {
                Name:       "Sheet1!$A$3",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$3:$D$3",
            },
            {
                Name:       "Sheet1!$A$4",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$4:$D$4",
            }},
        Title: excelize.ChartTitle{
            Paragraph: []excelize.RichTextRun{
                {
                    Text: "Fruit 3D Clustered Column Chart",
                },
            },
        },
    }); err != nil {
        fmt.Println(err)
        return
    }
    // Save spreadsheet by the given path.
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

You can create a new Excel file using excelize.NewFile(). The workflow typically involves:

1. Initializing a new file.
2. Creating new sheets with NewSheet(name).
3. Setting cell values using SetCellValue(sheet, cell, value).
4. Setting the active sheet with SetActiveSheet(index).
5. Saving the file with SaveAs(path).

Always ensure you call f.Close() to release resources, preferably using defer.

package main

import (
    "fmt"
    "github.com/xuri/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Create a sheet
    index, err := f.NewSheet("Sheet2")
    if err != nil {
        fmt.Println(err)
        return
    }
    // Set cell values
    f.SetCellValue("Sheet2", "A2", "Hello world.")
    f.SetCellValue("Sheet1", "B2", 100)
    // Set default sheet
    f.SetActiveSheet(index)
    // Save file
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

You can insert images into a worksheet using AddPicture(). You can control the appearance and behavior using excelize.GraphicOptions:

• Scaling: Use ScaleX and ScaleY to resize the image.
• Offsetting: Use OffsetX and OffsetY to position the image relative to the cell.
• Printing: Use PrintObject (pointer to bool) to control if the image is printed.
• Aspect Ratio: Use LockAspectRatio to maintain image proportions.
• Locking: Use Locked to control if the object can be moved/resized in Excel.

package main

import (
    "fmt"
    _ "image/gif"
    _ "image/jpeg"
    _ "image/png"

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

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Insert a picture.
    if err := f.AddPicture("Sheet1", "A2", "image.png", nil); err != nil {
        fmt.Println(err)
    }
    // Insert a picture to worksheet with scaling.
    if err := f.AddPicture("Sheet1", "D2", "image.jpg",
        &excelize.GraphicOptions{ScaleX: 0.5, ScaleY: 0.5}); err != nil {
        fmt.Println(err)
    }
    // Insert a picture offset in the cell with printing support.
    enable, disable := true, false
    if err := f.AddPicture("Sheet1", "H2", "image.gif",
        &excelize.GraphicOptions{
            PrintObject:     &enable,
            LockAspectRatio: false,
            OffsetX:         15,
            OffsetY:         10,
            Locked:          &disable,
        }); err != nil {
        fmt.Println(err)
    }
    // Save the spreadsheet with the origin path.
    if err = f.Save(); err != nil {
        fmt.Println(err)
    }
}

Use the AddPicture method to insert images into a worksheet.

Key features:

• Basic Insertion: AddPicture(sheet, cell, filename, options).
• Scaling: Use &excelize.GraphicOptions{ScaleX: float64, ScaleY: float64} to resize the image.
• Advanced Properties: Use &excelize.GraphicOptions to control:
  • PrintObject: Whether the image is printed.
  • LockAspectRatio: Whether to maintain the image aspect ratio.
  • OffsetX / OffsetY: Pixel offsets.
  • Locked: Whether the image is locked.

Note: You may need to import image formats (e.g., _ "image/png") to support specific file types.

package main

import (
    "fmt"
    _ "image/gif"
    _ "image/jpeg"
    _ "image/png"

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

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Basic insert
    if err := f.AddPicture("Sheet1", "A2", "image.png", nil); err != nil {
        fmt.Println(err)
    }
    // Insert with scaling
    if err := f.AddPicture("Sheet1", "D2", "image.jpg",
        &excelize.GraphicOptions{ScaleX: 0.5, ScaleY: 0.5}); err != nil {
        fmt.Println(err)
    }
    // Insert with advanced properties
    enable, disable := true, false
    if err := f.AddPicture("Sheet1", "H2", "image.gif",
        &excelize.GraphicOptions{
            PrintObject:     &enable,
            LockAspectRatio: false,
            OffsetX:         15,
            OffsetY:         10,
            Locked:          &disable,
        }); err != nil {
            fmt.Println(err)
    }
    if err = f.Save(); err != nil {
        fmt.Println(err)
    }
}