Net mvc use NPOI to import and export excel and mvcnpoi

Net mvc use NPOI to import and export excel and mvcnpoi

1. Export Excel:
REFERENCE The NPOI package first (Action must use FileResult)

/// <Summary> /// batch export the list to be exported /// </summary> /// <returns> </returns> public FileResult ExportStu2 () {// obtain 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 (); // create an Excel file object NPOI. HSSF. userModel. HSSFWorkbook book = new NPOI. HSSF. userModel. HSSFWorkbook (); // Add a sheet NPOI. SS. userModel. ISheet sheet1 = book. createSheet ("Sheet1"); // it seems that the font color and background of various styles can be set here, but it is not very convenient, here, we do not set // to add NPOI to the header of the first row for sheet1. SS. userModel. IRow row1 = sheet1.CreateRow (0); row1.CreateCell (0 ). setCellValue ("Number"); row1.CreateCell (1 ). setCellValue ("name ");//.... N rows // write data to each row of sheet1 gradually (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} // write to the 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 );}

View Code

The front-end can be directly written to achieve:

@ Html. ActionLink ("click to export Excel", "ExportStu2 ")

Here is an article dedicated to setting styles:
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, let's talk about the front-end. For mvc uploads, 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 name is "Sheet1", and the "computer number" is in cell A1 .) </Text> <input name = "file" type = "file" id = "file"/> <input type = "submit" name = "Upload" value = "batch Import first Batch of rosters "/>}</td>

View Code

Background implementation: only passing the path to get the DataTable:

/// <Summary> /// Excel import /// </summary> /// <param name = "filePath"> </param> /// <returns> </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. ISh Eet sheet = hssfworkbook. getSheetAt (0) {DataTable table = new DataTable (); IRow headerRow = sheet. getRow (0); // The first row title 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 ;}}

View Code

Add a class:

/// <Summary> /// obtain the column value 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 will 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 ();}}}

View Code

After obtaining the DataTable, you can operate it as needed.