# 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**.