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
102
103
104
105
106
// Copyright 2017 FoxyUtils ehf. All rights reserved.
package main

// This example demonstrates flattening all formulas from an input Excel file and outputs the flattened values to a new xlsx.

import (
	"fmt"
	"log"
	"os"
	"runtime"
	"time"

	"github.com/unidoc/unioffice/common/license"
	"github.com/unidoc/unioffice/spreadsheet"
	"github.com/unidoc/unioffice/spreadsheet/formula"
)

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, err := spreadsheet.Open("formulas.xlsx")
	if err != nil {
		log.Fatalf("error opening document: %s", err)
	}
	defer ss.Close()

	sheets := ss.Sheets()

	start := time.Now().UnixNano()
	formEv := formula.NewEvaluator()
	for _, sheet := range sheets {
		fmt.Println("Sheet name:", sheet.Name())
		ctx := sheet.FormulaContext()
		for _, row := range sheet.Rows() {
			for _, cell := range row.Cells() {
				// copying cell style
				cellStyle := spreadsheet.CellStyle{}
				x := cell.X()
				if x.SAttr != nil {
					sid := *x.SAttr
					cellStyle = ss.StyleSheet.GetCellStyle(sid)
				}

				// copying value
				c := ctx.Cell(cell.Reference(), formEv)
				value := ""
				if cell.X().V != nil {
					value = *cell.X().V
				}
				cell.Clear()
				setValue(cell, c, value)

				// setting cell style
				if !cellStyle.IsEmpty() {
					cell.SetStyle(cellStyle)
				}
			}
		}
	}
	finish := time.Now().UnixNano()
	fmt.Printf("total time: %d ns\n", finish-start)
	PrintMemUsage()

	ss.SaveToFile("values.xlsx")
}

func setValue(cell spreadsheet.Cell, c formula.Result, value string) {
	switch c.Type {
	case formula.ResultTypeNumber:
		if c.IsBoolean {
			cell.SetBool(value != "0")
		} else {
			cell.SetNumber(c.ValueNumber)
		}
	case formula.ResultTypeString:
		cell.SetString(c.ValueString)
	case formula.ResultTypeList:
		setValue(cell, c.ValueList[0], value)
	case formula.ResultTypeArray:
		setValue(cell, c.ValueArray[0][0], value)
	case formula.ResultTypeError:
		cell.SetError(c.ValueString)
	}
}

func PrintMemUsage() {
	var m runtime.MemStats
	runtime.ReadMemStats(&m)
	// For info on each, see: https://golang.org/pkg/runtime/#MemStats
	fmt.Println("Memory usage:")
	fmt.Printf("Alloc = %v MiB", bToMb(m.Alloc))
	fmt.Printf("\tTotalAlloc = %v MiB", bToMb(m.TotalAlloc))
	fmt.Printf("\tSys = %v MiB", bToMb(m.Sys))
	fmt.Printf("\tNumGC = %v\n", m.NumGC)
}

func bToMb(b uint64) uint64 {
	return b / 1024 / 1024
}