Reputation: 367
I am trying to dynamically generate an excel version of an HTML report using .NET Aspose Cells.
The situation requires the generation of the Excel file and then the passing of the Excel file to the user's browser for download.
I create a csv file from with the data I need from the HTML report.
Then I open an Excel template I created using Aspose Cells and populate it with the CSV contents.
This works fine when I save the file locally to my machine but never when I try to pass it back to the Javascript to download it via the browser.
Please find below my code:
C# - Aspose Excel Workbook Generation
Workbook temp = new Workbook(System.Web.Hosting.HostingEnvironment.MapPath($"~/templates/") + @"excel_template.xlsx");
Worksheet ws = temp.Worksheets[0];
ws.Cells.ImportCSV(csvFile, ",", false, 1, 0);
// Used to test the excel populates correctly
temp.Save("Path_to_file" + fileName + ".xlsx", SaveFormat.Xlsx);
Then I try to pass that Workbook to the browser via a HttpResponseMessage.
After a lot of searching, this is what I came up with (NOTE: could be completely wrong)
MemoryStream ms = new MemoryStream();
temp.Save(ms, SaveFormat.Xlsx);
byte[] bytes = ms.ToArray();
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
response.Content = new ByteArrayContent(bytes);
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
response.Content.Headers.ContentDisposition.FileName = fileName + ".xlsx";
response.Content.Headers.ContentType = new MediaTypeHeaderValue("Application/x-msexcel");
return response;
Then I try to interact with the response using Javascript to generate a download of the excel file that is visible via the browser.
JavaScript Code
$.get(ROOT + 'api_route', { 'csv': csv }, function (data) {
var byteArray = new Uint8Array(data);
var link = document.createElement('a');
var blob = new Blob(byteArray, { type: "application/octet-stream" });
link.href = window.URL.createObjectURL(blob);
link.download = "sample_excel_" + new Date(Date.now()).toLocaleDateString() + '_' + new Date(Date.now()).toLocaleTimeString() + ".xlsx";
link.click();
});
This downloads the excel file via the browser but the file is corrupted and it can't be opened.
What I am asking is, can this actually be done first off?
If so, is there something obvious that I have done wrong?
Upvotes: 1
Views: 4634
Reputation: 1331
In case anyone else ends up here, add the following to your AJAX call:
xhrFields: { responseType: "arraybuffer" }, dataType: "binary",
Upvotes: 0
Reputation: 1725
Update-1
Please try the following code, it may fix your issue. Please note,
It is also necessary, that you end the Response Stream using the End() method.
C#
// Save file and send to client browser using selected format
if (yourFileFormat == "XLS")
{
workbook.Save(HttpContext.Current.Response, "output.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "output.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
HttpContext.Current.Response.End();
Update-2
There is no JavaScript involved. Please see the code of
as given below. The following screenshot shows how the Excel file will be downloaded when you will click on one of the buttons.
Screenshot showing the Output of Default.aspx in Chrome
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnSaveToXLS" runat="server" Text="Save to XLS" OnClick="btnSaveToXLS_Click" /> <br /><br />
<asp:Button ID="btnSaveToXLSX" runat="server" Text="Save to XLSX" OnClick="btnSaveToXLSX_Click" />
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Aspose.Cells;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSaveToXLS_Click(object sender, EventArgs e)
{
//Create empty workbook.
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Put some value in cell C4
Cell cell = worksheet.Cells["C4"];
cell.PutValue("This is XLS format generated by Aspose.Cells API.");
// Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "outputSaveToXLS.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
HttpContext.Current.Response.End();
}
protected void btnSaveToXLSX_Click(object sender, EventArgs e)
{
//Create empty workbook.
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Put some value in cell C4
Cell cell = worksheet.Cells["C4"];
cell.PutValue("This is XLSX format generated by Aspose.Cells API.");
// Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "outputSaveToXLSX.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
HttpContext.Current.Response.End();
}
}
Note: I am working as Developer Evangelist at Aspose
Upvotes: 1