C# Merging multiple memory streams and returning via filestreamresult, showing only last memory stream

Question

I am trying to merge multiple pdf files into one memory stream and return it with FileStreamResult to web application.

My code is like that:

string contentType = "application/pdf";
                //---save as fileStream Excel
                if (filePath == null)
                    filePath = new DirectoryInfo(Server.MapPath("~") + ConfigurationManager.AppSettings["SomeTemplate"].ToString()).ToString();

                var sumPdfStream = new MemoryStream();
                
                string fileDownloadName = "SomeFile.pdf";

//Generating Excel file for every offer/file, getting each offer through ID from string offerIDs, comma separated values
                for (int i = 0; i < offerIDs.Split(',').Length - 1; i++)
                {
                    var pdfStream = new MemoryStream();
                    var fileStream = new MemoryStream();
                    var offerID = Convert.ToInt32(offerIDs.Split(',')[i]);
                    fileStream = GenerateExcel(offerID, ref offerNumber, filePath);
                    //string fileDownloadName = "Ponudba_" + offerNumber + "_" + DateTime.Now.ToShortDateString().Replace(".", "-").Replace("/", "-") + ".pdf";

                    Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();

                    workbook.LoadFromStream(fileStream, ExcelVersion.Version2013);
                    workbook.SaveToStream(pdfStream, Spire.Xls.FileFormat.PDF);

                    pdfStream.CopyTo(sumPdfStream);
                    pdfStream.Close();
                    pdfStream.Dispose();
                    fileStream.Close();
                    fileStream.Dispose();
                    //sumPdfStream.Write(pdfStream.GetBuffer(), (int)sumPdfStream.Position, (int)pdfStream.Length);
                }


                //sumPdfStream.Seek(0, 0);
                sumPdfStream.Position = 0;
                
                var fsr = new FileStreamResult(sumPdfStream, contentType);
                //sumPdfStream.Close();
                fsr.FileDownloadName = fileDownloadName;
                //fsr.FileStream.Seek(0, 0);
                return fsr;

Basically, I am trying to call some code to generate Excel file for each Offer (getting data from database) and then with Spire licence converting Excel to PDF and writing it to MemoryStream. Since there can be X offers/PDF files, I am trying to merge them into one with CopyTo method.

When I am debugging my code and setting breakpoint to line pdfStream.CopyTo(sumPdfStream), I can see that my sumPdfStream object, which is meant to hold all PDFs, is growing.

At the end I create FileStreamResult and copy sumPdfStream with all PDFs into it. Again, when I debug code all seems OK (FileStreamResult is bigger than just one PDF), but when I get FileStreamResult returned to the web application, only last PDF shows.

What am I doing wrong? Why is FileStreamResult showing only last one PDF, although there seems to be much more inside FileStreamResult?

Answer 1

After comment from Derek Pollard, I solved this problem by creating and merging multiple Excels into one and create a PDF from it.

All was done using filestream objects, no physical files were created, saved or deleted. After all done I send PDF to view with FileStreamResult object. Browser than opens a new window, or you can save file to downloads.

For merging Excel streams and converting file stream to PDF I have used Spire.XLS libraries with Licence version.

Here is the code:

            string contentType = "application/pdf";
            string offerNumber = "";
            //---save as fileStream Excel
            if (filePath == null)
                filePath = new DirectoryInfo(Server.MapPath("~") + ConfigurationManager.AppSettings["PonudbaTemplate"].ToString()).ToString();

            var sumPdfStream = new MemoryStream();
            string fileDownloadName = "blah.pdf";

            Spire.Xls.Workbook finalWorkbook = new Spire.Xls.Workbook();
            finalWorkbook.Worksheets.Clear();

            for (int i = 0; i < offerIDs.Split(',').Length - 1; i++)
            {
                //var pdfStream = new MemoryStream();
                var fileStream = new MemoryStream();
                var offerID = Convert.ToInt32(offerIDs.Split(',')[i]);
                fileStream = GenerateExcel(offerID, ref offerNumber, filePath);

                Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();

                workbook.LoadFromStream(fileStream, ExcelVersion.Version2013);
                for (int x = 0; x < workbook.Worksheets.Count; x++)
                {
                    //fit to one page width
                    workbook.Worksheets[x].PageSetup.FitToPagesWide = 1;
                    workbook.Worksheets[x].PageSetup.FitToPagesTall = 0;
                }

                
                workbook.ConverterSetting.SheetFitToPage = false;


                workbook.DocumentProperties.Author = NeoAms.GlobalVariables.NeoGlobal.Authentication.CurrentUserName;
                workbook.DocumentProperties.Company = "blah";
                workbook.DocumentProperties.Subject = "blahblah";
                workbook.DocumentProperties.Title = fileDownloadName;

                foreach (Spire.Xls.Worksheet sheet in workbook.Worksheets)
                {
                    //Copy each worksheet from the current workbook to the new workbook
                    finalWorkbook.Worksheets.AddCopy(sheet, WorksheetCopyType.CopyAll);
                }
            }

            finalWorkbook.SaveToStream(sumPdfStream, Spire.Xls.FileFormat.PDF);
            sumPdfStream.Position = 0;
            
            var fsr = new FileStreamResult(sumPdfStream, contentType);
            fsr.FileDownloadName = fileDownloadName;

            return fsr;