手把手教你mvc导入excel
程序员文章站
2022-11-19 22:15:11
准备工作:
1.在项目中添加对npoi的引用,npoi下载地址:
2.npoi学习
npoi下载,里面有五个dll,需要引用到你的项目,我这边用的mvc4+三层的方式...
准备工作:
1.在项目中添加对npoi的引用,npoi下载地址:
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 } }
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数据库
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
上一篇: php语言注释,单行注释和多行注释