Export object to .xlsx to client machine asp.net core

Question

I am current working on export the object into .xlsx file. This is so close to what i need, Export xlsx in ASP.NET Core , but the problem is - this is export to the local project folder wwwroot, and i want export to client machine.

i had tried this.

   private readonly IHostingEnvironment _hostingEnvironment;

    public ImportExportController(IHostingEnvironment hostingEnvironment)
    {
        _hostingEnvironment = hostingEnvironment;
    }

    [HttpGet]
    [Route("Export")]
    public FileStreamResult 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.
        }


        FileStream RptStream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open, FileAccess.Read);
        return new FileStreamResult(RptStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    }

but seems like i need to export the worksheet to wwwroot folder and later on return filestreamresult with the worksheet url. and i dont know how to get the url of the worksheet to pass in filestreamresult function.

Anyone can help me? I want to export this .xlsx on client machine instead of wwwroot.

Answer 1

Try Writing the file to a memory stream and having your method return a FileContentResult object (via Controller.File Method):

var stream = new MemoryStream(package.GetAsByteArray());
return File(stream.ToArray(), "application/vnd.ms-excel", sFileName);

Answer 2

in asp.net Core there is a library you can use named "EPPlus" download its nugget by add in Package manager console "Install-Package EPPlus -Version 4.5.2.1" then use the below code in the controller

there is a comment before every line for description.

    public async Task<IActionResult>  Export()
    {
        await Task.Yield();

        //Lets we have object userInfo so we will fill it
        var list = new List<UserInfo>()
        {
            new UserInfo { UserName = "catcher", Age = 18 },
            new UserInfo { UserName = "james", Age = 20 },
        };

        // Then use system.IO.MemeoryStream
        var stream = new MemoryStream();

        //Then generate the Sheet by below code "using OfficeOpenXml;" from EPPlus nugget
        using (var package = new ExcelPackage(stream))
        {
            //name the sheet "Sheet1"
            var workSheet = package.Workbook.Worksheets.Add("Sheet1");

            // simple way
            workSheet.Cells.LoadFromCollection(list, true);

            //// mutual
            //workSheet.Row(1).Height = 20;
            //workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            //workSheet.Row(1).Style.Font.Bold = true;
            //workSheet.Cells[1, 1].Value = "No";
            //workSheet.Cells[1, 2].Value = "Name";
            //workSheet.Cells[1, 3].Value = "Age";

            //int recordIndex = 2;
            //foreach (var item in list)
            //{
            //    workSheet.Cells[recordIndex, 1].Value = (recordIndex - 1).ToString();
            //    workSheet.Cells[recordIndex, 2].Value = item.UserName;
            //    workSheet.Cells[recordIndex, 3].Value = item.Age;
            //    recordIndex++;
            //}

            package.Save();
        }
        stream.Position = 0;

        //Name the file which will download
        string excelName = $"UserInfoList-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";

        //Then download 
        return File(stream, "application/octet-stream", excelName);
    }

reference link https://www.c-sharpcorner.com/article/using-epplus-to-import-and-export-data-in-asp-net-core/