-
Notifications
You must be signed in to change notification settings - Fork 275
Taking and skipping columns from a range
Mats Alm edited this page Jan 30, 2024
·
7 revisions
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.
// 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));
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 |
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)
);
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";
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles