手把手教你mvc導(dǎo)入excel_實(shí)用技巧

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

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

 /// 導(dǎo)入excel文檔
 public ActionResult importexcel()
 {
 //1.接收客戶端傳過(guò)來(lái)的數(shù)據(jù)
 HttpPostedFileBase file = Request.Files["file"];
 if (file == null || file.ContentLength <= 0)
 {
 return Json("請(qǐng)選擇要上傳的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);
 //獲取一個(gè)streamfile對(duì)象，該對(duì)象指向一個(gè)上傳文件，準(zhǔn)備讀取改文件的內(nèi)容
 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")
 {
 //創(chuàng)建一個(gè)webbook，對(duì)應(yīng)一個(gè)Excel文件(用于xls文件導(dǎo)入類)
 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("導(dǎo)入失敗 ！"+ex.Message, JsonRequestBehavior.AllowGet);
 }
 }
 
 }

}

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
 /// 擴(kuò)展名*.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]["頁(yè)面"].ToString();
 string fcategary = dt.Rows[i]["分類"].ToString();
 string fTitle = dt.Rows[i]["標(biāo)題"].ToString();
 string fUrl = dt.Rows[i]["鏈接"].ToString();
 FooterDAL.Addfoot(categary, fcategary, fTitle, fUrl);
 }
 }
 return dt;
 }

 #region 兩種不同版本的操作excel
 ///<summary>
 /// 擴(kuò)展名*.xls
 /// </summary>
 public static DataTable ImExport(DataTable dt, HSSFWorkbook hssfworkbook)
 {
 // 在webbook中添加一個(gè)sheet,對(duì)應(yīng)Excel文件中的sheet,取出第一個(gè)工作表，索引是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]["頁(yè)面"].ToString();
 string fcategary = dt.Rows[i]["分類"].ToString();
 string fTitle = dt.Rows[i]["標(biāo)題"].ToString();
 string fUrl = dt.Rows[i]["鏈接"].ToString();
 FooterDAL.Addfoot(categary, fcategary, fTitle, fUrl);
 }

 }
 return dt;
 }
 #endregion
 }
}

 public static partial class FooterDAL
 {
 /// <summary>
 /// 添加
 /// </summary>
 /// <param name="id"></param>
 /// <param name="catgary"></param>
 /// <param name="fcatgary"></param>
 /// <param name="fTitle"></param>
 /// <param name="fUrl"></param>
 /// <returns></returns>
 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<Foot>(CommonTool.dbname).ExcuteSql(sql,parm); 
 }
}