hughbe / excel-mapper Goto Github PK

It is unnecessary to throw when MakeOptional is called on a property map that is already optional. It has no benefit and even gives a false negative if the OptionalCellValueReader is already wrapped in another type of reader. On the other hand, it makes it hard to implement generic features such as making all columns optional after another type has already set up the mapping, and possibly called MakeOptional.
There is no case when making a property optional a second time would cause any error that must be prevented by throwing an exception. MakeOptional should simply return when calling it on an already optional mapping.

Is there an attribute that can be assigned to a class property that will default to a specified value if an excel cell being imported is blank or null? I was able to set it with the ExcelClassMap functionality but I was wondering if there was attribute that would do that instead to prevent writing a mapping class.

It seems that excel mapper need data rows filled to the last row of the sheet , but sometime I only want to read a rows area(eg. rows 10~20).

Travis or Appveyor

I'm struggle with this. Is there any way to skip blank lines without mapping them to the resulting list object?

Hi
Great that Excel-Mapper handles nested objects
Any idea on how to handle a nested object when it is a collection (i.e. a collection of a nested object such as "IEnumerable" one teacher has many students?
Thanks
Laz

I'm trying to read a dictionary property using a custom ICellValueMapper (basically the data would be stored as a JSON string). How can I map this property? Calling ExcelClassMap<T>.Map throws an exception (Don't know how to map type System.Collections.Generic.IDictionary...). Which is frustrating since the whole point of custom cell value mappers would be to allow mapping arbitrary values to arbitrary types. Is there a way I can map a property type that is not supported out of the box?

I.E

public class ExcelImporter
{
    // ..
    public ExcelSheet ReadSheet(string name)
    {
        // ..
    }

    public ExcelSheet ReadSheet(int index)
    {
        // ..
    }
    // ...
};

1. I found IFallBackItem and .WithInvalidFallbackItem() , is there a way to see why it failed to map or some kind of error message? I would like to be able to collect all the cell level errors (dont stop on the first error found) from the file and later display them to a user. ( I can handle the display part i just need a callback of some sort to enable me to collect them)

2. I would also like to be able to throw custom errors during the mapping conversion inside something like .WithConverter(value => int.Parse(value) / 100f) if certain conditions are met. Is this possible? Ideally would like to be able to see the error message in 1.

Thank you! this project looks really useful

Edit:
Looking at the source code i was considering adding an ErrorMessage property to ReadCellValueResult, and then setting it where applicable in the existing ICellValueMapper 's and then implementing my own ICellValueMappers where needed. :)

Is there a nuget package for Excel-Mapper? if so what is the name of the package?
Thanks
Laz

Classes such as ColumnNameReader and ColumnNameMatchingValueReader compute column indexes for every row. This is not necessary and degrades performance, as indexes do not depend on the row index. The API could be refactored to allow to compute indexes only once at Header creation, and then use indexes only.

For example:

public interface ISingleCellValueReaderFactory
{
    ISingleCellValueReader TryGetReader(ExcelSheet sheet, IExcelDataReader reader);
}

public class ColumnNameValueReaderFactory : ISingleCellValueReaderFactory
{
    public string ColumnName { get; }

    public ColumnNameValueReaderFactory(string columnName)
    {
        ColumnName = columnName;
    }

    public ISingleCellValueReader? TryGetReader(ExcelSheet sheet, IExcelDataReader reader)
    {
        return sheet.Heading.TryGetColumnIndex(ColumnName, out int index)
            ? new ColumnIndexValueReader(index) : null;
    }
}

And then:

propertyMap.WithReaderFactory(new ColumnNameReaderFactory())

In my Excel I have a cell with a COMMA delimited list.

This list is a list of ENUMs

