導出Excel的簡單方法

　　excel的操作，最常用的就是導出和導入，廢話不多說上代碼。

本例使用NPOI實現的，不喜勿噴哈。。。。

 1   /// <summary> 2         /// 導出Excel 3         /// </summary> 4         /// <param name="stime"></param> 5         /// <param name="etime"></param> 6         /// <returns></returns> 7         public ActionResult Export(FormCollection frm) 8         { 9             DataTable dts = new DataTable();10             dts = _shopMemeber.ExportMemberData(frm);11             IWorkbook workbook = new XSSFWorkbook();12             ISheet sheet = workbook.CreateSheet();13             IRow headerRow = sheet.CreateRow(0);14             foreach (DataColumn column in dts.Columns)15                 headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);16             int rowIndex = 1;17             foreach (DataRow row in dts.Rows)18             {19                 IRow dataRow = sheet.CreateRow(rowIndex);20                 foreach (DataColumn column in dts.Columns)21                 {22                     dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());23                 }24                 rowIndex++;25             }26             string filepath = Server.MapPath("/") + @"用戶列表.xlsx";27             FileStream file = new FileStream(filepath, FileMode.Create);28             workbook.Write(file);29             ExcelHelper.DownLoad(@"/用戶列表.xlsx");30             #region 不啟用31 32             #endregion33             return SuccessMsg("AdminMemberMemberIndex");34         }35 //這個是下載到桌面的方法，沒實現自選路徑36 public static void DownLoad(string FileName)37  {38              FileInfo fileInfo = new FileInfo(HttpContext.Current.Server.MapPath(FileName));39              //以字符流的形式下載文件40              FileStream fs = new FileStream(HttpContext.Current.Server.MapPath(FileName), FileMode.Open);41             byte[] bytes = new byte[(int)fs.Length];42               fs.Read(bytes, 0, bytes.Length);43             fs.Close();44             HttpContext.Current.Response.ContentType = "application/octet-stream";45                //通知瀏覽器下載文件而不是打開46             HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;  filename=" + HttpUtility.UrlEncode(fileInfo.Name, System.Text.Encoding.UTF8));47           HttpContext.Current.Response.BinaryWrite(bytes);48            HttpContext.Current.Response.Flush();49             HttpContext.Current.Response.End();50         }

上面是導出，下面我介紹下導入。

 1  /// <summary> 2         /// 導入數據 3         /// </summary> 4         /// <param name="file"></param> 5         /// <returns>true表示導入成功</returns> 6         public bool Impoart(HttpPostedFileBase file) 7         { 8             try 9             {10                 //保存excel11                 string path = HttpContext.Current.Server.MapPath("/");12                 file.SaveAs(path + file.FileName);13 14                 //讀取15 16                 FileStream sw = File.Open(path + file.FileName, FileMode.Open, Fileaccess.Read);17                 IWorkbook workbook = new XSSFWorkbook(sw);18                 ISheet sheet1 = workbook.GetSheet("Sheet1");19 20                 //最大行數21                 int rowsCount = sheet1.PhysicalNumberOfRows;22 23                 //判斷首行是否符合規范  也就是Excel中的列名24                 IRow firstRow = sheet1.GetRow(0);25                 if (26                     !(firstRow.GetCell(0).ToString() == "名稱" && firstRow.GetCell(1).ToString() == "簡稱" &&27                       firstRow.GetCell(2).ToString() == "分類" && firstRow.GetCell(3).ToString() == "參考價" &&28                       firstRow.GetCell(4).ToString() == "商品介紹"))29                 {30                     return false;31                 }32 33 34                 //跳過類型不正確的品項35                 for (int i = 1; i < rowsCount; i++)36                 {37                     IRow row = sheet1.GetRow(i);38                     Shop_PRoduct product = new Shop_Product();39 40                     string category = row.GetCell(2) != null ? row.GetCell(2).ToString() : null;41                     if (!string.IsNullOrEmpty(category))42                     {43                         var cate =44                             _unitOfWork.Shop_ProductCategoryRepository().GetAll().FirstOrDefault(t => t.Name == category);45                         if (cate != null)46                         {47                             product.ProductCategoryName = cate.Name;48                             product.Shop_ProductCategory_ID = cate.ID;49                         }50                         else51                         {52                             continue;53                         }54                     }55                     else56                     {57                         continue;58                     }59 60                     product.PName = row.GetCell(0) != null ? row.GetCell(0).ToString() : null;61                     product.PCName = row.GetCell(1) != null ? row.GetCell(1).ToString() : null;62                     if (row.GetCell(3) != null)63                     {64                         product.Price = Double.Parse(row.GetCell(3).ToString());65                     }66                     product.Description = row.GetCell(4) != null ? row.GetCell(4).ToString() : null;67 68                     _unitOfWork.Shop_ProductRepository().Insert(product);69                 }70 71                 _unitOfWork.Save();72             }73             catch74             {75                 return false;76             }77 78             return true;79         }