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.
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.
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.
Member
22 Points
216 Posts
Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 28, 2020 01:51 PM|bexasp|LINK
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
All-Star
52221 Points
23292 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 28, 2020 02:21 PM|mgebhard|LINK
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?
Run the code through the debugger to make sure the logic functions as you expect.
Member
22 Points
216 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 28, 2020 04:05 PM|bexasp|LINK
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.
All-Star
57874 Points
15507 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 28, 2020 04:31 PM|bruce (sqlwork.com)|LINK
What code calls the download action. Perhaps you used Ajax in which case the file data is passed to the success callcak.
Member
22 Points
216 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 28, 2020 04:47 PM|bexasp|LINK
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
All-Star
57874 Points
15507 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 28, 2020 05:23 PM|bruce (sqlwork.com)|LINK
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.
Member
22 Points
216 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 30, 2020 09:17 AM|bexasp|LINK
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
All-Star
48300 Points
18003 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 30, 2020 09:27 AM|PatriceSc|LINK
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.
Member
22 Points
216 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 30, 2020 10:56 AM|bexasp|LINK
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
All-Star
52221 Points
23292 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 30, 2020 11:10 AM|mgebhard|LINK
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.
Member
22 Points
216 Posts
Re: Export to excel in .net core 3.1 Razor pages and ClosedXML
Nov 30, 2020 11:17 AM|bexasp|LINK
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.