Dictionary 迴圈
Linq lambda 讀資料
下載彈出頁面
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