手把手教你mvc导入excel

准备工作:

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数据库

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。