EPPlus.DataExtractor
2.0.0-alpha0001
See the version list below for details.
dotnet add package EPPlus.DataExtractor --version 2.0.0-alpha0001
NuGet\Install-Package EPPlus.DataExtractor -Version 2.0.0-alpha0001
<PackageReference Include="EPPlus.DataExtractor" Version="2.0.0-alpha0001" />
paket add EPPlus.DataExtractor --version 2.0.0-alpha0001
#r "nuget: EPPlus.DataExtractor, 2.0.0-alpha0001"
// Install EPPlus.DataExtractor as a Cake Addin #addin nuget:?package=EPPlus.DataExtractor&version=2.0.0-alpha0001&prerelease // Install EPPlus.DataExtractor as a Cake Tool #tool nuget:?package=EPPlus.DataExtractor&version=2.0.0-alpha0001&prerelease
Summary
This is a simple EPPlus extension that make easier to extract POCO from spreadsheets.
Installation
You can download the library from nuget with the following command:
Install-Package EPPlus.DataExtractor
If you don´t have the EPPlus as a dependency, nuget will install it for you.
Usage
Using the package is really simple. Let's work with a simple example first.
Consider you want to extract the following data from a spreadsheet:
Just a define a class to hold the data.
public class SimpleRowData
{
public string CarName { get; set; }
public double Value { get; set; }
public DateTime CreationDate { get; set; }
}
Considering that the data is on a worksheet named worksheet1, this is the code that can be used to retrieve an IEnumerable
of SimpleRowData
:
using (var package = new ExcelPackage("spreadsheet/file/location/file.xlsx"))
{
var cars = package.Workbook.Worksheets["worksheet1"]
.Extract<SimpleRowData>()
// Here we can chain multiple definition for the columns
.WithProperty(p => p.CarName, "B")
.WithProperty(p => p.Value, "C")
.WithProperty(p => p.CreationDate, "D")
.GetData(4, 6) // To obtain the data we indicate the start and end of the rows.
// In this case, the rows 4, 5 and 6 will be extracted.
.ToList();
}
Yeah, it is that simple!
Callbacks
You can also use callbacks for filling properties values, where you can put custom code, validations and even abort the rest of the execution.
You can specify a callback that is executed over the object
( setPropertyValueCallback
) value or over the casted TValue
( setPropertyCastedValueCallback
) type.
The first parameter is of type PropertyExtractionContext
, that contains data about the cell address used to populate the property ( PropertyExtractionContext.CellAddress
)
and a Abort()
method that can be used to cancel the rest of the processing for the entire extraction. The rows extracted before the execution of the Abort
will be returned by the GetData
method, and an entity for the current row will also be returned with all the previous properties populated.
The following code is based on the previous example and uses the setPropertyCastedValueCallback
to print a message based on the value of the cell:
using (var package = new ExcelPackage("spreadsheet/file/location/file.xlsx"))
{
var cars = package.Workbook.Worksheets["worksheet1"]
.Extract<SimpleRowData>()
.WithProperty(p => p.CarName, "B")
.WithProperty(p => p.Value, "C")
.WithProperty(p => p.CreationDate, "D",
setPropertyCastedValueCallback: (propContext, creationDate) =>
{
if(creationDate < new DateTime(2000, 1, 1)) {
Console.WriteLine("The car in row {0} is here for too long, no one will buy it", propContext.CellAddress.Row);
}
// We could also abort if the date time value is not set (i.e. is the default one):
/*
if(creationDate == default(DateTime))
{
Console.WriteLine("Invalid value in cell {0}!", propContext.CellAddress.Address);
propContext.Abort();
}
*/
})
.GetData(4, 6)
.ToList();
}
Columns that should be rows
Sometimes the tables defined in spreadsheets does not have a friendly structure for a developer. Instead of creating multiple tables and foreign key relationships in excel it is simpler to put data that should go into different tables as columns in the existing table. It'll be clear with the following example:
Imagine that you want to extract this data into a class structure where the columns that indicates months/year should be a collection inside the entity that will keep the row data. So we can have two classes defined like that:
public class RowDataWithColumnBeingRow
{
public string Name { get; set; }
public int Age { get; set; }
public List<ColumnData> MoneyData { get; set; }
}
public class ColumnData
{
public double ReceivedMoney { get; set; }
public DateTime Date { get; set; }
}
You can use the following code to extract the spreadsheet data to these classes:
using (var package = new ExcelPackage("spreadsheet/file/location/file.xlsx"))
{
var data = package.Workbook.Worksheets["worksheet1"]
.Extract<RowDataWithColumnBeingRow>()
.WithProperty(p => p.Name, "F")
.WithProperty(p => p.Age, "G")
// Here, the collection property is defined using the "WithCollectionProperty" method.
// The following parameter is the expression indicating the property of "ColumnData"
// that will be used to receive the header data followed by an integer indicating the row
// that contains the header.
// The last expression indicates the other "ColumnData" property, this one will receive
// the row data. The two last strings are the start and end column from where
// this data will be extracted.
.WithCollectionProperty(p => p.MoneyData,
item => item.Date, 1,
item => item.ReceivedMoney, "H", "S")
.GetData(2, 4)
.ToList();
}
The GetData
method returns an IEnumerable
, and this IEnumerable
is not evaluated until you interate through it or execute something like ToList
over it. So make sure you'll do one of those things before disposing the EPPlus ExcelPackage
.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.0 is compatible. netstandard2.1 was computed. |
.NET Framework | net461 was computed. net462 was computed. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen40 was computed. tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
- EPPlus (>= 4.5.0)
NuGet packages (1)
Showing the top 1 NuGet packages that depend on EPPlus.DataExtractor:
Package | Downloads |
---|---|
Bat.Tools
For Contact Us Please Send Mail Or Call To : Tel : 09301919109 Mail : mehrannoruzi@gmail.com |
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
2.2.0 | 186,423 | 11/3/2019 |
2.1.0 | 2,238 | 10/26/2019 |
2.0.2 | 31,623 | 1/1/2019 |
2.0.1 | 10,887 | 7/30/2018 |
2.0.0 | 7,152 | 6/11/2018 |
2.0.0-alpha0003 | 1,400 | 6/8/2018 |
2.0.0-alpha0002 | 1,546 | 3/26/2018 |
2.0.0-alpha0001 | 1,402 | 3/14/2018 |
1.3.2 | 17,429 | 1/9/2018 |
1.3.1 | 18,267 | 4/17/2017 |
1.2.1 | 2,147 | 3/30/2017 |
1.2.0 | 1,584 | 3/30/2017 |
1.2.0-beta0001 | 1,373 | 3/29/2017 |
1.1.0 | 1,697 | 3/13/2017 |
1.0.0 | 1,740 | 3/5/2017 |