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:

1. Taking data from MSSQL server.
2. 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

Confutation

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). – Rinee

This is not solve my second problem (Downloading begins only when Excel document completed). – Confutation

I know, thats why this is not the answer, just a comment. – Rinee

Answer 1

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.

Confutation

Answer 2

The problem is a little deeper. Xlsx file is zip archive and OpenXml internally uses the ZipArchive class. Each file in archive has a header, it is placed before the data. ZipArchive writes data to the stream, then returns to the beginning of the file and writes file's header. It uses the Stream.Seek method and HttpResponse.OutputStream cannot work this way.

Undermanned

If you don't know, you can archivate with zip in streaming manner (there are many libraries as example SharpZipLib), without returning to beginning of the file. But OpenXml doesn't use this. And this is design error in OpenXml library, i think. – Confutation