Fendec
Fendec

Reputation: 367

Generating Excel File using .NET Aspose Cells and trying to download via browser

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

Answers (2)

Taraz
Taraz

Reputation: 1331

In case anyone else ends up here, add the following to your AJAX call:

xhrFields: { responseType: "arraybuffer" }, dataType: "binary",

Upvotes: 0

shakeel
shakeel

Reputation: 1725

Update-1

Please try the following code, it may fix your issue. Please note,

  • For XLS - you should use XlsSaveOptions
  • For XLSX - you should use OoxmlSaveOptions

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

  • Default.aspx
  • Default.aspx.cs

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 C# Excel Aspose Cells

Code

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

Related Questions