Hey there, let's dig into why that string-based List overload is causing your Excel file to get corrupted! I've spotted a couple of key issues in your code and approach:

1. Invalid String Format (Plus a Syntax Error in the Wiki Example) #

First off, the official Wiki code you referenced has a syntax typo that would generate a malformed string:

// Original (broken) Wiki code
var validOptions = $"\"{String.Join(",", options)}\";

This line is missing a closing double quote and curly brace, which would produce an incomplete string like "Option1,Option2,Option3 (instead of the correct "Option1,Option2,Option3").

Even with that fixed, ClosedXML's List(string list, bool inCellDropdown) overload expects a string that matches Excel's native data validation sequence format:

• For options without special characters (like commas or spaces), you don't need outer quotes—just use comma-separated values directly, e.g., "one,two,three".
• If your options include commas or special characters, wrap each individual option in double quotes, e.g., "\"one,1\",\"two,2\",\"three,3\"".

2. Repeated Calls to SetDataValidation() #

Your code calls SetDataValidation() multiple times for the same column:

// This creates/overwrites the data validation object each time
wb.Worksheet(1).Column(11).SetDataValidation().List(..., true);
wb.Worksheet(1).Column(11).SetDataValidation().IgnoreBlanks = true;

While ClosedXML should return the existing data validation object on subsequent calls, this pattern can lead to unexpected conflicts or incomplete rule definitions, which can also trigger Excel's corruption warnings.

Fixed Code Example #

Here's the corrected approach—we'll grab the data validation object once, set all properties in one go, and use the proper string format:

using (XLWorkbook wb = new XLWorkbook()) {
    wb.Worksheets.Add(dt);
    wb.Worksheets.Add(dt2);
    
    var worksheet1 = wb.Worksheet(1);
    // Get the data validation object once
    var dataValidation = worksheet1.Column(11).SetDataValidation();
    
    // Use the correct comma-separated string format
    dataValidation.List("one,two,three", true);
    dataValidation.IgnoreBlanks = true;
    dataValidation.InCellDropdown = true;

    wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
    wb.Style.Font.Bold = true;
    wb.SaveAs(targetFile);
}

If you need to handle options with commas, use this approach to build the valid string:

var options = new List<string> { "Apple, Red", "Banana, Yellow", "Grape, Purple" };
var validOptions = string.Join(",", options.Select(opt => $"\"{opt}\""));
dataValidation.List(validOptions, true);

Why This Fixes the Corruption #

The malformed string from the syntax error (or incorrect format) creates an invalid OOXML structure in the Excel file. Excel detects this invalid data validation rule and flags the file as corrupted. By fixing the string format and consolidating your data validation setup into a single object, you ensure the generated file adheres to Excel's formatting rules.

内容的提问来源于stack exchange，提问作者Gaurav Parek