public async Task <IActionResult> GetReport(QueryReportModel model, string Type) { ResultModel result = new ResultModel(); model.Type = Type; string address = _config["api"] + "/FinancialReport?Type=" + Type; if (!String.IsNullOrEmpty(model.Name)) { address = address + "&Name=" + model.Name.Trim(); } if (model.Year != 0) { address = address + "&Year=" + model.Year; } result = await _callApi.CallAPI(null, new Uri(address), "GET"); model.report = new Report(); if (result.IsSuccess == true && result.Data != null) { model.report = JsonConvert.DeserializeObject <Report>(result.Data.ToString()); } if (model.Year == 0) { TempData["Year"] = DateTime.Now.Year; } else { TempData["Year"] = model.Year; } if (TempData["IsSuccess"] == null) { TempData["IsSuccess"] = result.IsSuccess; TempData["msg"] = result.Message; } return(View(model)); }
public async Task <FileStreamResult> ExportExcel(QueryReportModel model) { ResultModel result = new ResultModel(); string address = _config["api"] + "/FinancialReport?Type=" + model.Type; if (!String.IsNullOrEmpty(model.Name)) { address = address + "&Name=" + model.Name.Trim(); } if (model.Year != 0) { address = address + "&Year=" + model.Year; } result = await _callApi.CallAPI(null, new Uri(address), "GET"); if (result.IsSuccess == true && result.Data != null) { string [] monthTw = new string[] { "一", "二", "三", "四", "五", "六", "七", "八", "九", "十", "十一", "十二" }; model.report = JsonConvert.DeserializeObject <Report>(result.Data.ToString()); var memoryStream = new MemoryStream(); using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook)) { var workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); var sheets = workbookPart.Workbook.AppendChild(new Sheets()); sheets.Append(new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet 1" }); var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); if (model.Year == 0) { model.Year = DateTime.Now.Year; } var row = new Row(); row.Append( new Cell() { CellValue = new CellValue(model.Year.ToString() + "年收入年報表"), DataType = CellValues.String }); sheetData.AppendChild(row); row = new Row(); row.Append( new Cell() { CellValue = new CellValue("財務項目"), DataType = CellValues.String } ); for (int i = 0; i < 12; i++) { row.Append( new Cell() { CellValue = new CellValue(monthTw[i] + "月"), DataType = CellValues.String }); } sheetData.AppendChild(row); foreach (var i in model.report.ReportItems) { row = new Row(); row.Append( new Cell() { CellValue = new CellValue(i.Name), DataType = CellValues.String } ); for (int j = 0; j < 12; j++) { row.Append( new Cell() { CellValue = new CellValue(Convert.ToInt32(i.Subtotal[j]).ToString()), DataType = CellValues.String }); } sheetData.AppendChild(row); } row = new Row(); row.Append( new Cell() { CellValue = new CellValue("總計"), DataType = CellValues.String } ); for (int j = 0; j < 12; j++) { row.Append( new Cell() { CellValue = new CellValue(Convert.ToInt32(model.report.MonthSubtotal[j]).ToString()), DataType = CellValues.String }); } sheetData.AppendChild(row); } memoryStream.Seek(0, SeekOrigin.Begin); return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")); } return(null); //result.Data = q.RitualMoneyRecords; //return result; }