Export To Excel from Asp Net Web API Controller

0

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