.Net Web项目实现：导入Excel表，向数据库增加记录

前端相关

　　界面样式：

 

 　　前端代码：在Index.cshtml

   <form method="post" action="/Home/Upload">
        <input id="fileUpload" type="file" name="file"  />
        <div class="input-group pull-right">
            <button type="submit" id="cc">提交</button>          
        </div>
    </form>

数据库相关

　　数据库名：student   表名：stuinfo

　　

　　进行插值的Excel表

　　

 

 

 后端代码

　　在HomeController.cs

using NPOI.XSSF.UserModel;
using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace excel316.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        
        [HttpPost]
        public ActionResult Upload()
        {
            var ExcelTable = new DataTable();
            var ExcelConnectionString = "Data Source=.; Initial Catalog=student;User ID=sa;Password=Admin12345";//student是数据库名
            var Adp = new SqlDataAdapter("select * from stuinfo", ExcelConnectionString);
            var DataCopier = new SqlBulkCopy(ExcelConnectionString) { DestinationTableName = "stuinfo" };//stuinfo是表名
            Adp.FillSchema(ExcelTable, SchemaType.Source);

            string path = Request.Form.Get("file");//获取前端控件中所选文件的路径，file是<input>中的name值

            var wk = new XSSFWorkbook(System.IO.File.OpenRead(path));
            var st = wk.GetSheetAt(0);
            for(var RowIndex = 1; RowIndex<= st.LastRowNum; RowIndex++)
            {
                var TableNewRow = ExcelTable.NewRow();
                var TempRow = st.GetRow(RowIndex);
                //数据表中有三个字段，第一个字段是ID。主键，自增，不需要我们给这个字段赋值
                //Excel表中只有两个字段
                //所以TableNewRow从下标1开始赋值，赋的值是Excel表的第0个字段
                TableNewRow[1] = TempRow.Cells[0].StringCellValue;
                TableNewRow[2] = TempRow.Cells[1].NumericCellValue;
                ExcelTable.Rows.Add(TableNewRow);
            }
            DataCopier.WriteToServer(ExcelTable);
            Console.WriteLine("数据上传成功");
            return View();
        }
    }
}