Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Data validation formula length limit is much stricter than Microsoft Excel #972

Closed
Arnie97 opened this issue Jul 27, 2021 · 1 comment
Closed
Labels
confirmed This issue can be reproduced

Comments

@Arnie97
Copy link
Contributor

Arnie97 commented Jul 27, 2021

Description

Some valid data validation patterns with non-ASCII characters were rejected by Excelize.

Steps to reproduce the issue:

package main

import (
	"strings"
	"github.com/360EntSecGroup-Skylar/excelize/v2"
)

func main() {
	var cjkStrings = []string{
		strings.Repeat("漢", 255),
	}
	newWorkbookWithDropList(cjkStrings)
}

func newWorkbookWithDropList(choices []string) {
	f := excelize.NewFile()
	dv := excelize.NewDataValidation(false)
	dv.Sqref = "A:A"
	if err := dv.SetDropList(choices); err != nil {
		panic(err)
	}
	f.AddDataValidation(f.GetSheetName(0), dv)
	f.SaveAs("TestDropList.xlsx")
}

Describe the results you received:

panic: data validation must be 0-255 characters

Describe the results you expected:

Produce a file TestDropList.xlsx, which could be loaded in Microsoft Excel successfully

Output of go version:

go version go1.17rc1 linux/amd64

Excelize version or commit ID:

Environment details (OS, Microsoft Excel™ version, etc.):

The data validation pattern provided above were valid in all the following implementations, but not Excelize:

  • Microsoft Office 365 for Mac, 16.49 (21050901)
  • Microsoft Office 365 for Windows, 2102 (Build 13801.20808)
  • Microsoft Office 2019 Professional Plus for Windows, 2105 (Build 14026.20302)
  • Microsoft Office 2013 Professional Plus for Windows, 15.0.4919.1002
@xuri xuri added confirmed This issue can be reproduced in progress Working in progress labels Jul 28, 2021
@Arnie97 Arnie97 mentioned this issue Jul 28, 2021
10 tasks
@xuri xuri closed this as completed in 7dbf88f Jul 28, 2021
@xuri
Copy link
Member

xuri commented Jul 28, 2021

Thanks for your feedback, I have fixed it, please try to use the master branch code, and this patch will be released in the next version.

@xuri xuri removed the in progress Working in progress label Jul 28, 2021
xuri pushed a commit that referenced this issue Jul 30, 2021
* 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.
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
- Escape XML character in the drop list
- Fix incorrect character count limit in the drop list
- Fix Excel time parse issue in some case
- Fix custom number format month parse issue in some case
- Fix corrupted file generated caused by concurrency adding pictures
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
* Fix `SetDropList` to allow XML special characters

* This closes qax-os#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 qax-os#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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
confirmed This issue can be reproduced
Projects
None yet
Development

No branches or pull requests

2 participants