Dynamically Creating Excel File from a Generic List for Email Attachment and Data Downloading

• Download source (including OpenXml library) - 2.1 MB

Introduction

Excel spreadsheets are one of the most popular forms to distribute, transfer, and report data records. Approaches of converting the data to Excel with Office InterOp libraries and generating physical files are not suitable for implementing server applications. The best and most efficient scenario without any third-party tool for exporting the data to Excel files from a modern object-oriented server application could be as follows:

• The data set is obtained from databases and cached in a generic list with the underlying model object type.
• The list is converted to a byte array with Excel data format using the Open XML SDK for Office library and the memory stream structure.
• The Excel file will dynamically be generated from the byte array during the data distribution processes.

This article and the sample ASP.NET MVC 5.0 web application will demonstrate these tasks by sending out emails with an Excel file attachment and downloading an Excel file from a data page. The main page of the sample application looks like this:

List to Excel Byte Array Extension Method

In some sections of my previously article, I described extension methods for converting a data list to Excel spreadsheet shown in the window. Similarly, here, I create the List class extension method in the ExcelHelper class to convert a data list to an Excel byte array. The syntax of the method is:

public void IList.ToExcelBytes<T>([string include = ""], 
       [string exclude = ""], string columnFixes = ""], string sheetName = ""])

All arguments of the method are optional:

• include: comma delimited string containing names of columns you only need to export from the output object model. If this is specified, the exclude argument is ignored.
• exclude: comma delimited string containing names of the columns you need to exclude from the output object model for the export.
• columnFixes: comma delimited string containing final modified names of the columns by adding prefixes or suffixes. See details later.
• sheetName: string for the worksheet name displayed on the Excel spreadsheet. If not specified, the model object name will be used by default.

Below is an example of calling the extension method with the optional argument sheetName:

List<Product> dataList = LocalData.GetProductList();
Byte[] excelBytes = dataList.ToExcelBytes<Product>(sheetName: "Product List");

Excel Cell Reference Issue

Generally, there is nothing very particular for streaming the Excel Workbook object to bytes using the Open XML SDK library except an issue related to the Excel cell reference. The CellReference property in the Cell object stores the address of the cell consisting of column letter and row number indexes. Internally, the Excel holds the real data in a worksheet in the below XML form when unzipping the xslx file. Note that the r attributes in the <x:c> node have the cell reference values, such as "A1" and "B1".

<x:worksheet>
 - - -
 <x:sheetData>
    <x:row>
      <x:c r="A1" t="str">
        <x:v>Product ID</x:v>
      </x:c>
      <x:c r="B1" t="str">
        <x:v>Product Name</x:v>
      </x:c>
      - - -
   </x:row>  
   - - -
 </x:sheetData>
</x:worksheet>

The value of an r attribute could be empty if not explicitly set in the code using the Open XML SDK library. If this occurs in a code-generated file and any user opens, then saves the file with the Office Excel application, all cell references (also string table values and links) will automatically be re-populated if missing. It could, however, cause a problem if the received original Excel file will programmatically be read with the tool using the Open XML SDK, such as one posted from my previous article for importing the Excel data to a generic list. The Excel reader program will render errors if valid cell reference values are not found. Thus it’s necessary to explicitly enter the reference values for each cell during transferring data from the data list to the Excel byte array.

A cell reference value consists of two parts, the alphabetic column letter or letters and the row index numbers. The column letters, a.k.a., built-in column names, are obtained by calling the generic GetColumnAddress function (see downloaded source for details). The Excel worksheet row indexes are particular since they are number one based. The first row commonly assigned for custom column names has the index number 1 and next data row has the index number 2. The code needs to append the rowIdx + 1 for cell reference values during the iterations as shown in the below line.

//Set cell reference.
CellReference = GetColumnAddress(colIdx) + (rowIdx + 1).ToString()

Friendly Column Names and Data Format

Another important part of data list to Excel conversions is to map model object properties to Excel columns using the reflection. I don't discuss the basic mapping processes here but rather focus on the implementation of friendly column names and optimal data format for Excel spreadsheets in higher quality.

