如何在.NET Core Web API中接收Angular上传的Excel并存入SQL Server

前置依赖安装 #

首先安装需要的NuGet包：

• ExcelDataReader：用于解析xls/xlsx格式Excel文件
• ExcelDataReader.DataSet：支持将Excel内容转为DataSet结构
• Microsoft.EntityFrameworkCore：EF Core ORM框架
• Microsoft.EntityFrameworkCore.SqlServer：SQL Server驱动
• System.Data.SqlClient：用于调用存储过程

1. 基础配置 #

1.1 appsettings.json 配置数据库连接串 #

"ConnectionStrings": {
  "DefaultConnection": "Server=你的SQL实例地址;Database=ShubhamPractiseEntities;Trusted_Connection=True;TrustServerCertificate=True;"
}

1.2 Program.cs 注册服务 #

// 注册DbContext
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
// 配置跨域（Angular和API端口不一致时必须开启）
builder.Services.AddCors(opt => {
    opt.AddPolicy("AllowAll", policy => {
        policy.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader();
    });
});
// 中间件配置部分加入跨域启用，放在路由和控制器中间
app.UseCors("AllowAll");

1.3 实体类与DbContext定义 #

// 对应数据库UserDetail表的实体类
public class UserDetail
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public string EmailId { get; set; }
    public string Gender { get; set; }
    public string Address { get; set; }
    public string MobileNo { get; set; }
    public string PinCode { get; set; }
}

// 数据库上下文
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    public DbSet<UserDetail> UserDetails { get; set; }
}

2. SQL Server 存储过程编写 #

符合你优先使用存储过程的要求，先创建用户数据插入存储过程：

CREATE PROCEDURE InsertUserDetail
    @UserName NVARCHAR(100),
    @EmailId NVARCHAR(100),
    @Gender NVARCHAR(10),
    @Address NVARCHAR(500),
    @MobileNo NVARCHAR(20),
    @PinCode NVARCHAR(10)
AS
BEGIN
    INSERT INTO UserDetails (UserName, EmailId, Gender, Address, MobileNo, PinCode)
    VALUES (@UserName, @EmailId, @Gender, @Address, @MobileNo, @PinCode)
END

3. API控制器实现 #

完全适配你现有Angular代码的请求路径、参数格式：

// 对接Excel上传的POST接口
[ApiController]
[Route("[controller]")]
public class ExcelExampleController : ControllerBase
{
    private readonly AppDbContext _dbContext;
    public ExcelExampleController(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    [HttpPost]
    public async Task<IActionResult> Upload([FromForm] IFormFile upload)
    {
        if (upload == null || upload.Length == 0)
            return BadRequest("未检测到上传文件");

        // 校验文件格式
        var ext = Path.GetExtension(upload.FileName).ToLower();
        if (ext is not ".xls" and not ".xlsx")
            return Ok("该文件格式不受支持");

        // 解析Excel内容
        using var stream = upload.OpenReadStream();
        var reader = ext switch
        {
            ".xls" => ExcelReaderFactory.CreateBinaryReader(stream),
            ".xlsx" => ExcelReaderFactory.CreateOpenXmlReader(stream),
            _ => null
        };

        var excelRecords = reader.AsDataSet();
        reader.Close();
        var dataTable = excelRecords.Tables[0];
        int successCount = 0;

        // 循环调用存储过程插入数据
        for (int i = 0; i < dataTable.Rows.Count; i++)
        {
            var row = dataTable.Rows[i];
            var parameters = new SqlParameter[]
            {
                new SqlParameter("@UserName", row[0].ToString()),
                new SqlParameter("@EmailId", row[1].ToString()),
                new SqlParameter("@Gender", row[2].ToString()),
                new SqlParameter("@Address", row[3].ToString()),
                new SqlParameter("@MobileNo", row[4].ToString()),
                new SqlParameter("@PinCode", row[5].ToString())
            };
            successCount += await _dbContext.Database.ExecuteSqlRawAsync(
                "EXEC InsertUserDetail @UserName, @EmailId, @Gender, @Address, @MobileNo, @PinCode",
                parameters);
        }

        var message = successCount > 0 ? "Excel文件上传成功" : "Excel文件上传失败";
        return Ok(message);
    }
}

// 对接获取用户列表的GET接口
[ApiController]
[Route("[controller]")]
public class UserDetailsController : ControllerBase
{
    private readonly AppDbContext _dbContext;
    public UserDetailsController(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    [HttpGet]
    public async Task<IActionResult> GetAll()
    {
        var users = await _dbContext.UserDetails.ToListAsync();
        return Ok(users);
    }
}

4. Angular代码优化提示 #

你现有Angular Service里的Header设置是无效的：HttpHeaders.append会返回新的实例，且FormData请求不需要手动设置multipart/form-data，浏览器会自动生成带边界的Content-Type，可简化为：

UploadExcel(formdata: FormData){
    return this.http.post(this.url+'/ExcelExample',formdata);
}

内容的提问来源于stack exchange，提问作者Shubham Shaw