Format Numbers and Date Time

This guide will demonstrate how to format numbers and date time in 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/number-date-time-formats folder in the unioffice-examples directory.

cd unioffice-examples/spreadsheet/number-date-time-formats/

How it works #

// Copyright 2017 FoxyUtils ehf. All rights reserved.
package main
 
import (
	"log"
	"os"
	"time"
 
	"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()
	sheet := ss.AddSheet()
 
	row := sheet.AddRow()
	cell := row.AddCell()
 
	// If no formatting/styles are applied, then the 'general' format is used.
	cell.SetNumber(1.234)
 
	// You can also apply a format at the same time you are setting a number.
	cell = row.AddCell()
	cell.SetNumberWithStyle(0.95, spreadsheet.StandardFormatPercent)
 
	// But that involves a few lookups, so if you're creating many, many cells
	// it wil be faster to
	cell = row.AddCell()
	// create the style
	dateStyle := ss.StyleSheet.AddCellStyle()
	// set its format
	dateStyle.SetNumberFormatStandard(spreadsheet.StandardFormatDate)
	// and apply it to a cell
	cell.SetDate(time.Now())
	cell.SetStyle(dateStyle)
 
	// It's even faster if repeatedly applying a style to apply the style index
	// directly. This is probably not worth the hassle most of the time, and
	// will generate the same content as calling setXWithStyle
	cs := ss.StyleSheet.AddCellStyle()
	cs.SetNumberFormatStandard(spreadsheet.StandardFormatTime)
	idx := cs.Index()
	for i := 0; i < 5; i++ {
		cell = row.AddCell()
		cell.SetDate(time.Now())
		cell.SetStyleIndex(idx)
	}
 
	// completely custom number formats can also be used
	customStyle := ss.StyleSheet.AddCellStyle()
	customStyle.SetNumberFormat("$#,##0.00")
	cell = row.AddCell()
	cell.SetNumber(1.234)
	cell.SetStyle(customStyle)
 
	if err := ss.Validate(); err != nil {
		log.Fatalf("error validating sheet: %s", err)
	}
 
	ss.SaveToFile("number-date-time-formats.xlsx")
}

The import section in lines 13-20 imports the necessary libraries from UniOffice and the Go standard libraries.
The init function in lines 13-20 is used to set the metered license key.

The main function is defined in lines 22-72. First a new workbook is created 23-25. In lines 27-31, a cell is set with a number without formatting. Lines 35-36 creates a cell with numeric value and a formatting style is set using:

cell = row.AddCell()
cell.SetNumberWithStyle(0.95, spreadsheet.StandardFormatPercent)

Here the StandardFormatPercent from spreadsheet is used. In line 41 a new CellStyle is created. The date format of the cell style is then set in line 43 using dateStyle.SetNumberFormatStandard(spreadsheet.StandardFormatDate). Then lines 45 and 46 a new date cell with the defined cell style is created using:

cell.SetDate(time.Now())
cell.SetStyle(dateStyle)

Lines 51-58 demonstrate the process of using a style index for faster implementation. This is done using the following code snippet.

cs := ss.StyleSheet.AddCellStyle()
cs.SetNumberFormatStandard(spreadsheet.StandardFormatTime)
idx := cs.Index()
for i := 0; i < 5; i++ {
  cell = row.AddCell()
  cell.SetDate(time.Now())
  cell.SetStyleIndex(idx)
}

First a new cell style with StandardFormatTime as its date formatting style is created. Then the index of this style is obtained by calling the Index method of the CellStyle object. Then in the for loop the style is set using cell.SetStyleIndex(idx).

Lines 61-65, shows how to create a custom number formatting style.

Finally, the workbook is validated and saved to file in lines 67-71.

Run the code #

Run the code using the following command.

go run main.go

Sample Output #