Series
The document model has the ability to automatically construct series of data using a specified pattern or data that is already in the worksheet. The Auto Fill feature can continue series of numbers, dates, time periods, or number and text combinations based on start and step values. The automatic fill supports the following series: linear, growth, date and auto fill.
To use the auto fill functionality, first you need to create a CellSelection for the range of cells that you want to populate. Note that the range should include starting values of the series. The CellSelection offers the following methods for series construction:
FillDataSeriesLinear: Calculates the value of each cell after the initial values by adding a specific Step value to the value of the previous cell.
FillDataSeriesLinearTrend: Calculates the values of the series using linear fitting algorithm for finding the best line for the initial values.
FillDataSeriesExponential: Calculates the values of each cell after the initial values by multiplying the value of the previous cell by a specific Step value.
FillDataSeriesExponentialTrend: Calculates the values of the series using exponential fitting algorithm for finding the best exponential curve for your initial values.
FillDataSeriesDate: Fills date values incrementally using a specific Step value that can represent the number of days, weekdays, months or years.
FillDataSeriesAuto: Automatically continues complex patterns of numbers, number and text combinations, dates, or time periods. Typically, it uses linear fitting algorithm to find the next value of the series.
The rest of the article contains detailed information and examples for each of the aforementioned methods.
Linear Series
The FillDataSeriesLinear() method of the CellSelection class constructs linear series of data. The method has two required and one optional parameters. The first parameter is of type CellOrientation and indicates whether the series are oriented horizontally or vertically. The second parameter determines the step value that will be added to each cell to continue the series. The optional parameter is used to stop the series at a certain value. If this parameter is set and the series reaches the specified stop value, all consequent cells will be left empty.
Example 1 creates a new worksheet that has the value 1 in cell A1 and 3 in B1. The FillDataSeriesLinear() method is invoked for the cell region A1:F1. Thus, the values 1, 3, 5, 7, 9 and 11 appear in the range A1:F1.
Example 1: Fill linear series
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue(1);
worksheet.Cells[0, 1].SetValue(3);
CellRange range = new CellRange(0, 0, 0, 5);
worksheet.Cells[range].FillDataSeriesLinear(CellOrientation.Horizontal, 2);
Figure 1 demonstrates the result of Example 1.
Figure 1: Linear series
Linear Trend Series
The FillDataSeriesLinearTrend() method produces series using a linear fitting algorithm for finding the best line for the initial values pattern. The method requires a single argument of type CellOrientation that determines if the series are oriented horizontally or vertically.
Example 2 shows how to use FillDataSeriesLinearTrend() to continue series values 1, 5 from the range A1:B1 and result will be series 1, 5, 9, 13, 17, 21 in the range A1:F1.
Example 2: Fill linear trend series
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue(1);
worksheet.Cells[0, 1].SetValue(8);
worksheet.Cells[0, 2].SetValue(3);
worksheet.Cells[0, 3].SetValue(10);
worksheet.Cells[0, 4].SetValue(5);
CellRange range = new CellRange(0, 0, 0, 9);
worksheet.Cells[range].FillDataSeriesLinearTrend(CellOrientation.Horizontal);
Figure 2 demonstrates the result of Example 2.
Figure 2: Linear trend series
Figure 3 illustrates the difference between the initial values and the result. Note that the result values construct the best fit line for the initial data.
Figure 3: Differences between initial values and linear trend
Exponential Series
The FillDataSeriesExponential() method calculates the values of each cell after the initial value by multiplying the contents of the previous cell by a specified step value. Like FillDataSeriesLinear(), the method has two required and one optional parameters. The first is of type CellOrientation and is used to determine whether the orientation of the series is horizontal or vertical. The second argument is a double value that indicates the step between each two consecutive values of the series. The optional parameter specifies the stop value of the series and is used to stop the series when a certain value is reached.
Example 3 shows how to use the FillDataSeriesExponential() method to continue series with initial values 1 and 3 that appear in cells A1 and B1 respectively. After the method is invoked, the region A1:F1 contains the following values: 1, 4, 16, 64, 256 and 1024.
Example 3: Fill exponential series
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue(1);
worksheet.Cells[0, 1].SetValue(3);
CellRange range = new CellRange(0, 0, 0, 5);
worksheet.Cells[range].FillDataSeriesExponential(CellOrientation.Horizontal, 4);
Figure 4 demonstrates the result of Example 3.
Figure 4: Exponential series
Exponential Trend Series
The FillDataSeriesExponentialTrend() method calculates the values of the series using exponential fitting algorithm for finding the best exponential curve for the initial values. It requires a single argument of type CellOrientation that indicates if the series are horizontal or vertical.
Example 4 shows how to use the FillDataSeriesLinearTrend() method to continue series with initial values 1 and 5 that appear in cells A1 and B1 respectively. After the linear trend is applied, the range A1:F1 holds the following values: 1, 5, 25, 125, 625 and 3125.
Example 4: Exponential trend series
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue(1);
worksheet.Cells[0, 1].SetValue(5);
worksheet.Cells[0, 2].SetValue(2);
worksheet.Cells[0, 3].SetValue(9);
CellRange range = new CellRange(0, 0, 0, 5);
worksheet.Cells[range].FillDataSeriesExponentialTrend(CellOrientation.Horizontal);
Figure 5 demonstrates the result of Example 4.
Figure 5: Exponential trend series
Figure 6 plots two series that contain the initial and result values respectively. Note that the result values form the best fit exponential curve for the initial data.
Figure 6: Differences between initial values and exponential trend
Date Series
The FillDataSeriesDate() method is used to fill date values incrementally using a specific Step that represents the number of days, weekdays, months or years added to each consecutive value. The method has three required and one optional parameters. The first required argument is of type CellOrientation and indicates if the series are horizontal or vertical. The second argument is of type DateUnitType and determines the type of the step value – day, weekday, month or year. The third required parameter specifies the step to be added to each consecutive value. The fourth argument is optional and specifies the stop value of the series. If this parameter is set and the series reaches the specified stop value, all consequent cells are left empty.
Example 5 shows how to construct series that use 5/28/2013 as a starting point and add two weekdays for each consecutive value.
Example 5: Fill date series
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue(new DateTime(2013, 5, 28));
CellRange range = new CellRange(0, 0, 0, 9);
worksheet.Cells[range].FillDataSeriesDate(CellOrientation.Horizontal, DateUnitType.Weekday, 2);
Figure 7 demonstrates the result of Example 5.
Figure 7: Date series
Taking a closer look at the result shows that 5/28/2013 is Tuesday, 5/30/2013 is Thursday, 6/3/2013 is Monday, 6/5/2013 is Wednesday, 6/7/2013 is Friday, and 6/11/2013 is Tuesday. All of the result dates are weekdays and the step between them is exactly two workdays.
Auto Fill Series
The FillDataSeriesAuto() method automatically constructs complex patterns of numbers, number and text combinations, dates or time periods. For most cases it uses linear fitting algorithm to find the next value of the series. The method can fill different types of data automatically. For example, if you input 1, 2 and 3 in consecutive cells and use AutoFill, the result will be 4, 5, 6 and 7. Here is a list with supported data for auto fill:
| Initial Selection | Extended Series |
|---|---|
| 1, 2, 3 | 4, 5, 6… |
| 9:00 | 10:00, 11:00, 12:00… |
| Mon | Tue, Wed, Thu… |
| Monday | Tuesday, Wednesday, Thursday… |
| Jan | Feb, Mar, Apr… |
| Jan, Apr | Jul, Oct, Jan… |
| 15-Jan, 15-Apr | 15-Jul, 15-Oct… |
| 2007, 2008 | 2009, 2010, 2011… |
| Q3 | Q4, Q1, Q2… |
| Quarter3 | Quarter4, Quarter1, Quarter2… |
| text1, textA | text2, textA, text3, textA… |
| Product 1 | Product 2, Product 3… |
If you invoke FillDataSeriesAuto() for data that does not appear in the list above, the method repeats its initial values.
Similarly to the rest auto filling methods, FillDataSeriesAuto() takes three arguments. The first parameter is called seriesOrientation and is of type CellOrientation. It is used to determine if the series are oriented horizontally or vertically. The second argument specifies the direction of the fill. For example, you can select values from cell A1 to F1 or from cell F1 to A1. The third parameter is optional and is used to define the number of initial values of the series to be used to generate the entire series.
Example 6 shows how to use the FillDataSeriesAuto() method for initial value 1st set in the cell A1. The resulting series filled in the range A1:F1 are as follows: 1st, 2nd, 3rd, 4th, 5th and 6th.
Example 6: Auto fill
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("1st");
CellRange range = new CellRange(0, 0, 0, 5);
worksheet.Cells[range].FillDataSeriesAuto(CellOrientation.Horizontal, true);
Figure 8 demonstrates the result of Example 6.
Figure 8: Auto fill
Example 7 demonstrates the behavior of the FillDataSeriesAuto() method. This time, the initial value 6th appears in cell F1 and the applied auto fill is with reversed direction. Note that the constructed CellRange is actually F1:A1, instead of A1:F1. The resulting series are: 11th, 12th, 9th, 8th, 7th and 6th.
Example 7: Auto fill reversed direction
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 5].SetValue("6th");
CellRange range = new CellRange(0, 5, 0, 0);
worksheet.Cells[range].FillDataSeriesAuto(CellOrientation.Horizontal, true);
Figure 9 demonstrates the result of Example 7.
Figure 8: Auto fill reversed direction
