mvc手把手教你写excel导入

<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>

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); ???????????????} ???????} ???????????????????}}

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 ???}}

 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); ??????????}}