1. Creating the corresponding output model object.

   The original model object example is like this:

   public class Product
   {
       public int ProductID { get; set; }
       public string ProductName { get; set; }
       public int? CategoryID { get; set; }        
       public decimal? UnitPrice { get; set; }
       public bool OutOfStock { get; set; }
       public DateTime? StockDate { get; set; }              
   }

   The corresponding output model object has contractors referencing the original model object and all properties are read-only. There are double underscores in the property names that will be replaced with spaces during the object conversion process later. Also note that the properties with decimal or DateTime type will be returned as the string type with desired data format.

   public class ProductExcel
   {
       private Product source;        
       public ProductExcel()
       {
           this.source = new Product();
       }        
       public ProductExcel(Product source)
       {
           this.source = source;
       }
   
       //Adding double underscores for outputting friendly column names.
       //Returning desired money and date format.
       public int Product__ID { get { return source.ProductID; } }
       public string Product__Name { get { return source.ProductName; } }
       public int? Category__ID { get { return source.CategoryID; } }
       public string Unit__Price { get { return source.UnitPrice != null ? 
                     source.UnitPrice.Value.ToString("0,0.00") : null; } }
       public bool Out__of__Stock { get { return source.OutOfStock; } }
       public string Stock__Date { get { return source.StockDate != null ? 
                     source.StockDate.Value.ToShortDateString() : null; } }              
   } 

2. Converting the original object to the output object by a mapping function before calling the List ToExcelBytes() extension method. The object conversion approach shown here is more direct and less overhead than the tools like AutoMapper but easier coding and maintainable than the pure manual mapping.

   //Convert to Excel output model for friendly column names and desired data format.
   private List<ProductExcel> ConvertToProductExcel(List<Product> inputList)
   {
       List<ProductExcel> outputList = new List<ProductExcel>();
       foreach (var item in inputList)
       {
           ProductExcel outputItem = new ProductExcel(item);
           outputList.Add(outputItem);
       }
       return outputList;
   }

3. During iterations for model object properties in the List ToExcelBytes() extension method, modify the output column names to those with the friendly name format:

   //Replace possible double underscores for friendly column names.
   var colName = prop.Name.Replace("__", " ");
                           
   //Use column names with added prefix or suffix items. 
   if (colFixList != null)
   {
       foreach (var item in colFixList)
       {
           if (item.Contains(colName))
           {
               colName = item;
               break;
           }
       }
   }

Attaching File to Email

The downloaded source provides a complete example for how to attach an Excel file dynamically generated from a byte array containing the Excel data.

1. Creating a model object, EmailForm, for email parameters. The ContextId property holds the value that uniquely defines the data context, such as an ID of a particular user, company, department, order, or invoice, etc. It’s a kind of data search criteria. For example, the ContextId could be a department ID of a store that can be used for filtering the Product data. For the simplicity of the demo, the value of the ContextId is manually set to 0.

   public class EmailForm
   {
       //Data Annotations for model validation not shown here
       //but implemented in the download source.
       public int ContextId { get; set; }
       public string From { get; set; }
       public string To { get; set; }
       public string CC { get; set; }
       public string Bcc { get; set; }
       public string Subject { get; set; }
       public string Message { get; set; }        
   }

2. An on-demand email can be initiated by using the Email Excel button on the ASP.NET MVC data page as shown in the first screenshot.

3. By clicking the Email Excel button, the email form will be loaded on a popup dialog with default entries obtained from the configuration and template files. See the details of JavaScript/JQuery code in the ProductList.js and server side C# code in the ProductsController.cs files. You may also see the details of the jqsDialog plugin for dynamically loading dialog content in my previous article if interested.

   

4. Clicking the Send button on the dialog will submit the email forms to the server and call the action method in the controller:

   public JsonResult SendEmailForProduct(EmailForm emailForm)
   {
       //Set file type format and name.
       var fileFormat = string.Format("Products-{0}-{1}.xlsx", 
           emailForm.ContextId.ToString(), DateTime.Now.ToString("MMddyyyyHHmmssfff"));
               
       //Send out email and attach the file via byte stream. 
       Emailer.SendEmail(emailForm, GetExcelBytesForProduct, fileFormat);
       return Json(new { EmailCode = "0" });
   }

   The method defines the output file format and then calls the generic SendEmail method by passing the emailForm object, the GetExcelBytesForProduct function as the delegation, and the fileFormat settings.

   Inside the GetExcelBytesForProduct function, the code will call the ConvertToProductExcel function for model object conversion, modify any column name if needed, and generate the Excel byte array from the data list. The contextId argument can be used to retrieve the data list if any filter is needed although the value of the contextId is left aside here.

   private byte[] GetExcelBytesForProduct(int contextId)
   {    
       //Call to convert Product to ProductExcel for output.            
       var dataList = ConvertToProductExcel(LocalData.GetProductList());            
   
       //Comma delimited string for outputting final column names 
       //with added suffixes if needed.
       //ToExcelBytes() will search and replace final column names.
       var colFixes = "Unit Price ($)";
       
       //Using custom sheet name, not default model object name.
       var sheetName = "Product List";
   
       return dataList.ToExcelBytes<ProductExcel>
              (columnFixes: colFixes, sheetName: sheetName);	
   }

   Now let’s see the code in the Emailer.SendEmail method that adds the attachment to the email with the byte stream. Note that the value of the EmailForm.ContextId is passed to the delegated function as the search criterion for retrieving the data list there.

   public static void SendEmail
   (EmailForm eForm, Func<int, byte[]> method, string attachFileFormat)        
   {            
       Attachment attachment = null;            
       using (MailMessage mail = new MailMessage())
       {
           //Setting up general email parameters.
           //... (see details in downloaded source).
   
           if (method != null && !string.IsNullOrEmpty(attachFileFormat))
           {                    
               //Add attachment from byte stream.
               using (MemoryStream stream = new MemoryStream(method(eForm.ContextId)))
               {
                   if (stream.Length > 0)
                   {
                       attachment = new Attachment(stream, attachFileFormat);
                       if (attachment != null)
                           mail.Attachments.Add(attachment);
                   }
                   //Sent out email.
                   using (SmtpClient smtp = new SmtpClient())
                   {
                       smtp.Send(mail);
                   }
               }
           }                              
       }                          
   }

