Repeat Values
The document model allows you to automatically repeat data that has already been entered in your worksheet. The auto fill feature is useful when you want to copy the contents of a row or a column into its adjacent rows or columns. You can spread the values into a specified range instead of populating the cells manually.
Repeating Values
To repeat the values, first create a CellSelection for the range of cells that you want to populate. Note that the range must include the values that you want to repeat. Then, invoke the FillData() method of the CellSelection instance and pass the appropriate FillDirection as an argument. There are four FillDirection values:
-
Left: The values in the rightmost column are copied to the rest of the columns in the range. -
Up: The values in the bottom row are copied to the rest of the rows in the range. -
Right: The values in the leftmost column are copied to the rest of the columns in the range. -
Down: The values in the top row are copied to the rest of the rows in the range.
Example 1 illustrates how the contents of column A can be copied to the rest of the columns in the range A1:D4. The code creates a new worksheet and populates the cells A1, A2, A3, and A4 with the values 5, 8, 13, and 21 respectively. It then invokes the FillData() method for the specified range with FillDirection.Right.
Example 1: Fill values to the right across a selected range
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
Worksheet activeWorksheet = workbook.ActiveWorksheet;
activeWorksheet.Cells[0, 0].SetValue(5);
activeWorksheet.Cells[1, 0].SetValue(8);
activeWorksheet.Cells[2, 0].SetValue(13);
activeWorksheet.Cells[3, 0].SetValue(21);
CellRange range = new CellRange(0, 0, 3, 3);
activeWorksheet.Cells[range].FillData(FillDirection.Right);Figure 1 demonstrates the result of Example 1.
Figure 1: Column A values repeated across columns A through D
Similarly, you can automatically copy the values of a row to its adjacent rows.
Example 2 invokes the FillData() method with FillDirection.Down for the range B2:D4. The sample code creates an empty worksheet and enters values in the range B2:D2. These values are propagated to the rest of the rows in the specified region.
Example 2: Fill values down across a selected range
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
Worksheet activeWorksheet = workbook.ActiveWorksheet;
activeWorksheet.Cells[1, 1].SetValue(34);
activeWorksheet.Cells[1, 2].SetValue(55);
activeWorksheet.Cells[1, 3].SetValue(89);
CellRange range = new CellRange(1, 1, 3, 3);
activeWorksheet.Cells[range].FillData(FillDirection.Down);Figure 2 demonstrates the result of Example 2.
Figure 2: Row 2 values repeated down through the selected rows
