Import of Data not possible with Versions > 1.31.0

[PhilippFaber]

Excel Type

• XLSX
• XLSM
• CSV
• OTHER

MiniExcel Version

All versions above 1.31.0

Description

We are unable to import any files with Versions above 1.31.0
We are using MiniExcel to import several files into our Database. Since these files all represent sensitive Data we are not able to share any files with you.
We already tested MiniExcel.Query as well as MiniExcel.QueryAsync. We always use custom SheetNames and StartCells, since all documents use different Start values.

A Stack Trace of the error can be found here:

It would be very kind if you could help us in this case.

[coffee0818]

I am not the author, but you should at least give the imported code block.
I wonder if your startCell and endCell use lowercase letters.

MiniExcel.QueryRange(path, startCell: "A2", endCell: "C3").Cast<IDictionary<string, object>>();

Query specified ranges (startCell / endCell: Must be capitalized)
startCell: "A2", endCell: "D3" => Right
startCell: "a2", endCell: "d3"  => Wrong

[PhilippFaber]

Since we use MiniExcel since at least 2023, we already know that startCell has to start with an Uppercase Letter.
Our Code ist configured that only uppercase Letters are allowed in this area.

I doubt that this will help much, but this is the codeblock that ultimately causes the exception:

public static async Task<IEnumerable<ReturnT>> GetMiniExcelQueryAsync<ReturnT>(string path, string sheetName, string startCell, int headerrow) where ReturnT : class, new()
        {
            var query = await MiniExcel.QueryAsync<ReturnT>(path, sheetName, startCell: startCell); // this call is causing the error
            return query.OfType<BaseDTO>()
                .Select((row, index) =>
                {
                    row.SheetName = sheetName;
                    row.RowNumber = index + 1 + headerrow;
                    return row;
                }).Cast<ReturnT>();
        }

[michelebastione]

@PhilippFaber SLR, I'll investigate this as soon as I can. Could you please share the layout of your BaseDto?

[PhilippFaber]

Our BaseDTO looks like this:

using ImportTool.Utils.CustomAttributes;

namespace ImportTool.ExcelMethods
{
    public class BaseDTO
    {
        public BaseDTO() { }

        [IgnoreProperty]
        public dynamic this[string propertyName]
        {
            get => GetType().GetProperty(propertyName).GetValue(this, null);
            set => GetType().GetProperty(propertyName).SetValue(this, value, null);
        }

        [IgnoreProperty]
        public int RowNumber { get; set; }

        [IgnoreProperty]
        public string SheetName { get; set; }
    }
}

For Logging and Debugging we need the RowNumber and SheetName in every Import we perform, so these are set centrally inside the BaseDTO. The IgnoreProperty is used for some checks if a Value inside the DTO is empty or not. Additionally there is some logic that lets us retrieve Property-Values by their Name. This is also needed for some validity checks.

The BaseDTO is used in every other DTO we have inside our Application. An Example-DTO (with some sample columns) might look like this:

using MiniExcelLibs.Attributes;

namespace ImportTool.ExcelMethods.CarList.Dtos
{
    internal class ImportCarListDTO : BaseDTO
    {
        [ExcelColumnName("Manufacturer Code")]
        public string ManufacturerCode { get; set; }

        [ExcelColumnName("Type")]
        public string Type { get; set; }

        [ExcelColumnIndex("O")]
        public int BHP { get; set; }

        public bool IsValid()
            => !string.IsNullOrWhiteSpace(ManufacturerCode) &&
            ManufacturerCode != "#"
            && ManufacturerCode.StartsWith("0");
    }
}

If the column name is available, we use ExcelColumnName, otherwise ExcelColumnIndex is used.
In this example the ManufacturerCode is an 4 Character Code that always starts with a 0 and should not have a #, since this would be invalid.

[michelebastione]

@PhilippFaber Please verify that adding ExcelIgnoreAttribute to the dynamic indexer of your BaseDTO solves the issue:

[ExcelIgnore]
public dynamic this[string propertyName]
{
    // your indexer logic
}

[PhilippFaber]

Thanks, this seems to indeed solve the problem that we were having.
We will continue to test this to verify that this was the only issue.

Would it be possible to add additional context to the Error Message in this context so that more users dont get confused by this behavior?

[michelebastione]

This is definitely an interesting edge case, I'm amazed how it's taken so long to be reported. In short, MiniExcel treats the indexer as a property and tries to invoke its getter method, which is generally safe to do but in this occurrence it expects a parameter (the index, of course). That's where it breaks.

I think the best course of action would be to intercept the invocation of the getter when it doesn't come with a parameterless signature and throw an InvalidOperationException with a clearer error message. @shps951023 what do you think?

[PhilippFaber]

After intensive testing, we can indeed confirm that your suggestion has fixed the initial problem.
However we have exactly one File that cannot be read by MiniExcel.

In detail, this is an Excel generated by SAP, which gets imported into our software for further processing.
For your Information, the file can be read, but inside one specific sheet the column Headers cannot be extracted from the sheet.
There seems to be a 'very hidden' sheet inside the excel containing some SAP-Connection related information, maybe this could cause the problems (we are not sure at the moment). Also there could be some SAP-related weird formatting that prevents this sheet from importing.

Sadly, we cannot provide the exact file due to it being sensitive business related data, so we are just providing this for documentation purposes.

[michelebastione]

@PhilippFaber Thank you for the feedback. If in the future you manage to track the cause down feel free to share a minimum reproducible sample for us to study.