We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

autofill by formula

Thread ID:

Created:

Updated:

Platform:

Replies:

41389 Feb 28,2006 11:08 PM May 2,2007 04:00 AM Windows Forms 22
loading
Tags: GridControl
bjhjh
Asked On February 28, 2006 11:08 PM

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 [Syncfusion]
Replied On March 1, 2006 09:55 AM

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
Replied On March 2, 2006 11:27 AM

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 [Syncfusion]
Replied On March 6, 2006 10:48 AM

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
Replied On March 8, 2006 03:18 AM

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
Replied On March 9, 2006 04:45 PM

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
Replied On March 12, 2006 10:52 PM

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
Replied On March 13, 2006 01:51 AM

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

Administrator [Syncfusion]
Replied On March 13, 2006 11:36 PM

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
Replied On March 17, 2006 02:35 AM

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 [Syncfusion]
Replied On March 20, 2006 01:28 AM

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
Replied On March 20, 2006 03:08 AM

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 [Syncfusion]
Replied On March 20, 2006 05:34 AM

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
Replied On March 20, 2006 05:21 PM

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 [Syncfusion]
Replied On March 20, 2006 11:01 PM

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
Replied On March 21, 2006 12:59 AM

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
Replied On April 14, 2006 08:25 AM

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 [Syncfusion]
Replied On April 18, 2006 01:42 AM

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
Replied On April 20, 2006 12:58 PM

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 [Syncfusion]
Replied On April 21, 2006 07:07 AM

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
Replied On April 21, 2006 09:17 AM

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 [Syncfusion]
Replied On April 24, 2006 01:13 AM

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
Replied On May 2, 2007 04:00 AM

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.

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;