Flatten Formula

This guide will explain the process of flattening all formulas from an input Excel file and outputting the flattened values to a new xlsx 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/flatten folder in the unioffice-examples directory.

cd unioffice-examples/spreadsheet/flatten/

How it works #

The import section in lines 6-16 imports the necessary libraries. The init function defined in lines 18-24, initializes the package by setting the metered license API key.

The main function is defined in lines 27-72. The input workbook file is loaded in line 28 using spreadsheet.Open("formulas.xlsx"). Then the list of spreadsheet.Sheet is obtained from the workbook object in line 34. The nested for loops in lines 38-66 iterates through each cell of each row of each sheet and runs the flattening process.

The formula context is obtained line 40. Lines 44-48 copy the cell style, lines 52-58 copy the cell data and then line 63 set the cell style. Lines 67-69 measures the time it took for flattening process and prints the message to the terminal. Line 71 saves the new workbook to a new file.

The setValue function defined in lines 74-91 gets the result from a formula and sets it to the new cell. The PrintMemUsage function defined in lines 93-102, helps print the memory usage information. The bToMb function in lines 104-106 is used to convert from byte to megabyte.

Run the code #

To create the spreadsheet run the following command.

go run main.go