asp.net mvc 使用NPOI插件導出excel

 1   　　　/// <summary>
 2         /// 交易帳單 導出交易列表
 3         /// </summary>
 4         /// <returns></returns>
 5         public FileResult TranToExcel()
 6         {
 7             Dictionary<string, object> ht = new Dictionary<string, object>();
 8 　　　　　　　//數據源
 9             List<UserTransactionViewModel> list = UserTransactionBLL.GetTransactionListExcelToAdmin(ht,langId);
10             //添加一個sheet
11             NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
12             //給sheet1添加第一行的頭部標題
13             NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
14             row1.CreateCell(0).SetCellValue(UserTransactionLangEnum.TransactionNumber.ToLang(langId));
15             row1.CreateCell(1).SetCellValue(UserTransactionLangEnum.TransactionTitle.ToLang(langId));
16             row1.CreateCell(2).SetCellValue(UserTransactionLangEnum.TransactionUser.ToLang(langId));
17             row1.CreateCell(3).SetCellValue(UserTransactionLangEnum.TransactionType.ToLang(langId));
18             row1.CreateCell(4).SetCellValue(UserTransactionLangEnum.TransactionState.ToLang(langId));
19             row1.CreateCell(5).SetCellValue(UserTransactionLangEnum.PayType.ToLang(langId));
20             row1.CreateCell(6).SetCellValue(UserTransactionLangEnum.TradingCurrency.ToLang(langId));
21             row1.CreateCell(7).SetCellValue(UserTransactionLangEnum.TransactionAmount.ToLang(langId));
22             row1.CreateCell(8).SetCellValue(UserTransactionLangEnum.ExchangeHour.ToLang(langId));
23             row1.CreateCell(9).SetCellValue(UserTransactionLangEnum.Remarks.ToLang(langId));    
24             //將數據逐步z寫入sheet1各個行
25             for (int i = 0; i < list.Count; i++)
26             {
27                 NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
28                 rowtemp.CreateCell(0).SetCellValue(list[i].Transaction.TranNo);
29                 rowtemp.CreateCell(1).SetCellValue(list[i].Transaction.Trantitle);
30                 rowtemp.CreateCell(2).SetCellValue(list[i].UserName);
31                 rowtemp.CreateCell(3).SetCellValue(Enum.GetName(typeof(TransactionTypeDbEnum), list[i].Transaction.TranTypeId));
32                 rowtemp.CreateCell(4).SetCellValue(Enum.GetName(typeof(StatusDbEnum), list[i].Transaction.StatusId));
33                 rowtemp.CreateCell(5).SetCellValue(Enum.GetName(typeof(PayTypeDbEnum), list[i].Transaction.PayTypeId));
34                 rowtemp.CreateCell(6).SetCellValue(list[i].CurrencyName);
35                 rowtemp.CreateCell(7).SetCellValue(list[i].Transaction.TranAmount.ToString());
36                 rowtemp.CreateCell(8).SetCellValue(list[i].Transaction.Addtime.ToString("yyyy-MM-dd HH:mm:ss"));
37                 rowtemp.CreateCell(9).SetCellValue(list[i].Transaction.Remarks);
38             }
39             // 寫入到客戶端 
40             System.IO.MemoryStream ms = new System.IO.MemoryStream();
41             book.Write(ms);
42             ms.Seek(0, SeekOrigin.Begin);
43             string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + UserTransactionLangEnum.TransactionHistory.ToLang(langId)+ ".xls";
44             return File(ms, "application/vnd.ms-excel", fileName);
45 
46         }