How to import MVC into Excel

Time:2020-10-7

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, need to refer to your project, I use the mvc4 + three-tier structure of the project

The tool I use is (vs2012 + sql2014)

When the preparations are finished, we begin to get into the topic

1. Front page, code:

<div> 
        @using (Html.BeginForm("importexcel", "foot", FormMethod.Post, new { enctype = "multipart/form-data" }))
          {
            < samp > please select the excel file to upload: < / samp >
            <span></span>
            < strong > select [email protected]*
            @ Html.AntiForgeryToken () // prevent cross Site Request Forgery( CSRF:Cross-site request  Forgery attack
           *@< input type = "submit" value = "submit" / > 
          }
      </div>

2. Next is the controller

public class footController : Controller
  {
    //
    // GET: /foot/
    private static readonly String Folder = "/files";
    public ActionResult excel()
    {
      return View();
    }

    ///Import excel document
    public ActionResult importexcel()
    {
      //1. Receive the data from the client
      HttpPostedFileBase file = Request.Files["file"];
      if (file == null || file.ContentLength <= 0)
      {
        Return JSON ("please select the excel file to upload)", 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);
      //Get a streamfile object, which points to an upload file, ready to read the contents of the modified file
      Stream streamfile = file.InputStream;
      DataTable dt = new DataTable();
      string FinName = Path.GetExtension(file.FileName);
      if (FinName != ".xls" && FinName != ".xlsx")
      {
        Return can only upload EXCEL documents, JsonRequestBehavior.AllowGet );
      }
      else
      {
        try
        {
          if (FinName == ".xls")
          {
            //Create a webbook corresponding to an excel file (used for XLS file import class)
            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 ("import failed! "+ 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
  {
    ///<summary>
    ///Two different versions of Excel
    ///Extension *. 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] [page]. Tostring();
          string fcategary =  dt.Rows [i] [classification]. Tostring();
          string fTitle =  dt.Rows [i] [title]. Tostring();
          string fUrl =  dt.Rows [i] [link]. Tostring();
          FooterDAL.Addfoot(categary, fcategary, fTitle, fUrl);
        }
      }
      return dt;
    }

    #Two different versions of operation excel of region
    ///<summary>
    ///Extension *. Xls
    /// </summary>
    public static DataTable ImExport(DataTable dt, HSSFWorkbook hssfworkbook)
    {
      //Add a sheet in the webbook, corresponding to the sheet in Excel file, take out the first sheet, and the index is 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] [page]. Tostring();
          string fcategary =  dt.Rows [i] [classification]. Tostring();
          string fTitle =  dt.Rows [i] [title]. Tostring();
          string fUrl =  dt.Rows [i] [link]. Tostring();
          FooterDAL.Addfoot(categary, fcategary, fTitle, fUrl);
        }

      }
      return dt;
    }
    #endregion
  }
}
public static partial class FooterDAL
  {
    /// <summary>
    ///Add
    /// </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 adds datatable, the data in Excel, to SQL database

The above is the whole content of this article, I hope to help you in your study, and I hope you can support developeppaer more.