Mvc hands-on teaches you how to write excel for import, mvc hands-on excel for Import

Mvc hands-on teaches you how to write excel for import, mvc hands-on excel for Import

Practice dogs daily new knowledge daily

Preparations:

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

2. NPOI Learning Series of tutorials recommended: http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html

The NPOI download contains five dll files that need to be referenced to your project. The mvc4 + layer-3 architecture project is used here.

The tool I used is (vs2012 + sql2014)

 

After the preparation is complete, we start to enter the topic.

1. Front-end page, code:

<Div class = "filebtn"> @ using (Html. beginForm ("importexcel", "foot", forw.hod. post, new {enctype = "multipart/form-data"}) {<samp> select the Excel file to upload: </samp> <span id = "txt_Path"> </span> <strong> select a file <input name = "file" type = "file" id = "file"/> </strong> @ * @ Html. antiForgeryToken () // prevents Cross-site request forgery (CSRF: Cross-site request forgery) attack * @ <input type = "submit" id = "ButtonUpload" value = "submit" class = "offer"/>}</div>

Excel

2. The next step is the controller.

Public class footController: Controller {// GET:/foot/private static readonly String Folder = "/files"; public ActionResult excel () {return View ();} /// import the excel document public ActionResult importexcel () {// 1. receive data from the client HttpPostedFileBase file = Request. files ["file"]; if (file = null | file. contentLength <= 0) {return Json ("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); // gets a streamfile object that points to an uploaded file, ready to read the content of the modified file Stream streamfile = file. inputStream; DataTable dt = new DataTable (); string FinName = Path. getExtension (file. fileName); if (FinName! = ". Xls" & FinName! = ". Xlsx ") {return Json (" only Excel files can be uploaded ", 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. allowGe T);} catch (Exception ex) {return Json ("Import failed! "+ Ex. Message, JsonRequestBehavior. AllowGet );}}}}

FootController. cs

3. Business logic layer [excelDAL]

Using System; using System. collections. generic; using System. linq; using System. text; using System. threading. tasks; using NPOI. SS. userModel; using NPOI. HSSF. userModel; using System. data; using NPOI. XSSF. userModel; namespace GJL. compoent {public class excelDAL {/// <summary> /// # excel file extensions * for different region versions *. xlsx // </summary> public static DataTable ImExport (DataTable dt, XSSFWorkbook hssfwork Book) {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. ICe Ll 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] ["category"]. toString (); string fTitle = dt. rows [I] ["title"]. toString (); string fUrl = dt. rows [I] ["Link"]. toString (); FooterDAL. addfoot (categary, fcategary, fTitle, fUrl) ;}}return dt ;}# region two different versions of excel // <summary> /// extension *. xls // </summary> public static DataTable ImExport (D AtaTable dt, HSSFWorkbook hssfworkbook) {// Add a sheet to the webbook, corresponding to the sheet in the Excel file, and obtain the first worksheet with an index of 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; Data Row 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] ["category"]. toString (); string fTitle = dt. rows [I] ["title"]. toString (); string fUrl = dt. rows [I] ["Link"]. toString (); FooterDAL. addfoot (categary, fcategary, fTitle, fUrl) ;}} return dt ;}# endregion }}

ExcelDAL

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

// FooterDAL: add the data in excel to the SQL database.