EPPlus: How can I move workbook property assignment commands to a helper function?

Question

To provide more context, if I simply want to export my model to an Excel file in my ActionResult, I have that worked out. I learned the commends to assign values to the workbook properties and cell formatting and the exported Excel file appears correctly.

My problem is that all that code exists within the ActionResult block. I would like to move out the code that assigns property values to a separate function. The problem I encounter is the null reference error. Below is the paste of the full ActionResult block to provide full context and in case it suggests any secondary issues I hadn't considered.

I tried creating a function in the controller private ExcelPackage AssignWorkbookProperties(ExcelPackage ep, string exportName), and while I had no compile time errors, there was a null argument exception. Essentially, the function either received nothing or returned nothing.

Is there some way I can move the blocked code into a helper function? (I've indicated with comment blocks in the code shown here).

    public ActionResult ExportToExcel()
    {
        string exportName = "FourCourseAudit";

        // This allows me to export only the columns I want.
        var exportQuery = query.Select(t => new { t.Campus, t.Student_Name, t.Course_Count });

        // epp is the model where I gather the predefined property values
        var epp = new ExportToExcelProperties();
        var prop = epp.WorkbookProperties.Where(t => t.ExportName == exportName);

        try
        {                                
            byte[] response;                
            using (var excelFile = new ExcelPackage())
            {   
                // Define worksheet data.
                var worksheet = excelFile.Workbook.Worksheets.Add("Sheet1");                    

                /* ------------------------------------------------------------------ */
                /* -------------Begin: Move to helper function ---------------------- */
                /* ------------------------------------------------------------------ */

                // Define workbook properties.
                var workbookProperties = excelFile.Workbook.Properties;

                workbookProperties.Author = HttpContext.User.Identity.Name;
                workbookProperties.Title = prop.Select(t => t.Title).ToString();
                workbookProperties.Comments = prop.Select(t => t.Comments).ToString();
                workbookProperties.Created = DateTime.Now;
                workbookProperties.Category = prop.Select(t => t.Category).ToString();


                // Define worksheet contextual data.
                worksheet.Cells["A1"].Value = "Title: ";
                worksheet.Cells["A2"].Value = "Export Date: ";

                worksheet.Cells["A1:A2"].Style.Font.Bold = true;

                worksheet.Cells["B1"].Value = prop.Select(t => t.Title).ToString();
                worksheet.Cells["B2"].Value = DateTime.Now;
                worksheet.Cells["B2"].Style.Numberformat.Format = "mm/dd/yyyy hh:mm";

                worksheet.Cells["A1:B2"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
                worksheet.Cells["A1:B2"].AutoFitColumns();

                Color bgColor = ColorTranslator.FromHtml("#2956B2");

                worksheet.Cells["A1:B2"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                worksheet.Cells["A1:B2"].Style.Fill.BackgroundColor.SetColor(bgColor);
                worksheet.Cells["A1:B2"].Style.Font.Color.SetColor(Color.White);

                worksheet.Cells["D1:F1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                worksheet.Cells["D1:F1"].Style.Fill.BackgroundColor.SetColor(bgColor);
                worksheet.Cells["D1:F1"].Style.Font.Color.SetColor(Color.White);

                /* ------------------------------------------------------------------ */
                /* ---------------End: Move to helper function ---------------------- */
                /* ------------------------------------------------------------------ */

                worksheet
                    .Cells["D1"]
                    .LoadFromCollection(Collection: exportQuery, PrintHeaders: true)                        
                    .Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);



                worksheet.Cells["A1:F200"].AutoFitColumns();

                response = excelFile.GetAsByteArray();
            }
            return File(response, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Export.xlsx");
        }
        catch (NullReferenceException) {
            return ViewBag.Errormsg = "There was a null reference exception.";
        }
        catch (ArgumentNullException)
        {
            return ViewBag.Errormsg = "There was an argument null exception.";
        }
    }

Answer 1

I found a resource that helped me figure out the problem.

The problem was that my function was returning and accepting the wrong object.

I created two different functions. The first function returns type ExcelWorksheet and the second function returns type OfficeProperties. Both functions have ExcelPackage as an input parameter.

So first, this is how clean the ActionResult is now:

    public ActionResult ExportToExcel()
    {
        string exportName = "FourCourseAudit";

        // This allows me to export only the columns I want.
        var exportQuery = query.Select(t => new { t.Campus, t.Student_Name, t.Course_Count });          

        try
        {                                
            byte[] response;                
            using (var excelFile = new ExcelPackage())
            {   
                // Use helper functions to fill worksheet and workbook definitions
                var worksheet = CreateSheet(excelFile,exportName);
                var workbook = AssignProperties(excelFile,exportName);

                // Fill worksheet with data to export
                worksheet
                    .Cells["D1"]
                    .LoadFromCollection(Collection: exportQuery, PrintHeaders: true)                        
                    .Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);

                worksheet.Cells["A1:F200"].AutoFitColumns();

                response = excelFile.GetAsByteArray();
            }
            return File(response, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Export.xlsx");
        }
        catch (NullReferenceException) {
            return ViewBag.Errormsg = "There was a null reference exception.";
        }
        catch (ArgumentNullException)
        {
            return ViewBag.Errormsg = "There was an argument null exception.";
        }
    }

For now, these two functions are in the same controller. The more I work out how to generalize them, I might move them elsewhere.

    private static ExcelWorksheet CreateSheet(ExcelPackage p,string exportName)
    {
        var epp = new ExportToExcelProperties().WorkbookProperties;
        var prop = epp.Where(t => t.ExportName == "FourCourseAudit").Select(t=>t.Title).Single();

        string sheetName = "Sheet1";
        p.Workbook.Worksheets.Add(sheetName);
        ExcelWorksheet worksheet = p.Workbook.Worksheets[1];
        worksheet.Name = sheetName; //Setting Sheet's name
        worksheet.Cells.Style.Font.Size = 11; //Default font size for whole sheet
        worksheet.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet

        // Define worksheet contextual data.
        worksheet.Cells["A1"].Value = "Title: ";
        worksheet.Cells["A2"].Value = "Export Date: ";

        worksheet.Cells["A1:A2"].Style.Font.Bold = true;

        worksheet.Cells["B1"].Value = prop;
        worksheet.Cells["B2"].Value = DateTime.Now;
        worksheet.Cells["B2"].Style.Numberformat.Format = "mm/dd/yyyy hh:mm";

        worksheet.Cells["A1:B2"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
        worksheet.Cells["A1:B2"].AutoFitColumns();

        Color bgColor = ColorTranslator.FromHtml("#2956B2");

        worksheet.Cells["A1:B2"].Style.Fill.PatternType = ExcelFillStyle.Solid;
        worksheet.Cells["A1:B2"].Style.Fill.BackgroundColor.SetColor(bgColor);
        worksheet.Cells["A1:B2"].Style.Font.Color.SetColor(Color.White);

        worksheet.Cells["D1:F1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
        worksheet.Cells["D1:F1"].Style.Fill.BackgroundColor.SetColor(bgColor);
        worksheet.Cells["D1:F1"].Style.Font.Color.SetColor(Color.White);

        return worksheet;
    }

    private static OfficeProperties AssignProperties(ExcelPackage p, string exportName)
    {
        // epp is the model where I gather the predefined property values
        var epp = new ExportToExcelProperties().WorkbookProperties;
        var query = from t in epp
                    where t.ExportName == exportName
                    select new { t.Title, t.Comments, t.Category };
        var title = query.Select(t=>t.Title).Single();
        var comments = query.Select(t=>t.Comments).Single();
        var category = query.Select(t=>t.Category).Single();

        OfficeProperties workbookProperties = p.Workbook.Properties;
        workbookProperties.Author = System.Web.HttpContext.Current.User.Identity.Name;
        workbookProperties.Title = title;
        workbookProperties.Comments = comments;
        workbookProperties.Created = DateTime.Now;
        workbookProperties.Category = category;

        return workbookProperties;
    }

This directly answers the question of how I can move the worksheet property code outside of the ActionResult, but I can clearly see there can be more done to generalize the code and make it more modular.