Question ASP.NET Core Blazor Excel Import

[raysefo]

Hello,

I am new to ASP.NET core blazor. I would like to know if there are some tutorials about importing excel data into SQL database. (The user selects the excel file then uploads it and saved data into the database is displayed on the view.)

Thanks in advance.

 

[Skydiver]

I'm assuming that you are talking about Blazor server, and not Blazor web assembly. Since it's server side, once the file has been uploaded, you would just process the file the way you would as if you were using old fashioned ASP.NET WebForms or ASP.NET MVC.

The difficult part opening the Excel file. If you need to support Excel 2003 files and older, then it the level of difficulty goes up even more.

DO NOT USE the JET or ACE drivers for reading the Excel file. Microsoft intends the JET and ACE drivers for use on the desktop, not server side. There are warnings on Microsoft's download site about this. Most people ignore this warning and then later wonder why their IIS server and/or web app is acting weird.

I suggest using a 3rd party library for reading the Excel files. If you are too cheap to pay for a library, then be prepared to go through the learning curve of the Microsoft OpenXML SDK for reading the Excel 2007 and newer files. For the Office 2003 and older files, you'll need to read through all the documents that the Department of Justice required Microsoft to submit documenting the older COM/OLE document storage format that Office was using. I believe that there is also an open source Java library that was ported to .NET which can read Office documents. My quick scan of that many years ago left me with the impression that it was a poorly documented kludge. It may have gotten better since then.

 

[raysefo]

Thank you @Skydiver, this excel file consists of orders and details. So I want to give another option to the user to insert data from the razor page other than uploading the excel file.
The main entities are order, order detail, vendor, and customer. I have implemented listing, adding, and updating for vendors and customers.


The tricky part is listing, adding, and updating the order/order details. One of the requirements is to upload data with excel. This excel file consists of orders and details and I want to give another option to the user to insert data from the razor page other than uploading the excel file.


Searching for free Blazor libraries which include master-detail grids having update, and insert features. I couldn't find a solution that would meet the requirements. Hope you can help me.

Orders

int OrderId,
int OrderNumber - autoincrement from 1,
DateTime OrderDate,
string DoneBy,
string Status,
string CustomerName,
string VendorName,
List OrderDetails

OrderDetails

int OrderDetailId,
int ProductNumber - autoincrement from 1,
string ProductCode,
string ProductName,
int BuyQuantity,
int SellQuantity,
double CostRatio,
double UnitCost,
double TotalBuyPrice,
double TotalSellPrice,
string ShippingNumber,
string Status,
string TrackingNumber,
Description,
int OrderId,
Order

 

[Skydiver]

It's because you are trying to get too much out of a single library. These are two separate problems:

1. Blazor master-detail grid view
2. Excel file access library

The two problems are orthogonal to each other. You can have the Blazor master-detail grid view without the Excel library, and you can have the Excel library without a Blazor master-detail grid view. It's your job to use the library to feed the data into the grid view. Most master detail grid views will require you to provider a data adapter so that it can talk to a backend data.

 

[raysefo]

I know these are 2 different things, excel upload is relatively easy. Done it a couple of times but master-detail grid having multiple edits on the rows is something I don't know. Looking for free libraries but unfortunately didn't come across any yet. Pretty much something can solve my problems:

Master Details Demo using the Grid and PopupForm | ASP.net Core Awesome

ASP.net Core Awesome controls demo app

demo.aspnetawesome.com

 

[Skydiver]

In the link you provided notice how you can expand the details, and then edit multiple values on a detail row, or create a new detail row.

 

[raysefo]

According to the excel format, I specified solved excel upload as below. But I still can't figure out how to make multiple edits on order details with Radzen in my other post.

@page "/upload"

@using System
@using System.IO
@using IMS.CoreBusiness
@using IMS.Plugins.EFCore
@using IMS.UseCases.Interfaces.Order
@using Microsoft.AspNetCore.Hosting
@using Microsoft.Extensions.Logging
@using OfficeOpenXml
@using FileInfo = System.IO.FileInfo
@inject IJSRuntime JS
@inject ILogger<UploadOrders> Logger
@inject IWebHostEnvironment Environment
@inject IAddOrderListUseCase AddOrderListUseCase
<style>
    .btns {
        padding: 5px;
        width: calc(16% - 10px);
    }
