. NET MVC uses Npoi import to export Excel

Source: Internet
Author: User
Tags rowcount

as a result of recent projects, so record:
1. Export Excel :
first refer to the Npoi package, download from here "download
(Action must be used Fileresult)
<summary>////Bulk Export list to export///</summary>//<returns></returns> P Ublic Fileresult ExportStu2 () {//Get list data var checklist = (from OC in DB. Organizecustoms join O in db.                            Organizes.defaultifempty () on oc.custom_id equals o.id where oc.organize_id = = 1                                Select New {customer_id = O.id, Customer_name = O.name}).            ToList (); The object that created the Excel file Npoi. HSSF. Usermodel.hssfworkbook book = new Npoi. HSSF.            Usermodel.hssfworkbook (); Add a sheet Npoi. Ss. Usermodel.isheet Sheet1 = Book.            Createsheet ("Sheet1"); Looks like here can set various styles font color background and so on, but is not very convenient, here does not set the//to Sheet1 add the first row header title Npoi. Ss. Usermodel.irow Row1 = Sheet1.            CreateRow (0); Row1. Createcell (0). Setcellvalue ("Number "); Row1. Createcell (1).            Setcellvalue ("name"); //.... n Rows//writes data progressively to Sheet1 individual lines for (int i = 0; i < Checklist.count; i++) {Npoi. Ss. Usermodel.irow rowtemp = Sheet1.                CreateRow (i + 1); Rowtemp. Createcell (0). Setcellvalue (checklist[i].customer_id.                ToString ()); Rowtemp. Createcell (1). Setcellvalue (Checklist[i].customer_name.                ToString ()); //....            n Rows}//written to client System.IO.MemoryStream ms = new System.IO.MemoryStream (); Book.            Write (MS); Ms.            Seek (0, Seekorigin.begin);            DateTime dt = DateTime.Now; String dateTime = dt.            ToString ("Yymmddhhmmssfff");            String fileName = "Query Result" + DateTime + ". xls";        Return File (MS, "application/vnd.ms-excel", fileName); }
The front desk can be written directly to achieve:
@Html. ActionLink ("Click to export Excel", "EXPORTSTU2")
Here is an article dedicated to styling:
2. Import Excel:
first say some front desk, MVC upload Note must add new {enctype = "multipart/form-data"}
<td>    @using (@Html. BeginForm ("Importstu", "Proschool", FormMethod.Post, new {enctype = "multipart/form-data })    {        <text> Select Upload file: (the worksheet is named "Sheet1" and "computer number" is in cell A1.) ) </text>        <input name= "file" type= "file" id= "file"/>        <input type= "Submit" Name= "Upload" value= "Batch import of the first batch of rosters"/>    }</td>
Background implementation: Only the path is derived from the DataTable:
<summary>///Excel Import///</summary>//<param name= "FilePath" ></param>//<r      Eturns></returns> Public DataTable importexcelfile (string filePath) {Hssfworkbook hssfworkbook;        #region//initialization information try {using (FileStream file = new FileStream (FilePath, FileMode.Open, FileAccess.Read))        {Hssfworkbook = new Hssfworkbook (file);      }} catch (Exception e) {throw e; } #endregion using (Npoi. Ss. Usermodel.isheet sheet = Hssfworkbook.        Getsheetat (0)) {DataTable table = new DataTable (); IRow HeaderRow = sheet.  GetRow (0);//First behavior header line int cellcount = Headerrow.lastcellnum;//lastcellnum = Physicalnumberofcells int RowCount = Sheet.        Lastrownum;//lastrownum = PhysicalNumberOfRows-1//handling header. for (int i = headerrow.firstcellnum; i < Cellcount; i++) {DataColumn column = new DataColumn (headerrow . GEtcell (i).          Stringcellvalue); Table.        Columns.Add (column); } for (int i = (sheet. Firstrownum + 1); I <= RowCount; i++) {IRow row = sheet.          GetRow (i); DataRow datarow = table.          NewRow (); if (row! = null) {for (int j = row). Firstcellnum; J < Cellcount; J + +) {if (row. Getcell (j) = null) datarow[j] = Getcellvalue (row.            Getcell (j)); }} table.        Rows.Add (DataRow);      } return table; }    }
add a class:
<summary>/////To get the value of a column based on the Excel column type//</summary>//<param name= "cell" >excel column </param>  <returns></returns> private static string Getcellvalue (Icell cell) {if (cell = = null) {return String. Empty; } switch (cell. Celltype) {case CellType.BLANK:return string.        Empty; Case CellType.BOOLEAN:return Cell.        Booleancellvalue.tostring (); Case CellType.ERROR:return Cell.        Errorcellvalue.tostring (); Case CellType.NUMERIC:case CellType.Unknown:default:return Cell. ToString ();//this is a trick to get the correct value of the cell.          Numericcellvalue would return a numeric value no matter the cell value is a date or a number case celltype.string: return cell.        Stringcellvalue; Case CellType.FORMULA:try {hssfformulaevaluator e = new Hssfformulaevaluator (cell.            Sheet.workbook); E.evaluateincell (CEll); return cell.          ToString (); } catch {return cell.          Numericcellvalue.tostring (); }      }    }
Once you get the DataTable, you can manipulate it as you want.

. NET MVC uses Npoi import to export Excel

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.