FormulaArray, вставленный из C# API, отображает только первый элемент массива до тех пор, пока формула массива не будет ⇐ C#

[Anonymous]

Background: I'm using the C# .NET COM API to retrieve data from a source using an API, then loading that data into a spreadsheet using the .NET Excel interface. I'm also using the Excel interface to create a "View Sheet", which I have conditional formatting, data validation, index match formulas etc. I'm using .NET 8.0 and Excel version 16.0

My problem is with array formulas. In the LOADSHEET (where I dump the raw data from the API), I have text separated by a comma delimiter.

Cell [B2] has Data Validation as follows:

Excel.Range range = (Excel.Range)newSheet.Cells[2, 2]; range.Validation.Delete(); range.Validation.Add( XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertWarning, Formula1: "=INDIRECT(\"LOADSHEET!$A$2:\"&ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1))" ); range.Validation.ErrorTitle = "Value Error"; range.Validation.ErrorMessage = "Please select a value from dropdown list."; range.Validation.ShowError = true; range.Validation.InCellDropdown = true; range.Validation.IgnoreBlank = true; Then the formula in D6 is:

newSheet.Cells[6, 4].FormulaArray = "=(TRANSPOSE(TEXTSPLIT(INDEX(INDIRECT(\"LOADSHEET!$D$2:\"& ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1)), MATCH(B2, INDIRECT(\"LOADSHEET!$A$2:\"& ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1))), 4), \",\")))"; In excel that puts

{=TRANSPOSE(TEXTSPLIT(INDEX(INDIRECT("LOADSHEET!$D$2:"&ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1)), MATCH(B2, INDIRECT("LOADSHEET!$A$2:"&ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1))), 4), ","))} When the spreadsheet is saved and closed, there is no pre-selected value in B2. There is no formatting done to E6 or D6 besides setting them to left horizontal align, and setting the column width.

At first I tried adding event handlers using Sheet.Change += Change_Handler

Sheet.Change += Change_Handler; private static void Change_Handler(Excel.Range target) { if (target.Address == "$B$2") target.Worksheet.Calculate(); } This didn't change anything.

I tried using various different ways of setting the value, such as:

newSheet.Range["D6"].Value2 = [Formula]; newSheet.Cells[6, 4] = [Formula]; Both of these didn't work, except instead of the formula being wrapped in curly brackets {}, there was an '@' symbol before the TRANSPOSE formula.

Source Data:
Name Client Client-ID Values1 Data1 Data2 Client Type Instance Type Values2 Last Updated Internal - Inactive - 2/27/2024 Internal 10 RANDOM_VALUE,OTHER_RANDOM_VALUE 0 0 Inactive Inactive ANOTHER_RANDOM_VALUE,FINAL_RANDOM_VALUE 27/02/2024
Current output:
Values1 Values2 RANDOM_VALUE ANOTHER_RANDOM_VALUE
Expected output:
Values1 Values2 RANDOM_VALUE ANOTHER_RANDOM_VALUE OTHER_RANDOM_VALUE FINAL_RANDOM_VALUE
It's worth noting that the formula does work if you edit the formula (dont change it) and press enter while in the Excel Desktop Client


Источник: https://stackoverflow.com/questions/781 ... -array-unt