Server side filtered rows indication

Posted by: asaf.shechter on 20 February 2023, 4:17 am EST

  • Posted 20 February 2023, 4:17 am EST

    Hello,

    I couldn’t find a way to tell if a specific row is filtered or not using the getAutoFilter property in server side ( in client it is possible…) ,

    is there anyway to do it?

    Thanks

  • Posted 21 February 2023, 5:46 am EST

    Hello,

    SpreadJS is a client-side based application. Please specify what your use case is and how exactly you are using spreadjs on backend so that we can investigate the issue more efficiently and provide you better assistance.

    Regards,

    Avinash

  • Posted 21 February 2023, 8:36 pm EST - Updated 21 February 2023, 8:41 pm EST

    Hi again,

    I attached images .

    as you can see in client side we have isRowFilteredOut.

    Server side don’t have this method , is there any other way to determine if row is filtered in server side?

  • Posted 22 February 2023, 6:20 pm EST

    Hi,

    It seems like you are using GCExcel Java on your backend. Please refer to the following code snippet to get if the row is filtered or not.

    boolean isRowFiltered = workbook.getWorksheets().get(sheetIndex).getAutoFilter().getRange().getRows().get(rowIndex).getHidden();

    Regards,

    Avinash

  • Posted 1 March 2023, 9:09 pm EST

    Hi,

    we checked this function and it doesn’t seem to work for us in a specific case :

    original sheet :

    image

    sheet after row 2 is filtered :

    image

    now if you check if row 3 is hidden in server side you will also get true ,

    seems like a bug with a big decimal numbers ,

    is there a way to overcome it or we need to wait for a bug fix ?

    Thanks.

  • Posted 2 March 2023, 6:40 pm EST

    Hi,

    We have forwarded this to the GcDocs team for investigation and will share the update soon.

    Regards,

    Avinash

  • Posted 6 March 2023, 2:36 pm EST

    Hello Asaf,

    Apologies for the delay in response.

    We have created an excel file as shown in your attached screenshot but do not face the issue of getting if the row is hidden or not.

    Please find the attached stripped-down sample implementing the same and let us know if you are doing anything different.

    Regards,

    Prabhat Sharma.

    RowFilterDemo.zip

  • Posted 6 March 2023, 9:15 pm EST

    Hi Prabhat,

    please try the example I posted message before,

    with 7 digits after the decimal point,

    Thanks.

  • Posted 9 March 2023, 5:50 pm EST

    Hello Asaf,

    Please find the attached excel file that we have tested at our end and it is working fine. Please update it accordingly so that we can replicate the issue on our end and assist you further accordingly.

    Also, let us know the version of the GcExcel Java API you are using.

    Regards,

    Prabhat Sharma.

    FilterDemo.zip

  • Posted 14 March 2023, 9:45 pm EST

    Hello again,

    we found what was the issue ,

    for some reason spreadjs create a default named styles and we also found which specific one causes this issue :

    after removing it from the workbook seems like this bug is solved ,

    what is your suggestion regarding this issue?

    currently we can remove this default style but seems like there is a bug in spreadjs side…

  • Posted 16 March 2023, 4:36 pm EST

    Hi Asaf,

    The “__builtInStyle1” styles are created internally by the SpreadJS.

    I tested with the latest version of SpreadJS V16.0.4 and I was unable to replicate the issue at my end. At my end, I tried importing the attached Excel file into the SpreadJS and it seems to be working fine at my end.

    With the excel file imported, if the row with value “1” is filtered, then SpreadJS does show it as filtered and vice-versa.

    Could you please share the exact steps to reproduce the issue with SpreadJS and GCExcel. Also, mention the SpreadJS and GCExcel version also.

    If you are facing the issues with any particular file, kindly share the excel file with us.

    Regards,

    Ankit

    excelfile.zip

  • Posted 20 March 2023, 8:13 pm EST

    Hi,

    please try to create this example in SpreadJS first (so you will have __builtInStyle2).

    After that send the workbook with the built in styles and this exact filter to the server and this bug should be reproduced.

    SpreadJS version 15.1.3

    GcExcel version 5.1.3

  • Posted 22 March 2023, 4:03 pm EST

    Hi Asaf,

    Currently, we are investigating the issue at our end with SpreadJS and GCExcel Java. We will update you on this soon.

    Regards,

    Ankit

  • Posted 23 March 2023, 1:58 pm EST - Updated 23 March 2023, 2:03 pm EST

    Hi Asaf,

    I tested with the latest versions of SpreadJS V16.0.5 and GCExcel Java V6.0.5 and I was unable to replicate the issue at my end. It seems like the issue has been fixed in the latest versions of SpreadJS and GCExcel. I request you to kindly upgrade to the latest version of SpreadJS and GCExcel and let us know if you face any issues.

    For SpreadJS Testing, I generated the excel file from SpreadJS by visiting the hosted SpreadJS Designer: https://www.grapecity.com/spreadjs/designer/index.html

    You could refer to the following gif that shows the steps.

    Then the exported excel file is imported in GCExcel Java and checked if the row is filtered out. It was also working fine as expected.

    You could refer to the following image that we have used for testing.

    For GCExcel, please find the sample that we have used for testing.

    GCExcelDemo (1).zip

    Please try upgrading the versions and if the issue still persists for you, kindly share a sample replicating the issue so that we could investigate further and assist you accordingly.

    Regards,

    Ankit

  • Posted 27 March 2023, 1:32 am EST - Updated 27 March 2023, 1:38 am EST

    Hello again Ankit,

    we are still having an issue even after upgrading to GcExcel 6.0.5,

    please use the attached json file and init workobook from it ,

    then you will see you are getting wrong hidden indication on server side,

    row 12 will be hidden …spreadJson.zip

  • Posted 27 March 2023, 8:32 pm EST

    Hello,

    Thank you for reporting this issue. We too can observe the issue and escalate this to the development team for investigation. We will let you know as soon as we get any update on this from their end.

    [Internal Tracking ID: DOCXLS-7864]

    Regards,

    Prabhat Sharma.

  • Posted 3 April 2023, 3:26 pm EST

    Hello Asaf,

    As per the developers, the issue is caused by the different results of the “General“ number format between SpreadJS and Excel.

    In Excel, the display text value of “1000.1234567“ in “General“ is 1000.123457.

    In SpreadJS, it is 1000.1234567.

    So in SJS, the filter condition of “equals to 1000.1234567“ can be matched but can not be matched in Excel/GcExcel.

    1. We have asked the SJS team to make the General format in SpreadJS, same as Excel.
    2. As a workaround you can use the following code to get the desired output using GcExcel API.

    sheet.getRange("H12").setNumberFormat("0.0000000");
    sheet.getAutoFilter().applyFilter();

    If you need any other help, please feel free to ask.

    Regards,

    Prabhat Sharma.

  • Posted 10 April 2023, 3:55 pm EST

    Hi,

    The spreadJs Dev team informed us that this is by design, as the number digit count is limited by 10 in Excel for General Formatters, and SpreadJS doesn’t have this limitation.

    regards,

    Avinash

  • Posted 17 April 2023, 2:39 am EST

    Hello again,

    I am sorry but not sure I understand the explanation.

    I have one specific cell with 7 digits after the decimal point and it seems the server side indication for isHidden is wrong in that case,

    I am also not sure I understand the solution as we might have different number of digits after the decimal point ,

    is there a more general solution to this issue , as it seems the given workaround is only for 7 digits?

  • Posted 18 April 2023, 7:36 pm EST

    Hello Asaf,

    We understand your concern and discussing it further with the GcExcel development team to see if we can get the value of the GetHidden method based on the internal value, not the formatted value.

    We have also asked them for a generic workaround if any.

    Regards,

    Prabhat Sharma.

  • Posted 4 May 2023, 12:16 am EST

    Hi again,

    is there any update regarding this issue?

  • Posted 4 May 2023, 3:22 pm EST

    Hello,

    We are getting in touch with the developers for an update and will get back to you as soon as get the information.

    Regards,

    Prabhat Sharma.

  • Posted 8 May 2023, 2:30 pm EST

    Hello,

    As per the latest updates, the development teams [GcExcel and SpreadJS]looking into it from both sides and are working to figure out the best way to handle this.

    We will update the ticket as soon as we get the updates.

    Regards,

    Prabhat Sharma.

  • Posted 9 May 2023, 5:03 pm EST

    HI,

    The dev team has provided the following workaround. Please try this out and let us know if you still face any issues.

    var oldTextFormat = GC.Spread.Sheets.CellTypes.Text.prototype.format;
    GC.Spread.Sheets.CellTypes.Text.prototype.format = function (value, format, formatData, context) {
        if (typeof value === 'number' && (!format || format === "General" || (format.formatString && format.formatString() === "General"))) {
            value = +value.toPrecision(10);
        }
        return oldTextFormat.apply(this, arguments);
    }

    regards,

    Avinash

  • Posted 10 May 2023, 9:08 pm EST

    Hi,

    we tried the suggested solution of changing the formatter but it doesn’t seem to work for us, the original issue still happens, when we check if cell with value 1000.1234567

    is hidden in server side we still get true even though it’s visible and not part of the filter.

  • Posted 14 May 2023, 8:55 pm EST

    Hi,

    Thanks for the information. We have shared the information with the team. We will update you once we have nay findings related to this issue.

    Regards,

    Avinash

  • Posted 21 May 2023, 10:04 pm EST

    Hi Avinash,

    just reminding you we are still waiting for a fix,

    is there any estimation ?

    It’s stopping us from making progress in some specific use cases ,

    thanks.

  • Posted 21 May 2023, 10:39 pm EST

    Hi,

    The team informed us that To solve this problem, besides the workaround code, we still need to update the kept condition value.

    Conclusion:

    Solution 1:

    Apply our workaround code:

    var oldTextFormat = GC.Spread.Sheets.CellTypes.Text.prototype.format;
    GC.Spread.Sheets.CellTypes.Text.prototype.format = function (value, format, formatData, context) {
        if (typeof value === 'number' && (!format || format === "General" || (format.formatString && format.formatString() === "General"))) {
            value = +value.toPrecision(10);
        }
        return oldTextFormat.apply(this, arguments);
    }

    1. load the sjs file.

    2. open the filter dialog, then click the ok button.

    3. save to sjs file

    Solution 2:

    The same with solution 1, #1.

    load the sjs file.

    then run the below API to update the kept condition value.

    sheet.rowFilter().getFilterItems(sheet.rowFilter().range.col)[0].expected("1000.123457")
    1. then save to sjs file.

    With these two options, both GcExcel and Excel will be correct.

    Regards,

    Avinash

Need extra support?

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

Learn More

Forum Channels