locked
Export to excel in .net core 3.1 Razor pages and ClosedXML RRS feed

  • Question

  • User-642154842 posted

    Hi,

    i have an application in .net core 3.1, i need to export to excel a list. The list is populated by a controller.
    On orders page a datatable showing order headers, a button from the datalist row is calling the orderdetail controller passing the paramer of Order ID. the post method on the controller is looping through the list but  i do not get any popups to open or save the excel file.

       [HttpPost]
            public IActionResult DownloadExcelDocument([FromBody] string orderNo)
            {
    //code to get the list data            
    var getorders= Orderdetail.ToList();
                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                string fileName = orderNo+".xlsx";
               
                    using (var workbook = new XLWorkbook())
                    {
                        IXLWorksheet worksheet =
                        workbook.Worksheets.Add("Authors");
                        worksheet.Cell(1, 1).Value = "Id";
                        worksheet.Cell(1, 2).Value = "FirstName";
                        worksheet.Cell(1, 3).Value = "LastName";
                        for (int index = 1; index <= getorders.Count; index++)
                        {
                            worksheet.Cell(index + 1, 1).Value =
                            getorders[index - 1].Id;
                            worksheet.Cell(index + 1, 2).Value =
                            getorders[index - 1].FirstName;
                            worksheet.Cell(index + 1, 3).Value =
                            getorders[index - 1].LastName;
                        }
                        using (var stream = new MemoryStream())
                        {
                            workbook.SaveAs(stream);
                            var content = stream.ToArray();
                            return File(content, contentType, fileName);
                        }
                    }    
            }

    runnign the code i dont get any errord but i don see the excel file popup.

    thank you

    Saturday, November 28, 2020 1:51 PM

Answers

  • User-474980206 posted

    Don’t use Ajax. In your success function, the data parameter is the file data. JavaScript can not save this as a file.

    Instead do a form post to a new window by setting the target to _blank. It’s common to use a hidden form for this.

    If you really must use Ajax, then have the server return the file content as a dataurl. Then in JavaScript create an anchor and set its href to the dataurl. Then call the click event of the anchor.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 28, 2020 5:23 PM
  • User-642154842 posted

    Hi All,

    thank you for your help, i found the solution to the format issues:

     worksheet.Column(2).Style.NumberFormat.Format = "@";

    everything is working great.

    thank you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 30, 2020 11:17 AM

All replies

  • User475983607 posted

    Usually, the browser downloads the file.  There's no popup.  

      You pass an orderNo but it is not used to fetch records.  Does Orderdetial return a collection?  

    var getorders= Orderdetail.ToList();

    Run the code through the debugger to make sure the logic functions as you expect. 

    Saturday, November 28, 2020 2:21 PM
  • User-642154842 posted

    Hi mgebhard,

    thank you for the reply, the orderNo is used as a parameter to filter the requse:

       var orderdetail = _unitOfWork.Ordersdetail.GetAll(u => u.orderNo == orderNo );
                var getorders= orderdetail.ToList();

    and getorders returns 21000 rows.

    Saturday, November 28, 2020 4:05 PM
  • User-474980206 posted

    What code calls the download action. Perhaps you used Ajax in which case the file data is passed to the success callcak.

    Saturday, November 28, 2020 4:31 PM
  • User-642154842 posted

    Hi Bruce, 

    yes i am using Ajax to call the action:

     {
                    "data": { orderNo: "orderNo" },
                      "render": function (data)
                      {
                            return ` <div class="text-center">
                                    <a class="btn btn-danger text-white" style="cursor:pointer; width:100px;" onclick=DownloadOrder('${data.orderNo}')>
                                      download
                                    </a></div>`;
    
                       }    
                 },
              
            ],
            "language": {
                "emptyTable": "no data found."
            },
            "width": "100%"
        });
    }
    
    function DownloadOrder(orderNo) {
        $.ajax({
            type: 'POST',
            url: '/api/OrdersDetail',      
            data: JSON.stringify(orderNo),
            contentType: "application/json",
            success: function (data) {
                if (data.success) {
                    toastr.success(data.message);
                }
                else {
                   toastr.error(data.message);
                }
            }
        });
    }

    i do get the success responce in ajax, how can i get the excel to download.

    thank you

    Saturday, November 28, 2020 4:47 PM
  • User-474980206 posted

    Don’t use Ajax. In your success function, the data parameter is the file data. JavaScript can not save this as a file.

    Instead do a form post to a new window by setting the target to _blank. It’s common to use a hidden form for this.

    If you really must use Ajax, then have the server return the file content as a dataurl. Then in JavaScript create an anchor and set its href to the dataurl. Then call the click event of the anchor.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 28, 2020 5:23 PM
  • User-642154842 posted

    Hi bruce,

    Can you give me an example of the post to a new window? the button that calls the controller is loaded on a datatable.

    thank you

    Monday, November 30, 2020 9:17 AM
  • User753101303 posted

    Hi,

    You could just us a a submit button "as usual". As the browser is handling the POST, it will process the response and based on header will see that it should a download dialog rather than replacing the current window with this content. So  there is no need to post to a new window.

    If using Ajax, the browser won't interfere and then you have yourself to add addititional code to trigger the download (the common approach is to turn the blob response to a data url and create a a link programmatically clicked to trigger the download).

    I prefer solution #1 unless I really want to trigger something else than just the download...

    Edit: seems also you are surprised by the number of rows. If not fixed a first confusing point is the code you first posted is not the same you posted later. If the later codestill doesn't work, double check that GetAll is really using this criteria.

    Monday, November 30, 2020 9:27 AM
  • User-642154842 posted

    Hi all,

    I managed to get the excel to download using your suggestions using POST.

    i have another issue with formatting: some of the cells need to be formatted to text.
    How can i format the columns:

     IXLWorksheet worksheet =
                        workbook.Worksheets.Add("Authors");
                        worksheet.Cell(1, 1).Value = "Id";
                        worksheet.Cell(1, 2).Value = "FirstName";
                        worksheet.Cell(1, 3).Value = "LastName";
                        worksheet.Cell(1, 4).Value = "Date";
                        for (int index = 1; index <= getorders.Count; index++)
                        {
                            worksheet.Cell(index + 1, 1).Value =
                            getorders[index - 1].Id;
                            worksheet.Cell(index + 1, 2).Value =
                            getorders[index - 1].FirstName;
                            worksheet.Cell(index + 1, 3).Value =
                            getorders[index - 1].LastName;
                            worksheet.Cell(index + 1, 3).Value =
                            getorders[index - 1].Date;
                        }
                        using (var stream = new MemoryStream())
                        {
                            workbook.SaveAs(stream);
                            var content = stream.ToArray();
                            return File(content, contentType, fileName);
                        }

    I tried  worksheet.Column(1).CellsUsed().SetDataType(XLDataType.Text); but it didnt work.

    thank you

    Monday, November 30, 2020 10:56 AM
  • User475983607 posted

    i have another issue with formatting: some of the cells need to be formatted to text.
    How can i format the columns:

    Just like using Excel normally, you'll need to format the column as text.  Read the ClosedXML documentation for how format cells.

    You can try placing a single quote at the start of the string which tells Excel the cell value is a string.  See Excel support.

    Monday, November 30, 2020 11:10 AM
  • User-642154842 posted

    Hi All,

    thank you for your help, i found the solution to the format issues:

     worksheet.Column(2).Style.NumberFormat.Format = "@";

    everything is working great.

    thank you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 30, 2020 11:17 AM