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