.net core npoi 将excal 导入数据库

.net core npoi 将excal 导入数据库

首先用vs 创建一个.net core的网站 和数据处理的类库，我用的是ef， 大家可以用自己的熟悉的框架

然后需要用打开nuget管理器 添加Savorboard.NPOI.Core.Ooxml 包如下图

创建一个view

<form asp-action="UploadFiles"
      asp-controller="Product"
      method="post"
      enctype="multipart/form-data">
    <input type="file" name="files" multiple />
    <input type="submit" value="Upload Selected Files" />
</form>

创建一个controller

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Net.Http.Headers;
using System.Collections.Generic;
using System.IO;
using Microsoft.AspNetCore.Hosting;
using Npoi.Core.XSSF.UserModel;
using Npoi.Core.SS.UserModel;
using System.Data;
using Core.Domain.Models;
using System.Reflection;
using System;
using System.Diagnostics;

namespace Coretest.Controllers
{
    public class ProductController : Controller
    {
        private IHostingEnvironment hostingEnv;
        public ProductController(IHostingEnvironment env)
        {
            this.hostingEnv = env;
        }

        public IActionResult UploadFiles()
        {
            return View();
        }
        [HttpPost]
        public IActionResult UploadFiles(IList<IFormFile> files)
        {

            long size = 0, second = 0;
            //IList<IFormFile> 内的IFormFile对象
            foreach (var file in files)
            {
                string filename = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim();
                filename = hostingEnv.WebRootPath + $@"\{filename.Replace("\"", "")}";
                size += file.Length;
                //将excal 写入本地
                using (FileStream fs = System.IO.File.Create(filename))
                {
                    file.CopyTo(fs);
                    fs.Flush();

                }
                //创建数据容器的实例
                DataTransfer.DataTable dt = new DataTransfer.DataTable();
                using (FileStream stream = System.IO.File.Open(filename, FileMode.Open, FileAccess.Read))
                {
                    //创建 XSSFWorkbook和ISheet实例
                    XSSFWorkbook workbook = new XSSFWorkbook(stream);
                    ISheet sheet = workbook.GetSheetAt(0);
                    //获取sheet的首行
                    IRow headerRow = sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    List<DataTransfer.DataColumn> Columnlist = new List<DataTransfer.DataColumn>();
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        //Column 添加ColumnName
                        Columnlist.Add(new DataTransfer.DataColumn(headerRow.GetCell(i).StringCellValue, headerRow.GetCell(i).CellType.GetType()));
                    }
                    int rowCount = sheet.LastRowNum;
                    object[] rowlist = new object[sheet.LastRowNum];
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {
                        object[] valuelist = new object[cellCount];
                        IRow row = sheet.GetRow(i);
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            //遍历添加Column的数据
                            if (row.GetCell(j) != null)
                                valuelist.SetValue(row.GetCell(j).ToString(), j);
                        }
                        //遍历将Column的数据添加到Datarow
                        rowlist.SetValue(valuelist, i - 1);
                        dt.Rows.Add(new DataTransfer.DataRow(Columnlist, valuelist));
                    }
                    List<Member> list = new List<Member>();
                    foreach (DataTransfer.DataRow dr in dt.Rows)
                    {
                        //填充entity 这个可换成自己的表
                        Member moudel = new Member();
                        foreach (PropertyInfo prop in moudel.GetType().GetRuntimeProperties())
                        {
                            if (dr[prop.Name] != null)
                            {
                                object obj = new object();
                                if (prop.Name == "SecondLevel")
                                {
                                    obj = Convert.ToInt32(dr[prop.Name]);
                                }
                                else
                                {
                                    obj = dr[prop.Name];

                                }
                                prop.SetValue(moudel, obj);
                            }
                        }
                        moudel.ViewSupportId = 0;
                        moudel.ViewSupportName = "";
                        moudel.FirstLevel = 111;
                        moudel.Keywords = 0;
                        moudel.IsCompany = false;
                        moudel.IsReady = false;
                        moudel.UnLock = true;
                        moudel.IsCrm = false;
                        moudel.IsOpera = false;
                        moudel.IP = "";
                        moudel.ImportType = "";
                        moudel.ProtectedTimes = DateTime.Now;
                        moudel.RegisterTime = DateTime.Now;
                        moudel.IsCheck = 0;
                        moudel.IsAborad = 0;
                        list.Add(moudel);
                    }
                    //创建计时器
                    Stopwatch watch = Stopwatch.StartNew();
                    //ef连接数据库
                    using (var context = new DataContext())
                    {
                        //批量insert
                        foreach (Member entity in list)
                        {
                            context.Member.Add(entity);
                        }
                        context.SaveChanges();
                    }
                    //获取处理数据所用的时间
                    second = watch.ElapsedMilliseconds;

                }
                ViewBag.Message = $"{files.Count} file(s) / { size} bytes  When used { second } uploaded successfully!";
            }
            return View();

        }
    }
}

