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());
}
}
