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
Forums Home / Spread / SpreadJS
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 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 :
sheet after row 2 is filtered :
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
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.
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.
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);
}
load the sjs file.
open the filter dialog, then click the ok button.
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")
With these two options, both GcExcel and Excel will be correct.
Regards,
Avinash