Can ClosedXml write range formulas that don't get marked by Excel with the Implicit Intersection Operator?

Question

We're trying to write a .xlsx file using a formula that would return a double[] using ClosedXml. The new versions of Excel are adding the Implicit Intersection Operator ('@') to the formula, and not allowing it to spill to the adjacent cells.

Is there a way to get it not to happen. Microsoft talks about setting Range.Formula2 (https://learn.microsoft.com/en-us/office/vba/api/excel.range.formula2), but I'm not sure if that's available through ClosedXml.

I'd rather not try to implement writing the file with OpenXml, as that seems very hairy.

Is there a way to do this?

Sample:

using ClosedXML.Excel;

namespace ClosedXmlWriter
{
    class Program
    {
        static void Main(string[] args)
        {
            XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled);
            IXLWorksheet sheet = workbook.Worksheets.Add("home");
            IXLCell cell = sheet.Cell(1, 1);
            cell.FormulaA1 = "=MyTestFunc()";
            workbook.SaveAs(@"d:\test.xlsx");
        }
    }
}

Using a project referencing ExcelDNA to create a custom UDF:

using ExcelDna.Integration;

namespace TestPlugin
{
    public static class FunctionDefinitions
    {
        [ExcelFunction(Name = "MyTestFunc", Description = "Function description")]
        public static object MyTestFunc()
        {
            return new double[] { 1.0, 2.0, 3.0 };
        }
    }
}

Run Excel against the .xll file produced by the test plugin and the file produced by ClosedXml.

Answer 1

You have to set the formula in a range of the output size, and put it between {}:

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sheet1");
    worksheet.Range("A3:C3").FormulaA1="{={1.0,2.0,3.0}}";
    workbook.SaveAs(myFile);
}

For your formula, it would be

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sample Sheet");
    worksheet.Range("A1:C1").FormulaA1="{=MyTestFunc()}";
    workbook.SaveAs(myFile);
}

Which produces: