From 576bfffbe6add78e719fc4fab851f40f5779a4d3 Mon Sep 17 00:00:00 2001
From: xuri <xuri.me@gmail.com>
Date: Tue, 22 Dec 2020 08:47:46 +0800
Subject: This closes #752, fix incorrectly merged cells on duplicate row, and
new formula function: LOWER, PROPER, UPPER
---
README.md | 43 ++++++++++--
README_zh.md | 43 ++++++++++--
calc.go | 211 ++++++++++++++++++++++++++++++++++++++++-------------------
calc_test.go | 24 +++++++
rows.go | 1 -
rows_test.go | 26 ++++----
6 files changed, 254 insertions(+), 94 deletions(-)
diff --git a/README.md b/README.md
index 4dbf532..7c67092 100644
--- a/README.md
+++ b/README.md
@@ -97,7 +97,7 @@ func main() {
### Add chart to spreadsheet file
-With Excelize chart generation and management is as easy as a few lines of code. You can build charts based off data in your worksheet or generate charts without any data in your worksheet at all.
+With Excelize chart generation and management is as easy as a few lines of code. You can build charts based on data in your worksheet or generate charts without any data in your worksheet at all.
<p align="center"><img width="650" src="./test/images/chart.png" alt="Excelize"></p>
@@ -111,8 +111,10 @@ import (
)
func main() {
- categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
- values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
+ categories := map[string]string{
+ "A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
+ values := map[string]int{
+ "B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
f := excelize.NewFile()
for k, v := range categories {
f.SetCellValue("Sheet1", k, v)
@@ -120,7 +122,29 @@ func main() {
for k, v := range values {
f.SetCellValue("Sheet1", k, v)
}
- if err := f.AddChart("Sheet1", "E1", `{"type":"col3DClustered","series":[{"name":"Sheet1!$A$2","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"},{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"title":{"name":"Fruit 3D Clustered Column Chart"}}`); err != nil {
+ if err := f.AddChart("Sheet1", "E1", `{
+ "type": "col3DClustered",
+ "series": [
+ {
+ "name": "Sheet1!$A$2",
+ "categories": "Sheet1!$B$1:$D$1",
+ "values": "Sheet1!$B$2:$D$2"
+ },
+ {
+ "name": "Sheet1!$A$3",
+ "categories": "Sheet1!$B$1:$D$1",
+ "values": "Sheet1!$B$3:$D$3"
+ },
+ {
+ "name": "Sheet1!$A$4",
+ "categories": "Sheet1!$B$1:$D$1",
+ "values": "Sheet1!$B$4:$D$4"
+ }],
+ "title":
+ {
+ "name": "Fruit 3D Clustered Column Chart"
+ }
+ }`); err != nil {
fmt.Println(err)
return
}
@@ -156,11 +180,18 @@ func main() {
fmt.Println(err)
}
// Insert a picture to worksheet with scaling.
- if err := f.AddPicture("Sheet1", "D2", "image.jpg", `{"x_scale": 0.5, "y_scale": 0.5}`); err != nil {
+ if err := f.AddPicture("Sheet1", "D2", "image.jpg",
+ `{"x_scale": 0.5, "y_scale": 0.5}`); err != nil {
fmt.Println(err)
}
// Insert a picture offset in the cell with printing support.
- if err := f.AddPicture("Sheet1", "H2", "image.gif", `{"x_offset": 15, "y_offset": 10, "print_obj": true, "lock_aspect_ratio": false, "locked": false}`); err != nil {
+ if err := f.AddPicture("Sheet1", "H2", "image.gif", `{
+ "x_offset": 15,
+ "y_offset": 10,
+ "print_obj": true,
+ "lock_aspect_ratio": false,
+ "locked": false
+ }`); err != nil {
fmt.Println(err)
}
// Save the spreadsheet with the origin path.
diff --git a/README_zh.md b/README_zh.md
index 25b2fbf..daafd1d 100644
--- a/README_zh.md
+++ b/README_zh.md
@@ -99,7 +99,7 @@ func main() {
使用 Excelize 生成图表十分简单，仅需几行代码。您可以根据工作表中的已有数据构建图表，或向工作表中添加数据并创建图表。
-<p align="center"><img width="650" src="./test/images/chart.png" alt="Excelize"></p>
+<p align="center"><img width="650" src="./test/images/chart.png" alt="使用 Excelize 在 Excel 电子表格文档中创建图表"></p>
```go
package main
@@ -111,8 +111,10 @@ import (
)
func main() {
- categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
- values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
+ categories := map[string]string{
+ "A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
+ values := map[string]int{
+ "B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
f := excelize.NewFile()
for k, v := range categories {
f.SetCellValue("Sheet1", k, v)
@@ -120,7 +122,29 @@ func main() {
for k, v := range values {
f.SetCellValue("Sheet1", k, v)
}
- if err := f.AddChart("Sheet1", "E1", `{"type":"col3DClustered","series":[{"name":"Sheet1!$A$2","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"},{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"title":{"name":"Fruit 3D Clustered Column Chart"}}`); err != nil {
+ if err := f.AddChart("Sheet1", "E1", `{
+ "type": "col3DClustered",
+ "series": [
+ {
+ "name": "Sheet1!$A$2",
+ "categories": "Sheet1!$B$1:$D$1",
+ "values": "Sheet1!$B$2:$D$2"
+ },
+ {
+ "name": "Sheet1!$A$3",
+ "categories": "Sheet1!$B$1:$D$1",
+ "values": "Sheet1!$B$3:$D$3"
+ },
+ {
+ "name": "Sheet1!$A$4",
+ "categories": "Sheet1!$B$1:$D$1",
+ "values": "Sheet1!$B$4:$D$4"
+ }],
+ "title":
+ {
+ "name": "Fruit 3D Clustered Column Chart"
+ }
+ }`); err != nil {
fmt.Println(err)
return
}
@@ -156,11 +180,18 @@ func main() {
fmt.Println(err)
}
// 在工作表中插入图片，并设置图片的缩放比例
- if err := f.AddPicture("Sheet1", "D2", "image.jpg", `{"x_scale": 0.5, "y_scale": 0.5}`); err != nil {
+ if err := f.AddPicture("Sheet1", "D2", "image.jpg",
+ `{"x_scale": 0.5, "y_scale": 0.5}`); err != nil {
fmt.Println(err)
}
// 在工作表中插入图片，并设置图片的打印属性
- if err := f.AddPicture("Sheet1", "H2", "image.gif", `{"x_offset": 15, "y_offset": 10, "print_obj": true, "lock_aspect_ratio": false, "locked": false}`); err != nil {
+ if err := f.AddPicture("Sheet1", "H2", "image.gif", `{
+ "x_offset": 15,
+ "y_offset": 10,
+ "print_obj": true,
+ "lock_aspect_ratio": false,
+ "locked": false
+ }`); err != nil {
fmt.Println(err)
}
// 保存文件
diff --git a/calc.go b/calc.go
index 7da2493..d2bab1d 100644
--- a/calc.go
+++ b/calc.go
@@ -24,6 +24,7 @@ import (
"strconv"
"strings"
"time"
+ "unicode"
"github.com/xuri/efp"
)
@@ -123,14 +124,15 @@ var tokenPriority = map[string]int{
// Supported formulas:
//
// ABS, ACOS, ACOSH, ACOT, ACOTH, AND, ARABIC, ASIN, ASINH, ATAN2, ATANH,
-// BASE, CEILING, CEILING.MATH, CEILING.PRECISE, COMBIN, COMBINA, COS,
-// COSH, COT, COTH, COUNTA, CSC, CSCH, DATE, DECIMAL, DEGREES, EVEN, EXP,
-// FACT, FACTDOUBLE, FLOOR, FLOOR.MATH, FLOOR.PRECISE, GCD, INT, ISBLANK,
-// ISERR, ISERROR, ISEVEN, ISNA, ISNONTEXT, ISNUMBER, ISO.CEILING, ISODD,
-// LCM, LN, LOG, LOG10, MDETERM, MEDIAN, MOD, MROUND, MULTINOMIAL, MUNIT,
-// NA, ODD, OR, PI, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANDBETWEEN,
-// ROUND, ROUNDDOWN, ROUNDUP, SEC, SECH, SIGN, SIN, SINH, SQRT, SQRTPI,
-// SUM, SUMIF, SUMSQ, TAN, TANH, TRUNC
+// BASE, CEILING, CEILING.MATH, CEILING.PRECISE, CLEAN, COMBIN, COMBINA,
+// COS, COSH, COT, COTH, COUNTA, CSC, CSCH, DATE, DECIMAL, DEGREES, EVEN,
+// EXP, FACT, FACTDOUBLE, FLOOR, FLOOR.MATH, FLOOR.PRECISE, GCD, INT,
+// ISBLANK, ISERR, ISERROR, ISEVEN, ISNA, ISNONTEXT, ISNUMBER, ISO.CEILING,
+// ISODD, LCM, LN, LOG, LOG10, LOWER, MDETERM, MEDIAN, MOD, MROUND,
+// MULTINOMIAL, MUNIT, NA, ODD, OR, PI, POWER, PRODUCT, PROPER, QUOTIENT,
+// RADIANS, RAND, RANDBETWEEN, ROUND, ROUNDDOWN, ROUNDUP, SEC, SECH, SIGN,
+// SIN, SINH, SQRT, SQRTPI, SUM, SUMIF, SUMSQ, TAN, TANH, TRIM, TRUNC,
+// UPPER
//
func (f *File) CalcCellValue(sheet, cell string) (result string, err error) {
var (
@@ -869,7 +871,7 @@ func formulaCriteriaEval(val string, criteria *formulaCriteria) (result bool, er
// ABS function returns the absolute value of any supplied number. The syntax
// of the function is:
//
-// ABS(number)
+// ABS(number)
//
func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -889,7 +891,7 @@ func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) {
// number, and returns an angle, in radians, between 0 and π. The syntax of
// the function is:
//
-// ACOS(number)
+// ACOS(number)
//
func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -908,7 +910,7 @@ func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) {
// ACOSH function calculates the inverse hyperbolic cosine of a supplied number.
// of the function is:
//
-// ACOSH(number)
+// ACOSH(number)
//
func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -928,7 +930,7 @@ func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) {
// given number, and returns an angle, in radians, between 0 and π. The syntax
// of the function is:
//
-// ACOT(number)
+// ACOT(number)
//
func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -947,7 +949,7 @@ func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) {
// ACOTH function calculates the hyperbolic arccotangent (coth) of a supplied
// value. The syntax of the function is:
//
-// ACOTH(number)
+// ACOTH(number)
//
func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -966,7 +968,7 @@ func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) {
// ARABIC function converts a Roman numeral into an Arabic numeral. The syntax
// of the function is:
//
-// ARABIC(text)
+// ARABIC(text)
//
func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1004,7 +1006,7 @@ func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) {
// number, and returns an angle, in radians, between -π/2 and π/2. The syntax
// of the function is:
//
-// ASIN(number)
+// ASIN(number)
//
func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1023,7 +1025,7 @@ func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) {
// ASINH function calculates the inverse hyperbolic sine of a supplied number.
// The syntax of the function is:
//
-// ASINH(number)
+// ASINH(number)
//
func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1043,7 +1045,7 @@ func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) {
// given number, and returns an angle, in radians, between -π/2 and +π/2. The
// syntax of the function is:
//
-// ATAN(number)
+// ATAN(number)
//
func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1062,7 +1064,7 @@ func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) {
// ATANH function calculates the inverse hyperbolic tangent of a supplied
// number. The syntax of the function is:
//
-// ATANH(number)
+// ATANH(number)
//
func (fn *formulaFuncs) ATANH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1082,7 +1084,7 @@ func (fn *formulaFuncs) ATANH(argsList *list.List) (result string, err error) {
// given set of x and y coordinates, and returns an angle, in radians, between
// -π/2 and +π/2. The syntax of the function is:
//
-// ATAN2(x_num,y_num)
+// ATAN2(x_num,y_num)
//
func (fn *formulaFuncs) ATAN2(argsList *list.List) (result string, err error) {
if argsList.Len() != 2 {
@@ -1105,7 +1107,7 @@ func (fn *formulaFuncs) ATAN2(argsList *list.List) (result string, err error) {
// BASE function converts a number into a supplied base (radix), and returns a
// text representation of the calculated value. The syntax of the function is:
//
-// BASE(number,radix,[min_length])
+// BASE(number,radix,[min_length])
//
func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) {
if argsList.Len() < 2 {
@@ -1147,7 +1149,7 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) {
// CEILING function rounds a supplied number away from zero, to the nearest
// multiple of a given number. The syntax of the function is:
//
-// CEILING(number,significance)
+// CEILING(number,significance)
//
func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -1191,7 +1193,7 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error)
// CEILINGMATH function rounds a supplied number up to a supplied multiple of
// significance. The syntax of the function is:
//
-// CEILING.MATH(number,[significance],[mode])
+// CEILING.MATH(number,[significance],[mode])
//
func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -1242,7 +1244,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err
// number's sign), to the nearest multiple of a given number. The syntax of
// the function is:
//
-// CEILING.PRECISE(number,[significance])
+// CEILING.PRECISE(number,[significance])
//
func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -1289,7 +1291,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err
// COMBIN function calculates the number of combinations (in any order) of a
// given number objects from a set. The syntax of the function is:
//
-// COMBIN(number,number_chosen)
+// COMBIN(number,number_chosen)
//
func (fn *formulaFuncs) COMBIN(argsList *list.List) (result string, err error) {
if argsList.Len() != 2 {
@@ -1324,7 +1326,7 @@ func (fn *formulaFuncs) COMBIN(argsList *list.List) (result string, err error) {
// COMBINA function calculates the number of combinations, with repetitions,
// of a given number objects from a set. The syntax of the function is:
//
-// COMBINA(number,number_chosen)
+// COMBINA(number,number_chosen)
//
func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error) {
if argsList.Len() != 2 {
@@ -1364,7 +1366,7 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error)
// COS function calculates the cosine of a given angle. The syntax of the
// function is:
//
-// COS(number)
+// COS(number)
//
func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1383,7 +1385,7 @@ func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) {
// COSH function calculates the hyperbolic cosine (cosh) of a supplied number.
// The syntax of the function is:
//
-// COSH(number)
+// COSH(number)
//
func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1402,7 +1404,7 @@ func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) {
// COT function calculates the cotangent of a given angle. The syntax of the
// function is:
//
-// COT(number)
+// COT(number)
//
func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1425,7 +1427,7 @@ func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) {
// COTH function calculates the hyperbolic cotangent (coth) of a supplied
// angle. The syntax of the function is:
//
-// COTH(number)
+// COTH(number)
//
func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1448,7 +1450,7 @@ func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) {
// CSC function calculates the cosecant of a given angle. The syntax of the
// function is:
//
-// CSC(number)
+// CSC(number)
//
func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1471,7 +1473,7 @@ func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) {
// CSCH function calculates the hyperbolic cosecant (csch) of a supplied
// angle. The syntax of the function is:
//
-// CSCH(number)
+// CSCH(number)
//
func (fn *formulaFuncs) CSCH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1494,7 +1496,7 @@ func (fn *formulaFuncs) CSCH(argsList *list.List) (result string, err error) {
// DECIMAL function converts a text representation of a number in a specified
// base, into a decimal value. The syntax of the function is:
//
-// DECIMAL(text,radix)
+// DECIMAL(text,radix)
//
func (fn *formulaFuncs) DECIMAL(argsList *list.List) (result string, err error) {
if argsList.Len() != 2 {
@@ -1522,7 +1524,7 @@ func (fn *formulaFuncs) DECIMAL(argsList *list.List) (result string, err error)
// DEGREES function converts radians into degrees. The syntax of the function
// is:
//
-// DEGREES(angle)
+// DEGREES(angle)
//
func (fn *formulaFuncs) DEGREES(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1546,7 +1548,7 @@ func (fn *formulaFuncs) DEGREES(argsList *list.List) (result string, err error)
// positive number up and a negative number down), to the next even number.
// The syntax of the function is:
//
-// EVEN(number)
+// EVEN(number)
//
func (fn *formulaFuncs) EVEN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1575,7 +1577,7 @@ func (fn *formulaFuncs) EVEN(argsList *list.List) (result string, err error) {
// EXP function calculates the value of the mathematical constant e, raised to
// the power of a given number. The syntax of the function is:
//
-// EXP(number)
+// EXP(number)
//
func (fn *formulaFuncs) EXP(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1603,7 +1605,7 @@ func fact(number float64) float64 {
// FACT function returns the factorial of a supplied number. The syntax of the
// function is:
//
-// FACT(number)
+// FACT(number)
//
func (fn *formulaFuncs) FACT(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1625,7 +1627,7 @@ func (fn *formulaFuncs) FACT(argsList *list.List) (result string, err error) {
// FACTDOUBLE function returns the double factorial of a supplied number. The
// syntax of the function is:
//
-// FACTDOUBLE(number)
+// FACTDOUBLE(number)
//
func (fn *formulaFuncs) FACTDOUBLE(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1651,7 +1653,7 @@ func (fn *formulaFuncs) FACTDOUBLE(argsList *list.List) (result string, err erro
// FLOOR function rounds a supplied number towards zero to the nearest
// multiple of a specified significance. The syntax of the function is:
//
-// FLOOR(number,significance)
+// FLOOR(number,significance)
//
func (fn *formulaFuncs) FLOOR(argsList *list.List) (result string, err error) {
if argsList.Len() != 2 {
@@ -1685,7 +1687,7 @@ func (fn *formulaFuncs) FLOOR(argsList *list.List) (result string, err error) {
// FLOORMATH function rounds a supplied number down to a supplied multiple of
// significance. The syntax of the function is:
//
-// FLOOR.MATH(number,[significance],[mode])
+// FLOOR.MATH(number,[significance],[mode])
//
func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -1731,7 +1733,7 @@ func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error
// FLOORPRECISE function rounds a supplied number down to a supplied multiple
// of significance. The syntax of the function is:
//
-// FLOOR.PRECISE(number,[significance])
+// FLOOR.PRECISE(number,[significance])
//
func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -1797,7 +1799,7 @@ func gcd(x, y float64) float64 {
// GCD function returns the greatest common divisor of two or more supplied
// integers. The syntax of the function is:
//
-// GCD(number1,[number2],...)
+// GCD(number1,[number2],...)
//
func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -1842,7 +1844,7 @@ func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) {
// INT function truncates a supplied number down to the closest integer. The
// syntax of the function is:
//
-// INT(number)
+// INT(number)
//
func (fn *formulaFuncs) INT(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1866,7 +1868,7 @@ func (fn *formulaFuncs) INT(argsList *list.List) (result string, err error) {
// sign), to the nearest multiple of a supplied significance. The syntax of
// the function is:
//
-// ISO.CEILING(number,[significance])
+// ISO.CEILING(number,[significance])
//
func (fn *formulaFuncs) ISOCEILING(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -1923,7 +1925,7 @@ func lcm(a, b float64) float64 {
// LCM function returns the least common multiple of two or more supplied
// integers. The syntax of the function is:
//
-// LCM(number1,[number2],...)
+// LCM(number1,[number2],...)
//
func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -1968,7 +1970,7 @@ func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) {
// LN function calculates the natural logarithm of a given number. The syntax
// of the function is:
//
-// LN(number)
+// LN(number)
//
func (fn *formulaFuncs) LN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -1987,7 +1989,7 @@ func (fn *formulaFuncs) LN(argsList *list.List) (result string, err error) {
// LOG function calculates the logarithm of a given number, to a supplied
// base. The syntax of the function is:
//
-// LOG(number,[base])
+// LOG(number,[base])
//
func (fn *formulaFuncs) LOG(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -2028,7 +2030,7 @@ func (fn *formulaFuncs) LOG(argsList *list.List) (result string, err error) {
// LOG10 function calculates the base 10 logarithm of a given number. The
// syntax of the function is:
//
-// LOG10(number)
+// LOG10(number)
//
func (fn *formulaFuncs) LOG10(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -2082,7 +2084,7 @@ func det(sqMtx [][]float64) float64 {
// MDETERM calculates the determinant of a square matrix. The
// syntax of the function is:
//
-// MDETERM(array)
+// MDETERM(array)
//
func (fn *formulaFuncs) MDETERM(argsList *list.List) (result string, err error) {
var num float64
@@ -2113,7 +2115,7 @@ func (fn *formulaFuncs) MDETERM(argsList *list.List) (result string, err error)
// MOD function returns the remainder of a division between two supplied
// numbers. The syntax of the function is:
//
-// MOD(number,divisor)
+// MOD(number,divisor)
//
func (fn *formulaFuncs) MOD(argsList *list.List) (result string, err error) {
if argsList.Len() != 2 {
@@ -2144,7 +2146,7 @@ func (fn *formulaFuncs) MOD(argsList *list.List) (result string, err error) {
// MROUND function rounds a supplied number up or down to the nearest multiple
// of a given number. The syntax of the function is:
//
-// MOD(number,multiple)
+// MROUND(number,multiple)
//
func (fn *formulaFuncs) MROUND(argsList *list.List) (result string, err error) {
if argsList.Len() != 2 {
@@ -2852,7 +2854,7 @@ func (fn *formulaFuncs) SUMIF(argsList *list.List) (result string, err error) {
// SUMSQ function returns the sum of squares of a supplied set of values. The
// syntax of the function is:
//
-// SUMSQ(number1,[number2],...)
+// SUMSQ(number1,[number2],...)
//
func (fn *formulaFuncs) SUMSQ(argsList *list.List) (result string, err error) {
var val, sq float64
@@ -2928,7 +2930,7 @@ func (fn *formulaFuncs) TANH(argsList *list.List) (result string, err error) {
// TRUNC function truncates a supplied number to a specified number of decimal
// places. The syntax of the function is:
//
-// TRUNC(number,[number_digits])
+// TRUNC(number,[number_digits])
//
func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -2967,7 +2969,7 @@ func (fn *formulaFuncs) TRUNC(argsList *list.List) (result string, err error) {
// COUNTA function returns the number of non-blanks within a supplied set of
// cells or values. The syntax of the function is:
//
-// COUNTA(value1,[value2],...)
+// COUNTA(value1,[value2],...)
//
func (fn *formulaFuncs) COUNTA(argsList *list.List) (result string, err error) {
var count int
@@ -2995,7 +2997,7 @@ func (fn *formulaFuncs) COUNTA(argsList *list.List) (result string, err error) {
// MEDIAN function returns the statistical median (the middle value) of a list
// of supplied numbers. The syntax of the function is:
//
-// MEDIAN(number1,[number2],...)
+// MEDIAN(number1,[number2],...)
//
func (fn *formulaFuncs) MEDIAN(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
@@ -3044,7 +3046,7 @@ func (fn *formulaFuncs) MEDIAN(argsList *list.List) (result string, err error) {
// returns TRUE; Otherwise the function returns FALSE. The syntax of the
// function is:
//
-// ISBLANK(value)
+// ISBLANK(value)
//
func (fn *formulaFuncs) ISBLANK(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -3069,7 +3071,7 @@ func (fn *formulaFuncs) ISBLANK(argsList *list.List) (result string, err error)
// logical value TRUE; If the supplied value is not an error or is the #N/A
// error, the ISERR function returns FALSE. The syntax of the function is:
//
-// ISERR(value)
+// ISERR(value)
//
func (fn *formulaFuncs) ISERR(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -3092,7 +3094,7 @@ func (fn *formulaFuncs) ISERR(argsList *list.List) (result string, err error) {
// an Excel Error, and if so, returns the logical value TRUE; Otherwise the
// function returns FALSE. The syntax of the function is:
//
-// ISERROR(value)
+// ISERROR(value)
//
func (fn *formulaFuncs) ISERROR(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -3115,7 +3117,7 @@ func (fn *formulaFuncs) ISERROR(argsList *list.List) (result string, err error)
// evaluates to an even number, and if so, returns TRUE; Otherwise, the
// function returns FALSE. The syntax of the function is:
//
-// ISEVEN(value)
+// ISEVEN(value)
//
func (fn *formulaFuncs) ISEVEN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -3142,7 +3144,7 @@ func (fn *formulaFuncs) ISEVEN(argsList *list.List) (result string, err error) {
// the Excel #N/A Error, and if so, returns TRUE; Otherwise the function
// returns FALSE. The syntax of the function is:
//
-// ISNA(value)
+// ISNA(value)
//
func (fn *formulaFuncs) ISNA(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -3161,7 +3163,7 @@ func (fn *formulaFuncs) ISNA(argsList *list.List) (result string, err error) {
// function returns TRUE; If the supplied value is text, the function returns
// FALSE. The syntax of the function is:
//
-// ISNONTEXT(value)
+// ISNONTEXT(value)
//
func (fn *formulaFuncs) ISNONTEXT(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -3180,7 +3182,7 @@ func (fn *formulaFuncs) ISNONTEXT(argsList *list.List) (result string, err error
// the function returns TRUE; Otherwise it returns FALSE. The syntax of the
// function is:
//
-// ISNUMBER(value)
+// ISNUMBER(value)
//
func (fn *formulaFuncs) ISNUMBER(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -3202,7 +3204,7 @@ func (fn *formulaFuncs) ISNUMBER(argsList *list.List) (result string, err error)
// to an odd number, and if so, returns TRUE; Otherwise, the function returns
// FALSE. The syntax of the function is:
//
-// ISODD(value)
+// ISODD(value)
//
func (fn *formulaFuncs) ISODD(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
@@ -3229,7 +3231,7 @@ func (fn *formulaFuncs) ISODD(argsList *list.List) (result string, err error) {
// meaning 'value not available' and is produced when an Excel Formula is
// unable to find a value that it needs. The syntax of the function is:
//
-// NA()
+// NA()
//
func (fn *formulaFuncs) NA(argsList *list.List) (result string, err error) {
if argsList.Len() != 0 {
@@ -3243,7 +3245,10 @@ func (fn *formulaFuncs) NA(argsList *list.List) (result string, err error) {
// Logical Functions
// AND function tests a number of supplied conditions and returns TRUE or
-// FALSE.
+// FALSE. The syntax of the function is:
+//
+// AND(logical_test1,[logical_test2],...)
+//
func (fn *formulaFuncs) AND(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
err = errors.New("AND requires at least 1 argument")
@@ -3284,7 +3289,10 @@ func (fn *formulaFuncs) AND(argsList *list.List) (result string, err error) {
}
// OR function tests a number of supplied conditions and returns either TRUE
-// or FALSE.
+// or FALSE. The syntax of the function is:
+//
+// OR(logical_test1,[logical_test2],...)
+//
func (fn *formulaFuncs) OR(argsList *list.List) (result string, err error) {
if argsList.Len() == 0 {
err = errors.New("OR requires at least 1 argument")
@@ -3326,7 +3334,11 @@ func (fn *formulaFuncs) OR(argsList *list.List) (result string, err error) {
// Date and Time Functions
-// DATE returns a date, from a user-supplied year, month and day.
+// DATE returns a date, from a user-supplied year, month and day. The syntax
+// of the function is:
+//
+// DATE(year,month,day)
+//
func (fn *formulaFuncs) DATE(argsList *list.List) (result string, err error) {
if argsList.Len() != 3 {
err = errors.New("DATE requires 3 number arguments")
@@ -3368,7 +3380,11 @@ func daysBetween(startDate, endDate int64) float64 {
// Text Functions
-// CLEAN removes all non-printable characters from a supplied text string.
+// CLEAN removes all non-printable characters from a supplied text string. The
+// syntax of the function is:
+//
+// CLEAN(text)
+//
func (fn *formulaFuncs) CLEAN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
err = errors.New("CLEAN requires 1 argument")
@@ -3385,7 +3401,11 @@ func (fn *formulaFuncs) CLEAN(argsList *list.List) (result string, err error) {
}
// TRIM removes extra spaces (i.e. all spaces except for single spaces between
-// words or characters) from a supplied text string.
+// words or characters) from a supplied text string. The syntax of the
+// function is:
+//
+// TRIM(text)
+//
func (fn *formulaFuncs) TRIM(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
err = errors.New("TRIM requires 1 argument")
@@ -3394,3 +3414,58 @@ func (fn *formulaFuncs) TRIM(argsList *list.List) (result string, err error) {
result = strings.TrimSpace(argsList.Front().Value.(formulaArg).String)
return
}
+
+// LOWER converts all characters in a supplied text string to lower case. The
+// syntax of the function is:
+//
+// LOWER(text)
+//
+func (fn *formulaFuncs) LOWER(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("LOWER requires 1 argument")
+ return
+ }
+ result = strings.ToLower(argsList.Front().Value.(formulaArg).String)
+ return
+}
+
+// PROPER converts all characters in a supplied text string to proper case
+// (i.e. all letters that do not immediately follow another letter are set to
+// upper case and all other characters are lower case). The syntax of the
+// function is:
+//
+// PROPER(text)
+//
+func (fn *formulaFuncs) PROPER(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("PROPER requires 1 argument")
+ return
+ }
+ buf := bytes.Buffer{}
+ isLetter := false
+ for _, char := range argsList.Front().Value.(formulaArg).String {
+ if !isLetter && unicode.IsLetter(char) {
+ buf.WriteRune(unicode.ToUpper(char))
+ } else {
+ buf.WriteRune(unicode.ToLower(char))
+ }
+ isLetter = unicode.IsLetter(char)
+ }
+
+ result = buf.String()
+ return
+}
+
+// UPPER converts all characters in a supplied text string to upper case. The
+// syntax of the function is:
+//
+// UPPER(text)
+//
+func (fn *formulaFuncs) UPPER(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("UPPER requires 1 argument")
+ return
+ }
+ result = strings.ToUpper(argsList.Front().Value.(formulaArg).String)
+ return
+}
diff --git a/calc_test.go b/calc_test.go
index f928797..ea60a50 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -470,6 +470,21 @@ func TestCalcCellValue(t *testing.T) {
// TRIM
"=TRIM(\" trim text \")": "trim text",
"=TRIM(0)": "0",
+ // LOWER
+ "=LOWER(\"test\")": "test",
+ "=LOWER(\"TEST\")": "test",
+ "=LOWER(\"Test\")": "test",
+ "=LOWER(\"TEST 123\")": "test 123",
+ // PROPER
+ "=PROPER(\"this is a test sentence\")": "This Is A Test Sentence",
+ "=PROPER(\"THIS IS A TEST SENTENCE\")": "This Is A Test Sentence",
+ "=PROPER(\"123tEST teXT\")": "123Test Text",
+ "=PROPER(\"Mr. SMITH's address\")": "Mr. Smith'S Address",
+ // UPPER
+ "=UPPER(\"test\")": "TEST",
+ "=UPPER(\"TEST\")": "TEST",
+ "=UPPER(\"Test\")": "TEST",
+ "=UPPER(\"TEST 123\")": "TEST 123",
}
for formula, expected := range mathCalc {
f := prepareData()
@@ -793,6 +808,15 @@ func TestCalcCellValue(t *testing.T) {
// TRIM
"=TRIM()": "TRIM requires 1 argument",
"=TRIM(1,2)": "TRIM requires 1 argument",
+ // LOWER
+ "=LOWER()": "LOWER requires 1 argument",
+ "=LOWER(1,2)": "LOWER requires 1 argument",
+ // UPPER
+ "=UPPER()": "UPPER requires 1 argument",
+ "=UPPER(1,2)": "UPPER requires 1 argument",
+ // PROPER
+ "=PROPER()": "PROPER requires 1 argument",
+ "=PROPER(1,2)": "PROPER requires 1 argument",
}
for formula, expected := range mathCalcError {
f := prepareData()
diff --git a/rows.go b/rows.go
index 591d7e9..1e29d8f 100644
--- a/rows.go
+++ b/rows.go
@@ -602,7 +602,6 @@ func (f *File) duplicateMergeCells(sheet string, ws *xlsxWorksheet, row, row2 in
if err := f.MergeCell(sheet, from, to); err != nil {
return err
}
- i++
}
}
return nil
diff --git a/rows_test.go b/rows_test.go
index e49b28a..02b00da 100644
--- a/rows_test.go
+++ b/rows_test.go
@@ -323,7 +323,7 @@ func TestDuplicateRowFromSingleRow(t *testing.T) {
assert.NoError(t, f.SetCellStr(sheet, "B1", cells["B1"]))
assert.NoError(t, f.DuplicateRow(sheet, 1))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.FromSingleRow_1"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "FromSingleRow_1"))) {
t.FailNow()
}
expect := map[string]string{
@@ -339,7 +339,7 @@ func TestDuplicateRowFromSingleRow(t *testing.T) {
}
assert.NoError(t, f.DuplicateRow(sheet, 2))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.FromSingleRow_2"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "FromSingleRow_2"))) {
t.FailNow()
}
expect = map[string]string{
@@ -380,7 +380,7 @@ func TestDuplicateRowUpdateDuplicatedRows(t *testing.T) {
assert.NoError(t, f.SetCellStr(sheet, "A2", cells["A2"]))
assert.NoError(t, f.SetCellStr(sheet, "B2", cells["B2"]))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.UpdateDuplicatedRows"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "UpdateDuplicatedRows"))) {
t.FailNow()
}
expect := map[string]string{
@@ -423,7 +423,7 @@ func TestDuplicateRowFirstOfMultipleRows(t *testing.T) {
assert.NoError(t, f.DuplicateRow(sheet, 1))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.FirstOfMultipleRows"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "FirstOfMultipleRows"))) {
t.FailNow()
}
expect := map[string]string{
@@ -451,7 +451,7 @@ func TestDuplicateRowZeroWithNoRows(t *testing.T) {
assert.EqualError(t, f.DuplicateRow(sheet, 0), "invalid row number 0")
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.ZeroWithNoRows"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "ZeroWithNoRows"))) {
t.FailNow()
}
@@ -493,7 +493,7 @@ func TestDuplicateRowMiddleRowOfEmptyFile(t *testing.T) {
assert.NoError(t, f.DuplicateRow(sheet, 99))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.MiddleRowOfEmptyFile"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "MiddleRowOfEmptyFile"))) {
t.FailNow()
}
expect := map[string]string{
@@ -537,7 +537,7 @@ func TestDuplicateRowWithLargeOffsetToMiddleOfData(t *testing.T) {
assert.NoError(t, f.DuplicateRowTo(sheet, 1, 3))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.WithLargeOffsetToMiddleOfData"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "WithLargeOffsetToMiddleOfData"))) {
t.FailNow()
}
expect := map[string]string{
@@ -582,7 +582,7 @@ func TestDuplicateRowWithLargeOffsetToEmptyRows(t *testing.T) {
assert.NoError(t, f.DuplicateRowTo(sheet, 1, 7))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.WithLargeOffsetToEmptyRows"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "WithLargeOffsetToEmptyRows"))) {
t.FailNow()
}
expect := map[string]string{
@@ -627,7 +627,7 @@ func TestDuplicateRowInsertBefore(t *testing.T) {
assert.NoError(t, f.DuplicateRowTo(sheet, 2, 1))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.InsertBefore"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "InsertBefore"))) {
t.FailNow()
}
@@ -673,7 +673,7 @@ func TestDuplicateRowInsertBeforeWithLargeOffset(t *testing.T) {
assert.NoError(t, f.DuplicateRowTo(sheet, 3, 1))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.InsertBeforeWithLargeOffset"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "InsertBeforeWithLargeOffset"))) {
t.FailNow()
}
@@ -722,7 +722,7 @@ func TestDuplicateRowInsertBeforeWithMergeCells(t *testing.T) {
assert.NoError(t, f.DuplicateRowTo(sheet, 2, 1))
assert.NoError(t, f.DuplicateRowTo(sheet, 1, 8))
- if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "TestDuplicateRow.InsertBeforeWithMergeCells"))) {
+ if !assert.NoError(t, f.SaveAs(fmt.Sprintf(outFile, "InsertBeforeWithMergeCells"))) {
t.FailNow()
}
@@ -742,9 +742,9 @@ func TestDuplicateRowInsertBeforeWithMergeCells(t *testing.T) {
})
}
-func TestDuplicateRowInvalidRownum(t *testing.T) {
+func TestDuplicateRowInvalidRowNum(t *testing.T) {
const sheet = "Sheet1"
- outFile := filepath.Join("test", "TestDuplicateRowInvalidRownum.%s.xlsx")
+ outFile := filepath.Join("test", "TestDuplicateRow.InvalidRowNum.%s.xlsx")
cells := map[string]string{
"A1": "A1 Value",
--
cgit v1.2.1