public HttpResponseMessage DownloadExcel() { //Database DB = new DatabaseProviderFactory().Create("Rock_2018_ConnectionString"); Models.WebApi WebApi = new Models.WebApi(); Models.NPOI NPOI = new Models.NPOI(); Models.WebApi.GetModel.Persons_req req = new Models.WebApi.GetModel.Persons_req(); req.limit = 20; req.page = 1; DataSet ds = new DataSet(); ds = WebApi.dsPersons(DB, req); DataTable dtPersons = ds.Tables[0]; using (var stream = new MemoryStream()) { HSSFWorkbook workbook = NPOI.NPOIGridviewToExcel(dtPersons); workbook.Write(stream); var result = new HttpResponseMessage(HttpStatusCode.OK) { Content = new ByteArrayContent(stream.ToArray()) }; result.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment") { FileName = "Orders" + ".xls" }; result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream"); return(result); } // processing the stream. }
//[EnableCors(origins: "*", headers: "*", methods: "GET,OPTIONS")] public IHttpActionResult Persons(int limit, int page, string words) { Models.WebApi.GetModel.Persons_req req = new Models.WebApi.GetModel.Persons_req(); Models.WebApi.GetModel.Persons_res res = new Models.WebApi.GetModel.Persons_res(); req.limit = limit; req.page = page; req.words = words; try { DataSet ds = new DataSet(); ds = WebApi.dsPersons(DB, req); DataTable dtPersons = ds.Tables[0]; DataTable dtOrders = ds.Tables[1]; DataTable dtTotalCount = ds.Tables[2]; //新增Columns到dtPersons dtPersons.Columns.Add("Tickets", typeof(List <Models.WebApi.GetModel.Table_res_Tickets>)); //dtPersons.Columns.Add("Tickets_TotalAmount", typeof(Int32)); //dtPersons.Columns.Add("Tickets_TotalCount", typeof(Int32)); int Tickets_TotalAmount; int Tickets_TotalCount; //輪循dtPersons每一個資料 foreach (DataRow drPersons in dtPersons.Rows) { Tickets_TotalAmount = 0; Tickets_TotalCount = 0; //建立一個LIST List <Models.WebApi.GetModel.Table_res_Tickets> Tickets = new List <Models.WebApi.GetModel.Table_res_Tickets>(); //輪循dtOrders每一個資料 foreach (DataRow drOrders in dtOrders.Rows) { //如果guid相同 新增進LIST if (drOrders["person_guid"].ToString() == drPersons["guid"].ToString()) { Models.WebApi.GetModel.Table_res_Tickets ticket = new Models.WebApi.GetModel.Table_res_Tickets(); ticket.type = Convert.ToInt32(drOrders["ticket_type"]); ticket.count = Convert.ToInt32(drOrders["ticket_count"]); ticket.amount = Convert.ToInt32(drOrders["ticket_amount"]); Tickets_TotalAmount += ticket.amount; Tickets_TotalCount += ticket.count; Tickets.Add(ticket); } } //ADD LIST TO DATAROW // 增加<List>到 Row drPersons["Tickets"] = Tickets; //drPersons["Tickets_TotalAmount"] = Tickets_TotalAmount; //drPersons["Tickets_TotalCount"] = Tickets_TotalCount; } //200 res.code = 20000; res.data = dtPersons; res.total = Convert.ToInt32(dtTotalCount.Rows[0]["TotalCount"].ToString()); return(Ok(res)); } catch (Exception e) { Console.WriteLine(e.Message); //500 伺服器錯誤 return(InternalServerError()); } }