Set Formula

This guide will show how to set and execute spreadsheet formulas 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/formula folder in the unioffice-examples directory.

cd unioffice-examples/spreadsheet/formula/

How it works #

The code starts by importing the necessary packages from UniOffice and the Go library. Then package is initialized by setting the metered license key in the init function.

It creates a new spreadsheet.Workbook and adds a new spreadsheet.Sheet to the workbook in lines 25-27, i.e. inside the main function. A new cell style that sets a horizontal center alignment is created in lines 29-30. After that, the fill properties of the cell style is set in lines 32-35 using:

lightGray := ss.StyleSheet.Fills().AddFill()
lightGrayPattern := lightGray.SetPatternFill()
lightGrayPattern.SetFgColor(color.LightGray)
hdrStyle.SetFill(lightGray)

In lines 37-44, two row cells are created using AddRow and AddCell methods of the spreadsheet.Sheet and spreadsheet.Row objects respectively. The for loop in line 46-53 populates the rest of the rows.

The code in lines 55-57 creates one more row to hold the cell for total value that will be calculated using a formula. Then in line 58 the cell’s formula is set using:

totalCell.SetFormulaRaw("SUM(B2:B11)")

The formula of the cell is evaluated using ss.RecalculateFormulas() in line 60. Finally, the spreadsheet is validated and written to file in lines 61-61.

Run the code #

To generate the spreadsheet file run the following command.

go run main.go

Sample Output #

Here is a preview first page the converted PDF.