Export To Excel from Asp Net Web API Controller

Question

I am working on New web application which is Using Web API as Business Layer and Knock out Js as client side frame work to binding. I have a requirement like Pass the certain search criteria to Web API Controller and get the Data from DB and Create and Send the Excel/MS-Word file on the fly as a downloadable content.

I am new to both the Web API and Knock out, I am searching on the Net and get partial solution and I am looking here to get more optimal solution for this use case.

Below is my code:

Client:

 function GetExcelFile() {
    var $downloadForm = $("<form method='POST'>")
      .attr("action", baseUrl + "api/FileHandler/GetExcelFileTest")
              .attr("target", "_blank")
    $("body").append($downloadForm);
    $downloadForm.submit();
    $downloadForm.remove();
}

On Button Click having this code snippet to create a form on the fly and Get response from Web API.

Web API Code:

[HttpPost]
        public HttpResponseMessage GetExcelFileTest()
        {
            var response = new HttpResponseMessage();
            //Create the file in Web App Physical Folder
            string fileName = Guid.NewGuid().ToString() + ".xls";
            string filePath = HttpContext.Current.Server.MapPath(String.Format("~/FileDownload/{0}", fileName));

            StringBuilder fileContent = new StringBuilder();
            //Get Data here
            DataTable dt = GetData();
            if (dt != null)
            {
                string str = string.Empty;
                foreach (DataColumn dtcol in dt.Columns)
                {
                    fileContent.Append(str + dtcol.ColumnName);
                    str = "\t";
                }
                fileContent.Append("\n");
                foreach (DataRow dr in dt.Rows)
                {
                    str = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        fileContent.Append(str + Convert.ToString(dr[j]));
                        str = "\t";
                    }
                    fileContent.Append("\n");
                }
            }
            // write the data into Excel file
            using (StreamWriter sw = new StreamWriter(fileName.ToString(), false))
            {
                sw.Write(fileContent.ToString());
            }
            IFileProvider FileProvider = new FileProvider();
            //Get the File Stream
            FileStream fileStream = FileProvider.Open(filePath);
            //Set response
            response.Content = new StreamContent(fileStream);
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
            response.Content.Headers.ContentDisposition.FileName = fileName;
            response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/ms-excel");
            response.Content.Headers.ContentLength = fileStream.Length;
            //Delete the file

            //if(File.Exists(filePath))
            //{
            //    File.Delete(filePath);
            //}
            return response;
        }

Using this code I am able to download an Excel File. Still I have some more open questions to make this code optimal.

Q1) I need to Pass view model(Search Criteria) to API Controller Using the dynamically create form ? (OR) Any better ways to get Excel file from Web API.

Q2) I am sure it's not a good way to create Excel file in Physical folder and Get FileStream and send as a respone. How to do on the fly ? OR any other optimal ways.

Please suggest me to do better ways.. Thanks

Answer 1

Q1) You can quite easily pass the view-model, but it's also similarly easy to pull that information from the posted form.

Passing the view-model

If you want to pass the view-model to a WebAPI method then remember that said method must take as a parameter an object with the same properties. So if the object that you wish to post back always has the same properties then it's trivial to build a server-side class with the same properties and receive an instance of that class.

To post back this client-side object you can do something like this (uses jQuery, which I see you're already using):

$.ajax({
    contentType: "application/json",
    data: my-view-model.toJSON(),
    type: "POST",
    url: baseUrl + "api/FileHandler/GetExcelFileTest" });

I haven't attached any success or error handlers here because the JavaScript isn't concerned with the return, but you might wish to add some handlers in case an exception is thrown in your WebAPI method. I recommend doing that by adding the following to the above $.ajax() call:

statusCode: {
    500: function(jqXhr, textStatus, errorThrown) {
    },
    [other HTTP error codes]
}

[Read the documentation for the $.ajax() call here.]

One additional tip here: when you call my-view-model.toJSON() (or self.toJSON(), if called from within your view-model) Knockout will first of all determine if your view-model contains a toJSON() method. If so, it will use this method; if not then it will call the browser's implementation of this function. However, the browser's implementation of this function will serialise everything, which can be particularly length if you have, for example, long select lists in your view-model. Therefore, if you wish only to send back a subset of the view-model's properties then define your own toJSON function on your view-model like so:

var toJSON = function() {
    return {
        Property1: ...,
        Property2: ...
    };
}

[Read more about converting a view-model to JSON here.]

Posting the form as-is

If you don't wish to expend the effort to do the view-model wiring then you can just post the form exactly like you have in your question. You can then retrieve the values from the form by using

Request.Form["my-field"];

Q2)

You're probably right in pointing out that it's not wise to create the Excel file in the physical folder. However, as far as I'm aware (interested if someone says otherwise) you'll have to use a 3rd-party library for this. Microsoft do offer an Office automation library but I have a suspicion that you also need Office to be installed at the same location.

Creating Excel spreadsheets dynamically is something I've done several times but for the actual creation I use Aspose.Cells, which requires a license. Although I do create a physical version and then delete it, I believe Aspose.Cells may allow you to create it as a stream. But take a look around, there are certainly other libraries which offer Excel automation.

Returning the File from the Server

Calling $.ajax({...}) alone won't allow you to present the user with a "Save as..." dialog. What I do in this situation - and this won't work if you wish to store the generated file only in memory (FileStream, for example) and not on the file system - is to respond to the $.ajax({...}) call with a filename for the generated file.

The next step is to direct the user towards that filename.

So I have something like this in my JavaScript:

$.ajax({
    dataType: "json",
    type: "GET",    // you'll probably want POST in your case
    url: ...,
    success: function(response) {
        if (response && response.Uri && response.Uri.length) {
            window.location.href = [root URL] + response.Uri;
        }
    }
});

But don't be alarmed by this redirect. That window.location.href points directly to a folder on the server, no controller needed. Because the browser then receives a file it presents the "Save as..." dialog while remaining on the same webpage.