eraydin / epplus.core.extensions

An extensions library for EPPlus package to generate and manipulate Excel files easily.

EPPlus.Core.Extensions

Installation

It's as easy as PM> Install-Package EPPlus.Core.Extensions from nuget

Dependencies

.NET Framework 4.6.1       EPPlus >= 4.5.3.3       System.ComponentModel.Annotations >= 4.7.0

.NET Standard 2.0       EPPlus >= 4.5.3.3       System.ComponentModel.Annotations >= 4.7.0

Documentation and Examples

The project will be documented soon but you can look at the test project for now. I hope it has enough number of examples to give you better idea about how to use these extension methods.

• Converts IEnumerable into an Excel worksheet/package
• Reads data from Excel packages and convert them into a List.

Basic examples:

public class PersonDto
    {      
        [ExcelTableColumn("First name")]
        [Required(ErrorMessage = "First name cannot be empty.")]
        [MaxLength(50, ErrorMessage = "First name cannot be more than {1} characters.")] 
        public string FirstName { get; set; }

        [ExcelTableColumn(columnName = "Last name", isOptional = true)]       
        public string LastName { get; set; }
        
        [ExcelTableColumn(3)]
        [Range(1900, 2050, ErrorMessage = "Please enter a value bigger than {1}")]
        public int YearBorn { get; set; }
        
        public decimal NotMapped { get; set; }

        [ExcelTableColumn(isOptional = true)]
        public decimal OptionalColumn1 { get; set; }

        [ExcelTableColumn(columnIndex=999, isOptional = true)]
        public decimal OptionalColumn2 { get; set; }
    }      

• Converting from Excel to list of objects

    // Direct usage: 
        excelPackage.ToList<PersonDto>(configuration => configuration.SkipCastingErrors());

    // Specific worksheet: 
        excelPackage.GetWorksheet("Persons").ToList<PersonDto>(); 

• From a list of objects to Excel package

    List<PersonDto> persons = new List<PersonDto>();
         
    // Convert list into ExcelPackage
        ExcelPackage excelPackage = persons.ToExcelPackage();

    // Convert list into byte array 
        byte[] excelPackageXlsx = persons.ToXlsx();
       

    // Generate ExcelPackage with configuration

    List<PersonDto> pre50 = persons.Where(x => x.YearBorn < 1950).ToList();
    List<PersonDto> post50 = persons.Where(x => x.YearBorn >= 1950).ToList();
        
    ExcelPackage excelPackage = pre50.ToWorksheet("< 1950")
                             .WithConfiguration(configuration => configuration.WithColumnConfiguration(x => x.AutoFit()))
                             .WithColumn(x => x.FirstName, "First Name")
                             .WithColumn(x => x.LastName, "Last Name")
                             .WithColumn(x => x.YearBorn, "Year of Birth")
                             .WithTitle("< 1950")
                             .NextWorksheet(post50, "> 1950")
                             .WithColumn(x => x.LastName, "Last Name")
                             .WithColumn(x => x.YearBorn, "Year of Birth")
                             .WithTitle("> 1950")
                             .ToExcelPackage(); 

• Generating an Excel template from ExcelWorksheetAttribute marked classes

    [ExcelWorksheet("Stocks")]
    public class StocksDto
    {
        [ExcelTableColumn("SKU")]
        public string Barcode { get; set; }
    
        [ExcelTableColumn]
        public int Quantity { get; set; }
    }   

    // To ExcelPackage
    ExcelPackage excelPackage = Assembly.GetExecutingAssembly().GenerateExcelPackage(nameof(StocksDto));
 
    // To ExcelWorksheet
    using(var excelPackage = new ExcelPackage()){ 
    
        ExcelWorksheet worksheet = excelPackage.GenerateWorksheet(Assembly.GetExecutingAssembly(), nameof(StocksDto));
    
    }  

epplus.core.extensions's Issues

ToList behaviour

How about if we enrich the ToList<> as behavioral. For example;

excelPackage.Workbook.Worksheets.First().ToList<CandidateProduct>(@candidateProduct => {
   @candidateProduct.Barcode = @candidateProduct.Barcode.Trim();
   @candidateProduct.LocalizedString = L(@candidateProduct.LocalizedKey);
});

If we can use a delegate to intercept ToList action along with converted item that would be fine.

How to set CultureInfo when parsing excel

How do I explicitely set CultureInfo when parsing DateTime like in following example?

public class MyClass
{
    [ExcelTableColumn]
    public DateTime DateColumn { get; set; }
}

var items = sheets1.ToList<MyClass>();

Provide an option to change start index row of Excel worksheet before parsing

Related with #21, and 69233d4

Custom converter when parsing workbook

I have existing excel, with column with values 0 or 1 (general data type).

Is it possible to map it to Boolean?

Please fix a bug as soon as possible

bug :Object reference not set to an instance of an object.
in src/EPPlus.Core.Extensions/ExcelTableExtensions.cs 185

fixed:
-- col = table.Columns[propertyInfo.Name].Position;
+col = table.Columns.FirstOrDefault(x => x.Name.Equals(propertyInfo.Name)).Position;

How to remove the empty rows ?

I am reading excel file and converting to list but I don't want to add empty rows to my list.
Is there any extension which ignores empty rows ?

I am using this :
excelPackage.GetWorksheet("Persons").ToList<PersonDto>(); // before adding to list empty rows should be ignored.

Thanks in advance.

Optional Columns

Hey :)

Is there a way to set a column to optional when parsing a sheet?
The following throws an error if the column is not present.

   [ExcelTableColumn("First name")]
   public string FirstName { get; set; }

Refactorings

• IExcelConfiguration as action

Missing column name in exception message

GIVEN that I use ExcelTableColumAttribute without explicitly specifying the column name:

    [ExcelTableColumn]
    public string MyColumn { get; set; }

WHEN the column is missing in excel

THEN then .ToList<MyClass>() throws exception, but the exception message does not contain the column name. It just shows empty quotes

Bug: Cannot parse workseet created by collection.ToExcelPackage();

public class BugRepo
{
    [Fact]
    public void Worksheet_CreatedBy_ToExcelPackage_ShouldBeParseable()
    {
        var rows = new[]
        {
            new DtoString(){ DateColumn = new DateTime(2019,12,30).ToString() }
        };
        var excelPackage = rows.ToExcelPackage();
        var worksheet = excelPackage.Workbook.Worksheets["DtoString"];

        //throws EPPlus.Core.Extensions.Exceptions.ExcelValidationException : 'DateColumn' column could not found on the worksheet.
        var parsedRows = worksheet.ToList<DtoString>();      
    }


    public class DtoString
    {
        [ExcelTableColumn]
        public string DateColumn { get; set; }
    }
}

I've cloned Dev branch: https://github.com/eraydin/EPPlus.Core.Extensions/tree/534b6f20a418587eed3f6e64e12b849aa6479255

The problem is that .ToExcelPackage() generated excel table with column Column1, although header is correct

How to download the excel file ?

I try to read and manipulate xlsx file and dowload the file, how to do that ?
and there is no Saveas method ?

FileInfo existingFile = new FileInfo(csvPath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
//get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.First();
worksheet.Protection.IsProtected = false;
worksheet.Protection.AllowSelectLockedCells = false;
package.Saveas() ?
}

Thanks

Bug is still going on

Bug is still going on

ERROR 2018-07-01 20:58:52,336 [6 ] Mvc.ExceptionHandling.AbpExceptionFilter - '' column could not found on the worksheet.
EPPlus.Core.Extensions.Exceptions.ExcelValidationException: '' column could not found on the worksheet.
at EPPlus.Core.Extensions.ExcelTableExtensions.d__51.MoveNext() at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at EPPlus.Core.Extensions.ExcelTableExtensions.d__21.MoveNext() at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

Get Excel template from DTO

Nested class fields displayed in the sheet of the root class

I want to display fields in columns of a nested class that is in a field of the parent class. Is there any way to do that or is there no such feature yet?

ExcelConfiguration

• Global Excel configuration,
• DTO based Excel configuration

Upgrade EPPlus package from 4.1.1 to the latest package, and remove EPPlus.Core

AsExcelTable() doesn't work as expected when .NET property order doesn't match Excel columns

My scenario is as follows:

• Worksheet does not have a Table, but does have a header row
• Using ExcelTableColumnAttributes with ColumnNames specified
• I have some properties on the POCO that should not map to an Excel column

The behavior that I expected was that the ColumnNames in the ExcelTableColumnAttributes would simply tell EPPlus which properties map to which columns.

But, since AsExcelTable() uses the indexes of the PropertyInfo array as the index accessor of the Columns array, it's effectively setting the Table column names based on the order of T's properties. These 1.) leads to properties being improperly mapped, and 2.) doesn't allow for non-mapped properties.

I was messing around with something similar to the following, but I don't think it's quite right. I guess I'm a little confused, because I don't see a scenario in which I would want the current behavior of that loop through propInfo[].

            if (useExcelColumnNames)
            {
                for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
                {
                    var headerValue = worksheet.Cells[1, col].Value.ToString();
                    worksheet.Tables[tableName].Columns[col - worksheet.Dimension.Start.Column].Name = headerValue.Trim();
                }

                return worksheet.Tables[tableName];
            }

I'm happy to iterate on this with you to come up with a good solution. I'm actively using EPPlus and your extensions in a project.

Needs refactoring

Settting header row index in "ToList" method

Hi.

I think it'd be a good idea to have a "headerRowIndex" parameter in "ToList" extension method to start reading the rows from a certain row index. "CheckAndThrowIfDuplicatedColumnsFound" and "CheckHeadersAndThrow" methods have this parameter and the validation works well.

Events

• Excel operation started event
• Excel operation finished event