CODEWITHSUNDEEP
excelexcel-formulaepplus
jonh
jonh

Reputation: 254

Is there a way to trigger a dynamic "spilled" array in Excel to resize?

A little background on my problem:

  1. I am creating a "template" xlsx file that includes one "Data" worksheet for raw data (that will be inserted programmatically) and other worksheets that use formulas to derive from the raw data.
  2. I am using EPPlus to insert the raw data into the "Data" worksheet.
  3. I have another worksheet that uses the UNIQUE() function to create a dynamic array of unique values from one of the columns in the "Data" worksheet: =UNIQUE(Data!A2:A10000,FALSE,FALSE)

The problem is that after programmatically inserting the raw data using EPPlus, the UNIQUE() formula does not automatically "spill". It does update the value in the first row, but it doesn't "expand" to show the full number of rows.

(Note: I've also tried other formulas that produce a dynamic array, such FILTER(), with the same result.)

According to this article: https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531

When you press Enter to confirm your formula, Excel will dynamically size the output range for you, and place the results into each cell within that range.

My question is, is there a way to trigger sizing the output range without having to press Enter? If possible, I would like it to resize when opening the Excel file, without any scripting. The generated Excel file is intended for the end user and I don't want to make them focus the formula and press Enter in order to see the calculated results.

Upvotes: 1

Views: 621

Answers (1)

jonh
jonh

Reputation: 254

I found that GrapeCity's GcExcel component does support dynamic arrays. Using GcExcel instead of EPPlus solved the problem. To trigger GcExcel to update the spill range:

var f = cell.Formula2;
cell.Formula2 = null;
cell.Formula2 = f;

I'm going to accept this as the best answer so far. However, according to this github issue on EPPlus: https://github.com/EPPlusSoftware/EPPlus/issues/768

Yes, we are working on a major redesign of the dependency chain and expression handling where we will implement dynamic array formulas

Upvotes: 1

Related Questions