Excel in-cell dropdown

[hansie-the-quorum]

I have this code below. The purpose of this code is to set very specific cell's to have drop down values. Not all cells in the column must have this dropdown and the data types differ for each cell.

This works fine and excel is created.

The issue here is that in Excel, for each cell that have a dropdown applied you need to:

1. Select the cell
2. Select the "Data" tab
3. Select "Data Validation"
4. Check the "in-cell dropdown" checkbox
   before the cell have a drop down option

This code was developed using several sources to try and resolve the issue, and all of the sources advise more or less the same code

if (data.ApplyLookup(x, rowIndex))
{
	string columnName = GetExcelColumnName(x + 1);
	
	DataValidation dataValidation = new DataValidation
	{
		Type = DataValidationValues.List,
		ShowDropDown = true,
		AllowBlank = true,
		SequenceOfReferences = new ListValue<StringValue> { InnerText = $"{columnName}{rowIndex}" }
	};
	
	string lookupString = $"\"{string.Join(",", data.LookupValues[x].Select(kv => kv.Value))}\"";
	dataValidation.Formula1 = new Formula1(lookupString);
	
	dataValidations.Append(dataValidation);
}

[mikeebowen]

Hi @hansie-the-quorum, Can you give some more detail about your issue? Does the code you posted not work or is the issue something else?

[hansie-the-quorum]

Hi Michael, Thank you for your speedy response. The code provided works, but not as expected. The spreadsheet that I am building have some dropdown values that are very cell specific in the context that In a column it may or may not be set, as the data in lets say E2 may have the required lookup, but E3 then will not have the lookup as this is a free text field, the next row E4, then will have the lookup values and so on until the end of the sheet data. This may be for lest say Columns E,F and G, where each columns lookup differ from each other. The code executes fine, and the data is set, but, always the ‘but’, sorry for that, but the drop down does not show in Excel. To allow the data cell to display the drop down in the spread sheet the user then needs to select each cell, one my one, select the data tab, then the Data Validation and manually set the “In-cell dropdown” to ticked. (See image blow) Now according to each and other blog, including co-pilot, the code provided should set that tick-box to true, and allow the user to see all the dropdowns from the word go. I hope this explains it a bit better. I was thinking that maybe I am asking the wrong question, or understood it wrong, so, if this is a blunder from my side, sorry for the inconvenience caused. Kind regards Hansie ***@***.*** From: Michael Bowen ***@***.***> Sent: Friday, February 9, 2024 7:03 PM To: dotnet/Open-XML-SDK ***@***.***> Cc: Hansie van Straaten ***@***.***>; Mention ***@***.***> Subject: Re: [dotnet/Open-XML-SDK] Excel in-cell dropdown (Issue #1665) Hi @hansie-the-quorum<https://github.com/hansie-the-quorum>, Can you give some more detail about your issue? Does the code you posted not work or is the issue something else? — Reply to this email directly, view it on GitHub<#1665 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BBGFJ55QLB7FTF6GMIBY7NTYSZJDNAVCNFSM6AAAAABDBIVBI2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMZWGI3TQMBWGQ>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[mikeebowen]

Hi @hansie-the-quorum, There appears to be an issue with ShowDropDown = true. As a workaround, remove that line and do not set the ShowDropDown value and it will work. This is not the expected behavior and requires further investigation, but this will resolve the issue.

if (data.ApplyLookup(x, rowIndex))
{
	string columnName = GetExcelColumnName(x + 1);
	
	DataValidation dataValidation = new DataValidation
	{
		Type = DataValidationValues.List,
		// ShowDropDown = true, // Do not set this property.
		AllowBlank = true,
		SequenceOfReferences = new ListValue<StringValue> { InnerText = $"{columnName}{rowIndex}" }
	};
	
	string lookupString = $"\"{string.Join(",", data.LookupValues[x].Select(kv => kv.Value))}\"";
	dataValidation.Formula1 = new Formula1(lookupString);
	
	dataValidations.Append(dataValidation);
}

[hansie-the-quorum]

Hi Michael, Almost forgot, thank you for the work around, it works just fine now. And thank you for your kind and speedy response. Have the best day ever. Kind regards Hansie From: Michael Bowen ***@***.***> Sent: Tuesday, February 13, 2024 12:05 AM To: dotnet/Open-XML-SDK ***@***.***> Cc: Hansie van Straaten ***@***.***>; Mention ***@***.***> Subject: Re: [dotnet/Open-XML-SDK] Excel in-cell dropdown (Issue #1665) Hi @hansie-the-quorum<https://github.com/hansie-the-quorum>, There appears to be an issue with ShowDropDown = true. As a workaround, remove that line and do not set the ShowDropDown value and it will work. This is not the expected behavior and requires further investigation, but this will resolve the issue. if (data.ApplyLookup(x, rowIndex)) { string columnName = GetExcelColumnName(x + 1); DataValidation dataValidation = new DataValidation { Type = DataValidationValues.List, // ShowDropDown = true, // Do not set this property. AllowBlank = true, SequenceOfReferences = new ListValue<StringValue> { InnerText = $"{columnName}{rowIndex}" } }; string lookupString = $"\"{string.Join(",", data.LookupValues[x].Select(kv => kv.Value))}\""; dataValidation.Formula1 = new Formula1(lookupString); dataValidations.Append(dataValidation); } — Reply to this email directly, view it on GitHub<#1665 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BBGFJ57NDF53VNLJQKIQ35DYTKGYXAVCNFSM6AAAAABDBIVBI2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMZZGY3TCMBXHE>. You are receiving this because you were mentioned.Message ID: ***@***.***>