Taking and skipping columns from a range

The methods for taking and skipping rows/columns on a range were added in EPPlus 7.0. They are useful in many situations, for example when adding series to a chart. When using the Take- and Skip-methods you don't have to know the size of the source range, which helps when you work with ranges that are created with the LoadFrom[...] methods or ranges that is the result of a dynamic array formula.

Example

// Load an IEnumerable of class instances into the worksheet (see the LoadFromCollection method for details).
var fullRange = worksheet.Cells["A1"].LoadFromCollection(myCollection, true, OfficeOpenXml.Table.TableStyles.Medium1);;
var range = fullRange.SkipRows(1); // remove the headers row
//Add a line chart
var chart = ws.Drawings.AddLineChart("LineChartWithDroplines", eLineChartType.Line);
var serie = chart.Series.Add(range.TakeSingleColumn(1), range.TakeSingleColumn(0));

Methods

Name	Description
SkipColumns(int count)	Returns a new range, created by skipping a number of columns from the start.
SkipRows(int count)	Returns a new range, created by skipping a number of rows from the start.
TakeColumns(int count)	Returns a new range, created by taking a number of columns from the start. If count is greater than number of columns in the source range the entire source range will be returned.
TakeRows(int count)	Returns a new range, created by taking a number of rows from the start. If count is greater than number of rows in the source range the entire source range will be returned.
TakeSingleColumn(int offset)	Returns a single column as a new range by the zero-based offset from the start column of the source range.
TakeSingleRow(int offset)	Returns a single row as a new range by the zero-based offset from the start row of the source range.
TakeColumnsBetween(int offset, int count)	Returns a new range, created by taking a specific number of columns starting from the offset parameter.
TakeRowsBetween(int offset, int count)	Returns a new range, created by taking a specific number of rows starting from the offset parameter.
TakeSingleCell(int rowOffset, int columnOffset)	Returns a single cell within a range

Example with dynamic array formula and chart

sheet.Cells["A6"].Formula = $"FILTER(currencyTable[], currencyTable[To Currency]=B3)";
// Dynamic array formulas must always be calculated before saving the workbook.
sheet.Calculate();
// or if you only want to calculate this formula:
//sheet.Cells["A6"].Calculate();

// The FormulaAddress property contains the range used by the dynamic
// array formula after calculation. The variable fr will be used to refer
// to address of the dynamic array formulas result range.
var fr = sheet.Cells["A6"].FormulaRange;

// set date format for the data in column 3 of the dynamic array.
// we are using the new TakeSingleColumn function which provides easier
// access to entire columns.
fr.TakeSingleColumn(2).Style.Numberformat.Format = "yyyy-MM-dd";
// Now let's add a chart for the filtered array
var chart = sheet.Drawings.AddLineChart("Dynamic Chart", eLineChartType.Line);
chart.Title.LinkedCell = sheet2.Cells["B3"];
var series = chart.Series.Add(
    fr.TakeSingleColumn(3),
    fr.TakeSingleColumn(2)
);

Fluent syntax

Since these methods always returns a range you can combine them in one statement (a.k.a. fluent syntax):

var tableRange = sheet1.Cells["A1"].LoadFromDataReader(reader, true, "currencyTable", OfficeOpenXml.Table.TableStyles.Medium1);
// set date format for the data in column 3.
tableRange
    .SkipRows(1)
    .TakeSingleColumn(2)
    .Style.Numberformat.Format = "yyyy-MM-dd";

See also

• Sample 5.3 for Linecharts (C#) or Sample 5.3 for Linecharts (VB)