net mvc 利用NPOI導入導出excel

1、導出Excel ：
首先引用NPOI包（Action一定要用FileResult）

    /// <summary>  
            /// 批量導出需要導出的列表  
            /// </summary>  
            /// <returns></returns>  
            public FileResult ExportStu2()  
            {  
                //獲取list數據  
                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();  
      
                //創建Excel文件的對象  
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();  
                //添加一個sheet  
                NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");  
      
                //貌似這裡可以設置各種樣式字體顏色背景等，但是不是很方便，這裡就不設置了  
      
                //給sheet1添加第一行的頭部標題  
                NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);  
                row1.CreateCell(0).SetCellValue("編號");  
                row1.CreateCell(1).SetCellValue("姓名");  
                //....N行  
      
                //將數據逐步寫入sheet1各個行  
                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行  
                }  
                // 寫入到客戶端   
                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 = "查詢結果" + dateTime + ".xls";  
                return File(ms, "application/vnd.ms-excel", fileName);  
            }  

前臺直接寫就可實現：

@Html.ActionLink("點擊導出Excel", "ExportStu2")  

這裡有一篇專門介紹設置樣式的文章：
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、導入Excel：

首先說一些前臺吧，mvc上傳註意必須加 new { enctype = "multipart/form-data" }

    <td>  
        @using (@Html.BeginForm("ImportStu", "ProSchool", FormMethod.Post, new { enctype = "multipart/form-data" }))  
        {  
            <text>選擇上傳文件：（工作表名為“Sheet1”，“電腦號”在A1單元格。）</text>  
            <input name="file" type="file" id="file" />  
            <input type="submit" name="Upload" value="批量導入第一批名冊" />  
        }  
    </td>  

後臺實現：只傳路徑得出DataTable：

    /// <summary>  
        /// Excel導入  
        /// </summary>  
        /// <param name="filePath"></param>  
        /// <returns></returns>  
        public DataTable ImportExcelFile(string filePath)  
        {  
          HSSFWorkbook hssfworkbook;  
          #region//初始化信息  
          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);//第一行為標題行  
            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;  
          }  
        }  

補充一個類：

    /// <summary>  
        /// 根據Excel列類型獲取列的值  
        /// </summary>  
        /// <param name="cell">Excel列</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();  
              }  
          }  
        }  

得到DataTable後，就想怎麼操作就怎麼操作了。