From 7ac37edfebebc9bee201fad001e2f2f8b780a9a8 Mon Sep 17 00:00:00 2001 From: Arnie97 Date: Sat, 31 Jul 2021 00:31:51 +0800 Subject: 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. --- datavalidation.go | 33 +++++++++++++++++++-------------- 1 file changed, 19 insertions(+), 14 deletions(-) (limited to 'datavalidation.go') 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( + `&`, `&`, + `<`, `<`, + `>`, `>`, + `"`, `""`, +) + // 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(`"%s"`, 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("%.17g", f1) + dd.Formula2 = fmt.Sprintf("%.17g", f2) dd.Type = convDataValidationType(t) dd.Operator = convDataValidationOperatior(o) return nil -- cgit v1.2.1