Skip to content

Open-source eCommerce platform for C# and ASP.NET Core 10 with MongoDB. Build multi-store shops and multi-vendor marketplaces with B2B/B2C, REST API, plugins, Vue 3 and Docker.

11.6 KB
Raw Blame
using Ganss.Excel;
using Ganss.Excel.Exceptions;
using NPOI.SS.UserModel;
using System.Collections;
using System.Reflection;
using System.Text.Json;

namespace Grand.Business.Common.Utilities;

public class PowerExcelMapper : ExcelMapper
{
    private readonly Dictionary<Type, Func<object>> ObjectFactories = new();

    private Dictionary<string, Dictionary<int, object>> Objects { get; } = new();
    private IWorkbook Workbook { get; set; }

    private static async Task<Stream> ReadAsync(Stream stream)
    {
        var ms = new MemoryStream();
        await stream.CopyToAsync(ms);
        return ms;
    }

    public async Task<IEnumerable<T>> FetchAsync<T>(Stream stream, int sheetIndex = 0,
        Func<string, object, object> valueParser = null)
    {
        using var ms = await ReadAsync(stream);
        ms.Position = 0;
        return Fetch(ms, typeof(T), sheetIndex, valueParser).OfType<T>();
    }

    public IEnumerable Fetch(Stream stream, Type type, int sheetIndex,
        Func<string, object, object> valueParser = null)
    {
        Workbook = WorkbookFactory.Create(stream);
        return Fetch(type, sheetIndex, valueParser);
    }

    public IEnumerable Fetch(Type type, int sheetIndex = 0, Func<string, object, object> valueParser = null)
    {
        var sheet = Workbook.GetSheetAt(sheetIndex);
        return Fetch(sheet, type, valueParser);
    }

    private IEnumerable Fetch(ISheet sheet, Type type, Func<string, object, object> valueParser = null)
    {
        var firstRowNumber = HeaderRowNumber;

        if (!HeaderRow)
            firstRowNumber = sheet.Where(r => r.RowNum >= MinRowNumber && r.RowNum <= MaxRowNumber)
                .OrderByDescending(r => r.LastCellNum).FirstOrDefault()?.RowNum ?? 0;

        var firstRow = sheet.GetRow(firstRowNumber);

        if (firstRow == null)
            yield break;

        var cells = Enumerable.Range(0, firstRow.LastCellNum)
            .Select(i => firstRow.GetCell(i, MissingCellPolicy.RETURN_BLANK_AS_NULL));
        var firstRowCells = cells
            .Where(c => !HeaderRow || !string.IsNullOrWhiteSpace(c.ToString()));
        var typeMapper = type != null ? TypeMapperFactory.Create(type) : TypeMapper.Create(firstRowCells, HeaderRow);

        if (TrackObjects) Objects[sheet.SheetName] = new Dictionary<int, object>();

        var objInstanceIdx = 0;

        foreach (IRow row in sheet)
        {
            var i = row.RowNum;

            if (i < MinRowNumber) continue;
            if (i > MaxRowNumber) break;

            // optionally skip header row and blank rows
            if ((!HeaderRow || i != HeaderRowNumber) && (!SkipBlankRows || row.Cells.Any(c => !IsCellBlank(c))))
            {
                var o = MapCells(type, valueParser, typeMapper, firstRowCells, ref objInstanceIdx, row,
                    new HashSet<Type> { type });
                yield return o;
            }
        }
    }

    private static bool IsCellBlank(ICell cell)
    {
        return cell.CellType switch {
            CellType.String => string.IsNullOrWhiteSpace(cell.StringCellValue),
            CellType.Blank => true,
            _ => false
        };
    }

