public Task DownloadExcelEform(EformDownloadExcelModel excelModel) { const int bufferSize = 4086; var buffer = new byte[bufferSize]; Response.OnStarting(async() => { if (!await _permissionsService.CheckEform(excelModel.TemplateId, AuthConsts.EformClaims.EformsClaims.ExportEformExcel)) { Forbid(); } var result = await _eformExcelExportService.EformExport(excelModel); if (result.Model == null) { var message = _localizationService.GetString("NoDataInSelectedPeriod"); Response.ContentLength = message.Length; Response.ContentType = "text/plain"; Response.StatusCode = 400; var bytes = Encoding.UTF8.GetBytes(message); await Response.Body.WriteAsync(bytes, 0, message.Length); await Response.Body.FlushAsync(); } else { await using var fileStream = result.Model; int bytesRead; Response.ContentLength = fileStream.Length; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; while ((bytesRead = await fileStream.ReadAsync(buffer, 0, buffer.Length)) > 0 && !HttpContext.RequestAborted.IsCancellationRequested) { await Response.Body.WriteAsync(buffer, 0, bytesRead); await Response.Body.FlushAsync(); } } }); return(Task.CompletedTask); }
public async Task <OperationDataResult <Stream> > EformExport(EformDownloadExcelModel excelModel) { try { var core = await _coreHelper.GetCore(); var cultureInfo = new CultureInfo("de-DE"); var language = await _userService.GetCurrentUserLanguage(); var timeZoneInfo = await _userService.GetCurrentUserTimeZoneInfo(); var customPathForUploadedData = $"{await core.GetSdkSetting(Settings.httpServerAddress)}/" + "api/template-files/get-image/"; var ts = new TimeSpan(0, 0, 0); var startDate = excelModel.DateFrom.Date + ts; ts = new TimeSpan(23, 59, 59); var endDate = excelModel.DateTo.Date + ts; var dataSet = await core.GenerateDataSetFromCases( excelModel.TemplateId, startDate, endDate, customPathForUploadedData, ",", "", false, cultureInfo, timeZoneInfo, language, true, false).ConfigureAwait(false); if (dataSet == null) { return(new OperationDataResult <Stream>( false, _localizationService.GetString("DataNotFound"))); } //var sourceFileName = // Path.Combine(await core.GetSdkSetting(Settings.fileLocationJasper), // Path.Combine("templates", $"{excelModel.TemplateId}", "compact", $"{excelModel.TemplateId}.xlsx")); Directory.CreateDirectory(Path.Combine(Path.GetTempPath(), "results")); var timeStamp = $"{DateTime.UtcNow:yyyyMMdd}_{DateTime.UtcNow:hhmmss}"; var resultDocument = Path.Combine(Path.GetTempPath(), "results", $"{timeStamp}_{excelModel.TemplateId}.xlsx"); Directory.CreateDirectory(Path.Combine(Path.GetTempPath(), "results")); if (core.GetSdkSetting(Settings.s3Enabled).Result.ToLower() == "true") { try { Log.LogEvent($"Trying to open {excelModel.TemplateId}.xlsx"); var objectResponse = await core.GetFileFromS3Storage($"{excelModel.TemplateId}.xlsx"); Directory.CreateDirectory(Path.Combine(Path.GetTempPath(), "results")); await using var fileStream = File.Create(resultDocument); await objectResponse.ResponseStream.CopyToAsync(fileStream); await fileStream.FlushAsync(); await fileStream.DisposeAsync(); fileStream.Close(); } catch (Exception exception) { try { Log.LogException($"EformExcelExportService.EformExport: Got exeption {exception.Message}"); var objectResponse = await core.GetFileFromS3Storage($"{excelModel.TemplateId}_xlsx_compact.zip"); var zipFileName = Path.Combine(Path.GetTempPath(), $"{excelModel.TemplateId}.zip"); await using var fileStream = File.Create(zipFileName); await objectResponse.ResponseStream.CopyToAsync(fileStream); fileStream.Close(); var fastZip = new FastZip(); // Will always overwrite if target filenames already exist var extractPath = Path.Combine(Path.GetTempPath(), "results"); Directory.CreateDirectory(extractPath); fastZip.ExtractZip(zipFileName, extractPath, ""); var extractedFile = Path.Combine(extractPath, "compact", $"{excelModel.TemplateId}.xlsx"); await core.PutFileToStorageSystem(extractedFile, $"{excelModel.TemplateId}.xlsx"); File.Move(extractedFile, resultDocument); } catch (Exception e) { Console.WriteLine(e.Message); throw; } } } //string resultFile = Path.Combine(Path.GetTempPath(), $"{excelModel.TemplateId}.xlsx"); if (!File.Exists(resultDocument)) { return(new OperationDataResult <Stream>( false, _localizationService.GetString("ExcelTemplateNotFoundInStorage"))); } var wb = new XLWorkbook(resultDocument); try { var workSheetToDelete = wb.Worksheets.Worksheet($"Data_{excelModel.TemplateId}"); workSheetToDelete.Clear(); //workSheetToDelete.Delete(); } catch { // ignored } //var worksheet = wb.Worksheets.Add($"Data_{excelModel.TemplateId}"); var worksheet = wb.Worksheets.SingleOrDefault(x => x.Name == $"Data_{excelModel.TemplateId}"); if (worksheet == null) { worksheet = wb.Worksheets.Add($"Data_{excelModel.TemplateId}"); } for (var y = 0; y < dataSet.Count; y++) { var dataX = dataSet[y]; for (var x = 0; x < dataX.Count; x++) { var dataY = dataX[x]; if (x != 0) { switch (y) { case 0: case 10: worksheet.Cell(x + 1, y + 1).Value = int.Parse(dataY); break; case 1: worksheet.Cell(x + 1, y + 1).Value = dataY; //worksheet.Cell(x + 1, y + 1).Style = "yyyy-MM-dd"; break; case 2: worksheet.Cell(x + 1, y + 1).Value = dataY; //worksheet.Cell(x + 1, y + 1).Style.Numberformat.Format = "HH:mm:ss"; break; case 6: worksheet.Cell(x + 1, y + 1).Value = dataY; //worksheet.Cell(x + 1, y + 1).Style.Numberformat.Format = "yyyy"; break; case 7: worksheet.Cell(x + 1, y + 1).Value = dataY; //worksheet.Cell(x + 1, y + 1).Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss"; break; default: if (dataY == "checked") { worksheet.Cell(x + 1, y + 1).Value = 1; } else { if (float.TryParse(dataY, out var i)) { worksheet.Cell(x + 1, y + 1).Value = dataY.Replace(",", "."); worksheet.Cell(x + 1, y + 1).DataType = XLDataType.Number;; } else { worksheet.Cell(x + 1, y + 1).Value = dataY; } } break; } } if (x == 0) { worksheet.Cell(x + 1, y + 1).Value = dataY; worksheet.Cell(x + 1, y + 1).Style.Font.Bold = true; } } } wb.SaveAs(resultDocument); Stream result = File.Open(resultDocument, FileMode.Open); return(new OperationDataResult <Stream>(true, result)); } catch (Exception e) { _logger.LogError(e, e.Message); return(new OperationDataResult <Stream>( false, _localizationService.GetString("ErrorWhileExportingExcelFile"))); } }