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