How do from http response which contain ExcelFile as string convert back instance ExcelFile(library GemBox)? C#

Question

I want to write integration test for my controller which return HttpResponseMessage which have "Content" as string and his content-type "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

How do me convert this string back instance ExcelFile - libarary gembox?

this controller for example:

        /// <summary>
        /// Save Data
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost]
        [Route(Routes.Download)]
        [ValidateToken]
        [SwaggerResponse(HttpStatusCode.OK, "Xlsx-file with result", typeof(HttpStatusCode))]
        [SwaggerResponse(HttpStatusCode.Unauthorized, "invalid token", typeof(HttpStatusCode))]
        [SwaggerResponse(HttpStatusCode.BadRequest, "Message with description error of validate", typeof(HttpStatusCode))]
        public HttpResponseMessage PostUnloadExcelFile([FromBody]DownloadRequest request)
        {
            var logData = _logMessageBuilder.GetLogMessageDownload(request.Request);

            LogRequest(logData);

            var response = Request.CreateResponse(HttpStatusCode.OK);

            _downloadService.GetExcelFile(request.Data).Save(response, $"{Routes.Download}.xlsx");

            return response;
        }

this example DownloadService

public class DownloadService : BaseDownloadService, IDownloadService
    {
        private const string Sheet = "Work sheet";      
        public ExcelFile GetExcelFile(IList<Event> data)
        {
            var xlsFile = new ExcelFile();
            var workSheet = xlsFile.Worksheets.Add(Sheet);

            SetHeaderWorksSheet(workSheet);

            for (var i = 0; i < data.Count; i++)
            {
                SetCellWorksSheet(data[i], workSheet, i);
            }

            for (var j = 0; j < data.Count + 1; j++)
            {
                SetCellStyleWorksSheet(workSheet, j);
            }
            return xlsFile;
        }
    }

    public class BaseDownloadService
    {

        protected void SetHeaderWorksSheet(ExcelWorksheet workSheet)
        {
            workSheet.Cells[0, ExcelFileIndexes.Item].Value = ConstantsExcelHeader.Item;
            workSheet.Cells[0, ExcelFileIndexes.FirstDate].Value = ConstantsExcelHeader.FirstDate;
            workSheet.Cells[0, ExcelFileIndexes.LastDate].Value = ConstantsExcelHeader.LastDate;

        }

        protected void SetCellWorksSheet(Triplet rowData, ExcelWorksheet workSheet, int i)
        {
            workSheet.Cells[i + 1, ExcelFileIndexes.Item].Value = (i + 1).ToString();
            workSheet.Cells[0, ExcelFileIndexes.FirstDate].Value = rowData.FirstDate;
            workSheet.Cells[0, ExcelFileIndexes.LastDate].Value = rowData.LastDate;

        }

        protected void SetCellStyleWorksSheet(ExcelWorksheet workSheet, int i)
        {
            workSheet.Columns[i].AutoFit();
            workSheet.Cells[0, i].Style
                .FillPattern
                .SetPattern(
                    FillPatternStyle.Solid,
                    SpreadsheetColor.FromName(ColorName.LightGreen),
                    SpreadsheetColor.FromName(ColorName.LightGreen));
        }
    }

this test for example:

        [Theory]
        [MemberData(nameof(DataForTrySaveInExcelFileTesting))]
        public async Task Should_ReturnCode200TrySaveDataInFileExcel(DownloadRequest request)
        {
            // Arrange
            var token = await GetValidTokenAsync();
            // Act
            var response = await ActAsync<DownloadRequest, DataRecord>(request, token, DownloadUrl);

            var excelBytes = Encoding.UTF8.GetBytes(response.Content);
            var excelFile = new ExcelFile();      

            using (MemoryStream stream = new MemoryStream())
            {
                stream.Write(excelBytes, 0, excelBytes.Length);
                stream.Position = 0;
                excelFile = ExcelFile.Load(stream, LoadOptions.XlsxDefault);
            }

            var ws = excelFile.Worksheets.ActiveWorksheet;
            ws.Cells["B2"].Value.ShouldBeEqualTo("1234567890");
        }

This is response for front

Answer 1

jdweng Thanks you, you made me think and comed good idea.We refused to convert string in stream and we just extended model of response for post of requset and added stream for content an example is shown below.

    protected async Task<TestRequestResponse> PostAsync<TPayload>(string url, TPayload payload, string token = null)
        {
            using (var server = new HttpServer(_config))
            {
                var client = new HttpClient(server);

                var request = new HttpRequestMessage
                {
                    RequestUri = new Uri(url),
                    Method = HttpMethod.Post,
                    Content = new ObjectContent<TPayload>(payload, new JsonMediaTypeFormatter())
                };

                request.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                request.Headers.Add("token", token);

                var httpRequest = new HttpRequest(string.Empty, url, string.Empty);
                httpRequest.AddServerVariable("UserHostAddress", "192.168.1.1");
                httpRequest.AddServerVariable("UserAgent", "chrome");
                HttpContext.Current = new HttpContext(httpRequest, new HttpResponse(new StringWriter()));

                using (var response = await client.SendAsync(request))
                {
                    var streamContent = await response.Content.ReadAsStreamAsync();
                    var memoryStream = new MemoryStream();
                    streamContent.CopyTo(memoryStream);
                    memoryStream.Position = 0;
                    var content = await response.Content.ReadAsStringAsync();

                    return new TestRequestResponse
                    {
                        Content = content,
                        StatusCode = response.StatusCode,
                        Message = response.ReasonPhrase,
                        StreamContent = memoryStream
                    };
                }
            }
        }

        [Theory]
        [MemberData(nameof(DataForTrySaveInExcelFileTesting))]
        public async Task Should_ReturnCode200TrySaveDataInFileExcel(DownloadRequest request)
        {
            // Arrange
            var token = await GetValidTokenAsync();

            // Act
            var responseBilling = await ActAsync<DownloadRequest, Record>(request, token, DownloadUrl);
            var excelFile = ExcelFile.Load(response.StreamContent, LoadOptions.XlsxDefault);

            var ws = excelFile.Worksheets.ActiveWorksheet;
            ws.Cells["E2"].Value.ShouldBeEqualTo("11111");
            ws.Cells["Q3"].Value.ShouldBeEqualTo("11111");
            ws.Cells["C4"].Value.ShouldBeEqualTo(11111);
            ws.Cells["F5"].Value.ShouldBeEqualTo("11111");
            ws.Cells["O6"].Value.ShouldBeEqualTo("11111");
            ws.Rows.Count.ShouldBeEqualTo(request.Data.Count + 1);
        }

        public async Task<TestRequsetResponse> ActAsync<TRequest, TItem>(TRequest reuqset = null, string token = null, string url = null) where TRequest : DownloadRequest<Item>
        {
            return await PostAsync(url, request, token);
        }