Download OpenXML generated

Question

I am currently creating an Excel Sheet in a console application for testing purposes. I need to implement my code in an ASP.Net .aspx page.

How can I offer this Excel sheet to the user for download without saving it in any form locally?

This is my code:

        static void Main(string[] args)
    {
        SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(DateTime.Now.ToString("ddMMyyyyHHmmss") + @".xlsx", SpreadsheetDocumentType.Workbook);

        WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        // Add a WorksheetPart to the WorkbookPart.
        WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new 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 = "mySheet"
        };

        sheets.Append(sheet);

        Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);
        cell.CellValue = new CellValue("23.289374");
        cell.DataType = new EnumValue<CellValues>(CellValues.Number);

        Cell cellString = InsertCellInWorksheet("B", 1, worksheetPart);
        cellString.CellValue = new CellValue("hello");
        cellString.DataType = new EnumValue<CellValues>(CellValues.String);

        // Close the document.
        workbookpart.Workbook.Save();
        spreadsheetDocument.Close();
    }

    // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
    // If the cell already exists, returns it. 
    private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
    {
        Worksheet worksheet = worksheetPart.Worksheet;
        SheetData sheetData = worksheet.GetFirstChild<SheetData>();
        string cellReference = columnName + rowIndex;

        // If the worksheet does not contain a row with the specified row index, insert one.
        Row row;
        if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
        {
            row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
        }
        else
        {
            row = new Row() { RowIndex = rowIndex };
            sheetData.Append(row);
        }

        // If there is not a cell with the specified column name, insert one.  
        if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
        {
            return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
        }
        else
        {
            // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Cell refCell = null;
            foreach (Cell cell in row.Elements<Cell>())
            {
                if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                {
                    refCell = cell;
                    break;
                }
            }

            Cell newCell = new Cell() { CellReference = cellReference };
            row.InsertBefore(newCell, refCell);

            worksheet.Save();
            return newCell;
        }
    }

Answer 1

Use a MemoryStream. e.g.

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

    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Code omitted for brevity....

    // Close the document.
    workbookpart.Workbook.Save();
    spreadsheetDocument.Close();
    return mem;
}

You will then need to do something like this in the code behind of your aspx page:

using (MemoryStream mem = new CreateSpreadSheet(mem))
{
    Response.Clear();
    Response.ContentType = 
        @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
    Response.AddHeader("content-disposition", "attachment;filename=name_your_file.xlsx");
    mem.WriteTo(Response.OutputStream);
    Response.End();
}