    private object MapCells(Type type, Func<string, object, object> valueParser, TypeMapper typeMapper,
        IEnumerable<ICell> cells, ref int objInstanceIdx, IRow row, ISet<Type> callChain)
    {
        var sheet = row.Sheet;
        var i = row.RowNum;
        List<(ColumnInfo Col, object CellValue, ICell Cell, int ColumnIndex)> initValues = new();
        var columns = cells
            .Select(c => (Index: c.ColumnIndex,
                Columns: GetColumnInfo(typeMapper, c)
                    .Where(c => c.Directions.HasFlag(MappingDirections.ExcelToObject) && !c.IsSubType).ToList()))
            .Where(c => c.Columns.Any())
            .ToList();

        foreach (var (columnIndex, columnInfos) in columns)
        {
            var cell = row.GetCell(columnIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK);

            if (cell != null && (!SkipBlankCells || !IsCellBlank(cell)))
                foreach (var ci in columnInfos)
                {
                    object cellValue;

                    try
                    {
                        cellValue = GetCellValue(cell, ci);
                    }
                    catch (Exception e)
                    {
                        cellValue = GetCellValue(cell);
                        TriggerOrThrowParsingError(new ExcelMapperConvertException(cellValue, ci.PropertyType, i,
                            columnIndex, e));
                    }

                    try
                    {
                        if (valueParser != null)
                            cellValue = valueParser(
                                string.IsNullOrWhiteSpace(ci.Name) ? columnIndex.ToString() : ci.Name, cellValue);

                        initValues.Add((ci, cellValue, cell, columnIndex));
                    }
                    catch (Exception e)
                    {
                        TriggerOrThrowParsingError(new ExcelMapperConvertException(cellValue, ci.PropertyType, i,
                            columnIndex, e));
                    }
                }
        }

        if (!IgnoreNestedTypes)
            foreach (var ci in typeMapper.ColumnsByName.SelectMany(c => c.Value).Where(c => c.IsSubType))
                if (!callChain.Contains(ci.PropertyType) // check for cycle in type hierarchy
                    && !initValues.Any(v =>
                        v.Col.Property.IsIdenticalTo(ci.Property))) // map subtypes only if not already mapped
                {
                    callChain.Add(ci.PropertyType);
                    var subTypeMapper = TypeMapperFactory.Create(ci.PropertyType);
                    var subObject = MapCells(ci.PropertyType, valueParser, subTypeMapper, cells, ref objInstanceIdx,
                        row, callChain);
                    initValues.Add((ci, subObject, null, -1));
                }

        object o;

        if (type == null)
        {
            o = typeMapper.CreateExpando();
        }
        else
        {
            if (typeMapper.Constructor != null)
            {
                var parms = typeMapper.Constructor.GetParameters();
                var vals = parms.Select(p => GetDefault(p.ParameterType)).ToArray();

                foreach (var initVal in initValues.ToList())
                    if (typeMapper.ConstructorParams.TryGetValue(initVal.Col.Property.Name, out var parm))
                        try
                        {
                            object v;

                            if (initVal.Cell != null)
                                v = PowerExcelExtensions.GetPropertyValue(initVal.Col, null, initVal.CellValue,
                                    initVal.Cell);
                            else
                                v = initVal.CellValue;

                            vals[parm.Position] = v;

                            initValues.Remove(initVal);
                        }
                        catch (Exception ex)
                        {
                            TriggerOrThrowParsingError(new ExcelMapperConvertException(initVal.CellValue,
                                initVal.Col.PropertyType, i, initVal.ColumnIndex, ex));
                        }

                try
                {
                    o = typeMapper.Constructor.Invoke(vals);
                }
                catch (Exception ex)
                {
                    throw new ExcelMapperConvertException($"Failed to initialize type {type.FullName}.", ex);
                }
            }
            else
            {
                if (ObjectFactories.TryGetValue(type, out var factory))
                    o = factory();
                else
                    try
                    {
                        o = Activator.CreateInstance(type);
                    }
                    catch (Exception)
                    {
                        o = null;
                    }

                if (o == null)
                    return null;
            }
        }

        if (initValues.Any())
            foreach (var val in initValues)
                try
                {
                    if (val.Cell != null)
                        PowerExcelExtensions.SetProperty(val.Col, o, val.CellValue, val.Cell);
                    else
                        val.Col.Property.SetValue(o, val.CellValue);
                }
                catch (Exception ex)
                {
                    TriggerOrThrowParsingError(new ExcelMapperConvertException(val.CellValue, val.Col.PropertyType, i,
                        val.ColumnIndex, ex));
                }

        if (TrackObjects) Objects[sheet.SheetName][i] = o;

        objInstanceIdx++;
        return o;
    }

    private static object GetDefault(Type t)
    {
        return t.GetTypeInfo().IsValueType ? Activator.CreateInstance(t) : null;
    }

    private static void TriggerOrThrowParsingError(ExcelMapperConvertException excelMapperConvertException)
    {
        var parsingError = new ParsingErrorEventArgs(excelMapperConvertException);
        //ErrorParsingCell?.Invoke(this, parsingError);
        if (!parsingError.Cancel)
            throw excelMapperConvertException;
    }

    private List<ColumnInfo> GetColumnInfo(TypeMapper typeMapper, ICell cell)
    {
        var colByIndex = typeMapper.GetColumnByIndex(cell.ColumnIndex);

        if (!HeaderRow || colByIndex != null)
            return colByIndex ?? new List<ColumnInfo>();

        var name = cell.ToString();
        var colByName = typeMapper.GetColumnByName(name);

        // map column by name only if it hasn't been mapped to another property by index
        if (colByName != null
            && !typeMapper.ColumnsByIndex.SelectMany(ci => ci.Value)
                .Any(c => c.Property.Name.Equals(name, StringComparison.OrdinalIgnoreCase)))
            return colByName;

        return new List<ColumnInfo>();
    }

    private object GetCellValue(ICell cell, ColumnInfo targetColumn)
    {
        var formulaResult = cell.CellType == CellType.Formula &&
                            (targetColumn.PropertyType != typeof(string) || targetColumn.FormulaResult);
        var cellType = formulaResult ? cell.CachedFormulaResultType : cell.CellType;
        const int maxDate = 2958465; // 31/12/9999

        switch (cellType)
        {
            case CellType.Numeric:
                if (!formulaResult && targetColumn.PropertyType == typeof(string))
                    return DataFormatter.FormatCellValue(cell);

                if (cell.NumericCellValue < maxDate && DateUtil.IsCellDateFormatted(cell)) return cell.DateCellValue;
                return cell.NumericCellValue;
            case CellType.Formula:
                return cell.CellFormula;
            case CellType.Boolean:
                return cell.BooleanCellValue;
            case CellType.Error:
                return cell.ErrorCellValue;            
            case CellType.Blank:
            case CellType.String:
            default:
                if (targetColumn.Json)
                    return JsonSerializer.Deserialize(cell.StringCellValue, targetColumn.PropertyType);
                return cell.StringCellValue;
        }
    }

    private object GetCellValue(ICell cell)
    {
        return cell.CellType switch {
            CellType.Numeric => cell.NumericCellValue,
            CellType.Formula => cell.CellFormula,
            CellType.Boolean => cell.BooleanCellValue,
            CellType.Error => cell.ErrorCellValue,
            CellType.String => cell.StringCellValue,
            CellType.Blank => string.Empty,
            _ => "<unknown>"
        };
    }
}