Teach you how to import mvc into excel, and import mvc into excel

Source: Internet
Author: User

Teach you how to import mvc into excel, and import mvc into excel

Preparations:

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

2. NPOI Learning

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>

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 );}}}}

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 }}
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: add the data in excel to the SQL database.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.