In MS-Excel, instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells. Similarly you can fill the formula in one cell to multiple cells. One way to accomplish the same is to drag the fill handle at the bottom right corner of the cell. Capturing this Fill Handle and dragging this on the neighboring cells fill them with the same formula. Another option is you can automatically fill a formula downward, for all cells that it applies to, by simply double-clicking the Fill Handle of the first cell that contains the formula. For example, cells A1:A15 and B1:B15 contain numbers, and you type the formula =A1+B1 in cell C1. To copy that formula into cells C2:C15, select cell C1 and double-click the fill handle. Spread already provides built in functionality to drag and fill data or formulas using the Fill Handle by setting the AllowDragFill property of Spread to true. But it does not provide the second approach to fill the formula in a column by double clicking on the Fill Handle. This blog describes how you can implement the Excel shortcut of filling the formula in a column. All you need to do is to check the mouse location in the CellDoubleClick event of Spread to see if it's in the drag fill area of the cell. You can use the HitTest method to determine if the mouse is on the Fill Handle currently. This method takes the mouse coordinates as input and returns which part of the Spread was clicked. Once you get to know that the drag handle has been clicked, you can simply manipulate the row and column indexes to copy/paste the formula in the desired cells. Here is the code to implement the same:
FarPoint.Win.Spread.HitTestInformation hti;
FarPoint.Win.Spread.ViewportHitTestInformation vi;
hti = fpSpread1.HitTest(e.X, e.Y);
vi = hti.ViewportInfo;
if (hti.Type == FarPoint.Win.Spread.HitTestType.Viewport)
{
if (vi.InDragFill == true)
{
if (fpSpread1.ActiveSheet.Cells[e.Row, e.Column].Formula != "")
{
int lindex = -1, rindex = -1;
int r = e.Row;
if (e.Column != 0)
{
while (fpSpread1.ActiveSheet.Cells[r, e.Column - 1].Value != null)
{
lindex = r;
r++;
}
if (lindex == -1)
{
while (fpSpread1.ActiveSheet.Cells[r, e.Column + 1].Value != null)
{
rindex = r;
r++;
}
}
}
else
{
while (fpSpread1.ActiveSheet.Cells[r, e.Column + 1].Value != null)
{
rindex = r;
r++;
}
}
if (lindex != -1)
{
for (int r1 = e.Row + 1; r1 <= lindex; r1++)
{
string form = fpSpread1.ActiveSheet.Cells[r1 - 1, e.Column].Formula;
char[] arr = form.ToCharArray();
string newformula = "";
for (int index = 0; index < arr.Length; index++)
{
char temp = arr[index];
int atemp = (int)temp;
string s = "";
if (atemp >= 48 && atemp <= 57)
{
int sindex;
if (index != 0)
{
int btemp = arr[index - 1];
if ((btemp >= 65 && btemp <= 90) || (btemp >= 97 && btemp <= 122))
{
for (sindex = index; sindex < arr.Length; sindex++)
{
if ((int)arr[sindex] >= 48 && (int)arr[sindex] <= 57)
{
s = s + arr[sindex].ToString();
}
else
{
break;
}
}
index = sindex - 1;
int sint = Convert.ToInt32(s);
sint = sint + 1;
s = sint.ToString();
newformula = newformula + s;
}
else
{
newformula = newformula + arr[index];
}
}
}
else
{
newformula = newformula + arr[index];
}
}
fpSpread1.ActiveSheet.Cells[r1, e.Column].Formula = newformula;
}
}
else
{
if (rindex != -1)
{
for (int r1 = e.Row + 1; r1 <= rindex; r1++)
{
string form = fpSpread1.ActiveSheet.Cells[r1 - 1, e.Column].Formula;
char[] arr = form.ToCharArray();
string newformula = "";
for (int index = 0; index < arr.Length; index++)
{
char temp = arr[index];
int atemp = (int)temp;
string s = "";
if (atemp >= 48 && atemp <= 57)
{
int sindex;
if (index != 0)
{
int btemp = arr[index - 1];
if ((btemp >= 65 && btemp <= 90) || (btemp >= 97 && btemp <= 122))
{
for (sindex = index; sindex < arr.Length; sindex++)
{
if ((int)arr[sindex] >= 48 && (int)arr[sindex] <= 57)
{
s = s + arr[sindex].ToString();
}
else
{
break;
}
}
index = sindex - 1;
int sint = Convert.ToInt32(s);
sint = sint + 1;
s = sint.ToString();
newformula = newformula + s;
}
else
{
newformula = newformula + arr[index];
}
}
}
else
{
newformula = newformula + arr[index];
}
}
fpSpread1.ActiveSheet.Cells[r1, e.Column].Formula = newformula;
}
}
}
}
}
}
Refer to the attached samples for complete implementation. Download Sample C# Download Sample VB
MESCIUS inc.