I've got a issue when create a .xlsx file with dropdown list cell, the code is pasted hereunder.
The code is succeeded with no exception, but when I open the file with my local excel365 client, the excel prompts a dialog and says "We found a problem with some content in "test_export.xlsx". Do you want use to try to recover as much as we can? If you trust the source of this workbook, click Yes".
If I click Yes, the file can be opened but the dropdown cell has no dropdown. If I click No, the file won't be opened.
I've tried several times, seems it related to the length of the dropdown items. Please help to investigate.
BTW, I'm using Spire.XLS 11.3.4
- Code: Select all
public static void GetWorkbookWithDropDownCell()
{
var items = new List<string>()
{
"TestCase1",
"TestCase2",
"TestCase3",
"TestCase4",
"TestCase5",
"TestCase6",
"TestCase7",
"TestCase8",
"TestCase9",
"TestCase10",
"TestCase11",
"TestCase12",
"TestCase13",
"TestCase14",
"TestCase15",
"TestCase16",
"TestCase17",
"TestCase18",
"TestCase19",
"TestCase20",
"TestCase21",
"TestCase22",
"TestCase23",
"TestCase24",
"TestCase25",
"TestCase26",
"TestCase27",
"TestCase28",
"TestCase29",
"TestCase30",
"TestCase31",
"TestCase32",
"TestCase33",
"TestCase34",
"TestCase35",
"TestCase36",
"TestCase37",
"TestCase38",
"TestCase39",
"TestCase40",
"TestCase41",
"TestCase42",
"TestCase43",
"TestCase44",
"TestCase45",
"TestCase46",
"TestCase47",
"TestCase48",
"TestCase49",
"TestCase50",
};
var workbook = new Workbook();
workbook.Version = ExcelVersion.Version2016;
var workSheet = workbook.Worksheets[0];
var indexOfFirstRow = workSheet.FirstRow;
workSheet.SetValue(1, 1, "Name");
workSheet.SetValue(1, 2, "Value");
workSheet.SetValue(2, 1, "TestCaseName");
var cell = workSheet.Rows[1].CellList[1];
cell.Text = items?.First();
cell.DataValidation.IsListInFormula = true;
cell.DataValidation.AllowType = CellDataType.Formula;
cell.DataValidation.Values = items.Distinct().ToArray();
cell.DataValidation.IsSuppressDropDownArrow = false;
workbook.SaveToFile("C:\\test_export.xlsx", ExcelVersion.Version2016);
}