这个按照打他table的思路简单的搭建了一个数据容器， 好多的方法都可以自己扩展

using System;
using System.Collections.Generic;

namespace Coretest.DataTransfer
{
    public class DataTable
    { /// <summary>
      /// 整个查询语句结果的总条数，而非本DataTable的条数
      /// </summary>
        public int TotalCount { get; set; }
        public List<DataColumn> Columns { get; set; } = new List<DataColumn>();
        public List<DataRow> Rows { get; set; } = new List<DataRow>();
        public DataColumn[] PrimaryKey { get; set; }
        public DataRow NewRow()
        {
            return new DataRow(this.Columns, new object[Columns.Count]);
        }
    }
    public class DataColumn
    {
        public string ColumnName { get; set; }
        public Type ColumnType { get; set; }
        public DataColumn(string columnName, Type columnType)
        {
            this.ColumnName = columnName;
            this.ColumnType = columnType;
        }
    }
    public class DataRow
    {
        private object[] _ItemArray;
        public List<DataColumn> Columns { get; private set; }
        public DataRow(List<DataColumn> columns, object[] itemArray)
        {
            this.Columns = columns;
            this._ItemArray = itemArray;
        }
        public object this[int index]
        {
            get
            {
                //如果传入的index不存在返回null
                if (Columns.Count < index || index < 0)
                {
                    return null;
                }
                return _ItemArray[index];
            }
            set { _ItemArray[index] = value; }
        }
        public object this[string columnName]
        {
            get
            {
                int i = 0, n = 0;
                foreach (DataColumn column in Columns)
                {
                    if (column.ColumnName != columnName)
                    {
                        n++;
                    }
                    if (column.ColumnName == columnName)
                        break;
                    i++;
                }
                //如果传入的columnName不存在返回null
                if (Columns.Count == i)
                {
                    return null;
                }
                return _ItemArray[i];
            }
            set
            {
                int i = 0;
                foreach (DataColumn column in Columns)
                {
                    if (column.ColumnName == columnName)
                        break;
                    i++;
                }

                _ItemArray[i] = value;
            }
        }
    }
}

路由设置

在Configuration 添加services.AddDbContext 方法

ef 数据配置

using Microsoft.EntityFrameworkCore;

namespace Core.Domain.Models
{
    public class DataContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseSqlServer(@"Data Source=.;Initial Catalog=Datatest;User ID=sa;Password=123456");
        public DbSet<User> Users { get; set; }
        public DbSet<Member> Member { get; set; }
    }
}

