Not all excel functions nor custom UDF functions execute in GridJs

Aspose.Cells Product Family
1

In the attached template, some Excel functions and some UDF custom functions do not calculate.

Add class CalculationEngine implementingGridAbstractCalculationEngine to gridjs-demo-.netcore demo project.

Code:

    public override void Calculate(GridCalculationData data)
    {
        var functionName = data.FunctionName.ToUpper();
        switch (functionName)
        {
            case "DCREFERSTOSHEETNAME":
                if (data.ParamCount == 0)
                {
                    data.CalculatedValue = data.SheetName;
                }
                else if (data.GetParamValue(0) is GridReferredArea area)
                {
                    data.CalculatedValue = area.SheetName;
                }
                return;
            default:
                data.CalculatedValue = DateTime.Now.ToString("MM/dd/yyyy HH:mm");
                return;
        }
    }

Add CalculationEngine instance on startup:

        var ce = new CalculationEngine();
        GridJsWorkbook.CalculateEngine = ce;

Issues on each sheet
TOC
Hyperlinks not working for cells:
B5, B6, B8, B10, B12, B14, B15, B16, B17, B18, B19, B22
Identifiers
B18, Now function not run.
OLE
Unable to open embedded OLE object
Filters, CalculationChain, Error Values, Hides, inputs, Designer Protection, etc
None of the functions run

2
  1. I can reproduce the hyperlinks issue, we 've raise a ticket CELLSGRIDJS-699 to track on this issue
  2. the calculation issue,
    it not run ,because wb.Settings.FormulaSettings.CalculateOnOpen is false.
    you can set in the workbook object
    like:
    wb.Settings.FormulaSettings.CalculateOnOpen to true
    or in controller action:
    like this:
    GridWorkbookSettings setting = new GridWorkbookSettings();
    setting.ReCalculateOnOpen = true;
    wbj.Settings = setting;
    then the function will calculated.
3

I am having the same issue when I change DetailFileJsonWithUid to

public ActionResult DetailFileJsonWithUid(string filename, string uid)
{
String file = Path.Combine(TestConfig.ListDir, filename);
GridJsWorkbook wbj = new GridJsWorkbook();

GridWorkbookSettings setting = new GridWorkbookSettings
{
	ReCalculateOnOpen = true
};
wbj.Settings = setting;

//check if already in cache
StringBuilder sb = wbj.GetJsonByUid(uid, filename);
if (sb == null)
{
	Workbook wb = new Workbook(file);
	wb.Settings.FormulaSettings.CalculateOnOpen = true;
	wbj.ImportExcelFile(uid, wb);
	sb = wbj.ExportToJsonStringBuilder(filename);
}


return Content(sb.ToString(), "text/plain", System.Text.Encoding.UTF8);

}
Here is a screenshot of the filters worksheet

4

@Moonglum,

Thanks for the screenshot and updated code segment.

We will evaluate your code segment and get back to you with further details/findings.

5

Innore all this. I just realized all functions were commented out. Sorry about that!

Dean

6

one more ,for the ole object ,
we’ve add a feature request to support it.

1 Like
7

@Moonglum the ole related feture is supported in 23.1 version now ,
you shall add xs.setOleDownloadInfo(oleDownloadUrl); in html page
and add the related action in controller.cs
//get ole object /GridJs2/Ole
public ActionResult Ole()
{
int oleid = int.Parse(HttpContext.Request.Query[“id”]);
string uid = HttpContext.Request.Query[“uid”];
string sheet = HttpContext.Request.Query[“sheet”];
GridJsWorkbook gwb = new GridJsWorkbook();
string filename;
byte[] filebyte = gwb.GetOle(uid, sheet, oleid, out filename);
if (filename != null)
{
FileContentResult ret = new FileContentResult(filebyte, GetMimeType(filename));
ret.FileDownloadName = filename;
return ret;
}
else
{
return NotFound();
}
}

please check our latest demo in github https://github.com/aspose-cells/Aspose.Cells-for-.NET/tree/master/Examples_GridJs
we can click the ole shape object to download the embedded OLE object