Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bulk excel import #829

Open
Mike6x opened this issue Mar 8, 2023 · 0 comments
Open

Bulk excel import #829

Mike6x opened this issue Mar 8, 2023 · 0 comments
Labels
enhancement New feature or request

Comments

@Mike6x
Copy link

Mike6x commented Mar 8, 2023

Please demo Bulk excel import function whith gerenic method . I try as following but processsing is slow:

public class ExcelReader : IExcelReader
{
public async Task<IList> ImportAsync(FileUploadRequest request, FileType supportedFileType, string sheetName = "Sheet1")
{

    string base64Data = Regex.Match(request.Data, string.Format("data:{0}/(?<type>.+?),(?<data>.+)", supportedFileType.ToString().ToLower())).Groups["data"].Value;
    var streamData = new MemoryStream(Convert.FromBase64String(base64Data));

    List<T> list = new List<T>();
    Type typeOfObject = typeof(T);
    using (IXLWorkbook workbook = new XLWorkbook(streamData))
    {
        // Read the first Sheet from Excel file.
        var worksheet = workbook.Worksheets.FirstOrDefault(w => w.Name == sheetName);
        if (worksheet != null)
        {
            var properties = typeOfObject.GetProperties();

            // header column texts
            var columns = worksheet.FirstRow().Cells().Select((v, i) => new { v.Value, Index = i + 1 });

            // indexing in closedxml starts with 1 not from 0
            // Skip first row which is used for column header texts
            foreach (IXLRow row in worksheet.RowsUsed().Skip(1))
            {
                T item = (T)Activator.CreateInstance(typeOfObject);
                foreach (var prop in properties)
                {
                    try
                    {
                        var type = prop.PropertyType;
                        int colIndex = columns.Single(c => c.Value.ToString() == prop.Name).Index;

                        object? obj = GetObjectByDataType(type, row.Cell(colIndex).Value);
                        prop.SetValue(item, obj);
                    }
                    catch
                    {
                    }
                }
                if (item != null) list.Add(item);
            }
        }
    }

    return await Task.FromResult(list);
}

private static object? GetObjectByDataType(Type propertyType, XLCellValue cellValue)
{
    if (cellValue.ToString() == "null" )
    {
        return null;
    }

    object? val;

    if (propertyType.IsEnum)
    {
        val = Convert.ToInt32(cellValue.GetNumber());
        return Enum.ToObject(propertyType, val);
    }
    else if (propertyType == typeof(Guid) || propertyType == typeof(Guid?))
    {
        val = Guid.Parse(cellValue.ToString());
    }
    else if (propertyType == typeof(int) || propertyType == typeof(int?))
    {
        val = Convert.ToInt32(cellValue.GetNumber());
    }
    else if (propertyType == typeof(decimal))
    {
        val = Convert.ToDecimal(cellValue.GetNumber());
    }
    else if (propertyType == typeof(long))
    {
        val = Convert.ToInt64(cellValue.GetNumber());
    }
    else if (propertyType == typeof(bool) || propertyType == typeof(bool?))
    {
        val = Convert.ToBoolean(cellValue.GetBoolean());
    }
    else if (propertyType == typeof(DateTime) || propertyType == typeof(DateTime?))
    {
        val = Convert.ToDateTime(cellValue.GetDateTime());
    }
    else
    {
        val = cellValue.ToString();
    }
    return Convert.ChangeType(val, Nullable.GetUnderlyingType(propertyType) ?? propertyType);
}

}

@iammukeshm iammukeshm added the enhancement New feature or request label Apr 7, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

2 participants