I have an array formula:
=func(100, 3, “…”)
If the string has more than 253 chars then the formula fails. Is it right?
Here is a program that shows you how to reproduce the issue:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Aspose.Cells;
namespace TestAspose
{
class Program
{
static void Main(string[] args)
{
Procedure8();
Console.WriteLine(“Done!”);
Console.ReadKey();
}
private static void Procedure8()
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Get the cells collection in the sheet
Cells cells = worksheet.Cells;
string value = new string(’ ', 254);
cells[“A1”].SetArrayFormula(String.Format("=myarrayfunc("{0}")", value), 10, 100);
cells[“A11”].Formula = “=Len(A1)”;
workbook.CalculateFormula(true, new CustomFunction());
Console.WriteLine(cells[“E1”].Value);
Console.WriteLine(cells[“E2”].Value);
Console.WriteLine(cells[“E3”].Value);
Console.WriteLine(cells[“A11”].Value);
}
public class CustomFunction : ICustomFunction
{
public object CalculateCustomFunction(string functionName, System.Collections.ArrayList paramsList, System.Collections.ArrayList contextObjects)
{
if (functionName.ToLower().Equals(“myarrayfunc”))
{
return new object[][]
{
new object[] { 11, 12, 13, 14, 15 },
new object[] { 21, 22, 23, 24, 25 },
new object[] { 31, 32, 33, 34, 35 }
};
}
return null;
}
}
}
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
I was able to replicate this issue using your sample code, when the string gets larger than 253 characters, then formula fails.
I have tested your code with the latest version:
Aspose.Cells
for .NET v7.3.0.2
and problem still occurs.
We have logged this issue in our database. We will look into it and fix this issue if possible. Once the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as CELLSNET-40917.
Hi,
We evaluated your issue further. The max length of the string parameter in the formula (including array formula) is 255 in MS Excel.
We will fix this issue that If the string has more than 253 chars then the formula fails to support the string parameter whose length is less than 256.
Thank you.