How to use [Display(Name="")] as column headers with LoadFromCollection

Question

In my MVC 5 site, I have an Export to Excel function that is stable, but the users have requested that I make the column headers "user friendly."

In my model, I use the [Display(Name="Friendly Column Name")] annotations, and they appear as intended on the View pages, but they do not carry over to the exported Excel file when the user triggers the export function. Instead, the true names appear.

I know that I can load the worksheet cells through another method than LoadFromCollection, but I was wondering if it is possible to still use LoadFromCollection and use the model's Display(Name()) annotations.

This is what I currently have:

public ActionResult ExportToExcel(string _selectedCampus)
    {
        // This is the query result set the user wishes to export to file.
        IEnumerable<Mia1aSec1FayExport> exportQuery = unitOfWork
            .Mia1aSec1FayRepository.Get().Select(n => new Mia1aSec1FayExport
        {
            Campus = n.Campus,
            StudentName = n.StudentName,
            CreditsBeforeSchoolYear = n.CreditsBeforeSchoolYear,
            CreditsDuringSemester1 = n.CreditsDuringSemester1,
            EligibleFayCount = n.EligibleFayCount,
            EligibleFayMeetingGoal = n.EligibleFayMeetingGoal
        }).Where(n => n.Campus == _selectedCampus).OrderBy(n => n.StudentName)
        .AsEnumerable();

        // The current iteration saves the table contents, but without the   
        // [Display{Name"xxx)] annotation from the model.

        byte[] response;   
        using (var excelFile = new ExcelPackage())
        {
            excelFile.Workbook.Properties.Title = "MIA 1A (i) Eligible FAY Students";                
            var worksheet = excelFile.Workbook.Worksheets.Add("Sheet1");                   
            worksheet.Cells["A1"]
                .LoadFromCollection(Collection: exportQuery, PrintHeaders: true);
            response = excelFile.GetAsByteArray();
        }

        // Save dialog appears through browser for user to save file as desired.
        return File(response, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 
            "Mia1aSec1Fay.xlsx");
    }

An workaround is to overwrite the header cells, but this is undesirable for two reasons: (1) I already have the column names written as annotations in the model, meaning that I now have the same information written in two different places, and (2) I have to manually keep the information in sync, in case the users want more changes, which I might forget to do.

// This loads teh table as seen by the user in the index view.
            worksheet.Cells["A1"].LoadFromCollection(Collection: exportQuery, PrintHeaders: true);

            // Workaround overwrite header cells, as I'm not able to use Display Name annotation.
            worksheet.Cells[1, 1].Value = "Campus";
            worksheet.Cells[1, 2].Value = "Student Name";
            worksheet.Cells[1, 3].Value = "Credits Before SY";
            worksheet.Cells[1, 4].Value = "Credits During Semester 1";
            worksheet.Cells[1, 5].Value = "Legible Population";
            worksheet.Cells[1, 6].Value = "Eligible Students Earning 2+ Credits";                               

That being written, I truly hope that this isn't the only option. There has to be a way to write the Display Name values in the headers rather than the true name.

Mohn

Are you trying to color the headers or set some kind of formatting?there is worksheet.Cells[1, 1].AutoFitColumns() to adjust cells against contents – Parquetry

No. I am trying to replace the name of the column header with the "display name" in the model. I am not doing anything with formatting. You'll notice in my second code block what I did as an undesirable workaround. That is the result I need. What I want to do is use the information that is already in the model data annotations to do that. – Mohn

Answer 1

LoadFromCollection only responds to the DisplayName or the Description attribute and not the Display attribute.

So you could try adding one of these attributes to your current properties.

[DisplayName("Friendly Column Name")]
[Display(Name = "Friendly Column Name")]
public string StudentName { get; set; }

Also make sure you have the PrintHeaders parameter to true when calling LoadFromCollection, but you say the true names appear, so that's probably already OK.

Square

Using [DisplayName("User-friendly name")] worked perfectly. I needed to includeusing System.ComponentModel. – Mohn

Since then I learned how to use View Models. I have one class for Epplus export and a separate one for the Model, which gives me the opportunity make a clearer separation between Excel file appearance and View appearance. – Mohn

Answer 2

#Exporting records to csv file.
1. Create a model class with the headers

               public class EmployeeDetails 
                {
                     [Name("ID")]
                    public string id{ get; set; }
            
                     [Name("Name")]
                    public string name{ get; set; }
            
                     [Name("Designation")]
                    public string  designation{ get; set; }
                }

2.Export using CSV Helper class

       var data = new EmployeeDetails();
       List<EmployeeDetails> rows = new List<EmployeeDetails>() { data };
                   using (var memoryStream = new MemoryStream())
                   using (var streamWriter = new StreamWriter(memoryStream))
                   using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.CurrentCulture))
                   {
                       csvWriter.WriteHeader(typeof(EmployeeDetails));
                       csvWriter.NextRecord();
                       csvWriter.WriteRecords(rows);
       
                       csvWriter.Flush();
                       streamWriter.Flush();
                       memoryStream.Position = 0;
       
                       return memoryStream;
                   }  

Adar