Models

    [System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "9.10.58.0 (Newtonsoft.Json v11.0.0.0)")]
    public partial class AccountApis : System.ComponentModel.INotifyPropertyChanged
    {
        private System.Collections.ObjectModel.ObservableCollection<ApisItem> _apis;
    
        /// <summary>List of apis.</summary>
        [Newtonsoft.Json.JsonProperty("apis", Required = Newtonsoft.Json.Required.Default, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore, ItemConverterType = typeof(Newtonsoft.Json.Converters.StringEnumConverter))]
        public System.Collections.ObjectModel.ObservableCollection<ApisItem> Apis
        {
            get { return _apis; }
            set 
            {
                if (_apis != value)
                {
                    _apis = value; 
                    RaisePropertyChanged();
                }
            }
        }
    
        public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
        
        protected virtual void RaisePropertyChanged([System.Runtime.CompilerServices.CallerMemberName] string propertyName = null)
        {
            var handler = PropertyChanged;
            if (handler != null) 
                handler(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
        }
    }

[System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "9.10.58.0 (Newtonsoft.Json v11.0.0.0)")]
    public partial class CreateAccountReq : AccountApis, System.ComponentModel.INotifyPropertyChanged
    {
        private string _displayLabel;
        private bool? _active;
        private string _expirationDate;
    
        /// <summary>Display label for account.</summary>
        [Newtonsoft.Json.JsonProperty("displayLabel", Required = Newtonsoft.Json.Required.Default, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        [System.ComponentModel.DataAnnotations.StringLength(1024, MinimumLength = 1)]
        public string DisplayLabel
        {
            get { return _displayLabel; }
            set 
            {
                if (_displayLabel != value)
                {
                    _displayLabel = value; 
                    RaisePropertyChanged();
                }
            }
        }
    
        /// <summary>Account status is actived or deactived.</summary>
        [Newtonsoft.Json.JsonProperty("active", Required = Newtonsoft.Json.Required.Default, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public bool? Active
        {
            get { return _active; }
            set 
            {
                if (_active != value)
                {
                    _active = value; 
                    RaisePropertyChanged();
                }
            }
        }
    
        /// <summary>Date account is set to expire. Set to null for no expiration. ISO-8601 date format.</summary>
        [Newtonsoft.Json.JsonProperty("expirationDate", Required = Newtonsoft.Json.Required.Default, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public string ExpirationDate
        {
            get { return _expirationDate; }
            set 
            {
                if (_expirationDate != value)
                {
                    _expirationDate = value; 
                    RaisePropertyChanged();
                }
            }
        }
        public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
        
        protected virtual void RaisePropertyChanged([System.Runtime.CompilerServices.CallerMemberName] string propertyName = null)
        {
            var handler = PropertyChanged;
            if (handler != null) 
                handler(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
        }
    
    }

 [System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "9.10.58.0 (Newtonsoft.Json v11.0.0.0)")]
    [System.Flags]
    public enum ApisItem
    {
        [System.Runtime.Serialization.EnumMember(Value = "COMMON")]
        COMMON = 1,
        [System.Runtime.Serialization.EnumMember(Value = "CONTENT_INTEGRATION")]
        CONTENT_INTEGRATION = 2,
        [System.Runtime.Serialization.EnumMember(Value = "REPORTING")]
        REPORTING = 4,
        [System.Runtime.Serialization.EnumMember(Value = "SUMTOTAL_ADAPTER")]
        SUMTOTAL_ADAPTER = 8,
        [System.Runtime.Serialization.EnumMember(Value = "USER_MANAGEMENT")]
        USER_MANAGEMENT = 16,
        [System.Runtime.Serialization.EnumMember(Value = "CONTENT_DISCOVERY")]
        CONTENT_DISCOVERY = 32,
    }

I have written a mapper for the APIS value from the Excel that uses a string extension that converts a COMMA delimited list of values to:

    System.Collections.ObjectModel.ObservableCollection<ApisItem>

Mapper

    Map(createaccountreq => createaccountreq.Apis)
                .WithColumnName("APIS")
                .WithConverter(value => value.ToObservableCollection<ApisItem>())
                .WithEmptyFallback(new ObservableCollection<ApisItem>() { ApisItem.COMMON })
                .WithInvalidFallback(new ObservableCollection<ApisItem>() { ApisItem.COMMON });

But when I try and read my file I get an exception

Don't know how to map type System.Collections.ObjectModel.ObservableCollection`1[ApisItem].

What am I doing wrong?

IExcelDataReader supports reading values with the desired type. This can improve performance for boolean, numeric and date cells. A numeric cell in Excel is stored as a number, and can be read directly as number with, for example, reader.GetDecimal(). A DateTime cell in Excel is stored as a number, and can be read directly as DateTime with reader.GetDateTime(). But Excel Mapper always reads cell values as string. This "stringly-typed" aproach requires to format the value to string, and then parse the string to the property type, degrading performance. I suggest to read values from the data reader with the property type.

First off, GREAT solution, thanks. I am pretty sure I have found a bug and am reporting it. In "ExcelMapper.Readers.ColumnIndexValueReader" (filename is "ColumnIndexReader.cs") this is what currently exists

public bool TryGetValue(ExcelSheet sheet, int rowIndex, IExcelDataReader reader, out ReadCellValueResult result)
{
    if (ColumnIndex > reader.ResultsCount)
    {
        result = default;
        return false;
    }

    var value = reader[ColumnIndex]?.ToString();
    result = new ReadCellValueResult(ColumnIndex, value);
    return true;
}

As I understand it, "ResultsCount" is the number of worksheets in the workbook. As such, this will prevent the return of any columns beyond the number of worksheets. I believe instead it should be "FieldCount" as shown here:

public bool TryGetValue(ExcelSheet sheet, int rowIndex, IExcelDataReader reader, out ReadCellValueResult result)
{
    if (ColumnIndex > reader.FieldCount)
    {
        result = default;
        return false;
    }

    var value = reader[ColumnIndex]?.ToString();
    result = new ReadCellValueResult(ColumnIndex, value);
    return true;
}

After this change, everything works (as expected) on my end.

My site gets the file uploaded to the controller. Is it possible to use this lib to read data from the file in memory or do I need to write it to disk?

I'm trying to map a dictionary list to a column in a class and for the most part its working but the WithInvalidFallBack functionality doesn't seem to be working correctly. If a key is found in the dictionary list, i'm getting the correct value but if its not found, i'm getting the key back as the value instead of what i'm passing to WithInvalidFallBack. Shouldn't I be getting back the fallback value?

AutoMapper.TryCreateClassMap probably doesn't detect reference loops in the type.
Repro:

public class MyType {
    public MyType Child { get; set; }
}

AutoMapper.TryCreateClassMap<MyType>(FallbackStrategy.SetToDefaultValue, out var map);

I have a class with a list of class, so how do I map for instance 3 columns into a object to add to the list?

Currently, if any of the property mappers throw an exception during ReadRows, there is no way to get the index of the problematic row. Making CurrentIndex a read-only public property would solve this problem.

ExcelSheet ignores its own SheetIndex when reading from the excel file.
The ResetReader call at the end of every ReadSheet overload will rewind the reader to the first sheet, but after that ExcelSheet never seeks back to the appropriate sheet index before reading the data.
Workaround is to manually seek the reader with NextResult before reading the rows.

When reading more than 1K rows, it failed. Is this the limitation?

Hi

I have now trialed several APIs and I am glad to say excel-mapper is the best, it has given me all I needed, what I found remarkable was the class map as a separate class, the fallback, and the implementation of converters. the IEnumerable mapping is second to none

I know you mentioned the API needs a rethink Please leave existing functionality, the only improvement I can think of to read from named ranges and possibly write and the ability to write.

Thanks for your help

Laz

When a mapping exception occurs in ReadRows(), the row id in the message starts at 0, even tough the invalid value is in row 2 of the spreadsheet.

Like you said with the case sensitive header, maybe introduce a property on ExcelSheet for this too?

When reading an xlsx file, with cell values as "" (empty), SkipBlankLines fails skip the line (because it checks for null).

Shouldn't an empty cell be treated the same way as null? If there's a specific reason for this, is there anyway to override this logic? I'm thinking of how the base library has the FilterRow setting.

I'll attach an example sheet with some code for quick verification:

using var stream = file.OpenReadStream();
using var importer = new ExcelImporter( stream );
importer.Configuration.SkipBlankLines = true;
importer.Configuration.RegisterClassMap<EventClassMap>();

var sheet = importer.ReadSheet();
var heading = sheet.ReadHeading();

foreach (var evt in sheet.ReadRows<Event>())
{
    // throws error as blank lines aren't skipped & non-nullable fields cannot be mapped to: ""
}

...

public class Event
{
    public string Name { get; set; }
    public string Location { get; set; }
    public int Attendance { get; set; }
    public DateTime Date { get; set; }
}
public class EventClassMap : ExcelClassMap<Event>
{
    public EventClassMap()
    {
        Map( e => e.Date )
            .WithDateFormats( "M/d/yyyy hh:mm:ss tt", "g" );
    }
}

An example sheet can be found here

I want to add the expression "(*)" to the header of the required fields in Excel.
I am reading required fields from a config file.

I know I can use ExcelClassMap for this, but I need ExcelColumnNameAttribute on the property in a few more places.
Creating an attribute of my own by inheriting the ExcelColumnNameAttribute will solve my problem, but this is not possible because you marked this class as sealed.

Is it possible for you to remove the sealed keyword and publish a new nuget package? It will also be necessary to change the name property to setter (protected?).

public class ExcelColumnNameAttribute : Attribute

and

public string Name { get; protected set; }

I can create a PR for it if you wish.
Thanks.

Hi,

I have some excel files which have a different heading but I want to map them to the same Property in my class, I have tried the following but it throws an exception:

Map(e => e.CourseDateTime)
                .WithColumnName("Time___Date_of_training")
                .WithColumnName("Time___Date_of_Training")
                .WithColumnName("Training_Date___Start_Time");

I was just wondering if I am missing a config setting when setting up my mapper or if this is even possible at the moment? Thanks

Hi there,

Thanks a lot for this extension as it saved quite valuable hours of mine :) I just want to mention a small thing about dependencies.

In the Nuget repository, ExcelDataReader (>= 3.4.0) is displayed in dependencies. I had ExcelDataReader 3.4.2 in one of my projects and trying using ExcelImporter with version 3.4.2 failed with missing/incorrect ExcelDataReader assembly error message.

Downgrading ExcelDataReader to 3.4.0 fixed the issue. It would be good to update dependency information in the Nuget repository.

Another point of extensibility that I desperately need: to modify the class map from outside. This would allow us to add custom generic extension methods, like make every column optional (which is currently impossible) or automatically add mappers based on property type, etc.
In fact, all currently internal mapping methods should be public and have a non-generic version, but that at least can be circumvented by dynamically invoking the generic versions.
I know I said 'I will do it over the weekend' in another post and never delivered, but if you say you'll merge the PR, I'll do it for real this time as I can't create a workaround for this.

It is a common task to read objects from an excel file that has properly named columns, but any column can be missing. Unfortunately, in this case we have to set up the mapping manually. If AutoMapper was public, we could auto-generate the map with SetToDefaultValue fallback strategy.

Hi,
I would like to read excel particular one cell value example 3rd row 4th column or cell value (D3).
Is there any way in excel-mapper.

i want to get a string LIst from a one colounn Sheet,
but readRows not support string type
how can i do?

This looks like a really interesting project, can you please add a license file and information in the NuSpec so we know if we should use it or not?

Please add a global FallbackStrategy property to ExcelImporterConfiguration to support scenarios where the excel file does not have all the columns. Currently it is a pain to load objects from excel tables when even a single property is missing - because in that case the AutoMapper utility will throw and we have to manually build the mapping for every type.

I would like to map the values in a column to an enum by way of the enum DescriptionAttribute. Is there a way to do this?

Looking for how to map the value 'Bar Baz' in column n to MyEnum.BarBaz (not the space in the description)?

public enum MyEnum
{
    [Description("Foo")]
    Foo,
    [Description("Bar Baz")]
    BarBaz
}

Imagine the data has a column with comma-separated values, but there is also a space after each comma, e.g.:
My Column
value1, value2, value3

Currently the only choices are:

• default mapping, which results in a list containing 'value1', '_value2', '_value3' (I put underscores instead of spaces for emphasis)
• using .WithSeparators(',',' ') which results in a list containing 'value1', null, 'value2', null, 'value3'

If we could use .WithSeparators(", ") instead, it would give 'value1', 'value2', 'value3' as desired.

The following code results in the value 0 in the column AmountPerLayer, but I expected the value null.

Am I doing something wrong?

Map(fust => fust.AmountPerLayer) .WithColumnIndex(3) .WithInvalidFallback((decimal?)null) .WithEmptyFallback((decimal?)null);

Sadly, this is more of a design problem, and only a breaking change could fix it: when we make a column optional using OptionalCellValueReader, and the column is actually missing, importing N lines with K missing columns will throw and catch N*K exceptions, dramatically degrading performance.

Proposed solution

Instead of throwing an exception, ReadCellValueResult could have a Success property that tells if the cell was read. The exception should be thrown only at the end of the chain, before invoking the cell value mappers, if none of the readers have reported success.

Automapping guids is currently not supported.

Please add a property to ExcelSheet that can be used to read the visibility of the sheet, preferably as an enum instead of text:

public enum ExcelSheetVisibility {
    Visible,
    Hidden,
    VeryHidden
}

Some input files can have hidden sheets and it's hard to automate things like reading data from every visible sheet (my current workaround being opening the file with ExcelDataReader first, and iterating through the sheets to find out which one is visible).

Merged in #6 but without unit tests. Would be nice to get 100% CC

Hi

Is there a way import the file without a header and just use the column index to map your file?

Please let me know.

Thanks

Sometimes the header is not on the first row, please let us be able to set it.