MVC使用NPOI到导入excel表

以你之姓@ 2022-05-20 05:27 234阅读 0赞

@*前台代码*@
    @{
        ViewBag.Title = "Index";
    }
    
    <form method="post" enctype="multipart/form-data" >
    
        请选择您要导入的文件：<input type="file" name="efile" />
           <input type="submit" value="导入" />
    
    </form>

//后台代码
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Data.OleDb;//引入命名空间
    using System.Data;
    using System.IO;
    using NPOI.HSSF.UserModel;
    using NPOI.HSSF.Util;
    
    namespace MvcApplication1.Controllers
    {
        public class ExcelController : Controller
        {
            //
            // GET: /Excel/
    
            /// <summary>
            /// 导入excel
            /// 运用NPOI导入
            /// </summary>
            /// <returns></returns>
            public ActionResult Index()
            {
                //判断是否有文件上传
                if(Request.Files.Count>0){
                    //得到文件名称
                    string filename=Request.Files[0].FileName;
                   
                    string houzui = filename.Substring(filename.LastIndexOf("."));
    
                    if (houzui.ToLower() == ".xls")
                    {
                        //得到新路径
                        //string path = Server.MapPath("~") + @"Excel/" + Guid.NewGuid() + houzui;
                        string path = Server.MapPath("~/Excel/"+Guid.NewGuid()+houzui);
    
                        //保存文件
                        Request.Files[0].SaveAs(path);
    
                        //打开文件
                        FileStream fs = new FileStream(path, FileMode.Open);
    
                        //读取整个Excel表
                        HSSFWorkbook workbook = new HSSFWorkbook(fs);
    
                        //查询Excel里面的表
                        HSSFSheet sheet = workbook.GetSheet("Sheet1") as HSSFSheet;
    
                        EntityDBEntities1 en = new EntityDBEntities1();
    
                        for (int i = 1; i < sheet.LastRowNum+1; i++)
                        {
                            stuinfo s = new stuinfo();
    
                            //获取行
                           HSSFRow row=sheet.GetRow(i) as  HSSFRow;
    
                            //获取第一列
                           var cell = row.GetCell(0);
                         
                           //判断第一列值的类型是否为string型
                           if(cell.CellType==NPOI.SS.UserModel.CellType.STRING){
                                s.stuno = cell.StringCellValue;
    
                            }
                           //判断第一列值的类型是否为int型
                            if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
                            {
                                s.stuno = cell.NumericCellValue.ToString();
    
                            }
    
                             cell = row.GetCell(1);
                            if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
                            {
                                s.name = cell.StringCellValue;
    
                            }
    
                            if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
                            {
                                s.name = cell.NumericCellValue.ToString();
    
                            }
    
                             cell = row.GetCell(2);
                             s.age =Convert.ToInt32(cell.NumericCellValue);
                            //添加到表里面
                             en.stuinfo.Add(s);
                        }
    
                        int count = en.SaveChanges();
                        //关闭
                        fs.Close();
                    }
                    else
                    {
                        Response.Write("选择文件格式有误");
                    }              
                  
                }
    
                return View();
            }
        }
    }