From 283339534741d3f0ff01c2ed2adc7c87445edf07 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 21 Feb 2021 00:21:45 +0800 Subject: This closes #787, avoid duplicate rich text string items, new formula fn: BIN2DEC, BIN2HEX, BIN2OCT, HEX2BIN, HEX2DEC, HEX2OCT, OCT2BIN, OCT2DEC, OCT2HEX --- calc.go | 279 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++- calc_test.go | 111 ++++++++++++++++++++++++ cell.go | 14 ++- 3 files changed, 398 insertions(+), 6 deletions(-) diff --git a/calc.go b/calc.go index f49477f..629aacf 100644 --- a/calc.go +++ b/calc.go @@ -222,6 +222,9 @@ var tokenPriority = map[string]int{ // AVERAGE // AVERAGEA // BASE +// BIN2DEC +// BIN2HEX +// BIN2OCT // BITAND // BITLSHIFT // BITOR @@ -266,6 +269,9 @@ var tokenPriority = map[string]int{ // GAMMA // GAMMALN // GCD +// HEX2BIN +// HEX2DEC +// HEX2OCT // HLOOKUP // IF // IFERROR @@ -300,6 +306,9 @@ var tokenPriority = map[string]int{ // MUNIT // NA // NOT +// OCT2BIN +// OCT2DEC +// OCT2HEX // ODD // OR // PERMUT @@ -1151,6 +1160,99 @@ func formulaCriteriaEval(val string, criteria *formulaCriteria) (result bool, er // Engineering Functions +// BIN2DEC function converts a Binary (a base-2 number) into a decimal number. +// The syntax of the function is: +// +// BIN2DEC(number) +// +func (fn *formulaFuncs) BIN2DEC(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "BIN2DEC requires 1 numeric argument") + } + token := argsList.Front().Value.(formulaArg) + number := token.ToNumber() + if number.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, number.Error) + } + return fn.bin2dec(token.Value()) +} + +// BIN2HEX function converts a Binary (Base 2) number into a Hexadecimal +// (Base 16) number. The syntax of the function is: +// +// BIN2HEX(number,[places]) +// +func (fn *formulaFuncs) BIN2HEX(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "BIN2HEX requires at least 1 argument") + } + if argsList.Len() > 2 { + return newErrorFormulaArg(formulaErrorVALUE, "BIN2HEX allows at most 2 arguments") + } + token := argsList.Front().Value.(formulaArg) + number := token.ToNumber() + if number.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, number.Error) + } + decimal, newList := fn.bin2dec(token.Value()), list.New() + if decimal.Type != ArgNumber { + return decimal + } + newList.PushBack(decimal) + if argsList.Len() == 2 { + newList.PushBack(argsList.Back().Value.(formulaArg)) + } + return fn.dec2x("BIN2HEX", newList) +} + +// BIN2OCT function converts a Binary (Base 2) number into an Octal (Base 8) +// number. The syntax of the function is: +// +// BIN2OCT(number,[places]) +// +func (fn *formulaFuncs) BIN2OCT(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "BIN2OCT requires at least 1 argument") + } + if argsList.Len() > 2 { + return newErrorFormulaArg(formulaErrorVALUE, "BIN2OCT allows at most 2 arguments") + } + token := argsList.Front().Value.(formulaArg) + number := token.ToNumber() + if number.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, number.Error) + } + decimal, newList := fn.bin2dec(token.Value()), list.New() + if decimal.Type != ArgNumber { + return decimal + } + newList.PushBack(decimal) + if argsList.Len() == 2 { + newList.PushBack(argsList.Back().Value.(formulaArg)) + } + return fn.dec2x("BIN2OCT", newList) +} + +// bin2dec is an implementation of the formula function BIN2DEC. +func (fn *formulaFuncs) bin2dec(number string) formulaArg { + decimal, length := 0.0, len(number) + for i := length; i > 0; i-- { + s := string(number[length-i]) + if 10 == i && s == "1" { + decimal += math.Pow(-2.0, float64(i-1)) + continue + } + if s == "1" { + decimal += math.Pow(2.0, float64(i-1)) + continue + } + if s != "0" { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + } + return newNumberFormulaArg(decimal) +} + // BITAND function returns the bitwise 'AND' for two supplied integers. The // syntax of the function is: // @@ -1263,21 +1365,38 @@ func (fn *formulaFuncs) dec2x(name string, argsList *list.List) formulaArg { } maxLimitMap := map[string]float64{ "DEC2BIN": 511, + "HEX2BIN": 511, + "OCT2BIN": 511, + "BIN2HEX": 549755813887, "DEC2HEX": 549755813887, + "OCT2HEX": 549755813887, + "BIN2OCT": 536870911, "DEC2OCT": 536870911, + "HEX2OCT": 536870911, } minLimitMap := map[string]float64{ "DEC2BIN": -512, + "HEX2BIN": -512, + "OCT2BIN": -512, + "BIN2HEX": -549755813888, "DEC2HEX": -549755813888, + "OCT2HEX": -549755813888, + "BIN2OCT": -536870912, "DEC2OCT": -536870912, + "HEX2OCT": -536870912, } baseMap := map[string]int{ "DEC2BIN": 2, + "HEX2BIN": 2, + "OCT2BIN": 2, + "BIN2HEX": 16, "DEC2HEX": 16, + "OCT2HEX": 16, + "BIN2OCT": 8, "DEC2OCT": 8, + "HEX2OCT": 8, } - maxLimit := maxLimitMap[name] - minLimit := minLimitMap[name] + maxLimit, minLimit := maxLimitMap[name], minLimitMap[name] base := baseMap[name] if decimal.Number < minLimit || decimal.Number > maxLimit { return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) @@ -1301,6 +1420,162 @@ func (fn *formulaFuncs) dec2x(name string, argsList *list.List) formulaArg { return newStringFormulaArg(strings.ToUpper(binary)) } +// HEX2BIN function converts a Hexadecimal (Base 16) number into a Binary +// (Base 2) number. The syntax of the function is: +// +// HEX2BIN(number,[places]) +// +func (fn *formulaFuncs) HEX2BIN(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "HEX2BIN requires at least 1 argument") + } + if argsList.Len() > 2 { + return newErrorFormulaArg(formulaErrorVALUE, "HEX2BIN allows at most 2 arguments") + } + decimal, newList := fn.hex2dec(argsList.Front().Value.(formulaArg).Value()), list.New() + if decimal.Type != ArgNumber { + return decimal + } + newList.PushBack(decimal) + if argsList.Len() == 2 { + newList.PushBack(argsList.Back().Value.(formulaArg)) + } + return fn.dec2x("HEX2BIN", newList) +} + +// HEX2DEC function converts a hexadecimal (a base-16 number) into a decimal +// number. The syntax of the function is: +// +// HEX2DEC(number) +// +func (fn *formulaFuncs) HEX2DEC(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "HEX2DEC requires 1 numeric argument") + } + return fn.hex2dec(argsList.Front().Value.(formulaArg).Value()) +} + +// HEX2OCT function converts a Hexadecimal (Base 16) number into an Octal +// (Base 8) number. The syntax of the function is: +// +// HEX2OCT(number,[places]) +// +func (fn *formulaFuncs) HEX2OCT(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "HEX2OCT requires at least 1 argument") + } + if argsList.Len() > 2 { + return newErrorFormulaArg(formulaErrorVALUE, "HEX2OCT allows at most 2 arguments") + } + decimal, newList := fn.hex2dec(argsList.Front().Value.(formulaArg).Value()), list.New() + if decimal.Type != ArgNumber { + return decimal + } + newList.PushBack(decimal) + if argsList.Len() == 2 { + newList.PushBack(argsList.Back().Value.(formulaArg)) + } + return fn.dec2x("HEX2OCT", newList) +} + +// hex2dec is an implementation of the formula function HEX2DEC. +func (fn *formulaFuncs) hex2dec(number string) formulaArg { + decimal, length := 0.0, len(number) + for i := length; i > 0; i-- { + num, err := strconv.ParseInt(string(number[length-i]), 16, 64) + if err != nil { + return newErrorFormulaArg(formulaErrorNUM, err.Error()) + } + if 10 == i && string(number[length-i]) == "F" { + decimal += math.Pow(-16.0, float64(i-1)) + continue + } + decimal += float64(num) * math.Pow(16.0, float64(i-1)) + } + return newNumberFormulaArg(decimal) +} + +// OCT2BIN function converts an Octal (Base 8) number into a Binary (Base 2) +// number. The syntax of the function is: +// +// OCT2BIN(number,[places]) +// +func (fn *formulaFuncs) OCT2BIN(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "OCT2BIN requires at least 1 argument") + } + if argsList.Len() > 2 { + return newErrorFormulaArg(formulaErrorVALUE, "OCT2BIN allows at most 2 arguments") + } + token := argsList.Front().Value.(formulaArg) + number := token.ToNumber() + if number.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, number.Error) + } + decimal, newList := fn.oct2dec(token.Value()), list.New() + newList.PushBack(decimal) + if argsList.Len() == 2 { + newList.PushBack(argsList.Back().Value.(formulaArg)) + } + return fn.dec2x("OCT2BIN", newList) +} + +// OCT2DEC function converts an Octal (a base-8 number) into a decimal number. +// The syntax of the function is: +// +// OCT2DEC(number) +// +func (fn *formulaFuncs) OCT2DEC(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "OCT2DEC requires 1 numeric argument") + } + token := argsList.Front().Value.(formulaArg) + number := token.ToNumber() + if number.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, number.Error) + } + return fn.oct2dec(token.Value()) +} + +// OCT2HEX function converts an Octal (Base 8) number into a Hexadecimal +// (Base 16) number. The syntax of the function is: +// +// OCT2HEX(number,[places]) +// +func (fn *formulaFuncs) OCT2HEX(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "OCT2HEX requires at least 1 argument") + } + if argsList.Len() > 2 { + return newErrorFormulaArg(formulaErrorVALUE, "OCT2HEX allows at most 2 arguments") + } + token := argsList.Front().Value.(formulaArg) + number := token.ToNumber() + if number.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, number.Error) + } + decimal, newList := fn.oct2dec(token.Value()), list.New() + newList.PushBack(decimal) + if argsList.Len() == 2 { + newList.PushBack(argsList.Back().Value.(formulaArg)) + } + return fn.dec2x("OCT2HEX", newList) +} + +// oct2dec is an implementation of the formula function OCT2DEC. +func (fn *formulaFuncs) oct2dec(number string) formulaArg { + decimal, length := 0.0, len(number) + for i := length; i > 0; i-- { + num, _ := strconv.Atoi(string(number[length-i])) + if 10 == i && string(number[length-i]) == "7" { + decimal += math.Pow(-8.0, float64(i-1)) + continue + } + decimal += float64(num) * math.Pow(8.0, float64(i-1)) + } + return newNumberFormulaArg(decimal) +} + // Math and Trigonometric Functions // ABS function returns the absolute value of any supplied number. The syntax diff --git a/calc_test.go b/calc_test.go index 04dce37..49af523 100644 --- a/calc_test.go +++ b/calc_test.go @@ -47,6 +47,24 @@ func TestCalcCellValue(t *testing.T) { "=2>=3": "FALSE", "=1&2": "12", // Engineering Functions + // BIN2DEC + "=BIN2DEC(\"10\")": "2", + "=BIN2DEC(\"11\")": "3", + "=BIN2DEC(\"0000000010\")": "2", + "=BIN2DEC(\"1111111110\")": "-2", + "=BIN2DEC(\"110\")": "6", + // BIN2HEX + "=BIN2HEX(\"10\")": "2", + "=BIN2HEX(\"0000000001\")": "1", + "=BIN2HEX(\"10\",10)": "0000000002", + "=BIN2HEX(\"1111111110\")": "FFFFFFFFFE", + "=BIN2HEX(\"11101\")": "1D", + // BIN2OCT + "=BIN2OCT(\"101\")": "5", + "=BIN2OCT(\"0000000001\")": "1", + "=BIN2OCT(\"10\",10)": "0000000002", + "=BIN2OCT(\"1111111110\")": "7777777776", + "=BIN2OCT(\"1110\")": "16", // BITAND "=BITAND(13,14)": "12", // BITLSHIFT @@ -78,6 +96,44 @@ func TestCalcCellValue(t *testing.T) { "=DEC2OCT(8,10)": "0000000010", "=DEC2OCT(-8)": "7777777770", "=DEC2OCT(237)": "355", + // HEX2BIN + "=HEX2BIN(\"2\")": "10", + "=HEX2BIN(\"0000000001\")": "1", + "=HEX2BIN(\"2\",10)": "0000000010", + "=HEX2BIN(\"F0\")": "11110000", + "=HEX2BIN(\"1D\")": "11101", + // HEX2DEC + "=HEX2DEC(\"A\")": "10", + "=HEX2DEC(\"1F\")": "31", + "=HEX2DEC(\"0000000010\")": "16", + "=HEX2DEC(\"FFFFFFFFF0\")": "-16", + "=HEX2DEC(\"111\")": "273", + "=HEX2DEC(\"\")": "0", + // HEX2OCT + "=HEX2OCT(\"A\")": "12", + "=HEX2OCT(\"000000000F\")": "17", + "=HEX2OCT(\"8\",10)": "0000000010", + "=HEX2OCT(\"FFFFFFFFF8\")": "7777777770", + "=HEX2OCT(\"1F3\")": "763", + // OCT2BIN + "=OCT2BIN(\"5\")": "101", + "=OCT2BIN(\"0000000001\")": "1", + "=OCT2BIN(\"2\",10)": "0000000010", + "=OCT2BIN(\"7777777770\")": "1111111000", + "=OCT2BIN(\"16\")": "1110", + // OCT2DEC + "=OCT2DEC(\"10\")": "8", + "=OCT2DEC(\"22\")": "18", + "=OCT2DEC(\"0000000010\")": "8", + "=OCT2DEC(\"7777777770\")": "-8", + "=OCT2DEC(\"355\")": "237", + // OCT2HEX + "=OCT2HEX(\"10\")": "8", + "=OCT2HEX(\"0000000007\")": "7", + "=OCT2HEX(\"10\",10)": "0000000008", + "=OCT2HEX(\"7777777770\")": "FFFFFFFFF8", + "=OCT2HEX(\"763\")": "1F3", + // Math and Trigonometric Functions // ABS "=ABS(-1)": "1", "=ABS(-6.5)": "6.5", @@ -740,6 +796,25 @@ func TestCalcCellValue(t *testing.T) { mathCalcError := map[string]string{ "=1/0": "#DIV/0!", // Engineering Functions + // BIN2DEC + "=BIN2DEC()": "BIN2DEC requires 1 numeric argument", + "=BIN2DEC(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // BIN2HEX + "=BIN2HEX()": "BIN2HEX requires at least 1 argument", + "=BIN2HEX(1,1,1)": "BIN2HEX allows at most 2 arguments", + "=BIN2HEX(\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BIN2HEX(1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BIN2HEX(12345678901,10)": "#NUM!", + "=BIN2HEX(1,-1)": "#NUM!", + "=BIN2HEX(31,1)": "#NUM!", + // BIN2OCT + "=BIN2OCT()": "BIN2OCT requires at least 1 argument", + "=BIN2OCT(1,1,1)": "BIN2OCT allows at most 2 arguments", + "=BIN2OCT(\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BIN2OCT(1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BIN2OCT(-12345678901 ,10)": "#NUM!", + "=BIN2OCT(1,-1)": "#NUM!", + "=BIN2OCT(8,1)": "#NUM!", // BITAND "=BITAND()": "BITAND requires 2 numeric arguments", "=BITAND(-1,2)": "#NUM!", @@ -804,6 +879,42 @@ func TestCalcCellValue(t *testing.T) { "=DEC2OCT(-536870912 ,10)": "#NUM!", "=DEC2OCT(1,-1)": "#NUM!", "=DEC2OCT(8,1)": "#NUM!", + // HEX2BIN + "=HEX2BIN()": "HEX2BIN requires at least 1 argument", + "=HEX2BIN(1,1,1)": "HEX2BIN allows at most 2 arguments", + "=HEX2BIN(\"X\",1)": "strconv.ParseInt: parsing \"X\": invalid syntax", + "=HEX2BIN(1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=HEX2BIN(-513,10)": "strconv.ParseInt: parsing \"-\": invalid syntax", + "=HEX2BIN(1,-1)": "#NUM!", + "=HEX2BIN(2,1)": "#NUM!", + // HEX2DEC + "=HEX2DEC()": "HEX2DEC requires 1 numeric argument", + "=HEX2DEC(\"X\")": "strconv.ParseInt: parsing \"X\": invalid syntax", + // HEX2OCT + "=HEX2OCT()": "HEX2OCT requires at least 1 argument", + "=HEX2OCT(1,1,1)": "HEX2OCT allows at most 2 arguments", + "=HEX2OCT(\"X\",1)": "strconv.ParseInt: parsing \"X\": invalid syntax", + "=HEX2OCT(1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=HEX2OCT(-513,10)": "strconv.ParseInt: parsing \"-\": invalid syntax", + "=HEX2OCT(1,-1)": "#NUM!", + // OCT2BIN + "=OCT2BIN()": "OCT2BIN requires at least 1 argument", + "=OCT2BIN(1,1,1)": "OCT2BIN allows at most 2 arguments", + "=OCT2BIN(\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=OCT2BIN(1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=OCT2BIN(-536870912 ,10)": "#NUM!", + "=OCT2BIN(1,-1)": "#NUM!", + // OCT2DEC + "=OCT2DEC()": "OCT2DEC requires 1 numeric argument", + "=OCT2DEC(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // OCT2HEX + "=OCT2HEX()": "OCT2HEX requires at least 1 argument", + "=OCT2HEX(1,1,1)": "OCT2HEX allows at most 2 arguments", + "=OCT2HEX(\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=OCT2HEX(1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=OCT2HEX(-536870912 ,10)": "#NUM!", + "=OCT2HEX(1,-1)": "#NUM!", + // Math and Trigonometric Functions // ABS "=ABS()": "ABS requires 1 numeric argument", `=ABS("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", diff --git a/cell.go b/cell.go index 4617b76..ae468e9 100644 --- a/cell.go +++ b/cell.go @@ -33,9 +33,9 @@ const ( ) // GetCellValue provides a function to get formatted value from cell by given -// worksheet name and axis in XLSX file. If it is possible to apply a format -// to the cell value, it will do so, if not then an error will be returned, -// along with the raw value of the cell. +// worksheet name and axis in spreadsheet file. If it is possible to apply a +// format to the cell value, it will do so, if not then an error will be +// returned, along with the raw value of the cell. func (f *File) GetCellValue(sheet, axis string) (string, error) { return f.getCellStringFunc(sheet, axis, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) { val, err := c.getValueFrom(f, f.sharedStringsReader()) @@ -43,7 +43,7 @@ func (f *File) GetCellValue(sheet, axis string) (string, error) { }) } -// SetCellValue provides a function to set value of a cell. The specified +// SetCellValue provides a function to set the value of a cell. The specified // coordinates should not be in the first row of the table, a complex number // can be set with string text. The following shows the supported data // types: @@ -645,6 +645,12 @@ func (f *File) SetCellRichText(sheet, cell string, runs []RichTextRun) error { textRuns = append(textRuns, run) } si.R = textRuns + for idx, strItem := range sst.SI { + if reflect.DeepEqual(strItem, si) { + cellData.T, cellData.V = "s", strconv.Itoa(idx) + return err + } + } sst.SI = append(sst.SI, si) sst.Count++ sst.UniqueCount++ -- cgit v1.2.1