Implementing Auto Fill Handle in Spread Studio

  • 0 Comments

Apart from being the most compelling and fascinating control, Spread Studio is one of the best when it comes to customization. Customizing Spread Studio is not at all a difficult task, all that is needed is a tiny amount of time along with a few quick efforts. Spread Studio provides us with a plenty of functionality. Oneof these functionalities is Copying value to a range of cells. But what if the requirement is to copy a series instead? Auto Fill Handle, the same feature which is already present in Excel, is available in Spread Studio. Instead of entering data manually on a worksheet, one can use the Auto Fill Handle feature to fill cells with data that follows a pattern or that is based on data in other cells. Dragging and selecting a range and copying its contents, formatting or series to the selected range of cells. AutoFillHandle2 This blog will demonstrate how you are able to implement the following functionalities in Spread Studio: 1. The 'Auto Fill Handle' as in Excel which implements the below functionalities. Copy Cells _ Fill Series_ _ Fill Formatting Only_ _ Fill Without Formatting_ 2. Filling a range of cells with Auto Increment/Decrement functionality.

Implementing the 'Auto Fill Handle'

1. Get the Selected Range


fpSpread1.ActiveSheet.GetSelection(0);

2. Select the operation to be performed from the ContextMenu of Spread Studio and perform the selected operation:


            //Copy complete cell along with formatting  
            FarPoint.Win.Spread.Cell cell = fpSpread1.ActiveSheet.Cells[rowbegin, columnbegin];  
            if (direction == "Down")  
            {  
                for (int i = rowbegin + 1; i < (fpSpread1.ActiveSheet.GetSelection(0).RowCount + rowbegin); i++)  
                {  
                    fpSpread1.ActiveSheet.Cells[i, columnbegin].Value  = cell.Value;  
                    copyFormat(i, columnbegin, cell);  
               }  
            }  
            else if (direction == "Up")  
            {  
                for (int i = rowbegin - 1; i > (rowbegin - fpSpread1.ActiveSheet.GetSelection(0).RowCount); i--)  
                {  
                    fpSpread1.ActiveSheet.Cells[i, columnbegin].Value = cell.Value;  
                    copyFormat(i, columnbegin, cell);  
                }  
            }  

            else if (direction == "Right")  
            {  
                for (int i = columnbegin + 1; i < (fpSpread1.ActiveSheet.GetSelection(0).ColumnCount + columnbegin); i++)  
                {  
                    fpSpread1.ActiveSheet.Cells[rowbegin, i].Value = cell.Value;  
                    copyFormat(rowbegin, i,cell);  
                }  
            }  

            else if (direction == "Left")  
            {  
                for (int i = columnbegin - 1; i > (columnbegin - fpSpread1.ActiveSheet.GetSelection(0).ColumnCount); i--)  
                {  
                    fpSpread1.ActiveSheet.Cells[rowbegin, i].Value = cell.Value;  
                    copyFormat(rowbegin, i, cell);  
                }  
            }

The above implementation will copy a cell value to the selected range of Cells along with its formatting.

Filling a range of Cells with Auto Increment/Decrement functionality

Implementing this feature of Excel is not a tough job either. Here is what you'll need to manage: 1. Check if the 'CTRL' key is pressed. 2. Perform the functionality(if 'CTRL' is pressed)


          //Auto increment/decrement by drag with CTRL key  
            if (ctrlpressed == true && celldrag == true)  
            {  
                int num = Convert.ToInt32(fpSpread1.ActiveSheet.Cells[e.RowBegin, e.ColumnBegin].Value);  
                FarPoint.Win.Spread.Cell cell = fpSpread1.ActiveSheet.Cells[e.RowBegin, e.ColumnBegin];  
                int j = 1;  
                if (e.Direction == FarPoint.Win.Spread.FillDirection.Down)  
                {  
                    for (int i = e.RowBegin + 1; i < (fpSpread1.ActiveSheet.GetSelection(0).RowCount + e.RowBegin); i++)  
                    {  
                        fpSpread1.ActiveSheet.Cells[i, e.ColumnBegin].Value = num + j;  
                        copyFormat(i, e.ColumnBegin, cell);  
                        ++j;  
                    }  
                }  

                else if (e.Direction == FarPoint.Win.Spread.FillDirection.Up)  
                {  
                    for (int i = e.RowBegin - 1; i > (e.RowBegin - fpSpread1.ActiveSheet.GetSelection(0).RowCount); i--)  
                    {  
                        fpSpread1.ActiveSheet.Cells[i, e.ColumnBegin].Value = num - j;  
                        copyFormat(i, e.ColumnBegin, cell);  
                        ++j;  
                    }  
                }  

                else if (e.Direction == FarPoint.Win.Spread.FillDirection.Right)  
                {  
                    for (int i = e.ColumnBegin + 1; i < (fpSpread1.ActiveSheet.GetSelection(0).ColumnCount + e.ColumnBegin); i++)  
                    {  
                        fpSpread1.ActiveSheet.Cells[e.RowBegin, i].Value = num + j;  
                        copyFormat(e.RowBegin, i, cell);  
                        ++j;  
                    }  
                }  

                else if (e.Direction == FarPoint.Win.Spread.FillDirection.Left)  
                {  
                    for (int i = e.ColumnBegin - 1; i > (e.ColumnBegin - fpSpread1.ActiveSheet.GetSelection(0).ColumnCount); i--)  
                    {  
                        fpSpread1.ActiveSheet.Cells[e.RowBegin, i].Value = num - j;  
                        copyFormat(e.RowBegin, i, cell);  
                        ++j;  
                    }  
                }  
            }  
            else  
            {  
                celldrag = false;  
                rowbegin = e.RowBegin;  
                columnbegin = e.ColumnBegin;  
                direction = e.Direction.ToString();  
                copyCellsToolStripMenuItem_Click(null, null);  
            }

Now you are ready to Auto Fill the data in the SpreadSheet :) Any suggestions or improvements are warmly welcomed. Download Sample - C# Download Sample - VB

MESCIUS inc.