diff options
| -rw-r--r-- | calc.go | 352 | ||||
| -rw-r--r-- | calc_test.go | 131 | ||||
| -rw-r--r-- | lib.go | 6 | ||||
| -rw-r--r-- | sheet.go | 9 | ||||
| -rw-r--r-- | test/Book1.xlsx | bin | 20750 -> 20753 bytes |
5 files changed, 467 insertions, 31 deletions
@@ -234,10 +234,12 @@ var tokenPriority = map[string]int{ // CEILING // CEILING.MATH // CEILING.PRECISE +// CHAR // CHOOSE // CLEAN // CODE // COLUMN +// COLUMNS // COMBIN // COMBINA // CONCAT @@ -305,6 +307,8 @@ var tokenPriority = map[string]int{ // MAX // MDETERM // MEDIAN +// MID +// MIDB // MIN // MINA // MOD @@ -327,6 +331,8 @@ var tokenPriority = map[string]int{ // RADIANS // RAND // RANDBETWEEN +// REPLACE +// REPLACEB // REPT // RIGHT // RIGHTB @@ -334,6 +340,8 @@ var tokenPriority = map[string]int{ // ROUND // ROUNDDOWN // ROUNDUP +// ROW +// ROWS // SEC // SECH // SHEET @@ -352,6 +360,8 @@ var tokenPriority = map[string]int{ // TRIM // TRUE // TRUNC +// UNICHAR +// UNICODE // UPPER // VLOOKUP // @@ -932,8 +942,17 @@ func (f *File) parseReference(sheet, reference string) (arg formulaArg, err erro cr := cellRef{} if len(tokens) == 2 { // have a worksheet name cr.Sheet = tokens[0] + // cast to cell coordinates if cr.Col, cr.Row, err = CellNameToCoordinates(tokens[1]); err != nil { - return + // cast to column + if cr.Col, err = ColumnNameToNumber(tokens[1]); err != nil { + // cast to row + if cr.Row, err = strconv.Atoi(tokens[1]); err != nil { + err = newInvalidColumnNameError(tokens[1]) + return + } + cr.Col = TotalColumns + } } if refs.Len() > 0 { e := refs.Back() @@ -943,9 +962,16 @@ func (f *File) parseReference(sheet, reference string) (arg formulaArg, err erro refs.PushBack(cr) continue } + // cast to cell coordinates if cr.Col, cr.Row, err = CellNameToCoordinates(tokens[0]); err != nil { + // cast to column if cr.Col, err = ColumnNameToNumber(tokens[0]); err != nil { - return + // cast to row + if cr.Row, err = strconv.Atoi(tokens[0]); err != nil { + err = newInvalidColumnNameError(tokens[0]) + return + } + cr.Col = TotalColumns } cellRanges.PushBack(cellRange{ From: cellRef{Sheet: sheet, Col: cr.Col, Row: 1}, @@ -1329,7 +1355,7 @@ func (fn *formulaFuncs) bitwise(name string, argsList *list.List) formulaArg { "BITRSHIFT": func(a, b int) int { return a >> uint(b) }, "BITXOR": func(a, b int) int { return a ^ b }, } - bitwiseFunc, _ := bitwiseFuncMap[name] + bitwiseFunc := bitwiseFuncMap[name] return newNumberFormulaArg(float64(bitwiseFunc(int(num1.Number), int(num2.Number)))) } @@ -4569,6 +4595,26 @@ func daysBetween(startDate, endDate int64) float64 { // Text Functions +// CHAR function returns the character relating to a supplied character set +// number (from 1 to 255). syntax of the function is: +// +// CHAR(number) +// +func (fn *formulaFuncs) CHAR(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "CHAR requires 1 argument") + } + arg := argsList.Front().Value.(formulaArg).ToNumber() + if arg.Type != ArgNumber { + return arg + } + num := int(arg.Number) + if num < 0 || num > 255 { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + return newStringFormulaArg(fmt.Sprintf("%c", num)) +} + // CLEAN removes all non-printable characters from a supplied text string. The // syntax of the function is: // @@ -4594,12 +4640,20 @@ func (fn *formulaFuncs) CLEAN(argsList *list.List) formulaArg { // CODE(text) // func (fn *formulaFuncs) CODE(argsList *list.List) formulaArg { + return fn.code("CODE", argsList) +} + +// code is an implementation of the formula function CODE and UNICODE. +func (fn *formulaFuncs) code(name string, argsList *list.List) formulaArg { if argsList.Len() != 1 { - return newErrorFormulaArg(formulaErrorVALUE, "CODE requires 1 argument") + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 1 argument", name)) } text := argsList.Front().Value.(formulaArg).Value() if len(text) == 0 { - return newNumberFormulaArg(0) + if name == "CODE" { + return newNumberFormulaArg(0) + } + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) } return newNumberFormulaArg(float64(text[0])) } @@ -4795,19 +4849,6 @@ func (fn *formulaFuncs) LENB(argsList *list.List) formulaArg { return newStringFormulaArg(strconv.Itoa(len(argsList.Front().Value.(formulaArg).String))) } -// TRIM removes extra spaces (i.e. all spaces except for single spaces between -// words or characters) from a supplied text string. The syntax of the -// function is: -// -// TRIM(text) -// -func (fn *formulaFuncs) TRIM(argsList *list.List) formulaArg { - if argsList.Len() != 1 { - return newErrorFormulaArg(formulaErrorVALUE, "TRIM requires 1 argument") - } - return newStringFormulaArg(strings.TrimSpace(argsList.Front().Value.(formulaArg).String)) -} - // LOWER converts all characters in a supplied text string to lower case. The // syntax of the function is: // @@ -4820,6 +4861,56 @@ func (fn *formulaFuncs) LOWER(argsList *list.List) formulaArg { return newStringFormulaArg(strings.ToLower(argsList.Front().Value.(formulaArg).String)) } +// MID function returns a specified number of characters from the middle of a +// supplied text string. The syntax of the function is: +// +// MID(text,start_num,num_chars) +// +func (fn *formulaFuncs) MID(argsList *list.List) formulaArg { + return fn.mid("MID", argsList) +} + +// MIDB returns a specific number of characters from a text string, starting +// at the position you specify, based on the number of bytes you specify. The +// syntax of the function is: +// +// MID(text,start_num,num_chars) +// +func (fn *formulaFuncs) MIDB(argsList *list.List) formulaArg { + return fn.mid("MIDB", argsList) +} + +// mid is an implementation of the formula function MID and MIDB. TODO: +// support DBCS include Japanese, Chinese (Simplified), Chinese +// (Traditional), and Korean. +func (fn *formulaFuncs) mid(name string, argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 3 arguments", name)) + } + text := argsList.Front().Value.(formulaArg).Value() + startNumArg, numCharsArg := argsList.Front().Next().Value.(formulaArg).ToNumber(), argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if startNumArg.Type != ArgNumber { + return startNumArg + } + if numCharsArg.Type != ArgNumber { + return numCharsArg + } + startNum := int(startNumArg.Number) + if startNum < 0 { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + textLen := len(text) + if startNum > textLen { + return newStringFormulaArg("") + } + startNum-- + endNum := startNum + int(numCharsArg.Number) + if endNum > textLen+1 { + return newStringFormulaArg(text[startNum:]) + } + return newStringFormulaArg(text[startNum:endNum]) +} + // 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 @@ -4844,6 +4935,54 @@ func (fn *formulaFuncs) PROPER(argsList *list.List) formulaArg { return newStringFormulaArg(buf.String()) } +// REPLACE function replaces all or part of a text string with another string. +// The syntax of the function is: +// +// REPLACE(old_text,start_num,num_chars,new_text) +// +func (fn *formulaFuncs) REPLACE(argsList *list.List) formulaArg { + return fn.replace("REPLACE", argsList) +} + +// REPLACEB replaces part of a text string, based on the number of bytes you +// specify, with a different text string. +// +// REPLACEB(old_text,start_num,num_chars,new_text) +// +func (fn *formulaFuncs) REPLACEB(argsList *list.List) formulaArg { + return fn.replace("REPLACEB", argsList) +} + +// replace is an implementation of the formula function REPLACE and REPLACEB. +// TODO: support DBCS include Japanese, Chinese (Simplified), Chinese +// (Traditional), and Korean. +func (fn *formulaFuncs) replace(name string, argsList *list.List) formulaArg { + if argsList.Len() != 4 { + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 4 arguments", name)) + } + oldText, newText := argsList.Front().Value.(formulaArg).Value(), argsList.Back().Value.(formulaArg).Value() + startNumArg, numCharsArg := argsList.Front().Next().Value.(formulaArg).ToNumber(), argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if startNumArg.Type != ArgNumber { + return startNumArg + } + if numCharsArg.Type != ArgNumber { + return numCharsArg + } + oldTextLen, startIdx := len(oldText), int(startNumArg.Number) + if startIdx > oldTextLen { + startIdx = oldTextLen + 1 + } + endIdx := startIdx + int(numCharsArg.Number) + if endIdx > oldTextLen { + endIdx = oldTextLen + 1 + } + if startIdx < 1 || endIdx < 1 { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + result := oldText[:startIdx-1] + newText + oldText[endIdx-1:] + return newStringFormulaArg(result) +} + // REPT function returns a supplied text string, repeated a specified number // of times. The syntax of the function is: // @@ -4892,6 +5031,47 @@ func (fn *formulaFuncs) RIGHTB(argsList *list.List) formulaArg { return fn.leftRight("RIGHTB", argsList) } +// TRIM removes extra spaces (i.e. all spaces except for single spaces between +// words or characters) from a supplied text string. The syntax of the +// function is: +// +// TRIM(text) +// +func (fn *formulaFuncs) TRIM(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "TRIM requires 1 argument") + } + return newStringFormulaArg(strings.TrimSpace(argsList.Front().Value.(formulaArg).String)) +} + +// UNICHAR returns the Unicode character that is referenced by the given +// numeric value. The syntax of the function is: +// +// UNICHAR(number) +// +func (fn *formulaFuncs) UNICHAR(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "UNICHAR requires 1 argument") + } + numArg := argsList.Front().Value.(formulaArg).ToNumber() + if numArg.Type != ArgNumber { + return numArg + } + if numArg.Number <= 0 || numArg.Number > 55295 { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + return newStringFormulaArg(string(rune(numArg.Number))) +} + +// UNICODE function returns the code point for the first character of a +// supplied text string. The syntax of the function is: +// +// UNICODE(text) +// +func (fn *formulaFuncs) UNICODE(argsList *list.List) formulaArg { + return fn.code("UNICODE", argsList) +} + // UPPER converts all characters in a supplied text string to upper case. The // syntax of the function is: // @@ -5133,6 +5313,63 @@ func (fn *formulaFuncs) COLUMN(argsList *list.List) formulaArg { return newNumberFormulaArg(float64(col)) } +// COLUMNS function receives an Excel range and returns the number of columns +// that are contained within the range. The syntax of the function is: +// +// COLUMNS(array) +// +func (fn *formulaFuncs) COLUMNS(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "COLUMNS requires 1 argument") + } + var min, max int + if argsList.Front().Value.(formulaArg).cellRanges != nil && argsList.Front().Value.(formulaArg).cellRanges.Len() > 0 { + crs := argsList.Front().Value.(formulaArg).cellRanges + for cr := crs.Front(); cr != nil; cr = cr.Next() { + if min == 0 { + min = cr.Value.(cellRange).From.Col + } + if min > cr.Value.(cellRange).From.Col { + min = cr.Value.(cellRange).From.Col + } + if min > cr.Value.(cellRange).To.Col { + min = cr.Value.(cellRange).To.Col + } + if max < cr.Value.(cellRange).To.Col { + max = cr.Value.(cellRange).To.Col + } + if max < cr.Value.(cellRange).From.Col { + max = cr.Value.(cellRange).From.Col + } + } + } + if argsList.Front().Value.(formulaArg).cellRefs != nil && argsList.Front().Value.(formulaArg).cellRefs.Len() > 0 { + cr := argsList.Front().Value.(formulaArg).cellRefs + for refs := cr.Front(); refs != nil; refs = refs.Next() { + if min == 0 { + min = refs.Value.(cellRef).Col + } + if min > refs.Value.(cellRef).Col { + min = refs.Value.(cellRef).Col + } + if max < refs.Value.(cellRef).Col { + max = refs.Value.(cellRef).Col + } + } + } + if max == TotalColumns { + return newNumberFormulaArg(float64(TotalColumns)) + } + result := max - min + 1 + if max == min { + if min == 0 { + return newErrorFormulaArg(formulaErrorVALUE, "invalid reference") + } + return newNumberFormulaArg(float64(1)) + } + return newNumberFormulaArg(float64(result)) +} + // HLOOKUP function 'looks up' a given value in the top row of a data array // (or table), and returns the corresponding value from another row of the // array. The syntax of the function is: @@ -5396,6 +5633,85 @@ func lookupCol(arr formulaArg) []formulaArg { return col } +// ROW function returns the first row number within a supplied reference or +// the number of the current row. The syntax of the function is: +// +// ROW([reference]) +// +func (fn *formulaFuncs) ROW(argsList *list.List) formulaArg { + if argsList.Len() > 1 { + return newErrorFormulaArg(formulaErrorVALUE, "ROW requires at most 1 argument") + } + if argsList.Len() == 1 { + if argsList.Front().Value.(formulaArg).cellRanges != nil && argsList.Front().Value.(formulaArg).cellRanges.Len() > 0 { + return newNumberFormulaArg(float64(argsList.Front().Value.(formulaArg).cellRanges.Front().Value.(cellRange).From.Row)) + } + if argsList.Front().Value.(formulaArg).cellRefs != nil && argsList.Front().Value.(formulaArg).cellRefs.Len() > 0 { + return newNumberFormulaArg(float64(argsList.Front().Value.(formulaArg).cellRefs.Front().Value.(cellRef).Row)) + } + return newErrorFormulaArg(formulaErrorVALUE, "invalid reference") + } + _, row, _ := CellNameToCoordinates(fn.cell) + return newNumberFormulaArg(float64(row)) +} + +// ROWS function takes an Excel range and returns the number of rows that are +// contained within the range. The syntax of the function is: +// +// ROWS(array) +// +func (fn *formulaFuncs) ROWS(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "ROWS requires 1 argument") + } + var min, max int + if argsList.Front().Value.(formulaArg).cellRanges != nil && argsList.Front().Value.(formulaArg).cellRanges.Len() > 0 { + crs := argsList.Front().Value.(formulaArg).cellRanges + for cr := crs.Front(); cr != nil; cr = cr.Next() { + if min == 0 { + min = cr.Value.(cellRange).From.Row + } + if min > cr.Value.(cellRange).From.Row { + min = cr.Value.(cellRange).From.Row + } + if min > cr.Value.(cellRange).To.Row { + min = cr.Value.(cellRange).To.Row + } + if max < cr.Value.(cellRange).To.Row { + max = cr.Value.(cellRange).To.Row + } + if max < cr.Value.(cellRange).From.Row { + max = cr.Value.(cellRange).From.Row + } + } + } + if argsList.Front().Value.(formulaArg).cellRefs != nil && argsList.Front().Value.(formulaArg).cellRefs.Len() > 0 { + cr := argsList.Front().Value.(formulaArg).cellRefs + for refs := cr.Front(); refs != nil; refs = refs.Next() { + if min == 0 { + min = refs.Value.(cellRef).Row + } + if min > refs.Value.(cellRef).Row { + min = refs.Value.(cellRef).Row + } + if max < refs.Value.(cellRef).Row { + max = refs.Value.(cellRef).Row + } + } + } + if max == TotalRows { + return newStringFormulaArg(strconv.Itoa(TotalRows)) + } + result := max - min + 1 + if max == min { + if min == 0 { + return newErrorFormulaArg(formulaErrorVALUE, "invalid reference") + } + return newNumberFormulaArg(float64(1)) + } + return newStringFormulaArg(strconv.Itoa(result)) +} + // Web Functions // ENCODEURL function returns a URL-encoded string, replacing certain diff --git a/calc_test.go b/calc_test.go index 8a07eef..c529312 100644 --- a/calc_test.go +++ b/calc_test.go @@ -712,6 +712,11 @@ func TestCalcCellValue(t *testing.T) { "=DATE(2020,10,21)": "2020-10-21 00:00:00 +0000 UTC", "=DATE(1900,1,1)": "1899-12-31 00:00:00 +0000 UTC", // Text Functions + // CHAR + "=CHAR(65)": "A", + "=CHAR(97)": "a", + "=CHAR(63)": "?", + "=CHAR(51)": "3", // CLEAN "=CLEAN(\"\u0009clean text\")": "clean text", "=CLEAN(0)": "0", @@ -761,19 +766,38 @@ func TestCalcCellValue(t *testing.T) { // LENB "=LENB(\"\")": "0", "=LENB(D1)": "5", - // TRIM - "=TRIM(\" trim text \")": "trim text", - "=TRIM(0)": "0", // LOWER "=LOWER(\"test\")": "test", "=LOWER(\"TEST\")": "test", "=LOWER(\"Test\")": "test", "=LOWER(\"TEST 123\")": "test 123", + // MID + "=MID(\"Original Text\",7,1)": "a", + "=MID(\"Original Text\",4,7)": "ginal T", + "=MID(\"255 years\",3,1)": "5", + "=MID(\"text\",3,6)": "xt", + "=MID(\"text\",6,0)": "", + // MIDB + "=MIDB(\"Original Text\",7,1)": "a", + "=MIDB(\"Original Text\",4,7)": "ginal T", + "=MIDB(\"255 years\",3,1)": "5", + "=MIDB(\"text\",3,6)": "xt", + "=MIDB(\"text\",6,0)": "", // 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", + // REPLACE + "=REPLACE(\"test string\",7,3,\"X\")": "test sXng", + "=REPLACE(\"second test string\",8,4,\"XXX\")": "second XXX string", + "=REPLACE(\"text\",5,0,\" and char\")": "text and char", + "=REPLACE(\"text\",1,20,\"char and \")": "char and ", + // REPLACEB + "=REPLACEB(\"test string\",7,3,\"X\")": "test sXng", + "=REPLACEB(\"second test string\",8,4,\"XXX\")": "second XXX string", + "=REPLACEB(\"text\",5,0,\" and char\")": "text and char", + "=REPLACEB(\"text\",1,20,\"char and \")": "char and ", // REPT "=REPT(\"*\",0)": "", "=REPT(\"*\",1)": "*", @@ -790,6 +814,19 @@ func TestCalcCellValue(t *testing.T) { "=RIGHTB(\"Original Text\",0)": "", "=RIGHTB(\"Original Text\",13)": "Original Text", "=RIGHTB(\"Original Text\",20)": "Original Text", + // TRIM + "=TRIM(\" trim text \")": "trim text", + "=TRIM(0)": "0", + // UNICHAR + "=UNICHAR(65)": "A", + "=UNICHAR(97)": "a", + "=UNICHAR(63)": "?", + "=UNICHAR(51)": "3", + // UNICODE + "=UNICODE(\"Alpha\")": "65", + "=UNICODE(\"alpha\")": "97", + "=UNICODE(\"?\")": "63", + "=UNICODE(\"3\")": "51", // UPPER "=UPPER(\"test\")": "TEST", "=UPPER(\"TEST\")": "TEST", @@ -812,6 +849,15 @@ func TestCalcCellValue(t *testing.T) { "=COLUMN(Sheet1!A1:B1:C1)": "1", "=COLUMN(Sheet1!F1:G1)": "6", "=COLUMN(H1)": "8", + // COLUMNS + "=COLUMNS(B1)": "1", + "=COLUMNS(1:1)": "16384", + "=COLUMNS(Sheet1!1:1)": "16384", + "=COLUMNS(B1:E5)": "4", + "=COLUMNS(Sheet1!E5:H7:B1)": "7", + "=COLUMNS(E5:H7:B1:C1:Z1:C1:B1)": "25", + "=COLUMNS(E5:B1)": "4", + "=COLUMNS(EM38:HZ81)": "92", // HLOOKUP "=HLOOKUP(D2,D2:D8,1,FALSE)": "Jan", "=HLOOKUP(F3,F3:F8,3,FALSE)": "34440", @@ -832,6 +878,21 @@ func TestCalcCellValue(t *testing.T) { "=LOOKUP(F8,F8:F9,F8:F9)": "32080", "=LOOKUP(F8,F8:F9,D8:D9)": "Feb", "=LOOKUP(1,MUNIT(1),MUNIT(1))": "1", + // ROW + "=ROW()": "1", + "=ROW(Sheet1!A1)": "1", + "=ROW(Sheet1!A1:B2:C3)": "1", + "=ROW(Sheet1!F5:G6)": "5", + "=ROW(A8)": "8", + // ROWS + "=ROWS(B1)": "1", + "=ROWS(B:B)": "1048576", + "=ROWS(Sheet1!B:B)": "1048576", + "=ROWS(B1:E5)": "5", + "=ROWS(Sheet1!E5:H7:B1)": "7", + "=ROWS(E5:H8:B2:C3:Z26:C3:B2)": "25", + "=ROWS(E5:B1)": "5", + "=ROWS(EM38:HZ81)": "44", // Web Functions // ENCODEURL "=ENCODEURL(\"https://xuri.me/excelize/en/?q=Save As\")": "https%3A%2F%2Fxuri.me%2Fexcelize%2Fen%2F%3Fq%3DSave%20As", @@ -1348,6 +1409,11 @@ func TestCalcCellValue(t *testing.T) { `=DATE(2020,"text",21)`: "DATE requires 3 number arguments", `=DATE(2020,10,"text")`: "DATE requires 3 number arguments", // Text Functions + // CHAR + "=CHAR()": "CHAR requires 1 argument", + "=CHAR(-1)": "#VALUE!", + "=CHAR(256)": "#VALUE!", + "=CHAR(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // CLEAN "=CLEAN()": "CLEAN requires 1 argument", "=CLEAN(1,2)": "CLEAN requires 1 argument", @@ -1387,18 +1453,32 @@ func TestCalcCellValue(t *testing.T) { "=LEN()": "LEN requires 1 string argument", // LENB "=LENB()": "LENB requires 1 string argument", - // 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", + // MID + "=MID()": "MID requires 3 arguments", + "=MID(\"\",-1,1)": "#VALUE!", + "=MID(\"\",\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=MID(\"\",1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // MIDB + "=MIDB()": "MIDB requires 3 arguments", + "=MIDB(\"\",-1,1)": "#VALUE!", + "=MIDB(\"\",\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=MIDB(\"\",1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // PROPER "=PROPER()": "PROPER requires 1 argument", "=PROPER(1,2)": "PROPER requires 1 argument", + // REPLACE + "=REPLACE()": "REPLACE requires 4 arguments", + "=REPLACE(\"text\",0,4,\"string\")": "#VALUE!", + "=REPLACE(\"text\",\"\",0,\"string\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=REPLACE(\"text\",1,\"\",\"string\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // REPLACEB + "=REPLACEB()": "REPLACEB requires 4 arguments", + "=REPLACEB(\"text\",0,4,\"string\")": "#VALUE!", + "=REPLACEB(\"text\",\"\",0,\"string\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=REPLACEB(\"text\",1,\"\",\"string\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // REPT "=REPT()": "REPT requires 2 arguments", "=REPT(INT(0),2)": "REPT requires first argument to be a string", @@ -1414,6 +1494,20 @@ func TestCalcCellValue(t *testing.T) { "=RIGHTB(\"\",2,3)": "RIGHTB allows at most 2 arguments", "=RIGHTB(\"\",\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", "=RIGHTB(\"\",-1)": "#VALUE!", + // TRIM + "=TRIM()": "TRIM requires 1 argument", + "=TRIM(1,2)": "TRIM requires 1 argument", + // UNICHAR + "=UNICHAR()": "UNICHAR requires 1 argument", + "=UNICHAR(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=UNICHAR(55296)": "#VALUE!", + "=UNICHAR(0)": "#VALUE!", + // UNICODE + "=UNICODE()": "UNICODE requires 1 argument", + "=UNICODE(\"\")": "#VALUE!", + // UPPER + "=UPPER()": "UPPER requires 1 argument", + "=UPPER(1,2)": "UPPER requires 1 argument", // Conditional Functions // IF "=IF()": "IF requires at least 1 argument", @@ -1429,6 +1523,13 @@ func TestCalcCellValue(t *testing.T) { "=COLUMN(\"\")": "invalid reference", "=COLUMN(Sheet1)": "invalid column name \"Sheet1\"", "=COLUMN(Sheet1!A1!B1)": "invalid column name \"Sheet1\"", + // COLUMNS + "=COLUMNS()": "COLUMNS requires 1 argument", + "=COLUMNS(1)": "invalid reference", + "=COLUMNS(\"\")": "invalid reference", + "=COLUMNS(Sheet1)": "invalid column name \"Sheet1\"", + "=COLUMNS(Sheet1!A1!B1)": "invalid column name \"Sheet1\"", + "=COLUMNS(Sheet1!Sheet1)": "invalid column name \"Sheet1\"", // HLOOKUP "=HLOOKUP()": "HLOOKUP requires at least 3 arguments", "=HLOOKUP(D2,D1,1,FALSE)": "HLOOKUP requires second argument of table array", @@ -1460,6 +1561,18 @@ func TestCalcCellValue(t *testing.T) { "=LOOKUP(D2,D1,D2)": "LOOKUP requires second argument of table array", "=LOOKUP(D2,D1,D2,FALSE)": "LOOKUP requires at most 3 arguments", "=LOOKUP(D1,MUNIT(1),MUNIT(1))": "LOOKUP no result found", + // ROW + "=ROW(1,2)": "ROW requires at most 1 argument", + "=ROW(\"\")": "invalid reference", + "=ROW(Sheet1)": "invalid column name \"Sheet1\"", + "=ROW(Sheet1!A1!B1)": "invalid column name \"Sheet1\"", + // ROWS + "=ROWS()": "ROWS requires 1 argument", + "=ROWS(1)": "invalid reference", + "=ROWS(\"\")": "invalid reference", + "=ROWS(Sheet1)": "invalid column name \"Sheet1\"", + "=ROWS(Sheet1!A1!B1)": "invalid column name \"Sheet1\"", + "=ROWS(Sheet1!Sheet1)": "invalid column name \"Sheet1\"", // Web Functions // ENCODEURL "=ENCODEURL()": "ENCODEURL requires 1 argument", @@ -33,14 +33,14 @@ func ReadZipReader(r *zip.Reader) (map[string][]byte, int, error) { fileList := make(map[string][]byte, len(r.File)) worksheets := 0 for _, v := range r.File { - fileName := v.Name - if partName, ok := docPart[strings.ToLower(v.Name)]; ok { + fileName := strings.Replace(v.Name, "\\", "/", -1) + if partName, ok := docPart[strings.ToLower(fileName)]; ok { fileName = partName } if fileList[fileName], err = readFile(v); err != nil { return nil, 0, err } - if strings.HasPrefix(v.Name, "xl/worksheets/sheet") { + if strings.HasPrefix(fileName, "xl/worksheets/sheet") { worksheets++ } } @@ -157,6 +157,9 @@ func (f *File) workSheetWriter() { for k, v := range sheet.SheetData.Row { f.Sheet[p].SheetData.Row[k].C = trimCell(v.C) } + if sheet.SheetPr != nil || sheet.Drawing != nil || sheet.Hyperlinks != nil || sheet.Picture != nil || sheet.TableParts != nil { + f.addNameSpaces(p, SourceRelationship) + } // reusing buffer _ = encoder.Encode(sheet) f.saveFileList(p, replaceRelationshipsBytes(f.replaceNameSpaceBytes(p, buffer.Bytes()))) @@ -455,7 +458,11 @@ func (f *File) getSheetMap() map[string]string { // path, compatible with different types of relative paths in // workbook.xml.rels, for example: worksheets/sheet%d.xml // and /xl/worksheets/sheet%d.xml - path := filepath.ToSlash(strings.TrimPrefix(filepath.Clean(fmt.Sprintf("%s/%s", filepath.Dir(f.getWorkbookPath()), rel.Target)), "/")) + path := filepath.ToSlash(strings.TrimPrefix( + strings.Replace(filepath.Clean(fmt.Sprintf("%s/%s", filepath.Dir(f.getWorkbookPath()), rel.Target)), "\\", "/", -1), "/")) + if strings.HasPrefix(rel.Target, "/") { + path = filepath.ToSlash(strings.TrimPrefix(strings.Replace(filepath.Clean(rel.Target), "\\", "/", -1), "/")) + } if _, ok := f.XLSX[path]; ok { maps[v.Name] = path } diff --git a/test/Book1.xlsx b/test/Book1.xlsx Binary files differindex d5a0591..64c9e70 100644 --- a/test/Book1.xlsx +++ b/test/Book1.xlsx |