Formulas with AsposeGridJS

Aspose.Cells Product Family
1

I have implemented Aspose GridJS for .NET. Seems like there are only a few functions available such as: SUM, AVERAGE, MAX, MIN, IF, AND etc. I can see that only these methods have been implemented in xspreadsheet.js. According to Aspose.Cell for .NET, there are more functions are supported as in the link: Supported Excel Functions|Documentation

If I want to add more function, do I have to implement them on my own in xspreadsheet.js, or do you have support for all of these formulas in xspreadsheet.js?

Thanks

2

@gokhanbal
Please try our demo.
we supported all the functions.
we just get the result from severside based on cells api.

3

I got two follow up questions.

1- The updated newer version have definitely more excel functions. However, I did not quite understand your statement “we just get the result from server side based on cells api”. Because, these functions are implemented in xspreadsheet.js. Not quite sure how to display any functions I want on the UI. Please check the screen shot. To go see the list of functions: On the UI - Click Insert → click Functions.

image.png592Ă—1510 96 KB
image.png (96.0 KB)

2- Does this editablegrid.js support pivot tables?

Thanks

4

@gokhanbal,

We will evaluate your questions and get back to you with details.

5

@gokhanbal
For the serverside, we use the GridJs dll .

For the client side js, we use gridjs-spreadsheet which is based on xspread.js

In the github demo, you can see how to use them together to build an application.

  1. We defined some formulas (which are common, and frequently used) tips in the client side js, so the formulas are shown in the insert menu.
    However the calculate result process is in serverside(we support for all the formulas, if your spreadsheet file contains the formula we haven’t defined in the ui, the result will still be calculated).
    When you enter value for example like “=SUM(A1:C1)” in a cell , it will trigger ajaxcall to get result from serverside.
    When you run the demo, open the page in the browser, you can open the network tab in browser to see what happened after you update the cell value with formula.

  2. We support to display pivot tables. However the operation, such as select filter value in pivot field is yet not implemented. We will plan to finish such feature in future.

If you still find any issue or have other requirements, please go further to tell us. We will improve it.

6

Thank you for your response.

I am aware of that updatecell api sends the formula to server and calculation is done on the server end as you say. We are on the same page for that.
However, from the user perspective (our clients), they do not need to remember any of these functions. They should be able to select these functions from the menu. That is specifically what I was asking. Looks like I have to modify xspreadsheet.js to include functions that are not on the menu yet, so they appear on the menu.

I know that on the server side, you can use pivot tables programmatically. In your demo, on the UI menu, I did not see anything related to pivot tables. For clarification, a regular user can create a pivot table with your current demo or not?

Many thanks

7

@gokhanbal

  1. Yes, you are right. Now we only list parts of formulas in the insert menu, which includes very common formula.

    q_20240131231826.png741Ă—707 22.6 KB
    q_20240131231826.png (22.6 KB)
    When you enter key, it will auto-popup the formulas. If you still want all the functions, can you provide a list of them? We will implement them in the insert menu.

  2. We cannot create a pivot table in the current ui. We only support to render it.
    When you import a spread file with pivot table, we will render the pivot table.
    However, the interaction for pivot field, such as, select filter, is not implemented yet. We will improve it in future.

1 Like
8

Thank you very much

9

@gokhanbal
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSGRIDJS-34,CELLSGRIDJS-1010

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

11

I got a follow up question and and a suggestion.

Follow up question: If you were to add proper, today, value, ifna, and trunc functions to UI menu, how long would it take you?

Suggestion: From a user perspective, when I click Insert - Functions menu item, the list is so long and not user friendly. Is there a way you can categorize these to enhance UX?

Thanks

12

@gokhanbal
we are sorry for the inconvinent.
We will add those formulas in the insert menu in the next v24.3 version.
We will adjust the menu height and make it suitable to display. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSGRIDJS-1009,CELLSGRIDJS-1013

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
14

The issues you have found earlier (filed as CELLSGRIDJS-1013,CELLSGRIDJS-1009) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

1 Like
15

I am wondering if this change has been released…
We will add those formulas in the insert menu in the next v24.3 version.
We will adjust the menu height and make it suitable to display. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

I just updated our Aspose.Cells and Aspose.Cells.GridJS nuget packages to version 24.4.0 and the menu items for functions still appears as a long list in a single column and not as it shows on your example site as shown here:

image.png3301Ă—1800 371 KB
image.png (371.3 KB)

If this is released in this version, is it possible the CSS elements were not included in the release. Please provide documentation with steps needed to update the UX menu to match what is shown on your example site.
Many thanks!

16

@bmcculloch
Please try the latest version 24.4.
We will check it too.

17

@bmcculloch
You need to update the client js to the latest v24.4.0 in npmjs also .
you can use the reference like this:

<link rel="stylesheet" href="https://unpkg.com/gridjs-spreadsheet@24.4.0/xspreadsheet.css">
<script src="https://unpkg.com/gridjs-spreadsheet@24.4.0/xspreadsheet.js"></script>

Please let us know if you still meet any issues.

18

Thank you, these updates have fixed my issue!

19

@bmcculloch
You are welcome. Thank you for your feedback. If you have any questions, please feel free to contact us.

20

Hi Aspose Team, I have found a small bug in the JavaScript code provided for the latest Aspose.Cells 24.4.0 release. The bug is that the RANK function tooltip is showing the tooltip from the FREQUENCY funtion…this issue can be replicated on your example site as shown in this screenshot:
image.png (467.8 KB)

The issue is caused by the JavaScript code for Rank using the frequency formula title:
key:“RANK”,title:it(“formula.frequency”)
image.jpg (526.7 KB)

This is a very minor issue showing the wrong tooltip, but just letting you know.
Thanks

21

@bmcculloch,

Thanks for your findings.

We noticed the issue as per your observation. It seems the RANK formula description is duplicated with the FREQUENCY function as you pointed out.

We will look into it soon. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSGRIDJS-1104
22

@bmcculloch,

We are pleased to inform you that the issue (logged as “CELLSGRIDJS-1104”) has been resolved now. The fix will be included in an upcoming release (Aspose.Cells.GridJs v24.6) scheduled for release in the second week of June 2024. You will be notified when the next version is released.