我有一个ASP.NET MVC应用程序。在此应用程序中,我有一个功能可以从数据库下载数据到Excel文件(使用OpenXML SDK)。现在起作用。但是,当数据较大时,从用户请求到使用下载窗口响应的时间变为10分钟以上。这是因为两个长过程:
- 从MSSQL Server获取数据。
- 在服务器上的内存中生成Excel文档。(仅在完成Excel文档完成时下载才开始)
第一个问题通过使用DataReader解决。现在,在用户请求成为Web服务器之后,开始生成Excel文件。
要解决第二个问题,我们需要在httpresponse.outputstream上生成Excel文档,但是此流不可寻求,并且在开始之前生成失败。
有人知道任何可以帮助解决此问题的解决方法吗?
我的生成功能的样本:
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);
}
我的fileStreamResultWithTransformation(用于使用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:无法打开软件包,因为filemode或fileaccess value 对流无效。]
system.io.packaging.package.validatemodeandaccess(流S,filemode 模式,fileaccess访问) 784533
system.io.packaging.package.open(流stream,filemode packagemode, fileaccess packageaccess,boolean流) 89
system.io.packaging.package.open(流stream,filemode packagemode, fileaccess packageaccess) 10
documentformat.openxml.packaging.openxmlpackage.createcore(流 流) 192
documentformat.openxml.packaging.spreadsheetdocument.create(流 流,电子表格documentType类型,布尔式autosave) 215
documentformat.openxml.packaging.spreadsheetdocument.create(流 流,电子表格documentType类型) 44
--------。生成preadsheettostream(idatareader datareader,stream utputstream) d: work epsilon developm web trunk sources epsilon.documentgenerator xlsxgenerator.cs:119
在我看来,这个问题无法解决。关于写作的最终确定,OpenXMLWriter寻求,在流的不同位置读写,没有此操作XLSX文件被打破。我认为,OpenXML库的设计有问题。
问题更深一些。XLSX文件是zip存档,OpenXML在内部使用ziparchive类。存档中的每个文件都有一个标题,将其放置在数据之前。ziparchive将数据写入流中,然后返回到文件的开头,然后写入文件的标题。它使用stream.seek方法和httpresponse.outputstream无法以此方式工作。