这篇文章主要为大家详细介绍了手把手教你mvc导入excel的方法，具有一定的参考价值，对mvc感兴趣的小伙伴们可以参考一下
准备工作:
1.在项目中添加对npoi的引用，npoi下载地址：http://npoi.codeplex.com/releases/view/38113
2.npoi学习
npoi下载，里面有五个dll，需要引用到你的项目，我这边用的mvc4+三层的方式架构的项目
我用的工具是(vs2012+sql2014)
准备工作做完，我们开始进入主题
1.前端页面，代码:
<p 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"/>
}
</p>
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
}
}
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);
}
}
//footerdal将datatable,就是excel里面的数据添加到sql数据库
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。
相关推荐：
mvc框架是什么 这里为你解答_实用技巧
spring mvc访问静态文件的详细介绍
关于tp5.0 mvc入门视频的资源分享
以上就是手把手教你mvc导入excel_实用技巧的详细内容。