</style>

<h1>Upload Orders</h1>
<br/>
<br/>
<p>
    <div class="row">
        <div class="col-lg-6">
            <RadzenCard>
                <label>
                    @*Upload up to @maxAllowedFiles file and of up to @maxFileSize bytes:*@
                    <InputFile OnChange="@LoadFiles" multiple/>
                </label>
            </RadzenCard>
        </div>
    </div>
</p>

@*<div class="row px-3">
    <div class="col-lg-6 offset-lg-3 p-3">
        <RadzenCard>
            <h4 class="mb-4">Upload Orders</h4>
            <RadzenFileInput @bind-FileName=@fileName @bind-FileSize=@fileSize TValue="string" Class="w-100" Accept="excel/*"
                             Change="@OnChange" Error=@(args => OnError(args, "FileInput")) />
        </RadzenCard>
    </div>
</div>

<RadzenCard>
    <h4 class="mb-4">Using RadzenProgressBar</h4>
    <RadzenUpload Url="upload/single" Progress=@TrackProgress Complete=@CompleteUpload class="w-100" />
    <RadzenProgressBar Value=@progress class="mt-4" Visible=@showProgress />
    <RadzenLabel Visible=@showComplete class="mt-4" Text="Upload Complete!" />
</RadzenCard>*@

@if (isLoading)
{
    <p>Progress: @string.Format("{0:P0}", progressPercent)</p>
}
else
{
    <ul>
        @foreach (var file in loadedFiles)
        {
            <li>
                <ul>
                    <li>Name: @file.Name</li>
                    <li>Last modified: @file.LastModified.ToString()</li>
                    <li>Size (bytes): @file.Size</li>
                    <li>Content type: @file.ContentType</li>
                </ul>
            </li>
        }
    </ul>
}

<h2>Excel Sample</h2>

<p>
    <button type="button" class="btn btn-primary btns" @onclick="DownloadFileFromStream">
        Download Excel File
    </button>
</p>

