0

I have the standard code for creating a spreadsheet using OpenXml but I would prefer to just hold it in memory to serve the user. I.e. when the user clicks a link I would generate it and the user could save it on their PC.

How is this done with OpenXml and MVC? Is there an inherent advantage to saving a file that would need to get regenerated every time?

Alex In Paris
  • 1,017
  • 9
  • 19
  • You should be able to save it to a [MemoryStream](https://learn.microsoft.com/en-us/dotnet/api/system.io.memorystream?view=netframework-4.8), and then send the bytes. – itsme86 Sep 30 '19 at 15:01
  • 1
    Does this help? https://stackoverflow.com/questions/22101338/openxml-spreadsheetdocument-return-byte-array-for-mvc-file-download – stuartd Sep 30 '19 at 15:02
  • Possible duplicate of [How to create Excel file using OpenXML without creating a local file?](https://stackoverflow.com/questions/29887503/how-to-create-excel-file-using-openxml-without-creating-a-local-file) – petelids Oct 03 '19 at 19:20

1 Answers1

1

So that everything's in one place:

In the view:

<a href="@Url.Action("GetRejects", "Home")" style="color:#ac0066"><span class="glyphicon glyphicon-download"></span> Download list of rejected invoices</a>

In the controller:

public FileContentResult GetRejects()
        {
            return File(WorksheetTools.GetRejectsExcelFile(userId),
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                string.Concat("Rejects.", DateTime.Now.ToString("ddMMyyyy.HHmmss"), ".xlsx"));
        }

GetRejectsExcelFile function:

public static byte[] GetRejectsExcelFile(string userId)
        {
            List<RejectsModel> rejectList = GetFactureFournisseur(userId);

        using (MemoryStream mem  = new MemoryStream())
        {
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(mem, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            SheetData sheetData = new SheetData();
            worksheetPart.Worksheet = new Worksheet(sheetData);

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "Invoices"
            };

            Row row = new Row() { RowIndex = 1 };
            Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Business"), DataType = CellValues.String };
            row.Append(header1);
            Cell header2 = new Cell() { CellReference = "B1", CellValue = new CellValue("Supplier code"), DataType = CellValues.String };
            row.Append(header2);
            Cell header3 = new Cell() { CellReference = "C1", CellValue = new CellValue("Supplier"), DataType = CellValues.String };
            row.Append(header3);

            sheetData.Append(row);

            uint rowIndex = 1;
            foreach(var ff in rejectList)
            {
                ++rowIndex;
                Row dataRow = new Row() { RowIndex = rowIndex };
                dataRow.Append(new Cell() { CellReference = "A" + rowIndex.ToString(), CellValue = new CellValue(ff.BusinessName), DataType = CellValues.String });
                dataRow.Append(new Cell() { CellReference = "B" + rowIndex.ToString(), CellValue = new CellValue(ff.SupplierCode), DataType = CellValues.String });
                dataRow.Append(new Cell() { CellReference = "C" + rowIndex.ToString(), CellValue = new CellValue(ff.SupplierName), DataType = CellValues.String });

                sheetData.Append(dataRow);
            }

            sheets.Append(sheet);
            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();

            return mem.ToArray();
        }
    }
Alex In Paris
  • 1,017
  • 9
  • 19