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:
Http://www.cnblogs.com/puzi0315/p/3265958.html
http://blog.csdn.net/xhccom/article/details/7687264
http://blog.csdn.net/bestreally/article/details/23257851
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