DeleteDataValidation Not Working On Existing Files

[Kindred87]

Description

DeleteDataValidation does not work as expected. When calling it with sqrefs collected from a GetDataValidations call on the same sheet:

validations, err := workbook.GetDataValidations(sheetName)
if err != nil {
    return fmt.Errorf("error getting data validations: %w", err)
}

var origSqrefs []string
for _, v := range validations {
    origSqrefs = append(origSqrefs, v.Sqref)
}

if len(origSqrefs) > 0 {
    if err := workbook.DeleteDataValidation(sheetName, origSqrefs...); err != nil {
        return fmt.Errorf("error deleting original data validations: %w", err)
    }
}

The data validations remain in the sheet post-deletion. Even after saving the file.

While debugging, I noticed that the delete function returns early. The object returned by the f.workSheetReader(sheet) call has a DataValidations field that is nil. Which causes the function to return nil before performing any work. This is unexpected since the GetDataValidations() call I made before, on the same sheet, successfully returned data validations:

func (f *File) DeleteDataValidation(sheet string, sqref ...string) error {
    ws, err := f.workSheetReader(sheet)
    if err != nil {
        return err
    }
    ws.mu.Lock()
    defer ws.mu.Unlock()
    if ws.DataValidations == nil {
        return nil  <--------------------------------- RETURNS EARLY HERE
    }
    if sqref == nil {
        ws.DataValidations = nil
        return nil
    }
}

Variables in the DeleteDataValidation call at runtime:

Variables in my code snippet (the one that calls DeleteDataValidation) at runtime:

Steps to reproduce the issue

1. Open an existing Excel file with a sheet containing data validations.
2. Call workbook.GetDataValidations on the sheet containing the data validations.
3. Pass the Sqrefs from the returned data validations to a workbook.DeleteDataValidations call, on the same sheet.
4. Pause a debugger on line 369 in datavalidation.go
5. Observe the empty DataValidations member of variable ws

Describe the results you received

No deletion of specified data validations.

Describe the results you expected

Deletion of specified data validations

Go version

1.24.3

Excelize version or commit ID

2.9.1

Environment

Windows 11, Microsoft® Excel® for Microsoft 365 MSO (Version 2502 Build 16.0.18526.20286) 64-bit

Validations

• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• The provided reproduction is a minimal reproducible example of the bug.

[Kindred87]

Attached example program and files. Zipped archive contains a main.go file and two spreadsheets, one for manual reference and the other for main.go to access.

Excelize Bug.zip

The output should resemble:

go run main.go
2025/05/14 22:10:48 Collected 6 data validations from Formula
2025/05/14 22:10:48 Data Validations Sqrefs:
2025/05/14 22:10:48   C11:D1048576
2025/05/14 22:10:48   G11:H1048576
2025/05/14 22:10:48   H11:I1048576
2025/05/14 22:10:48   N11:O1048576
2025/05/14 22:10:48   P11:Q1048576
2025/05/14 22:10:48   R11:S1048576
2025/05/14 22:10:48 Collected 6 data validations from Formula after deletion
2025/05/14 22:10:48 Data Validations Sqrefs:
2025/05/14 22:10:48   C11:D1048576
2025/05/14 22:10:48   G11:H1048576
2025/05/14 22:10:48   H11:I1048576
2025/05/14 22:10:48   N11:O1048576
2025/05/14 22:10:48   P11:Q1048576
2025/05/14 22:10:48   R11:S1048576

main.go contents for reference:

package main

import (
	"log"

	"github.com/xuri/excelize/v2"
)

func main() {
	wb, err := excelize.OpenFile("Example.xlsx")
	if err != nil {
		log.Fatalf("error opening file: %v", err)
	}
	defer func() {
		if err := wb.Close(); err != nil {
			log.Fatalf("error closing file: %v", err)
		}
	}()

	sheetName := "Formula"

	validations, err := wb.GetDataValidations(sheetName)
	if err != nil {
		log.Fatalf("error getting data validations: %v", err)
	}

	log.Printf("Collected %d data validations from %s\n", len(validations), sheetName)
	log.Println("Data Validations Sqrefs:")
	for _, validation := range validations {
		log.Printf("  %s\n", validation.Sqref)
	}

	if err := wb.DeleteDataValidation(sheetName, validations[0].Sqref); err != nil {
		log.Fatalf("error deleting data validation %s: %v", validations[0].Sqref, err)
	}

	if err := wb.SaveAs("Example.xlsx"); err != nil {
		log.Fatalf("error saving file: %v", err)
	}

	postDeleteValidations1, err := wb.GetDataValidations(sheetName)
	if err != nil {
		log.Fatalf("error getting data validations: %v", err)
	}

	log.Printf("Collected %d data validations from %s after deletion\n", len(postDeleteValidations1), sheetName)
	log.Println("Data Validations Sqrefs:")
	for _, validation := range postDeleteValidations1 {
		log.Printf("  %s\n", validation.Sqref)
	}

}

[xuri]

Thanks for your issue. This library doesn't support delete data validations in the ext list currently. I'll add support for this later.

[xuri]

Pull request #2137 added support for delete data validations in the ext list. Please upgrade to the master branch code by go get github.com/xuri/excelize/v2@master, and this feature will be released on the next version.