autofill by formula

[bjhjh]

hello:
Is there a way to enable the Auto Fill Option known from Excel in a Gridcontrol.

hello:

Auto Fill Option in Excel:
1.Select a single cell.
2. move the cursor right, bottom corner of the cell. The cursor will change into a + (plus) sign.
3. Hold down the left-mouse button at drag the mouse down.
4. Release the left-mouse button. Voila a number of cells are filled with the same value as the initial cell.

the question is:Voila a number of cells are filled with the same text value but no formula!

how this like excel ?

[Administrator]

Hi China, The mentioned behavior can be achieved by using the ExcelMarkerMouseController, which could be found in the \Syncfusion\Essential Suite\Grid\Samples\In Depth\ExcelSelectionMarker sample, and by setting the FormulaCopyFlags of GridFormulaEngine as mentioned below. GridFormulaEngine engine = ((GridFormulaCellModel)this.gridControl1.CellModels["FormulaCell"]).Engine; engine.FormulaCopyFlags |= GridFormulaCopyFlags.ClipBoardReferencesAdjusted; Then the first cell value should be copied and pasted in all the dragged cells in the MouseUp event of the ExcelMarkerMouseController. Sample attached. Regards, Calvin.

41389.zip

[bjhjh]

hi Calvin. your are right, but please see my atchment files. 1. why not so (fill copy formula) when I copy a formula cell to a rang or copy a rang to a rang? 2. the second column is readonly , why I can copy or drag a formula cell expand to this column ? this isn,t what i want . what can i do? 3. please select a rang an click right mousebutton. do you find something is wrong?(the rang hi_light). sorry for my english. china >Hi China, > >The mentioned behavior can be achieved by using the ExcelMarkerMouseController, which could be found in the \Syncfusion\Essential Suite\Grid\Samples\In Depth\ExcelSelectionMarker sample, and by setting the FormulaCopyFlags of GridFormulaEngine as mentioned below. > >GridFormulaEngine engine = ((GridFormulaCellModel)this.gridControl1.CellModels["FormulaCell"]).Engine; > engine.FormulaCopyFlags |= GridFormulaCopyFlags.ClipBoardReferencesAdjusted; > >Then the first cell value should be copied and pasted in all the dragged cells in the MouseUp event of the ExcelMarkerMouseController. Sample attached. > >Regards, >Calvin.

41389.zip

myexample.zip

[Administrator]

Hi,

I apologize for the delay in replaying. We are looking in to the mentioned issue (1, 2) and will get back soon with a solution.

Issue 3:- Setting the
this.gridControl1.SelectCellsMouseButtonsMask = ((System.Windows.Forms.MouseButtons)((System.Windows.Forms.MouseButtons.Left | System.Windows.Forms.MouseButtons.Middle)));

will avoid the selection being changed while the right mouse button is click.

Thanks for your patience.

Regards,
Calvin.

[bjhjh]

Hi Calvin:
Thanks for the response.

I have find some other mistakes with GC''s formula.

1. can not select the cell in other sheet by mouse!

2. if i hand type "=Sheet2!A1" in A1 of Sheet1, drag the mouse by hold the cell''s right, bottom corner .
then in A2, the formula will be "=Sheet3!A2"
,you know that''s a mistake!

3. if i type "=Sheet2!A1" in A1 of Sheet1, then rename Sheet2, the formula still be "=Sheet2!A1" , what can i do to solve this question?

4,if i Select a range( b2:c3),drag the mouse by hold the range''s right, bottom corner .
expand to a new range(b2:c5),Release the left-mouse button. then you can find : not only the cell in new range (b2:c5)''formula be fill.

many many other cell do. why?

thank you ,

i want the GC like excel and good than it.

[PLIACHAS PASCHALIS]

is it possible to get some code for this functionality in VB.Net? Thanks a lot PP >hi Calvin. > > your are right, but please see my atchment files. > > 1. why not so (fill copy formula) when I copy a formula cell to a rang or copy a rang to a rang? > > 2. the second column is readonly , why I can copy or drag a formula cell expand to this column ? this isn,t what i want . what can i do? > > 3. please select a rang an click right mousebutton. do you find something is wrong?(the rang hi_light). > > sorry for my english. > > > china > > >>Hi China, >> >>The mentioned behavior can be achieved by using the ExcelMarkerMouseController, which could be found in the \Syncfusion\Essential Suite\Grid\Samples\In Depth\ExcelSelectionMarker sample, and by setting the FormulaCopyFlags of GridFormulaEngine as mentioned below. >> >>GridFormulaEngine engine = ((GridFormulaCellModel)this.gridControl1.CellModels["FormulaCell"]).Engine; >> engine.FormulaCopyFlags |= GridFormulaCopyFlags.ClipBoardReferencesAdjusted; >> >>Then the first cell value should be copied and pasted in all the dragged cells in the MouseUp event of the ExcelMarkerMouseController. Sample attached. >> >>Regards, >>Calvin.

