Adding GridGroupingControl to "Workbook" with gridcontrols

[Travis Chambers]

Hi,

I have been playing around with the ExcelLikeUI sample and I like the functions it includes for the most part. One thing that is lacking, however, is the ability to filter data in a worksheet. I noticed that filtering functionality is largely unsupported in gridcontrol, however, the gridgroupingcontrol has a very strong filtering system. So, I was wondering...

1) is it possible to put a gridgroupingcontrol in the "workbook" along with the regular gridcontrols? So, most sheets would be gridcontrols as in the sample but one sheet could be a gridgroupingcontrol where the primary data is kept.

2) Could the formulas in the other sheets (gridcontrols) reference a gridgroupingcontrol?

3) Would it then be possible to filter the gridgroupingcontrol and have the formulas in the other sheets then reflect the filtered data?

Thanks!

[Jagadeesan Pichaimuthu]

Hi Travis, 

  

Thanks for using Syncfusion product. 

  

GridGroupingControl is record based control and record will be generated when you bind the collection for GridGroupingControl. GridGroupingControl rows will be created based on the binding collection. So, if you want to use GridGroupingControl like an excel, you should create the binding collection with columns and rows which you want. 

  

Please refer the following UG link to know about the GridGroupingControl binding support, 

https://help.syncfusion.com/windowsforms/gridgrouping/data-binding 

  

Otherwise, GridGroupingControl does have all the support(Formula and sheet reference). 

  

Note: 

Syncfusion does have the support for SpreadsheetControl it is also like an excel and does have the filtering support for programmatically. We have logged the feature report to provide the filtering support in UI level which will be available in our 2019 Volum3 release. 

  

Please get back to us if you need any further assistance on this. 

  

Regards, 

Jagadeesan 

[Travis Chambers]

Thanks for the update. I have tried out the spreadsheet control and while I love the idea of it I have experienced some issues with it. Mainly getting the big red X error after random actions, including scrolling. Those errors have been impossible to diagnose.

Also, the charting system in that control is a bit weak. It is image based which produces poor image quality in a document, even with its "best" quality property. A lot of the chart properties don't work properly either.

If it was a bit more stable and had better charting it would be great. Perhaps syncfusion should consider integrating the chartcontrol into the spreadsheet control.

I suppose I could implement a custom filter method by hiding rows that Dont match a criteria. If I did this, is there a way to force formulas to only recognize visible cells?

[Mohanraj Gunasekaran]

Hi Travis, 

 

Thanks for your update. 

 

Query	Response
I have tried out the spreadsheet control and while I love the idea of it I have experienced some issues with it. Mainly getting the big red X error after random actions, including scrolling. Those errors have been impossible to diagnose.	Can you please share the complete stack trace of this exception and let us know the exception occurred after importing the any excel file. If yes means please provide that excel file and also please provide the Syncfusion product version which you are using currently. It will more helpful for us to investigate further.
Also, the charting system in that control is a bit weak. It is image based which produces poor image quality in a document, even with its "best" quality property. A lot of the chart properties don't work properly either.  f it was a bit more stable and had better charting it would be great. Perhaps syncfusion should consider integrating the chartcontrol into the spreadsheet control.	By default, chart will be rendered using AddGraphicChartCellRenderer method which is available in Syncfusion.SpreadsheetHelper.Windows.dll. Please refer the following UG link,  https://help.syncfusion.com/windowsforms/spreadsheet/shapes#charts    Sample link: https://github.com/syncfusion/winforms-demos/tree/master/Spreadsheet.Windows/Samples/Chart/CS  Chart file location: https://github.com/syncfusion/winforms-demos/blob/master/Common/Data/Spreadsheet/Chart.xlsx    By default, all the chart properties are worked. Please let us know the details of the chart properties which is not working for chart control. It will be helpful for us to investigate further.
I suppose I could implement a custom filter method by hiding rows that Dont match a criteria. If I did this, is there a way to force formulas to only recognize visible cells	By default, If you hide the row column, value will be maintaining under that cell reference. So, value should be referred in formula calculation even if it is hided. This is our current behavior and also this the excel behavior.

 

Regards, 

