Teach you step by step how to import mvc into excel_Practical tips

This article mainly introduces you in detail how to import mvc into excel. It has certain reference value. Friends who are interested in mvc can refer to it

Preparation :

1. Add a reference to NPOI in the project, NPOI download address: http://npoi.codeplex.com/releases/view/38113

2.NPOI learning

NPOI download, there are five dlls in it, which need to be referenced to your project. The project I use here is mvc4+ three-layer architecture

Tools I use Yes (vs2012+sql2014)

After the preparation work is completed, we start to enter the topic

1. Front-end page, code:

##

 
        @using (Html.BeginForm("importexcel", "foot", FormMethod.Post, new { enctype = "multipart/form-data" }))
          {
            请选择要上传的Excel文件：
            
            选择文件@*
            @Html.AntiForgeryToken() //防止跨站请求伪造(CSRF:Cross-site request forgery)攻击
           *@ 
          }
      

2. Next is the

controller

##

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. Business logic layer [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
  {
    ///

    /// #region 两种不同版本的操作excel
    /// 扩展名*.xlsx
    /// 
    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
    ///

    /// 扩展名*.xls
    /// 
    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
  {
    /// 

    /// 添加
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    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(CommonTool.dbname).ExcuteSql(sql,parm);  
    }
}

//FooterDAL adds the datatable, which is the data in excel, to the sql database

The above is the entire content of this article, I hope it will help everyone learn It is helpful, and I hope everyone will support the PHP Chinese website.

Related recommendations:

What is the MVC framework? Here are the answers for you_Practical tips

Detailed introduction to Spring MVC access to static files

Resource sharing about TP5.0 MVC introductory video

The above is the detailed content of Teach you step by step how to import mvc into excel_Practical tips. For more information, please follow other related articles on the PHP Chinese website!