I have a pretty badly designed system on my hands right now written in ASP.NET MVC.

It does have a lot of logic implemented inside controllers without any outside models with business logic. I would like to change it a little and write a class that would be able to export some tables from views to Excel. Most of the views that would require this functionality do not have anything in common and I am not sure how to approach it.

Can I for example pass the database context (Entity Framework) from the controller as a parameter or should I get model information inside the controller and pass it to the method? Should I create a class model that would get all the information I would need to create an Excel file and pass information to it inside the controller?

  • Should I create a class model that would get all the information Doesn't the actual code have a model for drawing the table in the front end? – Laiv Jul 26 '17 at 11:05
  • No, every table looks completely different and is based on different model – feni000 Jul 26 '17 at 11:06
  • And I guess there's no hierarchy at all among these models. Right? – Laiv Jul 26 '17 at 11:07
  • No :), its more like one view is for worksheet with holidays and stuff like that and other view is for example for invoices and so that are not based at all on what is in worksheet. – feni000 Jul 26 '17 at 11:09

There are several ways to do this. An accurate answer could take us to know in more details the actual design. However, I have had to do something similar recently that worked fine and took me fewer components than expected.

First of all, regarding the 2 possible options, the 2nd seems to me the cleaner because it allows you to decouple the actual model from the new feature. I usually avoid letting Controllers get access to the persistence layer, so at the moment I would not go that way (#1).

As I commented, I have implemented something similar recently. At the time of making the decision, I decided to implement the less typed solution possible. By typed I mean, the solution that fewer classes or interfaces were required. That led me to approach the problem from a convention over configuration point of view. I got the idea looking at how we were supporting other representation formats such as JSON or XML.

The goal is to reuse the actual model in such a way that we don't have to modify it drastically, alter its current hierarchy or wrap it up with new classes.

Instead of typing the solution with new interfaces or classes -that in most of the cases are not going to get along with our current model-3, we use Attributes or Annotations.

Due to I'm not familiar with C#, if you don't mind, I will illustrate the answer with Java and @Annotations.

@JacksonXmlRootElement(localName = "kpi-accounts")
@XlsxSheetProperty(name = "accounts")
public class KPIAccountsTO implements Serializable {

    private static final long serialVersionUID = 1L;    
    private int active;
    private int onBoardingPending;
    private int notActive;

    // Constructor and setters omitted for brevity ...

    @JacksonXmlProperty(localName = "active")
    @JsonProperty(value = "active", index = 0)
    @XlsxColumnProperty(name = "active", index = 0)
    public int getActive() {
        return active;

    @JacksonXmlProperty(localName = "on-boarding-pending")
    @JsonProperty(value = "on-boarding-pending", index = 1)
    @XlsxColumnProperty(name = "on-boarding-pending", index = 1, widthModifier = 2)
    public int getOnBoardingPending() {
        return onBoardingPending;

    @JacksonXmlProperty(localName = "not-active")
    @JsonProperty(value = "not-active", index = 2)
    @XlsxColumnProperty(name = "not-active", index = 2)
    public int getNotActive() {
        return notActive;


Pay attention to the annotations @XlsxColumnPropertyand @XlsxSheetProperty. 1

Usually, I don't advocate in favour of reflection but, in this case it just works like a charm.

Loading @XlsxSheetProperty for building the sheet and its metadata.

public static XSSFSheet createDefaultSheet(XSSFWorkbook book, Class<?> clazzHeader) {
        XlsxSheetProperty sheetAnnotation = clazzHeader.getAnnotationsByType(XlsxSheetProperty.class)[0];
        XSSFSheet sheet = book.createSheet(sheetAnnotation.name().toUpperCase());
        createRowHeder(sheet, clazzHeader);
        return sheet;

Loading @XlsxColumnProperty for populating the header. 2

private static XSSFRow createRowHeder(XSSFSheet sheet, Class<?> clazzHeader) {
        XSSFRow header = sheet.createRow(0);            
        Method[] methods = Arrays.stream(clazzHeader.getMethods())
                .filter(method -> method.isAnnotationPresent(XlsxColumnProperty.class))
                // Sorted by index
                .sorted((m1, m2) -> Integer.compare(
                        // m1
                        // m2
                .toArray(size -> new Method[size]);

        //TODO: Move the stream pipeline
        for (int idx = 0; idx < methods.length; idx++) {
            XlsxColumnProperty annotation = methods[idx].getAnnotation(XlsxColumnProperty.class);
            createDefaultCell(idx, header).setCellValue(annotation.name().toUpperCase());
            sheet.setColumnWidth(idx, sheet.getColumnWidth(idx) * annotation.widthModifier());
        return header;

Remain to be solved how to map the entity fields to the sheet columns. The order, as you can see, is already provided by the annotation so, last to decide how to get the values. We have two options here, mappers or reflection again. If the export entity is complex I tend to implement mappers, for flat POJOs like the one exposed here, reflection is sufficient.

Note: Following this approach allowed me to build workbooks with different sheets, where each which is modelled by a different POCO.

1: Note that this TO belong to an API data model. My API actually supports several formats -among them JSON and XML-. However, it could be also the view model of any MVC.

2: Don't you pay too much attention to the implementation details. I'm still refactoring the code.

3: the more heterogeneous, the harder

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.