5. Checking the email attachment by delivering emails to a local directory without needing an SMTP service set on a server box. The sample application sets the local delivery folder to the “c:\smtp” in the web.config file. If the folder doesn’t exist, the SmtpClient.Send method will render an error. You need to add the folder to your local machine or set your own delivery folder with different location and names.

   <system.net>  
     <mailSettings>
       <!--Test without email server-->
       <smtp deliveryMethod="SpecifiedPickupDirectory" from="local@devtest.com">
         <specifiedPickupDirectory pickupDirectoryLocation="c:\smtp\"/>
       </smtp>
     </mailSettings>
   </system.net>

   When opening the delivered email file with extension "eml" in the Office Outlook, you can see the Excel file attached to the email as shown below:

   

Downloading Excel File from Web Page

Getting the file with byte array sources from an ASP.NET MVC page is simpler than you might imagine. Clicking the Download Excel button on the data page of the sample website will call the JavaScript function that creates a dynamic HTML form and submits the form to the server side. Any other data item can be sent to the server using a hidden input element as the example for the contextId.

downloadExcel: function (container, contextId) {
    var form = $(document.createElement("form"))
        .attr("action", "/Products/DownloadExcelForProduct")
        .attr("method", "POST").attr("id", "frmExportToExcel")
        .attr("name", "frmExportToExcel").attr("target", "new");
    var hdnContent = $(document.createElement("input"))
        .attr("type", "hidden").attr("id", "hdnContextId")
        .attr("name", "hdnContextId")
        .val(contextId);
    form.append(hdnContent[0]);
    document.body.appendChild(form[0]);
    form.submit();           
}

The server action method receives the request, calls the GetExcelBytesForProduct method directly instead of using the delegation to generate the Excel byte array from the data list, and then returns the ActionResult File type. Note that the contentType parameter is set to the full qualified Open XML format definition instead of the commonly used “vnd.ms-excel”. Otherwise, browsers other than IE would treat the file as the old Excel 2003 format and add additional “xls” extension to the file during the downloading process.

public ActionResult DownloadExcelForProduct()
{
    //Data can be passed with hidden input elements.
    string contextIdStr = Request.Form["hdnContextId"];
    int contextId = 0;
    Int32.TryParse(contextIdStr, out contextId);

    //Call to get Excel byte array.
    var excelBytes = GetExcelBytesForProduct(contextId);            
    
    //Set file name.
    var fileName = string.Format("Products-{0}-{1}.xlsx", 
                   contextId, DateTime.Now.ToString("MMddyyyyHHmmssfff"));

    //Return file with the type and name.
    //ContentType "application/vnd.ms-excel" does not work well for browsers other than IE. 
    return excelBytes != null ? File(excelBytes, 
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName) : null;  
}

The response stream containing the file will be sent back to the browser which then automatically prompts the user for opening the file in the Excel application if installed locally, or saving the file to any location.

Clicking the Open button on the dialog will automatically start the Excel application and show the spreadsheet containing the data in the generic list with the model object type.

Summary

Although exporting data to an Excel spreadsheet is an old topic, processes using an Excel byte array converted from a generic list with the Open XML SDK are revitalizing this area. The List collection with its model object type is powerful, flexible, and true OOP as cached data sources mediates the exporting tasks. In addition, the approaches of dynamically creating files for an email attachment or downloading from a website described in the article not only apply to the Excel file type, but can also be extended to other file types, such as CSV and PDF, depending on the byte array sources and content types.

History

• 26^th June, 2014: Initial version