public async Task <IActionResult> OutputReport(HistoryReportParam param)
{
var data = await _serviceHistoryReport.HistoryReportOutputExcel(param);
var dataResult = new List <HistoryOutputReport>();
dataResult = _mapper.Map <List <HistoryReportOutputDB>, List <HistoryOutputReport> >(data);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\HistoryReportOutput.xlsx");
dataResult.ForEach(item => {
item.StatusPercent = item.Status.ToString() + "%";
});
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", dataResult);
designer.Process();
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "Excel" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public async Task <List <HistoryReportOutputDB> > HistoryReportOutputExcel(HistoryReportParam param)
{
var data = new List <HistoryReportOutputDB>();
data = await(_context.HistoryReportOutputDB.FromSqlRaw("EXEC [dbo].[NSP_TRANSFER_HISTORY_O] @Transac_Type, @Date_S, @Date_E, @PO, @T2_Supplier_ID ",
new SqlParameter("Transac_Type", "O"),
new SqlParameter("Date_S", param.DateStart != "" ? param.DateStart : (object)DBNull.Value),
new SqlParameter("Date_E", param.DateEnd != "" ? param.DateEnd : (object)DBNull.Value),
new SqlParameter("PO", param.PO != "" ? param.PO : (object)DBNull.Value),
new SqlParameter("T2_Supplier_ID", param.T2_Supplier_ID != "" ? param.T2_Supplier_ID.Trim() :(object)DBNull.Value)
)).ToListAsync();
return(data);
}