Export DataList to Excel using Asp.net core

Question

I want to export data list to Excel format but I could not find any third party library or any refrence. I am building my project on .net core. Any one expert here to suggest any solution. Thanks

Answer 1

If cross plattform (Windows, Linux, Mac) is a major concern for you then you have to use some "pre release" stuff.

There is an issue for .NET Core support for OpenXML, which can be used to create Open XML Office Documents (e.g. XLSX), (https://github.com/OfficeDev/Open-XML-SDK/issues/65). Some work has to be done before it is ready.

Someone who had your demand as well ported it to .NET Core and published his project on GitHub (https://github.com/xrkolovos/Open-XML-SDK-for-NET-Platform-Standard). I have not tried it myself, but it may be worthwile to try.

If your application runs on Windows only, then you can build your ASP.NET Core project on top of the full .NET Framework (with the well known third party libraries for creating Excel).

Answer 2

i had posted this question almost 7 months ago and i have found the solution, so i want to share it.

add "PdfRpt.Core": "1.0.0-*" on project.json

on controller

[HttpGet("exportexcell")]
    public async Task<FileContentResult> ExportExcel()
    {
        var loggedUser = await GetCurrentUserAsync();
        var users = _userManager.Users.Select(u => new UserListVM
        {
            Id = u.Id,
            Name = u.UserName,
            Email = u.Email
        }).ToList();

        if (users == null) return null;

        //column Header name
        var columnsHeader = new List<string>{
            "S/N",
            "User Name",
            "Email"
        };
        var filecontent = ExportExcell(users, columnsHeader, "Users");
        return File(filecontent, "application/ms-excel", "users.xlsx"); ;
    }

helper method

 private static byte[] ExportExcell(List<UserListVM> data, List<string> columns, string heading)
    {
        byte[] result = null;

        using (ExcelPackage package = new ExcelPackage())
        {
            // add a new worksheet to the empty workbook
            var worksheet = package.Workbook.Worksheets.Add(heading);
            using (var cells = worksheet.Cells[1, 1, 1, 7])
            {
                cells.Style.Font.Bold = true;
                cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
                cells.Style.Fill.BackgroundColor.SetColor(Color.Green);
            }
            //First add the headers
            for (int i = 0; i < columns.Count(); i++)
            {
                worksheet.Cells[1, i + 1].Value = columns[i];
            }

            //Add values
            var j = 2;
            var count = 1;
            foreach (var item in data)
            {
                worksheet.Cells["A" + j].Value = count;
                worksheet.Cells["B" + j].Value = item.Name;
                worksheet.Cells["C" + j].Value = item.Email;
                worksheet.Cells["D" + j].Value = item.RoleNam

                j++;
                count++;
            }
            result = package.GetAsByteArray();
        }

        return result;
    }

//it work fine for me.. it may help to you too.

you can find demo here