手把手教你mvc导入excel

准备工作:

1.在项目中添加对NPOI的引用，NPOI下载地址：http://npoi.codeplex.com/releases/view/38113

2.NPOI学习

NPOI下载，里面有五个dll，需要引用到你的项目，我这边用的mvc4+三层的方式架构的项目

我用的工具是(vs2012+sql2014)

准备工作做完，我们开始进入主题

1.前端页面，代码:

 
 @using (Html.BeginForm("importexcel", "foot", FormMethod.Post, new { enctype = "multipart/form-data" }))
   {
     请选择要上传的Excel文件：
     
     选择文件@*
     @Html.AntiForgeryToken() //防止跨站请求伪造(CSRF:Cross-site request forgery)攻击
    *@ 
   }
      

2.接下来就是控制器

public class footController : Controller
  {
    //
    // GET: /foot/
    private static readonly String Folder = "/files";
    public ActionResult excel()
    {
      return View();
    }

    /// 导入excel文档
    public ActionResult importexcel()
    {
      //1.接收客户端传过来的数据
      HttpPostedFilebase file = Request.Files["file"];
      if (file == null || file.ContentLength <= 0)
      {
 return Json("请选择要上传的Excel文件", JsonRequestBehavior.AllowGet);
      }
      //string filepath = Server.MapPath(Folder);
      //if (!Directory.Exists(filepath))
      //{
      //  Directory.CreateDirectory(filepath);
      //}
      //var fileName = Path.Combine(filepath, Path.GetFileName(file.FileName));
      // file.SaveAs(fileName);
      //获取一个streamfile对象，该对象指向一个上传文件，准备读取改文件的内容
      Stream streamfile = file.InputStream;
      DataTable dt = new DataTable();
      string FinName = Path.GetExtension(file.FileName);
      if (FinName != ".xls" && FinName != ".xlsx")
      {
 return Json("只能上传Excel文档",JsonRequestBehavior.AllowGet);
      }
      else
      {
 try
 {
   if (FinName == ".xls")
   {
     //创建一个webbook，对应一个Excel文件(用于xls文件导入类)
     HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
     dt = excelDAL.ImExport(dt, hssfworkbook);
   }
   else
   {
     XSSFWorkbook hssfworkbook = new XSSFWorkbook(streamfile);
     dt = excelDAL.ImExport(dt, hssfworkbook);
   }
   return Json("",JsonRequestBehavior.AllowGet);
 }
 catch(Exception ex)
 {
   return Json("导入失败 ！"+ex.Message, JsonRequestBehavior.AllowGet);
 }
    }
      
    }

}

3.业务逻辑层[excelDAL]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using NPOI.XSSF.UserModel;

namespace GJL.Compoent
{
  public class excelDAL
  {
    ///

    /// #region 两种不同版本的操作excel
    /// 扩展名*.xlsx
    /// 
    public static DataTable ImExport(DataTable dt, XSSFWorkbook hssfworkbook)
    {
      NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
      System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
      for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
      {
 dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
      }
      while (rows.MoveNext())
      {
 XSSFRow row = (XSSFRow)rows.Current;
 DataRow dr = dt.NewRow();
 for (int i = 0; i < row.LastCellNum; i++)
 {
   NPOI.SS.UserModel.ICell cell = row.GetCell(i);
   if (cell == null)
   {
     dr[i] = null;
   }
   else
   {
     dr[i] = cell.ToString();
   }
 }
 dt.Rows.Add(dr);
      }
      dt.Rows.RemoveAt(0);
      if (dt!=null && dt.Rows.Count != 0)
      {
 for (int i = 0; i < dt.Rows.Count; i++)
 {
   string categary = dt.Rows[i]["页面"].ToString();
   string fcategary = dt.Rows[i]["分类"].ToString();
   string fTitle = dt.Rows[i]["标题"].ToString();
   string fUrl = dt.Rows[i]["链接"].ToString();
   FooterDAL.Addfoot(categary, fcategary, fTitle, fUrl);
 }
      }
      return dt;
    }

    #region 两种不同版本的操作excel
    ///

    /// 扩展名*.xls
    /// 
    public static DataTable ImExport(DataTable dt, HSSFWorkbook hssfworkbook)
    {
      // 在webbook中添加一个sheet,对应Excel文件中的sheet,取出第一个工作表，索引是0 
      NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
      System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
      for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
      {
 dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
      }
      while (rows.MoveNext())
      {
 HSSFRow row = (HSSFRow)rows.Current;
 DataRow dr = dt.NewRow();
 for (int i = 0; i < row.LastCellNum; i++)
 {
   NPOI.SS.UserModel.ICell cell = row.GetCell(i);
   if (cell == null)
   {
     dr[i] = null;
   }
   else 
   {
     dr[i] = cell.ToString();
   }
 }
 dt.Rows.Add(dr);
      }
      dt.Rows.RemoveAt(0);
      if (dt != null && dt.Rows.Count != 0)
      {
 for (int i = 0; i < dt.Rows.Count; i++)
 {
   string categary = dt.Rows[i]["页面"].ToString();
   string fcategary = dt.Rows[i]["分类"].ToString();
   string fTitle = dt.Rows[i]["标题"].ToString();
   string fUrl = dt.Rows[i]["链接"].ToString();
   FooterDAL.Addfoot(categary, fcategary, fTitle, fUrl);
 }

      }
      return dt;
    }
    #endregion
  }
}

 public static partial class FooterDAL
  {
    /// 

    /// 添加
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    public static int Addfoot(string categary, string fcategary, string fTitle, string fUrl)
    {
      string sql = string.Format("insert into Foot (categary,fcategary,fTitle,fUrl)values(@categary,@fcategary,@fTitle,@fUrl)");
      SqlParameter[] parm = 
 { 
    new SqlParameter("@categary",categary)
   ,new SqlParameter("@fcategary",fcategary)
   ,new SqlParameter("@fTitle",fTitle)
   ,new SqlParameter("@fUrl",fUrl)
 };
      return new DBHelperSQL(CommonTool.dbname).ExcuteSql(sql,parm);  
    }
}

//FooterDAL将datatable,就是excel里面的数据添加到sql数据库

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持考高分网。