OpenXml and HttpResponse.OutputStream

Question

I have an Asp.Net Mvc application. In this application i have a functionality to download data from Database to Excel file (with OpenXml Sdk). It works now. But when data is large, time from user request to response with download window becomes 10+ minutes. This is because of two long process:

Taking data from MSSQL server.
Generating Excel document in memory on server. (Downloading begins only when Excel document completed)

First problem was solved through using of DataReader. Now generating of excel file begins just after user request becomes to webserver.

For solving second problem we need to generate Excel document on HttpResponse.OutputStream, but this stream is not Seekable and generation fails before begining.

Does anyone knows any workaround that can help to work with this problem?

Sample of my generating function:

    public void GenerateSpreadSheetToStream(IDataReader dataReader, Stream outputStream)
    {
        var columnCaptions =                FillColumnCaptionsFromDataReader(dataReader.GetSchemaTable());
//fails on next line with exception "Cannot open package because FileMode or FileAccess value is not valid for the stream."
        using (var spreadsheetDocument = SpreadsheetDocument.Create(outputStream, SpreadsheetDocumentType.Workbook)) 
        {
            spreadsheetDocument.AddWorkbookPart();
            var workSheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
            OpenXmlWriter writer;
            using (writer = OpenXmlWriter.Create(workSheetPart))
            {
                using (writer.Write(new Worksheet()))
                {

                    using (writer.Write(new SheetData()))
                    {
                        using (writer.Write(w => 
                            w.WriteStartElement(new Row(), new[] {new OpenXmlAttribute("r", null, 1.ToString(CultureInfo.InvariantCulture))})))
                        {
                            var cells =
                                columnCaptions.Select(caption => new Cell()
                                    {
                                        CellValue = new CellValue(caption.Item2),
                                        DataType = CellValues.String
                                    });
                            foreach (var cell in cells)
                            {
                                writer.WriteElement(cell);
                            }
                        }
                        var i = 2;
                        while (dataReader.Read())
                        {
                            var oxa = new[] { new OpenXmlAttribute("r", null, i.ToString(CultureInfo.InvariantCulture)) };
                            using (writer.Write(w => w.WriteStartElement(new Row(), oxa)))
                            {
                                var cells =
                                    columnCaptions.Select(
                                        (c, j) =>
                                        new Cell
                                            {
                                                CellValue = new CellValue(dataReader[c.Item1].ToString()),
                                                DataType = CellValues.String,
                                                CellReference = new StringValue(GetSymbolByCellNumber(j))
                                            });
                                foreach (var cell in cells)
                                {
                                    writer.WriteElement(cell);
                                }
                            }
                            i++;
                        }
                    }
                }
            }
            using (writer = OpenXmlWriter.Create(spreadsheetDocument.WorkbookPart))
            {
                using (writer.Write(new Workbook()))
                {
                    using (writer.Write(new Sheets()))
                    {
                        var sheet = new Sheet
                        {
                            Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(workSheetPart),
                            SheetId = 1,
                            Name = SheetName
                        };
                        writer.WriteElement(sheet);
                    }
                }
            }
        }
    }
    private static string GetSymbolByCellNumber(int number)
    {
        var r = number/26;
        var s = (char) ((number%26) + 65);
        return new string(s, r);
    }

My FileStreamResultWithTransformation (for working with HttpResponse.OutputStream):

public class FileStreamResultWithTransformation : FileResult
{
    private readonly Action<Stream> _action;
    public FileStreamResultWithTransformation(Action<Stream> action, string contentType, string fileName) : base(contentType)
    {
        _action = action;
        FileDownloadName = fileName;
    }

    protected override void WriteFile(HttpResponseBase response)
    {
        response.BufferOutput = false;
        _action(response.OutputStream); ->> it fails there  
    }
}

StackTrace:

[IOException: Cannot open package because FileMode or FileAccess value is not valid for the stream.]
System.IO.Packaging.Package.ValidateModeAndAccess(Stream s, FileMode mode, FileAccess access) +784533
System.IO.Packaging.Package.Open(Stream stream, FileMode packageMode, FileAccess packageAccess, Boolean streaming) +89
System.IO.Packaging.Package.Open(Stream stream, FileMode packageMode, FileAccess packageAccess) +10
DocumentFormat.OpenXml.Packaging.OpenXmlPackage.CreateCore(Stream stream) +192
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(Stream stream, SpreadsheetDocumentType type, Boolean autoSave) +215
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(Stream stream, SpreadsheetDocumentType type) +44
-------.GenerateSpreadSheetToStream(IDataReader dataReader, Stream outputStream) in d:\Work\Epsilon\development\Web\trunk\Sources\Epsilon.DocumentGenerator\XlsXGenerator.cs:119

I was trying to get it to work some time ago. I didn't find the way back then and had to stick with MemoryStream and .WriteTo(response.OutputStream). — tpeczek
This is not solve my second problem (Downloading begins only when Excel document completed). — Kirill Bestemyanov

Kirill Bestemyanov Kirill Bestemyanov · Accepted Answer · 2012-10-25T08:10:44

It seems to me, that this problem cannot be solved. On finalization of writing, OpenXmlWriter seeks, read and write in different positions of stream and without this actions xlsx file is broken. I think, there is something wrong in design of OpenXml library.

OpenXml and HttpResponse.OutputStream

2 Answers