[BUG] The exported table inserts a drop-down menu for the cell. The cell cannot display drop-down options

[Lin-Min]

🐛 Bug Report

Lib version: 4.3.0

Steps To Reproduce

  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("sheet1");
  const colums = [
    {
      header: "测试1",
      key: "test1",
      width: 25
    },
    {
      header: "测试2",
      key: "test2",
      width: 25
    },
    {
      header: "测试3",
      key: "test3",
      width: 25
    },
    {
      header: "测试4",
      key: "test4",
      width: 25
    }
  ];
  ws.columns = colums;
  const cell = ws.getCell("A2");
  cell.dataValidation = {
    type: "list",
    allowBlank: true,
    formulae: [
      '"1234 Main St, Anytown 56789,5678 Elm Ave, Smallville 23456,9012 Oak Rd, Big City 34567,3456 Pine Dr, Tinytown 67890,7890 Maple Ln, Mediumtown 12345,2345 Hickory St, Suburbia 45678,6789 Cedar Cir, Urbanville 89012,0123 Birch Blvd, Countryside 23456,4567 Walnut Way, Metropolis 78901,8901 Cherry Lane, Hamlet 23456,2345 Poplar Place, Cityville 78901,6789 Spruce Street, Villagetown 12345,0123 Juniper Blvd, Boroughville 45678,4567 Acacia Court, Metroville 89012,8901 Sycamore Ave, Thetown 23456,2345 Cypress Circle, Hometown 78901,6789 Chestnut Sq, Thecity 12345,0123 Laurel Ave, Villageville 45678,4567 Grove Road, Lakeville 89012,8901 Flower Lane, Districtville 23456"'
    ]
  };
  wb.xlsx.writeBuffer().then((buffer) => {

    const blob = new Blob([buffer], {
      type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    });

    const link = document.createElement("a");
    link.href = window.URL.createObjectURL(blob);
    link.download = "test.xlsx";
    link.click();
  });

This is my duplicate address

The expected behaviour: Cells normally display drop-down options

Possible solution (optional, but very helpful):

[zurmokeeper]

@Lin-Min It may be because in Excel, a single cell set the maximum length of the data validation drop-down list string of 255 characters, more than this number of characters limit, it will not be able to properly display the drop-down list options.

Change the content of your formulae to

'"1234 Main St, Anytown 56789,5678 Elm Ave, Smallville 23456,9012 Oak Rd, Big City 34567,3456 Pine Dr, Tinytown 67890,7890 Maple Ln, Mediumtown 12345. 2345 Hickory St, Suburbia 45678,6789 Cedar Cir, Urbanville 89012,0123 Birch Blvd, Countryside 23456,4567 Wa"'

Less than or equal to 255 can be displayed normally, more than 255 will not be displayed normally

[Lin-Min]

@Lin-Min可能是因为在Excel中，单个单元格设置了数据验证下拉列表字符串的最大长度255，超过这个字符数限制，将无法正常显示下拉列表选项。

将您的内容更改formulae为

'"1234 Main St, Anytown 56789,5678 Elm Ave, Smallville 23456,9012 Oak Rd, Big City 34567,3456 Pine Dr, Tinytown 67890,7890 Maple Ln, Mediumtown 12345. 2345 Hickory St, Suburbia 45678,6789 Cedar Cir, Urbanville 89012,0123 Birch Blvd, Countryside 23456,4567 Wa"'

小于等于255可以正常显示，大于255不能正常显示

I don't think so, I think it is a bug, even if you said there is 255 characters limit, then I can not normally display the drop-down menu options with the data you provided, you can see my test link

[zurmokeeper]

@Lin-Min
Sorry, I copied it wrong when I copied it, here
Mediumtown 12345. 2345 Hickory St,
should be
Mediumtown 12345,2345 Hickory St,

Try this again
'"1234 Main St, Anytown 56789,5678 Elm Ave, Smallville 23456,9012 Oak Rd, Big City 34567,3456 Pine Dr, Tinytown 67890,7890 Maple Ln, Mediumtown 12345,2345 Hickory St, Suburbia 45678,6789 Cedar Cir, Urbanville 89012,0123 Birch Blvd, Countryside 23456,4567 Wal"'

In fact, it does not matter what string is inside, do not exceed 255 characters on the good, I gave you the one due to the wrong copy, resulting in the length has exceeded 255, you can output the length of the string to see for yourself

[Lin-Min]

As you said, there is a character length limit. I hope you can add an optimization check that the length exceeds 255 and throw a warning to remind the user, otherwise the user may feel the bug. For this character is too long I find the way to the next in the drop-down menu on another sheet reference: formulae: [= Sheet2! $A $2: $A $${reportAddressOptions. Length + 1}]. Thank you for your reply. Thank you!

[zurmokeeper]

@Lin-Min This is a great idea for a PR, and I'd like to ask about the use of formulae: ['$D$5:$F$5'] to avoid the problem of length limits, is that what you mean?

[Lin-Min]

Yes, I now use Formulae: ['$ D $ 5: $ F $ 5'] to avoid the problem of length limit. I hope that this reminder can be added to the official documentation so that users can timely discover the length of length limit.And when the user inserts the drop -down option in the form of character forms, if the character length exceeds 255, you can throw a warning warning that the user's character is too long, and the table cannot show the drop -down option. Please change another way and check the official documentation in detail.

[zurmokeeper]

@Lin-Min #2264 Do you want this PR like this?