main.go hosted by Oembed Proxy for GitHub
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
// Copyright 2017 FoxyUtils ehf. All rights reserved.
package main

import (
	"log"
	"math/rand"
	"os"

	"github.com/unidoc/unioffice/color"
	"github.com/unidoc/unioffice/common/license"
	"github.com/unidoc/unioffice/schema/soo/sml"
	"github.com/unidoc/unioffice/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()

	for r := 0; r < 20; r++ {
		if r != 0 && r%5 == 0 {
			sheet.AddRow()
		}
		row := sheet.AddRow()
		for c := 0; c < 5; c++ {
			cell := row.AddCell()
			cell.SetNumber(float64(rand.Intn(1000)) / 100.0)
		}
	}
	{
		cfmt := sheet.AddConditionalFormatting([]string{"A1:E5"})
		r := cfmt.AddRule()
		// cell is
		r.SetType(sml.ST_CfTypeCellIs)
		// greater than
		r.SetOperator(sml.ST_ConditionalFormattingOperatorLessThan)
		// four
		r.SetConditionValue("4")
		// should be formatted with this style
		green := ss.StyleSheet.AddDifferentialStyle()
		green.Fill().SetPatternFill().SetBgColor(color.SuccessGreen)
		r.SetStyle(green)

		r = cfmt.AddRule()
		// cell is
		r.SetType(sml.ST_CfTypeCellIs)
		// greater than
		r.SetOperator(sml.ST_ConditionalFormattingOperatorGreaterThan)
		// four
		r.SetConditionValue("7")
		// should be formatted with this style
		red := ss.StyleSheet.AddDifferentialStyle()
		red.Fill().SetPatternFill().SetBgColor(color.Red)
		r.SetStyle(red)
	}
	{
		// Color gradient by value
		cfmt := sheet.AddConditionalFormatting([]string{"A7:E11"})
		r := cfmt.AddRule()
		cs := r.SetColorScale()
		cs.AddFormatValue(sml.ST_CfvoTypeMin, "0")
		cs.AddGradientStop(color.Red)
		cs.AddFormatValue(sml.ST_CfvoTypePercentile, "50")
		cs.AddGradientStop(color.Yellow)
		cs.AddFormatValue(sml.ST_CfvoTypeMax, "0")
		cs.AddGradientStop(color.SuccessGreen)
	}
	{
		// Icons
		cfmt := sheet.AddConditionalFormatting([]string{"A13:E17"})
		r := cfmt.AddRule()
		icons := r.SetIcons()
		icons.SetIcons(sml.ST_IconSetType3TrafficLights1)
		icons.AddFormatValue(sml.ST_CfvoTypePercent, "0")
		icons.AddFormatValue(sml.ST_CfvoTypePercent, "040")
		icons.AddFormatValue(sml.ST_CfvoTypePercent, "90")
	}
	{
		cfmt := sheet.AddConditionalFormatting([]string{"A19:E23"})
		r := cfmt.AddRule()
		db := r.SetDataBar()
		db.AddFormatValue(sml.ST_CfvoTypeMin, "0")
		db.AddFormatValue(sml.ST_CfvoTypeMax, "0")
		db.SetColor(color.Blue)
	}

	if err := ss.Validate(); err != nil {
		log.Fatalf("error validating sheet: %s", err)
	}

	ss.SaveToFile("conditional-formatting.xlsx")
}