I have found solution for generating excel file (xlsx) on the server.On first call it deletes the file demo.xlsx if it exists and generates new demo.xlsx.
First call http://localhost:8000/api/importexport/export
it generates excel file (xlsx) and sends url for download
Second call http://localhost:8000/demo.xlsx
downloads the file. In Startup class in Configure method you must add app.UseStaticFiles();
Here is the link for solution http://www.talkingdotnet.com/import-export-xlsx-asp-net-core/
The problem with this solution is that I have two calls to server. I want in one call http://localhost:8000/api/importexport/export to directly download excel file (xlsx). I heard that its possible to download excel file (xlsx) in one call without creating file on the server(on the fly). I would be glad to see better solution in one call to the server.
Export data as an XLSX file in ASP.NET Core 3.0 You can create an Excel workbook using the following code. var workbook = new XLWorkbook(); You can then take advantage of the IXLWorkSheet interface to create and add worksheets to the workbook as shown below.
To return to Excel, in the Microsoft Query dialog box, click the Return Data button. Alternatively, on the File menu, click Return Data to Microsoft Excel. Back in Excel, the Import Data dialog box appears, where you can choose which type of report to create.
It's easy, just return File
(like of View
or Redirect
, but File
):
public async Task<ActionResult> Index()
{
var fileMemoryStream = await GenerateReportAndWriteToMemoryStream(...);
return File(
fileMemoryStream,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"report.xlsx");
}
This method is part of ControllerBase
and has several signatures, choose best that fit your needs:
File(byte[] fileContents, string contentType)
File(Stream fileStream, string contentType)
File(string virtualPath, string contentType)
File(string virtualPath, string contentType, string fileDownloadName)
File(byte[] fileContents, string contentType, string fileDownloadName)
File(Stream fileStream, string contentType, string fileDownloadName)
Here is my solution
private readonly IHostingEnvironment _hostingEnvironment;
public ImportExportController(IHostingEnvironment hostingEnvironment)
{
_hostingEnvironment = hostingEnvironment;
}
[HttpGet]
[Route("Export")]
public IActionResult Export()
{
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = @"demo.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
}
using (ExcelPackage package = new ExcelPackage(file))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Employee");
//First add the headers
worksheet.Cells[1, 1].Value = "ID";
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 3].Value = "Gender";
worksheet.Cells[1, 4].Value = "Salary (in $)";
//Add values
worksheet.Cells["A2"].Value = 1000;
worksheet.Cells["B2"].Value = "Jon";
worksheet.Cells["C2"].Value = "M";
worksheet.Cells["D2"].Value = 5000;
worksheet.Cells["A3"].Value = 1001;
worksheet.Cells["B3"].Value = "Graham";
worksheet.Cells["C3"].Value = "M";
worksheet.Cells["D3"].Value = 10000;
worksheet.Cells["A4"].Value = 1002;
worksheet.Cells["B4"].Value = "Jenny";
worksheet.Cells["C4"].Value = "F";
worksheet.Cells["D4"].Value = 5000;
package.Save(); //Save the workbook.
}
var result = PhysicalFile(Path.Combine(sWebRootFolder, sFileName), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
Response.Headers["Content-Disposition"] = new ContentDispositionHeaderValue("attachment")
{
FileName = file.Name
}.ToString();
return result;
}
If someone have better solution please post it.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With