Cypress automation testing on spreadJS

Posted by: sadiq.rahim on 5 September 2019, 10:55 am EST

  • Posted 5 September 2019, 10:55 am EST

    Hi there,

    My company is using spreadJS and we're trying to develop some cypress tests on spread JS that is built on canvas. I learnt that the spreadJS instance should be initialised but have no luck as of now. Do we have to use the spreadJS key as well to initialise or can it be done without?

    Following is a sample code snippet used in attempt to verifying the instance but unfortunately no luck.

    let spread = GC.Spread.Sheets.findControl(<host>);
    let sheet = spread.getActiveSheet().getRange(5, 10);
    console.result(sheet)


    I did come across some selenium and protractor codes but they weren't helpful. Appreciate if there is any sample test automation snippets in relation to my query above.

    Many Thanks
  • Replied 6 September 2019, 3:58 am EST

    Thanks a lot for getting back to me, this is very helpful. However, the spreadJS is built on canvas and I'm not sure how do we grab the spread instance of spreadJS that is built on canvas. I did see a few queries and answers related to canvas but didn't find them helpful to what I would like to do.

    Following are the steps we're attempting,
    - Go to the page that brings up the spreadJS
    - Attempt to do a search on the spread JS that is integrated with our DB
    - Return results based on the search
  • Replied 8 September 2019, 7:23 pm EST

    you could find spread instance using the findControl method as demonstrated in the previously shared sample:
    // get window object of the application
    var appWindow = await cy.window();
    // find spread instace
    var spread = appWindow.GC.Spread.Sheets.findControl("ss");

    findControl method accepts a DOM element and return the spread instance associated(if any) with that DOM element.
    After getting the spread instance, you need to use different api methods to perform different operations and test different functionality like if you want to test if a cell A1 contains value "abc" then you may write test like following:
    // get the application's window
    var appWindow = await cy.window();
    // find the spread instance
    var spread = appWindow.GC.Spread.Sheets.findControl("ss");
    // get the value of cell A1
    var value = spread.getActiveSheet().getValue(0, 0);
    // test the value
    expect(value ).to.eq("abc");


    >> - Attempt to do a search on the spread JS that is integrated with our DB
    Could you please let us know what type of search you need to perform? Do you need to test if some particular cell contains some particular value of if any cell contains some value or something else?
  • Replied 8 September 2019, 7:36 pm EST

    Many thanks for the prompt reply.

    For some reason, I do not see the DOM elements. Is it any different if it's build around canvas?

    Re-search the type of search, we would like to make sure that there is no values such as #DIV/0!, which is generated in the event of any errors with the figures/formulas. Is this actually possible?
  • Replied 8 September 2019, 8:41 pm EST

    DOM element here refers to the Spread host container that was used to initially load spread i.e. if you initialized spread like the following:
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
    then the div element with id = "ss" is host element for spread which we need to pass in the findControl method.
    If you are using some framework like angular, and spread was created using the template binding then you may get the spread host element by using the following query:
    let spreadHostElement = appWindow.document.querySelector('[gcuielement="gcSpread"]');
    // use findControl to find spread instance
    let spread = appWindow.GC.Spread.Sheets.findControl(spreadHostElement );


    >> Re-search the type of search, we would like to make sure that there is no values such as #DIV/0!,
    You may use the ISERROR formula to get the no of errors in the sheet and test the same. Please refer to the following code snippet:
    it("Should not contain errors", () => {
    let activeSheet = spread.getActiveSheet(),
    rCount = activeSheet.getRowCount(),
    cCount = activeSheet.getColumnCount();

    let formula = "=SUMPRODUCT(--ISERROR(R1C1:R" + rCount + "C" + cCount + "))";
    var errCount = appWindow.GC.Spread.Sheets.CalcEngine.evaluateFormula(
    activeSheet,
    formula,
    0,
    0,
    true
    );

    expect(errCount).to.eq(0);
    });


    API References:
    • evaluateFormula method: http://help.grapecity.com/spread/SpreadSheets12/webframe.html#SpreadJS~GC.Spread.Sheets.CalcEngine~evaluateFormula.html
  • Replied 9 September 2019, 5:02 am EST

    Many Thanks for continuing to respond so promptly. Much Appreciated.

    We're now able to connect. We comprehend that some of our spreadsheets are created using the template binding.

    re-Errors - We used the code snippet above to check for errors. Don't think ISERROR is working. Do you mind having a look on this as well please?
  • Replied 9 September 2019, 5:29 pm EST

    Could you please let us know what is the exact issue you are facing? We tested out the formula and it seems to be working fine. Please refer to the attached sample. In the attached sample, we intentionally added an error generating formula, which results in the test(expecting no error) fails as expected.
    spread-testing-cypress_updated.zip
  • Replied 10 September 2019, 3:51 am EST

    Thanks for getting back. Your tests works find at our end too. Unfortunately, there is no merry for us yet. The error that we are getting is {_error: #NAME?, _code: 29}. This occurs for spreadsheets that are with and without errors when using the evaluateFormula(). However, if we don't wrap the ISERROR function under SUMPRODUCT, it returns the actual value of the cell.

    Our spread is created using template binding and the spread instance is created as follow,

    let spreadHostElement = appWindow.document.querySelector('[gcuielement="gcSpread"]');
    spread = appWindow.GC.Spread.Sheets.findControl(spreadHostElement);

    Is it any different to use the evaluateFormula on the templates binding?
  • Replied 10 September 2019, 5:30 pm EST

    Hi Sadiq,

    There is no difference in the way evaluateFormula is used for template binding. Could you please share a small sample replicating the issue so that we could investigate the cause of the issue
    Also please let us know the exact spread version you are using so that we could test with the same version
  • Replied 10 September 2019, 9:04 pm EST

    Hi there,

    We use the spread version @grapecity/spread-sheets: "^12.2.4" to build up our financial statements. This is built as a canvas image on our website

    Steps followed:
    1. Login to Site
    2. Go to the page that brings up the financial statement on a spreadsheet
    3. Initialize the spread instance as illustrated on the code snippet below
    4. Evaluate for any errors where it returns an error despite of any formula error on the spreadsheet

    Tried clearing the cache of the browser/npm as well. Cypress already clears the browsers before it starts the tests. So, we cannot get our head around as to why it's not able to use the evaluateFormula() function as we wanted it to.

    Please find the code snippet used to validate for your perusal

    cy.window().then((appWindow) => {
    let spreadHostElement = appWindow.document.querySelector('[gcuielement="gcSpread"]');
    spread = appWindow.GC.Spread.Sheets.findControl(spreadHostElement);
    let value = spread.getActiveSheet().getValue(116, 5);
    let activeSheet = spread.getActiveSheet(),
    rCount = activeSheet.getRowCount(),
    cCount = activeSheet.getColumnCount();

    let formula = "=SUMPRODUCT(--ISERROR(R1C1:R" + rCount + "C" + cCount + "))";
    let errCount = appWindow.GC.Spread.Sheets.CalcEngine.evaluateFormula(
    activeSheet,
    formula,
    0,
    0,
    true
    );

    cy.log(value); //Returns the actual value of the cell
    cy.log(errCount); //Always returns the error {_error: #NAME?, _code: 29} despite a cell has a formula issue or not
    expect(errCount).to.eq(0); //Returns an error despite of the issue with the cell that contains the formula
    });
  • Replied 11 September 2019, 9:29 pm EST

    In the code snippet, everything seems to be fine.
    {_error: #NAME?, _code: 29} error means that the formula or range is not registered in the spread. Is it possible that in your application some of the inbuilt functions?
    Also, please share a sample that replicates the issue so that we could further investigate the issue and assist you accordingly. If it is not possible for you to share a sample on this public thread, then you may also create a case on our private support portal and share the sample.
    http://supportone.componentone.com
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels