ipvalverde / epplus.dataextractor Goto Github PK

Would it be possible to get a version built against .NET 4.0 in nuget?

Thank you and kind regards

Hi ipalverde,
this is an excellent project !
This is not an issue, it's an improvment request

How can I achieve this ?

1. WithCollectionProperty : I need to extract 2 columns , not just one : Id and Value
   in screenshot : Id is RMID and value is : RM poids
2. I don't know by advance how many columns ID,value I can have , I would need to iterate through columns untill column name is empty
   
   I've tried to customize your code but I can't get it to work:
   
   

If we want to read all rows how do we know the RowTo value of a file before uploading it?

excellent project
just a new example in case someone is looking for it:
rmWorkSheet.Extract<RawMaterial>() // Here we can chain multiple definition for the columns .WithProperty(p => p.Id, "A") .WithProperty(p => p.Name, "B", fnTrimString) .WithProperty(p => p.Code, "C", fnTrimString)

fnTrimString is converting, fot this example, input object to string
with
Func<object, string> fnTrimString = objStr => { if (objStr == null) return ""; return objStr.ToString().Trim(); };

I noticed that although the package's dependency description says it depends on EPPlus (>= 4.5.2.1), it actually depends on EPPlus (== 4.5.2.1) based on the code.

I am currently having problem with this since our projects are using EPPlus 4.5.3.2. which is the latest stable version.

I tried to download the solution and upgraded the package reference. All tests in EPPlus.DataExtractor.Tests work fine with EPPlus 4.5.3.2.

Is it possible to modify the project in order to allow all EPPlus packages with version equal to or greater than 4.5.2.1?

This is an excellent project, thank you @ipvalverde for the time. I have a quick question
How do i extract this data into this ScoresPoco

public class ScoresPoco
{
    public int ApplicantID { get; set; }
    public List<ScoreData> Scores { get; set; }
}

public class ScoreData
{
    public int SubjectID { get; set; }
    public double Score { get; set; }
}

I am extracting with this

var data = worksheet.Extract<ScoresPoco>()
.WithProperty(p => p.ApplicantID, "B")
.WithCollectionProperty(p => p.Scores,
                        item => item.SubjectID, 1,
                        item => item.Score, "C", "E")
.GetData(model.DataRowStart, row => worksheet.Cells[row, model.DataColStart].Value != null)
.ToList();

This only returns the ApplicantID and Scores correctly but the subjectID is always 0.
There can also be more than 2 or Subjects, but all the IDs will be in the Row 2.
When debugging, I can see all that data in the worksheet but a way to add it to the SubjectID is my question.

Hi,

I really like the simple approach, but imho DataExtractor.GetData() termination is lacking: it can either terminate based on the row number or on the value of a single property.
I propose to extend the termination test like this:

`
///

        int row = fromRow;
        while(true)
        {
            var dataInstance = new TRow();

            bool continueExecution = true;
            for (int index = 0; continueExecution && index < this.propertySetters.Count; index++)
                continueExecution = this.propertySetters[index].SetPropertyValue(dataInstance, row, this.worksheet.Cells);

            if (!continueExecution)
            {
                yield return dataInstance;
                break;
            }

            foreach (var collectionPropertySetter in this.collectionColumnSetters)
                collectionPropertySetter.SetPropertyValue(dataInstance, row, this.worksheet.Cells);

            foreach (var simpleCollectionColumnSetter in this.simpleCollectionColumnSetters)
                simpleCollectionColumnSetter.SetPropertyValue(dataInstance, row, this.worksheet.Cells);

            if(!whileFunc(row, dataInstance))
                break;

            yield return dataInstance;

            row++;
        }
    }

`

Row Skipping

In the setPropertyCallbackValue callback, I can currently call Abort to stop processing. I would like to be to call Skip or Ignore to simply omit the current row. The ability to call a SkipProperty function would also be extremely useful; this would continue processing the row, but not attempt to convert the current property.

In my current project, I am currently achieving Skip functionality using a helper method like this:

        public List<T> Read<T>(string filename, string wsName, Func<ExcelWorksheet, ICollectionPropertyConfiguration<T>> txObjs, int startRow, Func<T, bool> valid = null) where T: class, new()
        {
            using (var xl = new ExcelPackage(new FileInfo(filename)))
            {
                var ws = xl.Workbook.Worksheets[wsName];

                var retVal = new List<T>();
                if (ws == null)
                    return retVal;

                var tgt_cnt = ws.Dimension.Rows - startRow + 1; //number of rows to read before complete
                var read_cnt = 0; // tracks # of read rows
                while (read_cnt < tgt_cnt)
                {
                    var vals = txObjs(ws).GetData(startRow + read_cnt, ws.Dimension.Rows);
                    read_cnt += vals.Count();
                    retVal.AddRange(valid == null ? vals : vals.Where(valid));                    
                }

                return retVal;
            }
        }

The txObjs parameter is a configuration broken out like this:

        public static ICollectionPropertyConfiguration<Person> PersonCfg(ExcelWorksheet ws)
            => ws.Extract<Person>()
                 .WithProperty(p => p.PersonId,  "A")
                 .WithProperty(p => p.LastName,  "B")
                 .WithProperty(p => p.FirstName, "C");

Casting Override

In one of the WithProperty callbacks (whichever makes more sense internally), I'd like to be able to call an OverrideCast method that allows me to write code to convert from object to the property type. This is for situations where there may need to be special logic for a specific property (like dirty data that sometimes contains a "0" instead of an empty string). This is particularly important because overriding the cast can prevent an error when casting an invalid value, such as an error.

Error Handling

Excel has a number of built-in error types. In one of the WithProperty callbacks (or as a separate callback), I'd like to handle the case of having an error value (e.g. "#N/A"). This could also be handled manually via the Casting Override change, but having an explicit error handling mechanism would provide a much more focused event to handle.

Data Sanitization

After the data has been converted to the destination type, I would like the ability to "sanitize" the data (or, if you prefer, transform it). For example, I may wish to convert "123456" to "R-123456", or trim strings. This probably makes sense as its own callback.

I think implementing these features would make this extension incredibly powerful for data ingestion and allow for very succinct and expressive code.

Moin,

Strong Name Error, can you help

System.IO.FileLoadException: "Die Datei oder Assembly "EPPlus.DataExtractor, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" oder eine Abhängigkeit davon wurde nicht gefunden. Eine Assembly mit starkem Namen ist erforderlich.

Can the POCO have a Guid or as data type?

I am getting an error because a Guid can be parse but not casted.

System.InvalidCastException : Invalid cast from 'System.String' to 'System.Guid'.
at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
at OfficeOpenXml.Utils.ConvertUtil.GetTypedCellValue[T](Object value)
at EPPlus.DataExtractor.PropertyValueSetter2.SetPropertyValue(TModel dataInstance, ExcelRangeBase cell) at EPPlus.DataExtractor.DataExtractor1.d__8.MoveNext()

I use Entity framework virtual ICollection for proxy creation
how can I use WithCollectionProperty with ICollection please ?

There are constructors for HashSet, List and Collection

If I try to add a new constructor like

public ICollectionPropertyConfiguration<TRow> WithCollectionProperty<TCollectionItem>(
        Expression<Func<TRow, ICollection<TCollectionItem>>> propertyCollection,
        string startColumn, string endColumn) where TCollectionItem : class
    {
        var collectionConfiguration = new SimpleCollectionColumnDataExtractor<TRow, ICollection<TCollectionItem>, TCollectionItem>
            (propertyCollection, startColumn, endColumn);

        this.simpleCollectionColumnSetters.Add(collectionConfiguration);

        return this;
    }

but I get an error : ICollection must be a non abstract type with a public parameterless constructor

thank you

is it possible to pass convertDataFunc cell instead of cell.Value?

Hi, good job with the DataExtractor!

But what about anonymous types? Can i simply extract all the data to List?

What happen if i haven't any POCO class but want the data well formatted from the worksheet?

Regards

Hello, amazing job with DataExtractor!

but, is some solution about merged rows?

some like copying the merged row value on every List who has that value? or some kind of collection way?

Title says it all. This is very easy to fix, see here:

https://stackoverflow.com/questions/5205738/how-do-you-include-xml-docs-for-a-class-library-in-a-nuget-package