Hi all,
Lately I have been working on making a dynamic Excel sheet with dropdown fields. This plugin works wonders in making this happen. However, there is one (known) limitation; Excel only allows 255 string characters for defining the dropdown list.
In my use case, I want a user to be able to select a value within a dropdown which can contain over 200 items. Each item will be a text value of around 6-14 characters. Some quick calculations show this goes way beyond the 255 character limit (not even accounting for the commas to separate values).
I read on a forum (not related to OutSystems) that a workaround for the 255-character limit is to print the list in a different excel sheet (tab), then reference the location of the list values instead of the values themselves. In this way, you can reduce the character size in the dropdown from hundreds of characters down to "Sheetname!A1:A200", or similar syntax.
My question: is it possible to reference locations instead of values for the dropdown? If so, how can I do this?
Thanks in advance!
Gr
Okay, I am too committed in this journey now to not implement a solution for this issue.
After some digging, I decided to alter the C# code to make it work how I want it to.
I added a new functionality and inputs:
As you can see in the image, there is now a new (mandatory) Boolean which defines if you either use a list of values or you use an address within Excel. Furthermore, a new variable is added (ItemsAddress) where you can define the string of a location to use the new functionality.
In this example, I use the new functionality. This will create a location string instead of a list of strings that is added as values.
Back in Excel we get this result:
It was a fun journey to edit your code and compile my own C# addition. It has been a few years since I have last done so, thus it was a great way to get familiar again.
@Craig St.Jean I feel like sharing is caring. I would love to add this new functionality to your plugin. Please contact me by DM/PM to make this happen!
If anyone is reading this out of pure curiosity, no I have not lost my mind, you can read this forum post as a blog of someone rediscovering the steps made by the author of this plugin.
Hey Jochem,
I'm terribly sorry for my delayed response, but it seems the delay was fruitful! Well done on working through this problem, and I have added you to the project team ;)
Hi,
I need to know how to solve the error message by implementing this logic i have 20 records but now it only allow 10 records in dropdown list if i give more than that it display error message. So guide me how to add parameter in .net.
Thanks,
Prasath
Hi Prasath,
As explained in my earlier posts, use the ItemAddress instead of lists (if your list is longer than 255 chars). To do so, first import your list somewhere into your Excel using the normal Extended Excel functionalities, then use the ItemAddress field to point to its location.
I am no longer active as a OS dev, so for more questions please ask some of the other devs.
now it working fine. Thanks for your reply
Update: I have been playing around with this some more. Currently, I am making lists of values in a separate worksheet using Cell_WriteRange. From there, I set the "value" of a list to its location as seen here:
Doing so however, gives this result in Excel:
This is where it gets strange though; if I open the Data validation popup in Excel and check the value (see screenshot) there is nothing preventing from showing the list by location.
Furthermore, when I hit OK on this popup, the data validation changes and it actually works!
Although this is nice, this is not the intended end behavior as I want to show the user the list without them having to open the Data Validation popup in Excel.
Is there a workaround for this issue?
Another update:
I looked into the source code of the extension, to maybe find a solution myself.
First thing I noticed is that the plugin is based on the OfficeOpenXml.DataValidation.Contracts package from the EPPlus library.
As I scanned through this library, I found the construct that is used to form the list:
The promising part is that the code mentions the possibility to use an address as an input. However, in the current state, the plugin interprets every input as part of a list and thus creates a list of elements.
I have to admit that my current knowledge regarding this library ends here. I hope one of the authors can help me at this point!