準備工作:

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

2.NPOI學習

NPOI下載，里面有五個dll，需要引用到你的項目，我這邊用的mvc4+三層的方式架構的項目

我用的工具是(vs2012+sql2014)

準備工作做完，我們開始進入主題

1.前端頁面，代碼:

<div class="filebtn">         @using (Html.BeginForm("importexcel", "foot", FormMethod.Post, new { enctype = "multipart/form-data" }))          {            <samp>請選擇要上傳的Excel文件：</samp>            <span id="txt_Path"></span>            <strong>選擇文件<input name="file" type="file" id="file" /></strong>@*            @Html.AntiForgeryToken() //防止跨站請求偽造(CSRF:Cross-site request forgery)攻擊           *@<input type="submit" id="ButtonUpload" value="提交"  class="offer"/>           }      </div>

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  {    ///<summary>    /// #region 兩種不同版本的操作excel    /// 擴展名*.xlsx    /// </summary>    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    ///<summary>    /// 擴展名*.xls    /// </summary>    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  }}