Mohanraj G 

[Travis Chambers]

When testing the spreadsheet control I was using the most recent syncfusion version. It did happen with various documents, but usually large ones (about 4000 rows and 40 columns). The red X would appear simply by scrolling vertically or horizontally. Unfortunately, I cannot share the documents due to privacy concerns and I don't have the stack trace as i have removed that control from my project and have replaced it with gridcontrol set up like the ExcelUI sample. 

I like the gridcontrols in this setup, but I am having some performance struggles, especially after loading one or two sheets (grids) with large data. Is it possible to load a couple of grids as virtual grids with data from Excel and then run cross reference formulas on the virtual grids from standard grids within the workbook?

I have been saving and reloading each grid from XML serialization and that gets quite slow. 

Also, after having a couple of large sheets, if I try to serialize with a cell that has a background image it causes an outofmemory exception.

What I am trying to do is have a workbook where 2 sheets will have data loaded in them via import from Excel, and then 2 worksheets that summarize and visualize the data in the 2 "data sheets". These 4 sheets are saved and loaded via xml serialization. The summary sheets use formulas to manage data, gridcontrols are embedded and images are added via background image in a couple of cells here and there. This works well with small data sets, but when one data sheet has 5000 rows and 40 columns, and the other data sheet has about 10,000 rows and 40 columns, the formulas calculate slowly, images can't serialize without crashing, and the serialization is slow when it does succeed.

Do you have any recommendations for accomplishing my goal with better performance?

Thanks!

[Mohanraj Gunasekaran]

Hi Travis, 

Thanks for your update. 

Query	Response
When testing the spreadsheet control I was using the most recent syncfusion version. It did happen with various documents, but usually large ones (about 4000 rows and 40 columns). The red X would appear simply by scrolling vertically or horizontally. Unfortunately, I cannot share the documents due to privacy concerns and I don't have the stack trace as i have removed that control from my project and have replaced it with gridcontrol set up like the ExcelUI sample.	Our spreadsheet control doe have the support to load more than 4000+ rows and 40+ columns. So, we suspect that this issue may occur based on your customization. So, if you are not willing to share your code part in this public forum. You, can share your code part through our personal contact [e-mail].  It will be helpful for us address your reported scenario as much as possible. Because without knowing the stack trace and your customization we are unable to proceed further.
I like the gridcontrols in this setup, but I am having some performance struggles, especially after loading one or two sheets (grids) with large data. Is it possible to load a couple of grids as virtual grids with data from Excel and then run cross reference formulas on the virtual grids from standard grids within the workbook?	Yes, our VirtualGrid does have the support to compute the formula so cross sheet reference formula will also work. If you want to editing support for our virtual grid, you could use the SavecellInfo event and maintain the data in separate database. Please find the below UG link,  https://help.syncfusion.com/windowsforms/grid/virtual-grid?cs-save-lang=1&cs-lang=csharp#creating-a-data-source
have been saving and reloading each grid from XML serialization and that gets quite slow.     Also, after having a couple of large sheets, if I try to serialize with a cell that has a background image it causes an outofmemory exception.	We have tested your report scenario in gridcontrol with 10000 rows and 40 columns. There is no performance delay and out of memory exception while trying to serialize with BackGroundImage cell. So, we suspect that this issue may occur based on your customization. So, if it is possible please provide your sample application. If you are not willing to share your application through this public post. Please share your sample through our personal contact [e-mail]   It will be more helpful for us to investigate further and provide the solution as much as possible.  Please let us know if you have any hurdles to share your application through our personal contact [e-mail]
What I am trying to do is have a workbook where 2 sheets will have data loaded in them via import from Excel, and then 2 worksheets that summarize and visualize the data in the 2 "data sheets". These 4 sheets are saved and loaded via xml serialization. The summary sheets use formulas to manage data, gridcontrols are embedded and images are added via background image in a couple of cells here and there. This works well with small data sets, but when one data sheet has 5000 rows and 40 columns, and the other data sheet has about 10,000 rows and 40 columns, the formulas calculate slowly, images can't serialize without crashing, and the serialization is slow when it does succeed.

Regards, 

Mohanraj G