.Net Core Export excell

Question

Hi I'm trying to make export and return excell function with ClosedXml package. It works fine on demo project. I gotta make it for original project so this is the code.It's a IActionResult

 public class HomeController : Controller
    {
        private List<Employee> employees = new List<Employee>
        {
            new Employee{ Id=1,Name="Jr.Dev",Role="Dev"},
            new Employee{ Id=2,Name="Jr.Dev2",Role="Dev2"},
            new Employee{ Id=3,Name="Jr.Dev3",Role="Dev3"},
            new Employee{ Id=4,Name="Jr.Dev4",Role="Dev4"},
            new Employee{ Id=5,Name="Jr.Dev5",Role="Dev5"},
        };



        public IActionResult Index()
        {

            return Excel();
        }
        public IActionResult Excel()
        {
            using (var workbook = new XLWorkbook())
            {

                var worksheet = workbook.Worksheets.Add("Employees");

                var currentRow = 1;

                worksheet.Cell(currentRow, 1).Value = "Id";
                worksheet.Cell(currentRow, 2).Value = "Name";
                worksheet.Cell(currentRow, 3).Value = "Role";


                foreach (var item in employees)
                {
                    currentRow++;
                    worksheet.Cell(currentRow, 1).Value = item.Id;
                    worksheet.Cell(currentRow, 2).Value = item.Name;
                    worksheet.Cell(currentRow, 3).Value = item.Role;

                }
                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    var content = stream.ToArray();
                    return File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Employee.xlsx");
                }
            }
        }

    }

It really works fine and downloads the xlsx file.

I just want to make a call from view like export excell and call it with viewmodel. How can I implement it with best practices.Thank you

What do you mean by "best"? The definition of "best" varies from person to person. — shreyasm-dev, Nov 18 '20 at 19:20

score 1 · Answer 1 · answered Nov 19 '20 at 04:17

Here is a working demo:

Index.cshtml:

Use @Html.DisplayFor to display the list data and hidden inputs could be post to the action.

@model IEnumerable<Employee>
    
<h1>Index</h1>

<form asp-action="Excel" asp-controller="Home">
    <input type="submit" value="Export" class="btn btn-primary" />
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(model => model.Name)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Role)
                </th>
            </tr>
        </thead>
        <tbody>
            @for (var i = 0; i < Model.Count(); i++)
            {
                <input hidden asp-for="@Model.ElementAt(i).Id" name="[@i].Id" />
                <tr>
                    <td>
                        @Html.DisplayFor(x => x.ElementAt(i).Name)
                        <input hidden asp-for="@Model.ElementAt(i).Name" name="[@i].Name" />
                    </td>
                    <td>
                        @Html.DisplayFor(x => x.ElementAt(i).Role)
                        <input hidden asp-for="@Model.ElementAt(i).Role" name="[@i].Role" />
                    </td>
                </tr>
            }

        </tbody>
    </table>

</form>

Controller:

public class HomeController : Controller
{
    public IActionResult Index()
    {
        //for easy testing,I hard coded the data
        List<Employee> employees = new List<Employee>
        {
            new Employee{ Id=1,Name="Jr.Dev",Role="Dev"},
            new Employee{ Id=2,Name="Jr.Dev2",Role="Dev2"},
            new Employee{ Id=3,Name="Jr.Dev3",Role="Dev3"},
            new Employee{ Id=4,Name="Jr.Dev4",Role="Dev4"},
            new Employee{ Id=5,Name="Jr.Dev5",Role="Dev5"},
        };
        //you could also get the data from database
        //var employees = _context.Employee.ToList());
       
        return View(employees);  //render the employees in the Index.cshtml
    }
    
    [HttpPost]
    public IActionResult Excel(List<Employee> employees)//receive the list from view
    {
        using (var workbook = new XLWorkbook())
        {

            var worksheet = workbook.Worksheets.Add("Employees");

            var currentRow = 1;

            worksheet.Cell(currentRow, 1).Value = "Id";
            worksheet.Cell(currentRow, 2).Value = "Name";
            worksheet.Cell(currentRow, 3).Value = "Role";


            foreach (var item in employees)
            {
                currentRow++;
                worksheet.Cell(currentRow, 1).Value = item.Id;
                worksheet.Cell(currentRow, 2).Value = item.Name;
                worksheet.Cell(currentRow, 3).Value = item.Role;

            }
            using (var stream = new MemoryStream())
            {
                workbook.SaveAs(stream);
                var content = stream.ToArray();
                return File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Employee.xlsx");
            }
        }
    }
}

Result:

.Net Core Export excell

1 Answers1