Fill ranges

EPPlus 5.8 adds three methods to the ExcelRangeBase class to fill values.

• FillNumber
• FillDateTime
• FillList

FillNumber

FillNumber fills a range with numbers. Lets say you want to add a serie of number with an increment of one:

ws.SetValue("A1",50);
//FillNumber with no parameters will fill the range using the value in the top-left cell and increase by one.
ws.Cells["A1:A20"].FillNumber();

You can add a number of parameters to this method like initial value, increment and more. Here are a few samples:

//Fill with a start value of 30 and decrease by 2.
ws.Cells["B1:B20"].FillNumber(30, -2);

//Fill by starting with 100 and increase 5% for each cell. Fill to the left by row
ws.Cells["CD2:AA2"].FillNumber(x =>
{
  x.CalculationMethod = eCalculationMethod.Multiply;
  x.StartValue = 100;
  x.StepValue = 1.05;
  x.Direction = eFillDirection.Row;
});

The output will look like this:

FillDateTime

FillDateTime fills a range using the DateTime data type. It works similar to the FillNumber method.
Here are some samples:

//Default Fill, starting from the value in the top-left cell increase one day per cell.
ws.SetValue("A2", new DateTime(2021, 1, 1));
ws.Cells["A2:A60"].FillDateTime();

//Fill from a start date and increase with two months per cell.
ws.Cells["B2:B60"].FillDateTime(new DateTime(2021, 6, 30), eDateTimeUnit.Month, 2);

//Fill dates per last day of the quarter. If the start value is the last day of the month, this is used for all dates in the fill. 
//This sample exclude weekends and adds some holiday dates. 
//Column C2 and D2 are used as start values.
ws.Cells["C2"].Value = new DateTime(2015, 6, 30);
ws.Cells["D2"].Value = new DateTime(2009, 2, 28);
ws.Cells["C2:D60"].FillDateTime(x =>
{
  x.DateTimeUnit = eDateTimeUnit.Month;
  x.StepValue = 3;
  x.NumberFormat = "yyyy-mm-dd";
  x.SetExcludedWeekdays(DayOfWeek.Saturday, DayOfWeek.Sunday); //We exclude weekends. The day before is used instead.
  x.SetExcludedDates(                                          //These dates are also excluded. The day before is used instead.
    new DateTime(2010, 12, 31),
    new DateTime(2012, 12, 31),
    new DateTime(2013, 12, 31),
    new DateTime(2014, 12, 31),
    new DateTime(2015, 12, 31),
    new DateTime(2015, 12, 31),
    new DateTime(2018, 12, 31),
    new DateTime(2019, 12, 31),
    new DateTime(2020, 12, 31),
    new DateTime(2021, 12, 31),
    new DateTime(2024, 12, 31),
    new DateTime(2025, 12, 31),
    new DateTime(2026, 12, 31),
    new DateTime(2027, 12, 31),
    new DateTime(2029, 12, 31)
  );
});

FillList