表结构entity 代码

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Core.Domain.Models
{
    [Table(name:"Member")]
    public class Member
    {
        [Key,Column(name: "ID"), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        [Column(name: "Email")]
        public string Email { get; set; }
        [Column(name: "Password")]
        public string Password{ get; set; }
        [Column(name: "Name")]
        public string Name{ get; set; }
        [Column(name: "BrithDay")]
        public System.Nullable<System.DateTime> BrithDay{ get; set; }
        [Column(name: "Sex")]
        public System.Nullable<bool> Sex{ get; set; }
        [Column(name: "PhoneBelongAddress")]
        public string PhoneBelongAddress{ get; set; }
        [Column(name: "Phone")]
        public string Phone{ get; set; }
        [Column(name: "Mobile")]
        public string Mobile{ get; set; }
        [Column(name: "QQ")]
        public string QQ{ get; set; }
        [Column(name: "MSN")]
        public string MSN{ get; set; }

        [Column(name: "Skype")]
        public string Skype{ get; set; }
        [Column(name: "StudySchool")]
        public System.Nullable<int> StudySchool{ get; set; }
        [Column(name: "CityId")]
        public string CityId{ get; set; }
        [Column(name: "ViewSupportId")]
        public System.Nullable<int> ViewSupportId{ get; set; }
        [Column(name: "ViewSupportName")]
        public string ViewSupportName{ get; set; }
        [Column(name: "SupportID")]
        public System.Nullable<int> SupportID{ get; set; }

        [Column(name: "SupportName")]
        public string SupportName{ get; set; }
        [Column(name: "SupportCode")]
        public string SupportCode{ get; set; }
        [Column(name: "ComeUrl")]
        public string ComeUrl{ get; set; }
        [Column(name: "IPBelongAddress")]
        public string IPBelongAddress{ get; set; }
        [Column(name: "SessionId")]
        public System.Nullable<int> SessionId{ get; set; }
        [Column(name: "CustomerName")]
        public string CustomerName{ get; set; }
        [Column(name: "Description")]
        public string Description{ get; set; }
        [Column(name: "ImportType")]
        public string ImportType{ get; set; }
        [Column(name: "KeyWord")]
        public string KeyWord{ get; set; }
        [Column(name: "EnterType")]
        public string EnterType{ get; set; }
        [Column(name: "IsCompany")]
        public bool IsCompany{ get; set; }
        [Column(name: "CompanyName")]
        public string CompanyName{ get; set; }
        [Column(name: "IsReady")]
        public bool IsReady{ get; set; }
        [Column(name: "UnLock")]
        public bool UnLock{ get; set; }
        [Column(name: "IsOpera")]
        public bool IsOpera{ get; set; }
        [Column(name: "IsCrm")]
        public bool IsCrm{ get; set; }
        [Column(name: "IP")]
        public string IP{ get; set; }
        [Column(name: "RegisterTime")]
        public System.DateTime RegisterTime{ get; set; }
        [Column(name: "RegisterUrl")]
        public string RegisterUrl{ get; set; }
        [Column(name: "Age")]
        public string Age{ get; set; }
        [Column(name: "CityAreaId")]
        public System.Nullable<int> CityAreaId{ get; set; }
        [Column(name: "StudyPurpose")]
        public string StudyPurpose{ get; set; }
        [Column(name: "ProtectedTimes")]
        public System.Nullable<System.DateTime> ProtectedTimes{ get; set; }
        [Column(name: "IsAborad")]
        public System.Nullable<int> IsAborad{ get; set; }
        [Column(name: "IsCheck")]
        public int IsCheck{ get; set; }
        [Column(name: "OfferType")]
        public System.Nullable<int> OfferType{ get; set; }
        [Column(name: "FirstLevel")]
        public System.Nullable<int> FirstLevel{ get; set; }
        [Column(name: "SecondLevel")]
        public System.Nullable<int> SecondLevel{ get; set; }
        [Column(name: "Keywords")]
        public System.Nullable<int> Keywords{ get; set; }
        [Column(name: "DataType")]
        public System.Nullable<int> DataType{ get; set; }
        [Column(name: "StateLogId")]
        public System.Nullable<int> StateLogId{ get; set; }
        [Column(name: "StateLogKey")]
        public string StateLogKey{ get; set; }

    }
}