ClosedXML / ClosedXML/ClosedXML
Excel Export: Data Validation Alerts and Repair Issues
- 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

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