Fix data validation issues (#975)

* Fix `SetDropList` to allow XML special characters

* This closes #971, allow quotation marks in SetDropList()

This patch included a XML entity mapping table instead of
xml.EscapeText() to be fully compatible with Microsoft Excel.

* This closes #972, allow more than 255 bytes of validation formulas

This patch changed the string length calculation unit of data
validation formulas from UTF-8 bytes to UTF-16 code units.

* Add unit tests for SetDropList()

* Fix: allow MaxFloat64 to be used in validation range

17 decimal significant digits should be more than enough to represent
every IEEE-754 double-precision float number without losing precision,
and numbers in this form will never reach the Excel limitation of 255
UTF-16 code units.

1 files changed, 19 insertions, 14 deletions

diff --git a/datavalidation.go b/datavalidation.go
index a95f4d0..04dbe25 100644
--- a/datavalidation.go
+++ b/datavalidation.go
@@ -13,6 +13,7 @@ package excelize
 
 import (
 	"fmt"
+	"math"
 	"strings"
 	"unicode/utf16"
 )
@@ -35,10 +36,8 @@ const (
 )
 
 const (
-	// dataValidationFormulaStrLen 255 characters+ 2 quotes
-	dataValidationFormulaStrLen = 257
-	// dataValidationFormulaStrLenErr
-	dataValidationFormulaStrLenErr = "data validation must be 0-255 characters"
+	// dataValidationFormulaStrLen 255 characters
+	dataValidationFormulaStrLen = 255
 )
 
 // DataValidationErrorStyle defined the style of data validation error alert.
@@ -75,6 +74,15 @@ const (
 	DataValidationOperatorNotEqual
 )
 
+// formulaEscaper mimics the Excel escaping rules for data validation,
+// which converts `"` to `""` instead of `"`.
+var formulaEscaper = strings.NewReplacer(
+	`&`, `&`,
+	`<`, `&lt;`,
+	`>`, `&gt;`,
+	`"`, `""`,
+)
+
 // NewDataValidation return data validation struct.
 func NewDataValidation(allowBlank bool) *DataValidation {
 	return &DataValidation{
@@ -111,25 +119,22 @@ func (dd *DataValidation) SetInput(title, msg string) {
 
 // SetDropList data validation list.
 func (dd *DataValidation) SetDropList(keys []string) error {
-	formula := "\"" + strings.Join(keys, ",") + "\""
+	formula := strings.Join(keys, ",")
 	if dataValidationFormulaStrLen < len(utf16.Encode([]rune(formula))) {
-		return fmt.Errorf(dataValidationFormulaStrLenErr)
+		return ErrDataValidationFormulaLenth
 	}
-	dd.Formula1 = formula
+	dd.Formula1 = fmt.Sprintf(`<formula1>"%s"</formula1>`, formulaEscaper.Replace(formula))
 	dd.Type = convDataValidationType(typeList)
 	return nil
 }
 
 // SetRange provides function to set data validation range in drop list.
 func (dd *DataValidation) SetRange(f1, f2 float64, t DataValidationType, o DataValidationOperator) error {
-	formula1 := fmt.Sprintf("%f", f1)
-	formula2 := fmt.Sprintf("%f", f2)
-	if dataValidationFormulaStrLen < len(utf16.Encode([]rune(dd.Formula1))) || dataValidationFormulaStrLen < len(utf16.Encode([]rune(dd.Formula2))) {
-		return fmt.Errorf(dataValidationFormulaStrLenErr)
+	if math.Abs(f1) > math.MaxFloat32 || math.Abs(f2) > math.MaxFloat32 {
+		return ErrDataValidationRange
 	}
-
-	dd.Formula1 = formula1
-	dd.Formula2 = formula2
+	dd.Formula1 = fmt.Sprintf("<formula1>%.17g</formula1>", f1)
+	dd.Formula2 = fmt.Sprintf("<formula2>%.17g</formula2>", f2)
 	dd.Type = convDataValidationType(t)
 	dd.Operator = convDataValidationOperatior(o)
 	return nil