Generic Population of Data - Specifically formatting DateTime automatically

[Charles Vereker]

I think that my question is fairly specific, but I''ll give the background to make it clear!

I am encapsulating the GridControl in my own user control. I have written some generic data population methods - each takes the cell reference of the top left hand corner of the area where the data will be pasted into the grid. The third parameter is either a DataTable, an Array, my own Matrix class, or my own TimeSeries class (which encapsulates an array of DateTime and a Matrix of doubles). These all use GridControl.Model.PopulateValues.

MY QUESTION is to do with automatically formatting the data. I can explicitly set the format of each cell, but this slows things down. Is their a method similar to PopulateValues that can populate the format for a range. e.g. take a GridRangeInfo object and a format, and set the range to Dates.

Additionally, do I need to build on the solution to this problem to have my own right-click format settings (like Excel).

As a post-script, I have a few other small questions.
1) Is there an easy way to copy and paste formula such that the cell references are transposed (like excel does?). (Not just when explictly using copy and paste, but also when inserting a row or column).
2) Following on from the FormulaCellSelection example in the documentation, is there a way to scoll to the bottom of a range using (e.g.) the END key or SHIFT+END to highlight? I tried looping through the cells until I found an empty cell, but it was very very slow!
3) Array style formulae (i.e. formulae that return an array, rather then a single value). Are these supported? I guess that one might be able to hack something together with named ranges, but it wouldn''t be easy!

Suggestion: I tried to add a generic formula, but failed. It would be great if within the formula delegate one could find out the string name of the formula that was invoked. I have a database that control various statistics that we can calculate. I have a seperate engine that is always being added to that understands the statistic codes and can calculate the required statistics. To complete the circle in a very nice way, it would be great to let the GridControl formula engine be populated with the formula from the database. Then a single delegate would have to look up the name of the formula in a DataTable to find the StatisticID, which could then be passed to my Statistics Engine. I guess this can be implemented via reflection, by modifying my assembly on the fly.

[Administrator]

>MY QUESTION is to do with automatically formatting the data. I can explicitly set the format of each cell, but this slows things down. Is their a method similar to PopulateValues that can populate the format for a range. e.g. take a GridRangeInfo object and a format, and set the range to Dates.

Try grid.ChangeCells. There are several overloads. You can create a new GridStyleInfo object with the exact new settings you want to apply, and then use ChangeCells with MdoifyType = oveerride.

>
>Additionally, do I need to build on the solution to this problem to have my own right-click format settings (like Excel).

Yes. You will have to do this your self. There are no grid library classes that do this.

>1) Is there an easy way to copy and paste formula such that the cell references are transposed (like excel does?).

Yes. Make sure these properties are set. (With 3010, there are a couple of problems like the very last row will not adjust in some situations, but these known problems will be addressed in teh next release.)

GridFormulaEngine engine = ((GridFormulaCellModel)gridControl1.Model.CellModels["FormulaCell"]).Engine;
engine.FormulaCopyFlags |= GridFormulaCopyFlags.InsDelRangeReferencesUpdated; 
engine.FormulaCopyFlags |= GridFormulaCopyFlags.ClipBoardReferencesAdjusted;

>2) Following on from the FormulaCellSelection example in the documentation, is there a way to scoll to the bottom of a range using (e.g.) the END key or SHIFT+END to highlight? I tried looping through the cells until I found an empty cell, but it was very very slow!

There is no magic way to do this. You will have to loop through cells. If your grid is holding data in its grid.Data object, you can speed things up by a factor of probably 5-10 times by directly accessing the grid.Data object and not accessing the grid object through the indexer. This avoids events at the grid level.

if(grid.Data[row, col] != null)
{
  GridStyleInfo style = new GridStyleInfo(grid.Data[row, col]);
  if(style.Text.Length == 0)
  {
      // emty cell
  }
}

}

>3) Array style formulae (i.e. formulae that return an array, rather then a single value). Are these supported?

These are not directly supported. Here is an old forum thread that has a sample of si=omething of this nature in it.
http://www.syncfusion.com/Support/Forums/message.aspx?MessageID=1995

>
>Suggestion: I tried to add a generic formula, but failed. It would be great if within the formula delegate one could find out the string name of the formula that was invoked.

It would be possible to add an engine property that held the name of the function being called during the call, but I am not sure how this would work out duing recursive type calls, etc. Likely it would just work, but we would have to investigate this to make sure we would not be cauing a problem.

[Charles Vereker]

Thanks for your reply. I''m still working through their implementation, but things are looking rosy! Re:

>GridFormulaEngine engine = ((GridFormulaCellModel)gridControl1.Model.CellModels["FormulaCell"]).Engine;
>engine.FormulaCopyFlags |= GridFormulaCopyFlags.InsDelRangeReferencesUpdated; 
>engine.FormulaCopyFlags |= GridFormulaCopyFlags.ClipBoardReferencesAdjusted;

This throws a null reference exception in syncfusion.grid.windows.dll. ("Additional information: Object reference not set to an instance of an object.") I have looked at

http://www.syncfusion.com/Support/Forums/message.aspx?MessageID=1995

I have not had time to try this out yet, but notice that you say "It may take some non-trivial cleanup work, but attached>/B> is a sample that adds a MatrixAddNumber function to the function library and displays its result as a GridInCell." Is this example still available? It''s no longer still attached to the forum. Thank you Charlie

[Administrator]

Try that code in Form.Load. Make sure the gridControl1 has been created and added to it''s parent''s Controls collection before your try to execute it.

I was able to download the sample from that thread (luckily). I re-uploaded it to this location.

http://www.syncfusion.com/Support/user/uploads/DateMatrixFormulas_6039_a0bf4e74.zip

[Charles Vereker]

Make sure the gridControl1 has been created and added to it''s parent''s Controls collection before your try to execute it. That fixed it!