I have an action that returns an Excel as a custom FileResult. My solution is based on the ClosedXml library (internaly using OpenXml). My XlsxResult class uses a read-only .xlsx file on the server as a template. It then passes on the template into a memory stream that gets manipulated and saved back with ClosedXml. In the end the memory stream get written to the response.
This works fine both on Cassini as well as IIS Express but fails when deployed on azure with no error whatsoever. The only effect I am experiencing is the request sent to the server never gets any response. I am still waiting for something to happen after 60 minutes or so...
My action:
[OutputCache(Location= System.Web.UI.OutputCacheLocation.None, Duration=0)]
public FileResult Export(int year, int month, int day) {
var date = new DateTime(year, month, day);
var filename = string.Format("MyTemplate_{0:yyyyMMdd}.xlsx", date);
//return new FilePathResult("~/Content/templates/MyTemplate.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
var result = new XlsxExportTemplatedResult("MyTemplate.xlsx", filename, (workbook) => {
var ws = workbook.Worksheets.Worksheet("My Export Sheet");
ws.Cell("B3").Value = date;
// Using a OpenXML's predefined formats (15 stands for date)
ws.Cell("B3").Style.NumberFormat.NumberFormatId = 15;
ws.Columns().AdjustToContents(); // You can also specify the range of columns to adjust, e.g.
return workbook;
});
return result;
}
My FileResult
public class XlsxExportTemplatedResult : FileResult
{
// default buffer size as defined in BufferedStream type
private const int BufferSize = 0x1000;
public static readonly string TEMPLATE_FOLDER_LOCATION = @"~\Content\templates";
public XlsxExportTemplatedResult(string templateName, string fileDownloadName, Func<XLWorkbook, XLWorkbook> generate)
: base("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") {
this.TempalteName = templateName;
this.FileDownloadName = fileDownloadName;
this.Generate = generate;
}
public string TempalteName { get; protected set; }
public Func<XLWorkbook, XLWorkbook> Generate { get; protected set; }
protected string templatePath = string.Empty;
public override void ExecuteResult(ControllerContext context) {
templatePath = context.HttpContext.Server.MapPath(System.IO.Path.Combine(TEMPLATE_FOLDER_LOCATION, this.TempalteName));
base.ExecuteResult(context);
}
//http://msdn.microsoft.com/en-us/library/office/ee945362(v=office.11).aspx
protected override void WriteFile(System.Web.HttpResponseBase response) {
FileStream fileStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
using (MemoryStream memoryStream = new MemoryStream()) {
CopyStream(fileStream, memoryStream);
using (var workbook = new XLWorkbook(memoryStream)) {
Generate(workbook);
workbook.Save();
}
// At this point, the memory stream contains the modified document.
// grab chunks of data and write to the output stream
Stream outputStream = response.OutputStream;
byte[] buffer = new byte[BufferSize];
while (true) {
int bytesRead = memoryStream.Read(buffer, 0, BufferSize);
if (bytesRead == 0) {
// no more data
break;
}
outputStream.Write(buffer, 0, bytesRead);
}
}
fileStream.Dispose();
}
static private void CopyStream(Stream source, Stream destination) {
byte[] buffer = new byte[BufferSize];
int bytesRead;
do {
bytesRead = source.Read(buffer, 0, buffer.Length);
destination.Write(buffer, 0, bytesRead);
} while (bytesRead != 0);
}
}
So am I missing something (apparently I am).
Please Note:
- There are no dlls missing from Azure because I checked using RemoteAccess feature of the Windows Azure Tools 1.7
- My export is not a heavy long running task.
- when I changed the action to just return a FilePathResult with the template xlsx it worked on azure. But I need to process the file before returning it as u might suspect :-)
Tanks.
UPDATE: After I logged extensively in my code the execution hangs with no error at the ClosedXml "Save" method call. But still no error. Abstract from the WADLogsTable:
- Opening template file from path: E:\sitesroot\0\Content\templates\MyTemplate.xlsx
- Opened template from path: E:\sitesroot\0\Content\templates\MyTemplate.xlsx just
- copied template to editable memory stream. Bytes copied: 15955, Position: 15955
- modified the excel document in memory.
- here it hangs when a it calls to workbook.Save(); This is a ClosedXml method call.
