Spire.XLS is a professional Excel .NET library that can be used to any type of .NET( C#, VB.NET, ASP.NET, .NET Core) application and Java (J2SE and J2EE) application.

Tue Apr 13, 2021 1:50 pm

Hi,

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);
}

xiangdejun
 
Posts: 1
Joined: Wed Mar 26, 2014 9:37 am

Thu Apr 15, 2021 1:13 am

Hello,

Thanks for you inquiry.
I tested your case and did reproduce your issue. I have logged it in our bug tracking system with the ticket SPIREXLS-3203. If there is any update, we will let you know. Apologize for the inconvenience caused.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1224
Joined: Mon Oct 19, 2020 3:04 am

Sat May 08, 2021 7:43 am

Hello,

Thanks for your patience.
Regarding the issue SPIREXLS-3203, after further investigation, we found it is caused by the number of characters you set for DataValidation value exceeding the maximum limit. Our Spire.XLS is based on Microsoft Excel, and it limits the DataValidation source to no more than 255 characters. Therefore, when you set more than 255 characters, there will be problems with the content of the generated Excel document. Hope you can understand.
screenshot.png
screenshot.png (26.73 KiB) Viewed 649 times


Sincerely,
Brain
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1224
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS