CHOOSECOLS sample code

Aspose.Cells Product Family
1

Hi Aspose Family

I am working with a sheet that uses CHOOSECOLS formula.
I understand that this type of formula is new for Excel 365 and recently supported in Aspose V23.

I would like to know if there is any sample, post or documentation on how to handle CHOOSECOLS properly.

I have been searching in the forums but have not found any.
Could you please help?
Thank you and best regards

-Sergio

2

@scortezr,

Please see the page and click to display the examples below to get to know/understand on how to use and handle CHOOSECOLS formula for your reference. Aspose.Cells mimics and works the same ways MS Excel does for the formula/functions.

Also, see the documents on how to set and calculate formulas via Aspose.Cells APIs for your reference.

3

@scortezr
And if you need to get the same result with ms excel like inputting such kind of formula for a cell(ms excel takes it as the newly introduced kind of formula: dynamic array formula), please use SetDynamicArrayFormula to set the formula to a cell. After setting all such kind of formulas, you may call RefreshDynamicArrayFormulas to spill them into corresponding cell ranges according to calculated result of the formula.

4

Thanks for the feedback.

It is indeed used for an Array Formula which looks like this:

{=CHOOSECOLS(FILTER(A4:AE12,(AA4:AA12>=D6)*(AA4:AA12<=EOMONTH(D6,0))),2,3,5,6,4)}

The way I am using Aspose is like this pseudocode:

array_formula = wb.Worksheets[sheet_name].Cells[my_cell].Formula ← (I get the formula as it is defined in the sheet)

wb.Worksheets[sheet_name].Cells[my_cell].SetArrayFormula(array_formula, 32, 19) ← Here i am using SetArrayFormula and hardcode the size of the array, however I would like to set the size of the array dynamically . I expect that the array could be of different sizes.

workbook.CalculateFormula() ← Calculate formula.

This is working just fine but I suspect there could be a better way, probably the one suggested by Johnson, (using Dynamic Array formula).

looking forward to reading more of your comments and continue learning.
regards

5

@scortezr
Thank you for your feedback. Currently, regarding dynamic array formulas. You just need to call Cell.SetDynamicArrayFormula to set the dynamic array formula and calls Workbook.RefreshDynamicArrayFormulas before calling formula calculation. You can correctly use all the dynamic array formulas supported by Cells. Please refer to the attachment. DynamicArrayFormula.zip (46.5 KB)

The sample code as follows:

Workbook wb = new Workbook(filePath + "sample.xlsx");
Cell a10 = wb.Worksheets[0].Cells["A10"];
//set dynamic array formula
a10.SetDynamicArrayFormula("=CHOOSECOLS(A2:D7,3,4)", new FormulaParseOptions(), false);
wb.RefreshDynamicArrayFormulas(true);
wb.CalculateFormula();
wb.Save(filePath + "out_net.pdf");
wb.Save(filePath + "out_net.xlsx");

If you need to dynamically set various formulas, you need to control them through your own program. If you have any questions or confusion, please provide more detailed information and we will check it soon.

6

This is very helpful!
I appreciate your time and sharing your experience.
Thanks!

7

@scortezr,

You are welcome. Should you have further queries or comments, please feel free to write us back.