理查德筆記

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

namespace TestWeb.Controllers
{
    public class HomeController : Controller
    {      
        public ActionResult SPA_Index(string command)
        {
            if(command.StartsWith("print"))
            {
                object vm = TempData["ViewModel"];
                FilePathResult file = null;

                string 範本檔名稱 = Server.MapPath("~/TemplateFiles/") + "Test.xlsx";
                string 暫存檔名稱 = Server.MapPath("~/Temp/") + Guid.NewGuid() + ".xlsx";

                //編輯範本前，須取消任何唯獨屬性
                System.IO.File.SetAttributes(範本檔名稱, FileAttributes.Normal);

                try
                {
                    #region prepare dict data
                    //PK搭配目標清單Class
                    Dictionary<int, 清單class> dict清單 = vm.某清單.ToDictionary(x => x.PK編號, x => x);

                    //取Join
                    var liJoin其他表 = db.其他表.Join(db.主表, a => a.其他表PK, b => b.主表PK,
                                      (a, b) => new { Name1 = a.屬性1, Name2 = b.屬性1 })
                                      .Where(x => dict清單.Keys.Contains(x.其他表PK編號));

                    //取其他表與主表pk交集的資料
                    List<其他表class> li其他表 = db.其他表.Where(x => dict清單.Keys.Contains(x.其他表PK編號)).ToList();
                    #endregion

                    #region 寫入Excel
                    #region 註解
                    //這裡範例先用Aspose，就商業用套件須付費...
                    //不然用內建MiCrosoft.Office.Interop.Excel即可
                    //但是會遇到IIS卡住Excel權限的問題，需再查解法
                    //可以先把Excel表格設計好，程式塞資料就好
                    //若在程式裡設計表單很累人
                    //MiCrosoft.Office.Interop.Excel版本：
                    //using Excel = Microsoft.Office.Interop.Excel;
                    //Excel.Application xlApp = new Excel.Application();      //開啟Excel(IIS權限問題)
                    //xlApp.Visible = true;                                   //讓Excel文件可見，不在背景執行
                    //Excel.Workbook xlWorkbook = xlApp.Workbooks.Add();      //加入活頁簿
                    //Excel.Worksheet xlWorksheet = xlWorkbook.Worksheets[0]; //若先設計好表單，可指定sheet名稱Worksheets["sheet1"]
                    //xlWorkbook.Activate();                                  //設定活頁簿焦點?
                    //xlWorksheet.Name = "Test";                              //sheet 改名
                    //xlWorkbook.SaveAs(暫存檔名稱);
                    //xlWorkbook.Close();
                    //xlApp.Quit();
                    #endregion

                    Aspose.Cells.License awlic = new Aspose.Cells.License();
                    awlic.SetLicense("Aspose.Total.lic");
                    Workbook book = new Workbook(範本檔名稱);           //開啟範本
                    Worksheet ws = book.Worlsheets[0];                  //宣告Excel sheet
                    int int_Row;                                        //宣告寫入目標列數
                    int int_Member_count;                               //宣告寫入目標class數
                    Dictionary<int, 清單class> dict清單Selected = new Dictionary<int, 清單class>(); //宣告篩選過的dict

                    //將資料填入Excel Sheet
                    ws = book.Worksheets["表單名稱"];
                    int_Row = 1;
                    int_Member_count = 1;
                    foreach(KeyValuePair<int, 清單class> item in dict清單)
                    {
                        ws.Cells[int_Row, 0].Value = int_Row;
                        ws.Cells[int_Row, 1].Value = item.Value.屬性1;
                        ws.Cells[int_Row, 2].Value = item.Value.屬性2;
                        int_Row++;

                        foreach(var item2 in liJoin其他表.Where(x => x.PK編號 == item.Key))
                        {
                            ws.Cells.InsertRow(int_Row);
                            ws.Cells.CopyRow(ws.Cells, 1, int_Row);
                            ws.Cells[int_Row, 0].Value = int_Row;
                            ws.Cells[int_Row, 1].Value = item2.Value.屬性1;
                            ws.Cells[int_Row, 2].Value = item2.Value.屬性2;
                            int_Row++;
                        }

                        //新增下一列欄位
                        if (int_Member_count == dict清單.Count) break;
                        ws.Cells.InsertRow(int_Row);
                        ws.Cells.CopyRow(ws.Cells, 1, int_Row);
                        int_Member_count++;
                    }

                    book.Save(暫存檔名稱);
                    file = File(暫存檔名稱, "application/vnd.ms-excel", "結果表.xlsx");
                    return file;                    
                    /*
                    //ActionFilterAttribute OnResultExecuted的地方要寫刪除暫存檔
                    //Public override void OnResultExecuted(ResultExecutedContext filterContext)
                    //{
                    //  var fpr = filterContext.Result as FilePathResult;
                    //  filterContext.HttpContext.Response.Flush();
                    //  if (fpr != null)
                    //  {
                    //      System.IO.File.Delete(fpr.FileName);
                    //  }
                    //}
                    */
                    #endregion
                    
                }
                catch(Exception ex)
                {
                    return View("Error", new HandleErrorInfo(ex,"ControllerName","ActionName"));
                }
            }
            return PartialView("_SPA_Index");
        }    
    }
}

補充：
IIS權限問題，要去設定DCOM Microsoft Excel Application
關鍵字：mmc x64