41389.zip

myexample.zip

[bjhjh]

Hi Calvin: Thanks for the response. I have find some other mistakes with GC''''s formula. 1. can not select the cell in other sheet by mouse! 2. if i hand type "=Sheet2!A1" in A1 of Sheet1, drag the mouse by hold the cell''''s right, bottom corner . then in A2, the formula will be "=Sheet3!A2" ,you know that''''s a mistake! 3. if i type "=Sheet2!A1" in A1 of Sheet1, then rename Sheet2, the formula still be "=Sheet2!A1" , what can i do to solve this question? 4,if i Select a range( b2:c3),drag the mouse by hold the range''''s right, bottom corner . expand to a new range(b2:c5),Release the left-mouse button. then you can find : not only the cell in new range (b2:c5)''''formula be fill. many many other cell do. why? thank you , i want the GC like excel and good than it. please see my attachment!

y41388.zip

[PLIACHAS PASCHALIS]

is it possible to get that code in VB.net.
Thanks for help anyway.
PP

[Administrator]

Hi China, Thanks for the sample and, I am sorry for the delay in replying. >> 1. why not so (fill copy formula) when I copy a formula cell to a rang or copy a rang to a rang? The above issue can be worked around by handling the ClipboardPaste and the QueryCellFormattedText events. >> 2. the second column is readonly , why I can copy or drag a formula cell expand to this column ? this isn,t what i want . what can i do? This can be avoided by handling the SaveCellInfo event. >> 1. can not select the cell in other sheet by mouse! >>3. if i type "=Sheet2!A1" in A1 of Sheet1, then rename Sheet2, the formula still be "=Sheet2!A1" , what can i do to solve this question? There is no built in support for the above two behavior and we have to implement this and will update with a sample little latter. >>4 ,if i Select a range( b2:c3),drag the mouse by hold the range''''''''s right, bottom corner . expand to a new range(b2:c5),Release the left-mouse button. then you can find : not only the cell in new range (b2:c5)''''''''formula be fill. In the ExcelMarkerMouseController MouseUp event the algorithm which is used, is for copying from one cell to many. For copying a range of cell we have to use a different algorithm. And we are looking in to the other issues and will get back soon with a solution. Sorry for the inconvenience caused. Hi Pliachas, Sample attached in both C# and VB. Regards, Calvin.

F41389.zip

[PLIACHAS PASCHALIS]

I have a problem when i drag the selection down to copy the formula to the cells bellow. maybe i have a code problem. Thanks for help anyway

excel1.zip

[Administrator]

Hi Pliachas, I have made some small changes in the MouseUp and MouseMove methods of the ExcelMarkerMouseController class and also attached the ExcelMarkerMouseController.vb file. Let us know if this helps. Regards, Calvin.

ExcelMarkerMouseController.zip

[PLIACHAS PASCHALIS]

Hi Calvin i pasted the .vb file in my project folder. i noticed the following. In cells A2, A3, A4, A5, B2, B3, B4, B5 i put the following numbers: 23,45,1,2,4,5,6,7. In cell C2 i write =A2+B2. i drag the cell down till the fifth row and the c2 cell haws the following value 27=A2+B2, c3 has 27, c4 has 27, c5 has 27, and d2, d3, d4, d5 also 27. What is wrong? PP >Hi Pliachas, > >I have made some small changes in the MouseUp and MouseMove methods of the ExcelMarkerMouseController class and also attached the ExcelMarkerMouseController.vb file. Let us know if this helps. > >Regards, >Calvin.

ExcelMarkerMouseController.zip

[Administrator]

Hi Pliachas,

It works fine for me. Please check whether you have set the gridFormulaEngine.FormulaCopyFlags. Below is a code snippet.

Dim engine As GridFormulaEngine = (CType(Me.gridControl1.CellModels("FormulaCell"), GridFormulaCellModel)).Engine
engine.FormulaCopyFlags = engine.FormulaCopyFlags Or GridFormulaCopyFlags.ClipBoardReferencesAdjusted

Regards,
Calvin.

[PLIACHAS PASCHALIS]

Hi Calvin i fet the following error:"FormulaCopyFlags is not a member of Syncfusion.windows.Forms.grid.gridformulaengine"
maybe i have a version problem.
PP

>Hi Pliachas,
>
>It works fine for me. Please check whether you have set the gridFormulaEngine.FormulaCopyFlags. Below is a code snippet.
>
> Dim engine As GridFormulaEngine = (CType(Me.gridControl1.CellModels("FormulaCell"), GridFormulaCellModel)).Engine
> engine.FormulaCopyFlags = engine.FormulaCopyFlags Or GridFormulaCopyFlags.ClipBoardReferencesAdjusted
>
>Regards,
>Calvin.

[Administrator]

Hi Pliachas, The FormulaCopyFlags property has been there from the version 2.0.1.x and above. You can get the latest version(4.1.0.62) in the following link. ftp://syncpatch.syncfusion.com/EssentialStudio/v4.1.0.62/syncfusionessentialstudiosetup.exe Regards, Calvin.

[PLIACHAS PASCHALIS]

Thanks once again for your fast reply Calvin. I'' ll try to do that. But it''s about 222 MB for a n ISDN connection. Thanks anyway PP >Hi Pliachas, > >The FormulaCopyFlags property has been there from the version 2.0.1.x and above. You can get the latest version(4.1.0.62) in the following link. >ftp://syncpatch.syncfusion.com/EssentialStudio/v4.1.0.62/syncfusionessentialstudiosetup.exe > >Regards, >Calvin.

[IBAH]

Hello,

I''m implementing functionaly like one discussed in this topic. So I''m filling formulas by pasting from clipboard. It is working fine - but has really poor perfomance (try F41389.zip, mentioned in this topic, for example). It is OK for a few cells, but I need to fill a column with more then hundred rows.
Is there any way to improve it by using some Grid properties or methods? BeginUpdate before and EndUpdate after pasting each cell is helping a bit - but not too much. BeginUpdate before starting to paste and EndUpdate after finishing to paste all cells makes everything working fast - but only the first cell is updated correctly.

[Administrator]

Hi IBAH,

You have to activate the cell and then do the pasting after that, to get the formula adjusted and that causes the performance issue for more number of rows. I am not sure if there is any way to avoid this performance issue. Try also calling the grid.Model.SuspendChangeEvents()before and grid.Model.ResumeChangeEvents() after pasting each cell.

Regards,
Calvin.

[Michael]

Calvin,

I have been following this thread as many of the issues are prevalent to my needs. Copying and pasting cells (individually or ranges) with formulas and updating references is pretty important knowledge and difficult to gleam from documentation. So, this thread has been useful. I suggest this be turned into a documented example.

Regarding the performance issue, I too find it fairly sluggish when pasting formulas into a complete column. I understand the source of the performance, but a solution would be nice.

In the mean time, using the grid.Model.SuspendChangeEvents()before and grid.Model.ResumeChangeEvents() the paste, disabled reference updating, so it does not help. Is there anything you can think of anything else that might also help? Is there a way to disable refreshing the screen until the copy is completed? It might not improve the performance, but would at least make it more professional in that the user does not see the grid struggling to get the formulas pasted.

Thanks,

Michael

>Hi IBAH,
>
>You have to activate the cell and then do the pasting after that, to get the formula adjusted and that causes the performance issue for more number of rows. I am not sure if there is any way to avoid this performance issue. Try also calling the grid.Model.SuspendChangeEvents()before and grid.Model.ResumeChangeEvents() after pasting each cell.
>
>Regards,
>Calvin.

[Administrator]

Hi Michael,

Sandwich the owner.CurrentCell.MoveTo(row, col); and owner.Model.CutPaste.Paste(); with SuspendChangeEvents and ResumeChangeEvents pair works much better (in version 4.1). Please try the below changes in the ExcelMarkerMouseController class in the previous sample(F41389.zip) and let us know if that helps.

public void MouseUp(MouseEventArgs e)
{
// Stop automatic scrolling
owner.AutoScrolling = ScrollBars.None;

int rowIndex, colIndex;
Point point = new Point(e.X, e.Y);
owner.PointToRowCol(point, out rowIndex, out colIndex);

dragWindow.Visible = false;
dragWindow = null;

if(rowIndex == -1 )
rowIndex = r;
if(colIndex == -1)
colIndex =c;

GridRangeInfo range = GridRangeInfo.Cells(activeRange.Top, activeRange.Left, rowIndex, colIndex);
owner.CurrentCell.SetCurrentCellNoActivate(activeRange.Top, activeRange.Left);
owner.Model.CutPaste.Copy();

int row = 0;
int col = 0;

if (range.GetFirstCell(out row, out col))
{

owner.CurrentCell.SetCurrentCellNoActivate(col, row);
owner.Model.CutPaste.Paste();
while(range.GetNextCell(ref row, ref col))
{
owner.Model.SuspendChangeEvents();//Added
owner.CurrentCell.MoveTo(row, col);
owner.Model.CutPaste.Paste();
owner.Model.ResumeChangeEvents();//Added
}
owner.Selections.Add(range);
owner.Refresh();
}

}

Regards,
Calvin.

[Michael]

Calvin, I tried your suggestion and still no good. I just get the formula without updated references. Here is my code:

Dim engine As GridFormulaEngine = (CType(Me.grid.CellModels("FormulaCell"), GridFormulaCellModel)).Engine
engine.FormulaCopyFlags = engine.FormulaCopyFlags Or GridFormulaCopyFlags.ClipBoardReferencesAdjusted

Dim currentColumn As Integer
currentColumn = grid.CurrentCell.ColIndex

For i As Integer = 2 To grid.RowCount - 2
grid.Model.SuspendChangeEvents()
grid.CurrentCell.MoveTo(i, currentColumn)
grid.Model.CutPaste.Paste()
grid.Model.ResumeChangeEvents()

Next

Did I convert the C# code correctly for your suggestion?

[Administrator]

Hi Michael,

The SuspendChangeEvents() and the ResumeChangeEvents() works fine with the work around that given for “copy a formula cell to a rang or copy a rang to a rang” in the sample F41389.zip. Please try the below snipped code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim currentColumn As Integer
currentColumn = gridControl1.CurrentCell.ColIndex

gridControl1.Model.CutPaste.Copy()
For i As Integer = 2 To gridControl1.RowCount - 2
gridControl1.Model.SuspendChangeEvents()
gridControl1.CurrentCell.MoveTo(i, currentColumn)
gridControl1.Model.CutPaste.Paste()
gridControl1.Model.ResumeChangeEvents()
Next
Me.gridControl1.Invalidate()
End Sub

Private isPaste As Boolean = False
Private Sub gridControl1_ClipboardCopy(ByVal sender As Object, ByVal e As Syncfusion.Windows.Forms.Grid.GridCutPasteEventArgs) Handles gridControl1.ClipboardCopy
''Flag for ReferencesAdjusted Workaround....
Me.isPaste = True
End Sub

Private Sub gridControl1_QueryCellFormattedText(ByVal sender As Object, ByVal e As Syncfusion.Windows.Forms.Grid.GridCellTextEventArgs) Handles gridControl1.QueryCellFormattedText
If e.Style.CellType = "FormulaCell" Then
Dim model As GridFormulaCellModel = (CType(Me.gridControl1.CellModels("FormulaCell"), GridFormulaCellModel))
Dim fi As System.Reflection.FieldInfo = model.GetType().GetField("isPaste", System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.GetField Or System.Reflection.BindingFlags.NonPublic)
fi.SetValue(model, Me.isPaste)
End If
End Sub

Regards,
Calvin.

[Maria Catsoro]

It work but the solution is very slow.
There is another solution to solve the problem ?
I have to decide if by the product but I need copy formula is very fast.
Thank you
Maria


>Hi Michael,
>
>The SuspendChangeEvents() and the ResumeChangeEvents() works fine with the work around that given for “copy a formula cell to a rang or copy a rang to a rang” in the sample F41389.zip. Please try the below snipped code.
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
> Dim currentColumn As Integer
> currentColumn = gridControl1.CurrentCell.ColIndex
>
> gridControl1.Model.CutPaste.Copy()
> For i As Integer = 2 To gridControl1.RowCount - 2
> gridControl1.Model.SuspendChangeEvents()
> gridControl1.CurrentCell.MoveTo(i, currentColumn)
> gridControl1.Model.CutPaste.Paste()
> gridControl1.Model.ResumeChangeEvents()
> Next
> Me.gridControl1.Invalidate()
> End Sub
>
> Private isPaste As Boolean = False
> Private Sub gridControl1_ClipboardCopy(ByVal sender As Object, ByVal e As Syncfusion.Windows.Forms.Grid.GridCutPasteEventArgs) Handles gridControl1.ClipboardCopy
> 'Flag for ReferencesAdjusted Workaround....
> Me.isPaste = True
> End Sub
>
> Private Sub gridControl1_QueryCellFormattedText(ByVal sender As Object, ByVal e As Syncfusion.Windows.Forms.Grid.GridCellTextEventArgs) Handles gridControl1.QueryCellFormattedText
> If e.Style.CellType = "FormulaCell" Then
> Dim model As GridFormulaCellModel = (CType(Me.gridControl1.CellModels("FormulaCell"), GridFormulaCellModel))
> Dim fi As System.Reflection.FieldInfo = model.GetType().GetField("isPaste", System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.GetField Or System.Reflection.BindingFlags.NonPublic)
> fi.SetValue(model, Me.isPaste)
> End If
> End Sub
>
>Regards,
>Calvin.