This article and the sample ASP.NET MVC 5.0 web application will demonstrate how to convert a generic list to byte array in Excel by using Open XML SDK. You will also see how to dynamically create an Excel file for data distributions from an ASP.NET MVC website.
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.
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.
-
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;
}
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; } }
}
-
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.
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;
}
-
During iterations for model object properties in the List
ToExcelBytes()
extension method, modify the output column names to those with the friendly name format:
var colName = prop.Name.Replace("__", " ");
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.
-
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
{
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; }
}
-
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.
-
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.
- 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)
{
var fileFormat = string.Format("Products-{0}-{1}.xlsx",
emailForm.ContextId.ToString(), DateTime.Now.ToString("MMddyyyyHHmmssfff"));
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)
{
var dataList = ConvertToProductExcel(LocalData.GetProductList());
var colFixes = "Unit Price ($)";
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())
{
if (method != null && !string.IsNullOrEmpty(attachFileFormat))
{
using (MemoryStream stream = new MemoryStream(method(eForm.ContextId)))
{
if (stream.Length > 0)
{
attachment = new Attachment(stream, attachFileFormat);
if (attachment != null)
mail.Attachments.Add(attachment);
}
using (SmtpClient smtp = new SmtpClient())
{
smtp.Send(mail);
}
}
}
}
}
-
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>
<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()
{
string contextIdStr = Request.Form["hdnContextId"];
int contextId = 0;
Int32.TryParse(contextIdStr, out contextId);
var excelBytes = GetExcelBytesForProduct(contextId);
var fileName = string.Format("Products-{0}-{1}.xlsx",
contextId, DateTime.Now.ToString("MMddyyyyHHmmssfff"));
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
- 26th June, 2014: Initial version