ASP.NET Core Blazor Excel Import

Question

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 from file explorer then uploads it and saved data into the database is displayed on the view.

Thanks in advance.

Answer 1

According to your description, I suggest you could use System.Data.OleDb and System.Data.SqlClient package to achieve your requirement inside the blazor server app.

You could firstly save the uploaded excel inside the server and then use olddb to read it and insert it into the sql database.

More details, you could refer to below codes:

Connection string:

  "ConnectionStrings": {  
    "constr": "Server=(localdb)\\mssqllocaldb;Database=xxxxxxxxxx;Trusted_Connection=True;MultipleActiveResultSets=true",  
    "ExcelConString": "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"  
  }  

Razor codes(Please refer to below extension):

213069-pagecodes.txt

Result:

Answer 2

Hi @Brando Zhang-MSFT ,

Thanks for your reply. I wonder after saving to the DB can I navigate to the list page?

Something like this;

private async Task OnValidSubmit()  
    {  
        if (this.inventory != null)  
        {  
            NavigationManager.NavigateTo("/inventories");  
        }  
              
    }  

Answer 3

Hi,

I want to insert one to many related data; orders and order details. How can I achieve this?

Here is a sample excel

Here are the entities:

public class Order  
    {  
        public int Id { get; set; }  
          
        [Required]  
        public DateTime OrderDateTime { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public string CustomerName { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public string VendorName { get; set; }  
        public string Status { get; set; }  
        [MaxLength(50)]  
        public string DoneBy { get; set; }  
        public List<OrderDetail> OrderDetails { get; set; }  
  
  
    }  
  
public class OrderDetail  
    {  
        public int Id { get; set; }  
          
        [Required]  
        [MaxLength(100)]  
        public string ProductCode { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public string ProductName { get; set; }  
        [Required]  
        public int BuyQuantity { get; set; }  
        [Required]  
        public int SellQuantity { get; set; }  
        public double CostRatio { get; set; }  
        public double UnitCost { get; set; }  
        public double TotalBuyPrice { get; set; }  
        public double TotalSellPrice { get; set; }  
        [MaxLength(150)]  
        public string ShippingNumber { get; set; }  
        public string Status { get; set; }  
        [MaxLength(150)]  
        public string TrackingNumber { get; set; }  
        [MaxLength(400)]  
        public string Description { get; set; }  
        public int OrderId { get; set; }  
        public virtual Order Order { get; set; }  
    }