.Net Core 使用NPOI导入数据

你的名字 2023-08-17 16:55 46阅读 0赞

一、搭建环境

1.新建ASP.NET Core Web 应用程序

![1677460-20190828200227464-1006588213.png][]

2.选择API

![1677460-20190828200335426-1487441636.png][]

3.引用Swashbuckle.AspNetCore NuGet 包进行安装. Swashbuckle.AspNetCore 是一个开源项目，用于生成 ASP.NET Core Web API 的 Swagger 文档。

前端开发人员可在浏览器中访问此接口文档。

![1677460-20190828201218166-770104975.png][]

4.光引用此NuGet包是不行的，还要在Startup中添加并配置Swaage中间件

首先在ConfigureServices中写入以下代码:

然后在Configure中写入以下代码:

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
            {
                if (env.IsDevelopment())
                {
                    app.UseDeveloperExceptionPage();
                }
                else
                {
         
                    app.UseHsts();
                }
    
                #region
                app.UseSwagger();
                app.UseSwaggerUI(c =>
                {
                    c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");
                });
                #endregion
    
                app.UseHttpsRedirection();
                app.UseMvc();
            }

5.右键项目生成XML文件，并在Startup中注册服务,用于读取这个XML文档,不然写的接口都不会显示出来.

![1677460-20190828203816487-1969911405.png][]

引用NuGet包:Microsoft.Extensions.PlatformAbstractions

![1677460-20190828204543916-1072052352.png][]

接着在Startup类ConfigureServices方法中,添加如下代码:

public void ConfigureServices(IServiceCollection services)
            {
                services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
    
                #region
                services.AddSwaggerGen(c =>
                {
                    c.SwaggerDoc("v1", new Info { Title = "API文档", Version = "v1" });
    
                    #region
                    var basePath = PlatformServices.Default.Application.ApplicationBasePath;
                    //MyImport.xml是我的项目生成XML文档的后缀名,具体的以你项目为主
                    var xmlPath = Path.Combine(basePath, "MyImport.xml");
                    c.IncludeXmlComments(xmlPath);
                    #endregion
                });
                #endregion
            }

OK ,现在可以新建一个控制器，写一个接口看看有没有效果

![1677460-20190828210620541-1330147987.png][]

将地址栏修改一下

![1677460-20190920165943941-1423519733.png][]

![1677460-20190828210526110-881814203.png][]

我们也可以在launchSettings.json中做一下更改,这样就不用每次都改浏览器地址栏了,如下：

![1677460-20190920170155019-1822686994.png][]

二·、数据库新建表及项目中实体类的建立

--商品表
    Create Table Commodity
    (
    Cid int primary key identity(1,1),--主键,自增
    Name varchar(50) not null,--商品名称
    Price decimal(18,2) not null,--价格
    [Type] varchar(50) not null,--商品类别
    [Time] varchar(50) not null,--保质期
    Remarks varchar(100)--备注    
    )

using SqlSugar;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace MyImport.Model
    {
        /// <summary>
        /// 商品类
        /// </summary>
        public class Commodity
        {
            /// <summary>
            /// 主键Id
            /// </summary>
            [SugarColumn(IsNullable = false, IsPrimaryKey = true, IsIdentity = true)]
            public int Cid { get; set; }
    
            /// <summary>
            /// 商品名称
            /// </summary>
            [SugarColumn(Length = 50, IsNullable = false)]
            public string Name { get; set; }
    
            /// <summary>
            /// 商品价格
            /// </summary>
            [SugarColumn(IsNullable = false)]
            public double Price { get; set; }
    
            /// <summary>
            /// 商品类别
            /// </summary>
            [SugarColumn(Length = 50, IsNullable = false)]
            public string Type { get; set; }
    
            /// <summary>
            /// 保质期
            /// </summary>
            [SugarColumn(Length = 50, IsNullable = false)]
            public string Time { get; set; }
    
            /// <summary>
            /// 备注
            /// </summary>
            [SugarColumn(Length = 100, IsNullable = true)]
            public string Remarks { get; set; }
        }
    }

三、代码编写

注：在写代码之前,先引用两个NuGet包：NPOI(这个包用来操作Excel)和SqlSugarCore(这是一个高性能的ORM框架,操作数据库更便捷)

![1677460-20190920170650402-998593994.png][]

![1677460-20190920170730240-265011784.png][]

话不多说,贴代码:

