EPPlus.DataExtractor 2.0.0-alpha0001

This is a prerelease version of EPPlus.DataExtractor.
There is a newer version of this package available.
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
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="EPPlus.DataExtractor" Version="2.0.0-alpha0001" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add EPPlus.DataExtractor --version 2.0.0-alpha0001
#r "nuget: EPPlus.DataExtractor, 2.0.0-alpha0001"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// 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

Build status

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:

First table

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:

Table 2

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 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. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

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 138,612 11/3/2019
2.1.0 1,950 10/26/2019
2.0.2 30,021 1/1/2019
2.0.1 10,430 7/30/2018
2.0.0 6,783 6/11/2018
2.0.0-alpha0003 1,271 6/8/2018
2.0.0-alpha0002 1,390 3/26/2018
2.0.0-alpha0001 1,253 3/14/2018
1.3.2 15,864 1/9/2018
1.3.1 16,971 4/17/2017
1.2.1 1,838 3/30/2017
1.2.0 1,417 3/30/2017
1.2.0-beta0001 1,249 3/29/2017
1.1.0 1,527 3/13/2017
1.0.0 1,593 3/5/2017