locked
generate excel file using NPOI C# and telerik ui for ASP.NET MVC RRS feed

  • Question

  • User202943253 posted

     I tried to call a method action of my controller to generate an excel file after clicking on a button

    .ToolBar(toolbar =>
    {
    toolbar.Custom().Text("Create").IconClass("fa fa-file-excel").Action("GenerateExcelAction",  "GenerateExcelController").HtmlAttributes(new { @class = "btn btn-outline-primary" });
    })

     

    using NPOI.HSSF.UserModel;
    using System.IO;

    public ActionResult GenerateExcelAction()
            {
                try
                {
                    // Opening the Excel template...
                    FileStream fs =
                        new FileStream(Server.MapPath(@"\Content\Templates\myFile.xls"), FileMode.Open, FileAccess.Read);
             
                    // Getting the complete workbook...
                    HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

                    // Getting the worksheet by its name...
                    HSSFSheet shhet1= (HSSFSheet)templateWorkbook.GetSheet("sheet1");

                    HSSFRow dataRow = (HSSFRow)sheet1.GetRow(3);
                    dataRow .GetCell(5).SetCellValue("some text");
               
                    // Forcing formula recalculation...
                    sheet1.ForceFormulaRecalculation = true;

                    MemoryStream ms = new MemoryStream();

                    // Writing the workbook content to the FileStream...
                    templateWorkbook.Write(ms);
                   
                    // Sending the server processed data back to the user computer...
                    return File(ms.ToArray(), "application/vnd.ms-excel", "newFile.xls");

                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                    return RedirectToAction("Index");
                }
                
            }
        }

     

    this method works fine by generating me an excel but if I do it otherwise by calling a js ajax function to pass json data .. it enters the method and  nothing happens

     

    .ToolBar(toolbar =>
    {
                                                   
    toolbar.Custom().Text("Create").IconClass("fa fa-file-excel").HtmlAttributes(new { id = "createExcel", @class = "btn btn-outline-primary", onclick = "generateExcel();" });

    })

     

      function generateExcel() {

            //var modelData = { "modelData": "dt" };

            $.ajax({
                url: '/GenerateExcelController/GenerateExcelAction',
                type: "POST",
                dataType: "json",
                contentType: "application/json; charset=utf-8",
                data: JSON.stringify({ modelData: "dttt" }),

                success: function (data) {
                   alert("succeedd");
                },
                error: function (data) {
                    alert("KO");
                }
            });
        }

    going through the 2nd method, it recovers the data and goes through the method without returning the excel file to me like the first methode
    something missing ??

    Any help please?

    Wednesday, December 9, 2020 8:13 AM

Answers

  • User1686398519 posted

    Hi silbahi, 

    You cannot directly return the file to be downloaded via ajax call.

    You can first save the generated file to the project path, and then download the file. You can refer to the example I gave.

    Controller

        public class FileController : Controller
        {
            public ActionResult Index()
            {
                return View();
            }
            [HttpPost]
            public ActionResult GenerateExcel(string modelData)
            {
                FileStream fs = new FileStream(Server.MapPath(@"\Test.xls"), FileMode.Open, FileAccess.Read);
                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
                HSSFSheet sheet1 = (HSSFSheet)templateWorkbook.GetSheet("sheet1");
                HSSFRow dataRow = (HSSFRow)sheet1.GetRow(3);
                dataRow.GetCell(5).SetCellValue("some text");
                sheet1.ForceFormulaRecalculation = true;
                string temppath = Server.MapPath("~/TempFile");
                if (!Directory.Exists(temppath))
                {
                    Directory.CreateDirectory(temppath);
                }
                var tempfileName = "excelfile" +new Guid().ToString()+ ".xls";
                FileStream tempstream = new FileStream(Path.Combine(temppath, tempfileName), FileMode.Create, FileAccess.Write);
                templateWorkbook.Write(tempstream);
                tempstream.Close();
                fs.Close();
                return Json(tempfileName,JsonRequestBehavior.AllowGet);
            }
            public ActionResult download(string tempfileName)
            {
                string temppath = Server.MapPath("~/TempFile");
                string filePath = Path.Combine(temppath, tempfileName);
                var downloadfile = File(filePath, "application/vnd.ms-excel", tempfileName);
                return downloadfile;
            }
        }

    View

    <button id="test" type="submit">submit</button>
    @section scripts{
        <script>
            $("#test").click(function () {
                $.ajax({
                    url: '/File/GenerateExcel',
                    type: "POST",
                    dataType: "json",
                    contentType: "application/json; charset=utf-8",
                    data: JSON.stringify({ modelData: "dttt" }),
                    success: function (tempfileName) {
                        if (tempfileName!= null) {
                            window.location = "/File/download?tempfileName=" + tempfileName;
                        }
                        alert("succeedd");
                    },
                    error: function (data) {
                        alert("KO");
                    }
                });
            });
        </script>
    }

    Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 10, 2020 10:06 AM

All replies

  • User-474980206 posted

    When you call the method with Ajax, the excel file is returned to the success function, in your case the data variable. If you want the to save the file, you need the browser to request via anchor whose href has the data, or a form post. Just create a form with hidden fields for the data, the action set to the controller method, and target=“_blank”. Then on you click handler, set the hidden fields and submit the form

    Wednesday, December 9, 2020 3:30 PM
  • User1686398519 posted

    Hi silbahi, 

    You cannot directly return the file to be downloaded via ajax call.

    You can first save the generated file to the project path, and then download the file. You can refer to the example I gave.

    Controller

        public class FileController : Controller
        {
            public ActionResult Index()
            {
                return View();
            }
            [HttpPost]
            public ActionResult GenerateExcel(string modelData)
            {
                FileStream fs = new FileStream(Server.MapPath(@"\Test.xls"), FileMode.Open, FileAccess.Read);
                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
                HSSFSheet sheet1 = (HSSFSheet)templateWorkbook.GetSheet("sheet1");
                HSSFRow dataRow = (HSSFRow)sheet1.GetRow(3);
                dataRow.GetCell(5).SetCellValue("some text");
                sheet1.ForceFormulaRecalculation = true;
                string temppath = Server.MapPath("~/TempFile");
                if (!Directory.Exists(temppath))
                {
                    Directory.CreateDirectory(temppath);
                }
                var tempfileName = "excelfile" +new Guid().ToString()+ ".xls";
                FileStream tempstream = new FileStream(Path.Combine(temppath, tempfileName), FileMode.Create, FileAccess.Write);
                templateWorkbook.Write(tempstream);
                tempstream.Close();
                fs.Close();
                return Json(tempfileName,JsonRequestBehavior.AllowGet);
            }
            public ActionResult download(string tempfileName)
            {
                string temppath = Server.MapPath("~/TempFile");
                string filePath = Path.Combine(temppath, tempfileName);
                var downloadfile = File(filePath, "application/vnd.ms-excel", tempfileName);
                return downloadfile;
            }
        }

    View

    <button id="test" type="submit">submit</button>
    @section scripts{
        <script>
            $("#test").click(function () {
                $.ajax({
                    url: '/File/GenerateExcel',
                    type: "POST",
                    dataType: "json",
                    contentType: "application/json; charset=utf-8",
                    data: JSON.stringify({ modelData: "dttt" }),
                    success: function (tempfileName) {
                        if (tempfileName!= null) {
                            window.location = "/File/download?tempfileName=" + tempfileName;
                        }
                        alert("succeedd");
                    },
                    error: function (data) {
                        alert("KO");
                    }
                });
            });
        </script>
    }

    Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 10, 2020 10:06 AM