Sort and Filter

This guide will show how to add a sorting and filtering to spreadsheet using UniOffice.

Before you begin #

You should get your API key from your UniCloud account.

If this is your first time using UniOffice SDK, follow this guide to set up a local development environment.

Clone the project repository #

In your terminal, clone the examples repository. It contains the Go code we will be using for this guide.

git clone https://github.com/unidoc/unioffice-examples

To get the example navigate to the path spreadsheet/sort-filter folder in the unioffice-examples directory.

cd unioffice-examples/spreadsheet/sort-filter/

How it works #

// Copyright 2017 FoxyUtils ehf. All rights reserved.
package main
 
import (
	"fmt"
	"log"
	"os"
 
	"github.com/unidoc/unioffice/v2/common/license"
	"github.com/unidoc/unioffice/v2/spreadsheet"
)
 
func init() {
	// Make sure to load your metered License API key prior to using the library.
	// If you need a key, you can sign up and create a free one at https://cloud.unidoc.io
	err := license.SetMeteredKey(os.Getenv(`UNIDOC_LICENSE_API_KEY`))
	if err != nil {
		panic(err)
	}
}
 
func main() {
	ss := spreadsheet.New()
	defer ss.Close()
	// add a single sheet
	sheet := ss.AddSheet()
	hdrRow := sheet.AddRow()
	hdrRow.AddCell().SetString("Product Name")
	hdrRow.AddCell().SetString("Quantity")
	hdrRow.AddCell().SetString("Price")
	sheet.SetAutoFilter("A1:C6")
 
	// rows
	for r := 0; r < 5; r++ {
		row := sheet.AddRow()
		row.AddCell().SetString(fmt.Sprintf("Product %d", r+1))
		row.AddCell().SetNumber(float64(r + 2))
		row.AddCell().SetNumber(float64(3*r + 1))
 
	}
 
	// sort column C, starting a row 2 to skip the header row
	sheet.Sort("C", 2, spreadsheet.SortOrderDescending)
 
	if err := ss.Validate(); err != nil {
		log.Fatalf("error validating sheet: %s", err)
	}
 
	ss.SaveToFile("sort-filter.xlsx")
}

The import section in lines 4-11 imports the necessary packages from UniOffice and the Go standard library.
Then init function following the import section is used to initialize the package by setting the metered license key from the system’s environment using license.SetMeteredKey(os.Getenv(`UNIDOC_LICENSE_API_KEY`)).

The main function defined in lines 22-50 contains the code that creates a workbook with a sorting filter added. First it creates a new workbook with an empty sheet in lines 23-26. Then the header row is created following that. This done the following snippet.

hdrRow := sheet.AddRow()
hdrRow.AddCell().SetString("Product Name")
hdrRow.AddCell().SetString("Quantity")
hdrRow.AddCell().SetString("Price")
sheet.SetAutoFilter("A1:C6")

The for loop in lines 34-40 adds the rest of the rows populated with data. Then the rows are sorted based on column C using:

sheet.Sort("C", 2, spreadsheet.SortOrderDescending)

The arguments passed to the Sort method are the column name, the first row and the ordering type in order. Finally, the workbook is validated and saved to file in lines 45-49.

Run the code #

Use the following command to run the code.

go run main.go

Sample Output #