Repository: qax-os/excelize

Issue:
DeleteDataValidation Not Working On Existing Files
### Description

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


```go
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:
```go
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:

<img width="492" alt="Image" src="https://github.com/user-attachments/assets/16ca7277-71e9-4f42-9721-02f339f0a0bd" />

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

<img width="493" alt="Image" src="https://github.com/user-attachments/assets/e5d96978-21c0-4e34-8e8b-600ec1dfd163" />


### 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

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

### Validations

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

Hints from issue comments:
@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](https://github.com/user-attachments/files/20219990/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:
```go
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.@Kindred87: @DengY11 Thank you very much for getting the enhancement implemented so quickly.  Have a good day!

Solution patch:
diff --git a/datavalidation.go b/datavalidation.go
index ab61931..3b5a852 100644
--- a/datavalidation.go
+++ b/datavalidation.go
@@ -12,9 +12,11 @@
 package excelize
 
 import (
+	"encoding/xml"
 	"fmt"
 	"io"
 	"math"
+	"slices"
 	"strings"
 	"unicode/utf16"
 )
@@ -361,9 +363,27 @@ func getDataValidations(dvs *xlsxDataValidations) []*DataValidation {
 }
 
 // DeleteDataValidation delete data validation by given worksheet name and
-// reference sequence. This function is concurrency safe.
-// All data validations in the worksheet will be deleted
-// if not specify reference sequence parameter.
+// reference sequence. This function is concurrency safe. All data validations
+// in the worksheet will be deleted if not specify reference sequence parameter.
+//
+// Example 1, delete data validation on Sheet1!A1:B2:
+//
+//	err := f.DeleteDataValidation("Sheet1", "A1:B2")
+//
+// Example 2, delete data validations on Sheet1 with multiple cell ranges
+// A1:B2 and C1:C3 with reference sequence slice:
+//
+//	err := f.DeleteDataValidation("Sheet1", []string{"A1:B2", "C1:C3"}...)
+//
+// Example 3, delete data validations on Sheet1 with multiple cell ranges
+// A1:B2 and C1:C3 with blank separated reference sequence string, the result
+// same as example 2:
+//
+//	err := f.DeleteDataValidation("Sheet1", "A1:B2 C1:C3")
+//
+// Example 4, delete all data validations on Sheet1:
+//
+//	err := f.DeleteDataValidation("Sheet1")
 func (f *File) DeleteDataValidation(sheet string, sqref ...string) error {
 	ws, err := f.workSheetReader(sheet)
 	if err != nil {
@@ -371,17 +391,31 @@ func (f *File) DeleteDataValidation(sheet string, sqref ...string) error {
 	}
 	ws.mu.Lock()
 	defer ws.mu.Unlock()
-	if ws.DataValidations == nil {
+	if ws.DataValidations == nil && ws.ExtLst == nil {
 		return nil
 	}
 	if sqref == nil {
 		ws.DataValidations = nil
 		return nil
 	}
-	delCells, err := flatSqref(sqref[0])
+	delCells, err := flatSqref(strings.Join(sqref, " "))
 	if err != nil {
 		return err
 	}
+	if ws.DataValidations != nil {
+		if err = f.deleteDataValidation(ws, delCells); err != nil {
+			return err
+		}
+	}
+	if ws.ExtLst != nil {
+		return f.deleteX14DataValidation(ws, sqref)
+	}
+	return nil
+}
+
+// deleteDataValidation deletes data validation by given worksheet and cell
+// reference list.
+func (f *File) deleteDataValidation(ws *xlsxWorksheet, delCells map[int][][]int) error {
 	dv := ws.DataValidations
 	for i := 0; i < len(dv.DataValidation); i++ {
 		var applySqref []string
@@ -413,6 +447,64 @@ func (f *File) DeleteDataValidation(sheet string, sqref ...string) error {
 	return nil
 }
 
+// deleteX14DataValidation deletes data validation in the extLst element by
+// given worksheet and cell reference list.
+func (f *File) deleteX14DataValidation(ws *xlsxWorksheet, sqref []string) error {
+	var (
+		decodeExtLst          = new(decodeExtLst)
+		decodeDataValidations *xlsxDataValidations
+		x14DataValidations    *xlsxX14DataValidations
+	)
+	if err := f.xmlNewDecoder(strings.NewReader("<extLst>" + ws.ExtLst.Ext + "</extLst>")).
+		Decode(decodeExtLst); err != nil && err != io.EOF {
+		return err
+	}
+	for i, ext := range decodeExtLst.Ext {
+		if ext.URI == ExtURIDataValidations {
+			decodeDataValidations = new(xlsxDataValidations)
+			x14DataValidations = new(xlsxX14DataValidations)
+			_ = f.xmlNewDecoder(strings.NewReader(ext.Content)).Decode(decodeDataValidations)
+			x14DataValidations.XMLNSXM = NameSpaceSpreadSheetExcel2006Main.Value
+			x14DataValidations.DisablePrompts = decodeDataValidations.DisablePrompts
+			x14DataValidations.XWindow = decodeDataValidations.XWindow
+			x14DataValidations.YWindow = decodeDataValidations.YWindow
+			for _, dv := range decodeDataValidations.DataValidation {
+				if inStrSlice(sqref, dv.XMSqref, false) == -1 {
+					x14DataValidations.DataValidation = append(x14DataValidations.DataValidation, &xlsxX14DataValidation{
+						AllowBlank:       dv.AllowBlank,
+						Error:            dv.Error,
+						ErrorStyle:       dv.ErrorStyle,
+						ErrorTitle:       dv.ErrorTitle,
+						Operator:         dv.Operator,
+						Prompt:           dv.Prompt,
+						PromptTitle:      dv.PromptTitle,
+						ShowDropDown:     dv.ShowDropDown,
+						ShowErrorMessage: dv.ShowErrorMessage,
+						ShowInputMessage: dv.ShowInputMessage,
+						Sqref:            dv.Sqref,
+						XMSqref:          dv.XMSqref,
+						Type:             dv.Type,
+						Formula1:         dv.Formula1,
+						Formula2:         dv.Formula2,
+					})
+				}
+			}
+			x14DataValidations.Count = len(x14DataValidations.DataValidation)
+			x14DataValidationsBytes, _ := xml.Marshal(x14DataValidations)
+			decodeExtLst.Ext[i] = &xlsxExt{
+				xmlns: []xml.Attr{{Name: xml.Name{Local: "xmlns:" + NameSpaceSpreadSheetX14.Name.Local}, Value: NameSpaceSpreadSheetX14.Value}},
+				URI:   ExtURIDataValidations, Content: string(x14DataValidationsBytes),
+			}
+			if x14DataValidations.Count == 0 {
+				decodeExtLst.Ext = slices.Delete(decodeExtLst.Ext, i, i+1)
+			}
+		}
+	}
+	extLstBytes, err := xml.Marshal(decodeExtLst)
+	ws.ExtLst = &xlsxExtLst{Ext: strings.TrimSuffix(strings.TrimPrefix(string(extLstBytes), "<extLst>"), "</extLst>")}
+	return err
+}
+
 // squashSqref generates cell reference sequence by given cells coordinates list.
 func squashSqref(cells [][]int) []string {
 	if len(cells) == 1 {
diff --git a/xmlWorksheet.go b/xmlWorksheet.go
index dd22cb5..ed4f734 100644
--- a/xmlWorksheet.go
+++ b/xmlWorksheet.go
@@ -448,6 +448,39 @@ type xlsxDataValidation struct {
 	Formula2         *xlsxInnerXML `xml:"formula2"`
 }
 
+// xlsxX14DataValidation directly maps the single item of data validation
+// defined on a extLst element of the worksheet.
+type xlsxX14DataValidation struct {
+	XMLName          xml.Name      `xml:"x14:dataValidation"`
+	AllowBlank       bool          `xml:"allowBlank,attr"`
+	Error            *string       `xml:"error,attr"`
+	ErrorStyle       *string       `xml:"errorStyle,attr"`
+	ErrorTitle       *string       `xml:"errorTitle,attr"`
+	Operator         string        `xml:"operator,attr,omitempty"`
+	Prompt           *string       `xml:"prompt,attr"`
+	PromptTitle      *string       `xml:"promptTitle,attr"`
+	ShowDropDown     bool          `xml:"showDropDown,attr,omitempty"`
+	ShowErrorMessage bool          `xml:"showErrorMessage,attr,omitempty"`
+	ShowInputMessage bool          `xml:"showInputMessage,attr,omitempty"`
+	Sqref            string        `xml:"sqref,attr"`
+	Type             string        `xml:"type,attr,omitempty"`
+	Formula1         *xlsxInnerXML `xml:"x14:formula1"`
+	Formula2         *xlsxInnerXML `xml:"x14:formula2"`
+	XMSqref          string        `xml:"xm:sqref,omitempty"`
+}
+
+// xlsxX14DataValidations expresses all data validation information for cells in
+// a sheet extLst element which have data validation features applied.
+type xlsxX14DataValidations struct {
+	XMLName        xml.Name `xml:"x14:dataValidations"`
+	XMLNSXM        string   `xml:"xmlns:xm,attr,omitempty"`
+	Count          int      `xml:"count,attr,omitempty"`
+	DisablePrompts bool     `xml:"disablePrompts,attr,omitempty"`
+	XWindow        int      `xml:"xWindow,attr,omitempty"`
+	YWindow        int      `xml:"yWindow,attr,omitempty"`
+	DataValidation []*xlsxX14DataValidation
+}
+
 // xlsxC collection represents a cell in the worksheet. Information about the
 // cell's location (reference), value, data type, formatting, and formula is
 // expressed here.



Test patch:
diff --git a/datavalidation_test.go b/datavalidation_test.go
index f58f5a7..a979e2b 100644
--- a/datavalidation_test.go
+++ b/datavalidation_test.go
@@ -16,6 +16,7 @@ import (
 	"math"
 	"path/filepath"
 	"strings"
+	"sync"
 	"testing"
 
 	"github.com/stretchr/testify/assert"
@@ -242,4 +243,31 @@ func TestDeleteDataValidation(t *testing.T) {
 	// Test delete all data validations in the worksheet
 	assert.NoError(t, f.DeleteDataValidation("Sheet1"))
 	assert.Nil(t, ws.(*xlsxWorksheet).DataValidations)
+
+	t.Run("delete_data_validation_from_extLst", func(t *testing.T) {
+		f := NewFile()
+		f.Sheet.Delete("xl/worksheets/sheet1.xml")
+		f.Pkg.Store("xl/worksheets/sheet1.xml", fmt.Appendf(nil,
+			`<worksheet xmlns="%s"><sheetData/><extLst><ext xmlns:x14="%s" uri="%s"><x14:dataValidations xmlns:xm="%s" count="2"><x14:dataValidation allowBlank="true" showErrorMessage="true" showInputMessage="true" sqref="" type="list"><xm:sqref>A1:A2</xm:sqref><x14:formula1><xm:f>Sheet1!$A$2:$A$4</xm:f></x14:formula1></x14:dataValidation><x14:dataValidation allowBlank="true" showErrorMessage="true" showInputMessage="true" sqref="" type="list"><xm:sqref>B1:B2</xm:sqref><x14:formula1><xm:f>Sheet1!$B$2:$B$3</xm:f></x14:formula1></x14:dataValidation></x14:dataValidations></ext></extLst></worksheet>`,
+			NameSpaceSpreadSheet.Value, NameSpaceSpreadSheetExcel2006Main.Value,
+			ExtURIDataValidations, NameSpaceSpreadSheetExcel2006Main.Value))
+		f.checked = sync.Map{}
+		assert.NoError(t, f.DeleteDataValidation("Sheet1", "A1:A2"))
+		dvs, err := f.GetDataValidations("Sheet1")
+		assert.NoError(t, err)
+		assert.Len(t, dvs, 1)
+		assert.Equal(t, "B1:B2", dvs[0].Sqref)
+
+		assert.NoError(t, f.DeleteDataValidation("Sheet1", "B1:B2"))
+		dvs, err = f.GetDataValidations("Sheet1")
+		assert.NoError(t, err)
+		assert.Empty(t, dvs)
+	})
+
+	t.Run("delete_data_validation_failed_from_extLst", func(t *testing.T) {
+		f := NewFile()
+		assert.EqualError(t, f.deleteX14DataValidation(&xlsxWorksheet{
+			ExtLst: &xlsxExtLst{Ext: "<extLst><x14:dataValidations></x14:dataValidation></x14:dataValidations></ext></extLst>"},
+		}, nil), "XML syntax error on line 1: element <dataValidations> closed by </dataValidation>")
+	})
 }