ClosedXML / ClosedXML/ClosedXML

Excel Export: Data Validation Alerts and Repair Issues

Open
#2,594 10 comments 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
5.7k
Forks
936
Avg merge
1d 10h
Merged PRs (30d)
5

Description

## Read and complete the full issue template

Do not randomly delete sections. They are here for a reason.

**Do you want to request a *feature* or report a *bug*?**
- [x] Bug
- [ ] Feature
- [ ] Question

**Did you test against the latest CI build?**
- [x] Yes
- [ ] No

If you answered `No`, please test with [the latest development build](https://ci.appveyor.com/project/ClosedXML/ClosedXML/branch/develop/artifacts) first.

**Version of ClosedXML**

0.102.2

**What is the current behavior?**

While exporting an Excel sheet from the application, the following popup appears upon opening the file:

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Removed Records: Data validation from /xl/worksheets/sheet1.xml part
Removed Records: Data validation from /xl/worksheets/sheet2.xml part
Removed Records: Data validation from /xl/worksheets/sheet3.xml part
Removed Records: Data validation from /xl/worksheets/sheet4.xml part
Removed Records: Data validation from /xl/worksheets/sheet7.xml part
Removed Records: Data validation from /xl/worksheets/sheet8.xml part
Removed Records: Data validation from /xl/worksheets/sheet9.xml part
Removed Records: Data validation from /xl/worksheets/sheet10.xml part
Removed Records: Data validation from /xl/worksheets/sheet11.xml part
Removed Records: Data validation from /xl/worksheets/sheet13.xml part
Removed Records: Data validation from /xl/worksheets/sheet14.xml part
Removed Records: Data validation from /xl/worksheets/sheet15.xml part
Removed Records: Data validation from /xl/worksheets/sheet16.xml part
Removed Records: DRemoved Records: DRemoved Records: D

**What is the expected behavior or new feature?**

Since there is no data loss in the Excel file, validation popups should not appear when the user opens it.

**Is this a regression from the previous version?**

The same issue persists with the latest version of ClosedXML (0.104.2)

## Reproducibility
With the below logic and the closedXML version I've mentioned or with the latest version issue can be reproducible. Below is the screenshot
![snip1](https://github.com/user-attachments/assets/ac1cf355-be8f-4ad5-b02c-47ed6aa7e5e4)

**Code to reproduce problem:**

private void main()
{
if (list != null && list.Count() > 0)
{
// Here we are capturing validations into a dictionary for each column that is a List
Dictionary Validation = new Dictionary();
int columnCount = worksheet.Row((firstTableRow - 1)).CellsUsed().Count();
IXLCell xLCell;
for (int col = 1; col <= columnCount; col++)
{
xLCell = worksheet.Cell(firstTableRow, col);
IXLDataValidation xLDataValidation = xLCell.GetDataValidation();
if (xLDataValidation.AllowedValues == XLAllowedValues.List)
{
Validation.Add(col, xLDataValidation.MinValue);
}
}

worksheet.Cell(firstTableRow, 1).InsertData(list);

//Here we are updating datavalidation values from dictionary to excel after Inserting data
for (int col = 1; col <= columnCount; col++)
{
xLCell = worksheet.Cell(firstTableRow, col);
if (Validation.TryGetValue(col, out string value))
{
xLCell.GetDataValidation().AllowedValues = XLAllowedValues.List;
xLCell.GetDataValidation().MinValue = value;
}
}
}
}
```

- [ ] I attached a sample spreadsheet. (You can drag files on to this issue)

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the issue with the supplied GetDataValidation, InsertData, and XLAllowedValues.List sequence, then inspect ClosedXML's data-validation handling during worksheet data insertion and XLSX export. Done means the reproduced workbook opens in Excel without data-validation repair warnings and retains its validations.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
backend
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.