using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Threading.Tasks;
    using Microsoft.AspNetCore.Http;
    using Microsoft.AspNetCore.Mvc;
    using MyImport.Model;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using NPOI.XSSF.UserModel;
    using SqlSugar;
    
    namespace MyImport.Controllers
    {
        [Route("api/[controller]")]
        [ApiController]
        public class MyCoreController : Controller
        {
            public MyCoreController()
            {
    
            }
    
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "Data Source=LENOVO-PC;Initial Catalog=School;Integrated Security=True",
                DbType = SqlSugar.DbType.SqlServer,
                IsAutoCloseConnection = true
            });
    
            /// <summary>
            /// 数据导入
            /// </summary>
            /// <returns></returns>
            [HttpPost]
            public async Task<string> Import(IFormFile file)
            {
                string ReturnValue = string.Empty;
                //定义一个bool类型的变量用来做验证
                bool flag = true;
                try
                {
                    string fileExt = Path.GetExtension(file.FileName).ToLower();
                    //定义一个集合一会儿将数据存储进来,全部一次丢到数据库中保存
                    var Data = new List<Commodity>();
                    MemoryStream ms = new MemoryStream();
                    file.CopyTo(ms);
                    ms.Seek(0, SeekOrigin.Begin);
                    IWorkbook book;
                    if (fileExt == ".xlsx")
                    {
                        book = new XSSFWorkbook(ms);
                    }
                    else if (fileExt == ".xls")
                    {
                        book = new HSSFWorkbook(ms);
                    }
                    else
                    {
                        book = null;
                    }
                    ISheet sheet = book.GetSheetAt(0);
    
                    int CountRow = sheet.LastRowNum + 1;//获取总行数
    
                    if (CountRow - 1 == 0)
                    {
                        return "Excel列表数据项为空!";
    
                    }
                    #region 循环验证
                    for (int i = 1; i < CountRow; i++)
                    {
                        //获取第i行的数据
                        var row = sheet.GetRow(i);
                        if (row != null)
                        {
                            //循环的验证单元格中的数据
                            for (int j = 0; j < 5; j++)
                            {
                                if (row.GetCell(j) == null || row.GetCell(j).ToString().Trim().Length == 0)
                                {
         
                                    flag = false;
                                    ReturnValue += $"第{i + 1}行,第{j + 1}列数据不能为空。";
                                }
                            }
                        }
                    }
                    #endregion
                    if (flag)
                    {
                        for (int i = 1; i < CountRow; i++)
                        {
                            //实例化实体对象
                            Commodity commodity = new Commodity();
                            var row = sheet.GetRow(i);
    
                            if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                            {
                                commodity.Name = row.GetCell(0).ToString();
                            }
                            if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                            {
                                commodity.Price = Convert.ToDouble(row.GetCell(1).ToString());
                            }
                            if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                            {
                                commodity.Type = row.GetCell(2).ToString();
                            }
                            if (row.GetCell(3) != null && row.GetCell(3).ToString().Trim().Length > 0)
                            {
                                commodity.Time = row.GetCell(3).ToString().ToString();
                            }
                            if (row.GetCell(4) != null && row.GetCell(4).ToString().Trim().Length > 0)
                            {
                                commodity.Remarks = row.GetCell(4).ToString();
                            }
                            else
                            {
                                commodity.Remarks = "暂无";
                            }
                            Data.Add(commodity);
                        }
                        var data = db.Insertable<Commodity>(Data).ExecuteCommand();
                        ReturnValue = $"数据导入成功,共导入{CountRow - 1}条数据。";
                    }
                   
                    if (!flag)
                    {
                        ReturnValue = "数据存在问题！" + ReturnValue;
                    }
                }
                catch (Exception)
                {
                    return "服务器异常";
                }
    
                return ReturnValue;
            }
        }
    }

建一个Excel文件,填写好测试数据

![1677460-20190920171341427-2138132105.png][]

打开接口文档,选择这个Excel文件,进行导入

![1677460-20190920171920711-809601999.png][]

搞定！希望可以帮助到你、^-^

![1677460-20190920171722167-738892499.png][]

转载于:https://www.cnblogs.com/zhangnever/p/11426643.html

[1677460-20190828200227464-1006588213.png]: /images/20230809/621ae6fd53334cb19e18de1386ebdda9.png
[1677460-20190828200335426-1487441636.png]: /images/20230809/2afe0c609417465b804925c0c0da1f0e.png
[1677460-20190828201218166-770104975.png]: /images/20230809/f2735365ea6641bc80bb62088c0c7ca2.png
[1677460-20190828203816487-1969911405.png]: /images/20230809/a5431c2010d74ca18dd887bb1a3976cf.png
[1677460-20190828204543916-1072052352.png]: /images/20230809/21f1da30e19041f3a02c1f9cfa22244a.png
[1677460-20190828210620541-1330147987.png]: /images/20230809/e13b5704dc0b4ac080a50cca85aa7869.png
[1677460-20190920165943941-1423519733.png]: /images/20230809/60ed162d3244495c9094f9dbc70fe46f.png
[1677460-20190828210526110-881814203.png]: /images/20230809/fa64ff158cb7456aa29e70d05ce326d5.png
[1677460-20190920170155019-1822686994.png]: /images/20230809/cbccfc4015fb4a3ea647b72e6b38d6ad.png
[1677460-20190920170650402-998593994.png]: /images/20230809/298fb720a6dd44578e7215bddadd3c48.png
[1677460-20190920170730240-265011784.png]: /images/20230809/c8ee69ec19df41bfaebc19afcad2a13d.png
[1677460-20190920171341427-2138132105.png]: /images/20230809/acae77ef07824be982084a8692e8501e.png
[1677460-20190920171920711-809601999.png]: /images/20230809/d3755e2f74204f44ac866c80f2e90f8a.png
[1677460-20190920171722167-738892499.png]: /images/20230809/229498ef3d41450380c1d2617020cd94.png