@code {
    private List<IBrowserFile> loadedFiles = new();
    private long maxFileSize = 1024 * 1024 * 15;
    private int maxAllowedFiles = 1;
    private bool isLoading;
    private decimal progressPercent;

    string fileName;
    long? fileSize;

    int progress;
    bool showProgress;
    bool showComplete;

    void TrackProgress(UploadProgressArgs args)
    {
        showProgress = true;
        showComplete = false;
        progress = args.Progress;
    }

    void CompleteUpload()
    {
        showProgress = false;
        showComplete = true;
    }

    void OnChange(string value)
    {
        Console.WriteLine(value);
    }

    void OnError(UploadErrorEventArgs args, string name)
    {
        
    }

    private async Task LoadFiles(InputFileChangeEventArgs e)
    {
        isLoading = true;
        loadedFiles.Clear();
        progressPercent = 0;

        foreach (var file in e.GetMultipleFiles(maxAllowedFiles))
        {
            try
            {
                var trustedFileName = file.Name;
                var path = Path.Combine(Environment.ContentRootPath,
                    Environment.EnvironmentName, "unsafe_uploads", trustedFileName);

                await using FileStream writeStream = new(path, FileMode.Create);
                await using var readStream = file.OpenReadStream(maxFileSize);
                var bytesRead = 0;
                var totalRead = 0;
                var buffer = new byte[1024 * 10];

                while ((bytesRead = await readStream.ReadAsync(buffer)) != 0)
                {
                    totalRead += bytesRead;

                    await writeStream.WriteAsync(buffer, 0, bytesRead);

                    progressPercent = Decimal.Divide(totalRead, file.Size);

                    StateHasChanged();
                }

                loadedFiles.Add(file);

                //Get file
                var newfile = new FileInfo(file.Name);
                var fileExtension = newfile.Extension;

                //Check if file is an Excel File
                if (fileExtension.Contains(".xls"))
                {
                    using var ms = new MemoryStream();
                    await loadedFiles[0].OpenReadStream().CopyToAsync(ms);


                    // If you use EPPlus in a noncommercial context
                    // according to the Polyform Noncommercial license:
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    using ExcelPackage package = new ExcelPackage(ms);
                    var workSheet = package.Workbook.Worksheets["Order"];
                    var totalRows = workSheet.Dimension.Rows;

                    var orderList = new List<Order>();
                    List<OrderDetail> orderDetailList = null;

                    var k = 0;

                    for (var i = 2; i <= totalRows; i++)
                    {

                        if(workSheet.Cells[i, 1].Value != null && workSheet.Cells[i, 6].Value != null){
                            orderDetailList = new List<OrderDetail>();
                            //Hem order hem detail var
                            orderList.Add(new Order
                            {
                                OrderDateTime = Convert.ToDateTime(workSheet.Cells[i, 1].Value),
                                CustomerName = workSheet.Cells[i, 2].Value.ToString(),
                                VendorName = workSheet.Cells[i, 3].Value.ToString(),
                                Status = workSheet.Cells[i, 4].Value.ToString(),
                                DoneBy = workSheet.Cells[i, 5].Value.ToString()

                            });

                            orderDetailList.Add(new OrderDetail
                            {
                                ProductCode = workSheet.Cells[i, 6].Value.ToString(),
                                ProductName = workSheet.Cells[i, 7].Value.ToString(),
                                BuyQuantity = Convert.ToInt32(workSheet.Cells[i, 8].Value),
                                SellQuantity = Convert.ToInt32(workSheet.Cells[i, 9].Value),
                                CostRatio = Convert.ToInt32(workSheet.Cells[i, 10].Value),
                                UnitCost = Convert.ToInt32(workSheet.Cells[i, 11].Value),
                                TotalBuyPrice = Convert.ToDouble(workSheet.Cells[i, 12].Value),
                                TotalSellPrice = Convert.ToDouble(workSheet.Cells[i, 13].Value),
                                ShippingNumber = workSheet.Cells[i, 14].Value.ToString(),
                                TrackingNumber = workSheet.Cells[i, 15].Value.ToString(),
                                Status = workSheet.Cells[i, 16].Value.ToString(),
                                Description = workSheet.Cells[i, 17].Value.ToString()
                            });

                            orderList[k].OrderDetails = orderDetailList;
                            k++;

                          
                        }else if(workSheet.Cells[i, 1].Value == null && workSheet.Cells[i, 6].Value != null)
                        {
                            //Sadece detail var
                            orderDetailList.Add(new OrderDetail
                            {
                                ProductCode = workSheet.Cells[i, 6].Value.ToString(),
                                ProductName = workSheet.Cells[i, 7].Value.ToString(),
                                BuyQuantity = Convert.ToInt32(workSheet.Cells[i, 8].Value),
                                SellQuantity = Convert.ToInt32(workSheet.Cells[i, 9].Value),
                                CostRatio = Convert.ToInt32(workSheet.Cells[i, 10].Value),
                                UnitCost = Convert.ToInt32(workSheet.Cells[i, 11].Value),
                                TotalBuyPrice = Convert.ToDouble(workSheet.Cells[i, 12].Value),
                                TotalSellPrice = Convert.ToDouble(workSheet.Cells[i, 13].Value),
                                ShippingNumber = workSheet.Cells[i, 14].Value.ToString(),
                                TrackingNumber = workSheet.Cells[i, 15].Value.ToString(),
                                Status = workSheet.Cells[i, 16].Value.ToString(),
                                Description = workSheet.Cells[i, 17].Value.ToString()
                            });
                            orderList[k - 1].OrderDetails = orderDetailList;

                            
                        }                       

                        
                    }
                    await AddOrderListUseCase.ExecuteAsync(orderList);
                    
                }


            }
            catch (Exception ex)
            {
                Logger.LogError("File: {Filename} Error: {Error}",
                    file.Name, ex.Message);
                
            }
        }

        isLoading = false;
    }

    private Stream GetFileStream()
    {
        var trustedFileName = "Orders.xlsx";
        return File.OpenRead(Path.Combine(Environment.ContentRootPath,
            Environment.EnvironmentName, "unsafe_downloads", trustedFileName));
    }

    private async Task DownloadFileFromStream()
    {
        var fileStream = GetFileStream();
        var fileName = "Orders.xlsx";

        using var streamRef = new DotNetStreamReference(stream: fileStream);

        await JS.InvokeVoidAsync("downloadFileFromStream", fileName, streamRef);
    }
}