动态创建 Excel 文件，用于电子邮件附件和数据下载
使用 Open XML SDK 将通用列表转换为 Excel 字节数组，并动态创建 Excel 文件以从 ASP.NET MVC 网站分发数据
引言
Excel 电子表格是分发、传输和报告数据记录的最常用格式之一。使用 Office InterOp 库将数据转换为 Excel 并生成物理文件的处理方法不适用于实现服务器应用程序。对于现代面向对象的服务器应用程序，在没有任何第三方工具的情况下将数据导出到 Excel 文件的最佳且最高效的场景可以是以下几种：
- 数据集从数据库中获取，并以基础模型对象类型的通用列表形式缓存。
- 使用 Open XML SDK for Office 库和内存流结构将列表转换为具有 Excel 数据格式的字节数组。
- Excel 文件将在数据分发过程中从字节数组动态生成。
本文和示例 ASP.NET MVC 5.0 Web 应用程序将通过发送带有 Excel 文件附件的电子邮件以及从数据页面下载 Excel 文件来演示这些任务。示例应用程序的主页如下所示：
列表到 Excel 字节数组的扩展方法
在我之前的一篇文章的某些部分，我描述了将数据列表转换为窗口中显示的 Excel 电子表格的扩展方法。同样，在这里，我在 `ExcelHelper` 类中创建了 `List` 类扩展方法，以将数据列表转换为 Excel 字节数组。该方法的语法如下：
public void IList.ToExcelBytes<T>([string include = ""],
[string exclude = ""], string columnFixes = ""], string sheetName = ""])
方法的所有参数都是可选的
include：逗号分隔的字符串，包含您只需要从输出对象模型中导出的列名。如果指定了此参数，则忽略 `exclude` 参数。exclude：逗号分隔的字符串，包含您需要从输出对象模型中排除以进行导出的列名。columnFixes：逗号分隔的字符串，包含列的最终修改名称，通过添加前缀或后缀。稍后详见。sheetName：用于在 Excel 电子表格上显示的 Excel 工作表的 `string`。如果未指定，则默认使用模型对象名称。
以下是使用可选参数 `sheetName` 调用扩展方法的示例：
List<Product> dataList = LocalData.GetProductList();
Byte[] excelBytes = dataList.ToExcelBytes<Product>(sheetName: "Product List");
Excel 单元格引用问题
通常，使用 Open XML SDK 库将 Excel 工作簿对象流式传输到字节数组并没有什么特别之处，除了一个与 Excel 单元格引用相关的问题。`Cell` 对象中的 `CellReference` 属性存储单元格的地址，该地址包含列字母和行号索引。在内部，Excel 在解压缩 xslx 文件时，会在工作表中以以下 XML 形式保存实际数据。请注意，<x:c> 节点中的 `r` 属性具有单元格引用值，例如“**A1**”和“**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>
如果在使用 Open XML SDK 库的代码中没有显式设置，则 `r` 属性的值可能为空。如果这种情况发生在代码生成的 Excel 文件中，并且用户打开并使用 Office Excel 应用程序保存该文件，那么所有单元格引用（以及字符串表值和链接）将在缺失时自动重新填充。然而，如果接收到的原始 Excel 文件将通过使用 Open XML SDK 的程序化工具进行读取，例如我之前文章中用于将 Excel 数据导入通用列表的工具，则可能会出现问题。如果找不到有效的单元格引用值，Excel 读取器程序将显示错误。因此，在将数据从数据列表传输到 Excel 字节数组期间，有必要为每个单元格显式输入引用值。
单元格引用值包含两部分：字母列或列以及行索引号。列字母（也称为内置列名）通过调用通用 `GetColumnAddress` 函数获得（详情请参阅下载的源代码）。Excel 工作表行索引比较特殊，因为它们是从 1 开始计数的。通常用于自定义列名的第一行索引号为 1，下一数据行的索引号为 2。在迭代期间，代码需要为单元格引用值附加 `rowIdx + 1`，如下行所示：
//Set cell reference.
CellReference = GetColumnAddress(colIdx) + (rowIdx + 1).ToString()
友好列名和数据格式
数据列表到 Excel 转换的另一个重要部分是使用反射将模型对象属性映射到 Excel 列。这里不讨论基本的映射过程，而是侧重于实现友好列名和最佳数据格式，以获得更高质量的 Excel 电子表格。
-
创建相应的输出模型对象。
原始模型对象示例如下：
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; } }相应的输出模型对象具有引用原始模型对象的构造函数，并且所有属性都是只读的。属性名中存在双下划线，将在后续的对象转换过程中用空格替换。另请注意，具有 `decimal` 或 `DateTime` 类型的属性将返回为 `string` 类型，并带有所需的数据格式。
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; } } } -
在调用 `List` `ToExcelBytes()` 扩展方法之前，通过映射函数将原始对象转换为输出对象。此处所示的对象转换方法比 AutoMapper 等工具更直接，开销更小，并且比纯手动映射更容易编写和维护。
//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; } -
在 `List` `ToExcelBytes()` 扩展方法中迭代模型对象属性时，将输出列名修改为友好名称格式：
//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; } } }
将文件附加到电子邮件
下载的源代码提供了一个完整的示例，说明如何附加一个由包含 Excel 数据的字节数组动态生成的 Excel 文件。
-
创建一个用于电子邮件参数的模型对象 `EmailForm`。`ContextId` 属性保存唯一标识数据上下文的值，例如特定用户、公司、部门、订单或发票等的 ID。这是一种数据搜索条件。例如，`ContextId` 可以是商店的部门 ID，用于过滤 `Product` 数据。为了演示的简单性，`ContextId` 的值手动设置为 `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; } } -
可以通过单击 ASP.NET MVC 数据页面上的“**Email Excel**”按钮来启动按需电子邮件，如第一个屏幕截图所示。
-
通过单击“**Email Excel**”按钮，将在弹出对话框中加载电子邮件表单，其中包含从配置文件和模板文件中获取的默认条目。如果您有兴趣，可以查看 ProductList.js 中的 JavaScript/JQuery 代码以及 ProductsController.cs 文件中的服务器端 C# 代码的详细信息。您也可以查看我之前文章中关于动态加载对话框内容的 `jqsDialog` 插件的详细信息。
- 单击对话框上的“**Send**”按钮会将电子邮件表单提交到服务器并调用控制器中的操作方法。
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" }); }该方法定义了输出文件格式，然后通过传递 `emailForm` 对象、`GetExcelBytesForProduct` 函数作为委托以及 `fileFormat` 设置来调用通用的 `SendEmail` 方法。
在 `GetExcelBytesForProduct` 函数中，代码将调用 `ConvertToProductExcel` 函数进行模型对象转换，根据需要修改任何列名，并从数据列表中生成 Excel 字节数组。`contextId` 参数可用于检索数据列表（如果需要任何过滤器），尽管此处 `contextId` 的值被忽略了。
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); }现在让我们看看 `Emailer.SendEmail` 方法中的代码，该方法将附件添加到带有字节流的电子邮件中。请注意，`EmailForm.ContextId` 的值将作为搜索条件传递给委托函数，以便在那里检索数据列表。
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); } } } } } -
通过将电子邮件传递到本地目录来检查电子邮件附件，而无需在服务器上设置 SMTP 服务。示例应用程序在 web.config 文件中将本地传递文件夹设置为“*c:\smtp*”。如果文件夹不存在，`SmtpClient.Send` 方法将显示错误。您需要在本地计算机上添加该文件夹，或者设置您自己的具有不同位置和名称的传递文件夹。
<system.net> <mailSettings> <!--Test without email server--> <smtp deliveryMethod="SpecifiedPickupDirectory" from="local@devtest.com"> <specifiedPickupDirectory pickupDirectoryLocation="c:\smtp\"/> </smtp> </mailSettings> </system.net>在 Office Outlook 中打开扩展名为“*eml*”的已传递电子邮件文件时，您可以看到 Excel 文件作为附件附加到电子邮件中，如下所示：
从网页下载 Excel 文件
从 ASP.NET MVC 页面获取带有字节数组源的文件比您想象的要简单。单击示例网站数据页面上的“**Download Excel**”按钮将调用 JavaScript 函数，该函数创建一个动态 HTML 表单并将其提交到服务器端。可以使用隐藏输入元素作为 `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();
}
服务器操作方法接收请求，直接调用 `GetExcelBytesForProduct` 方法而不是使用委托来从数据列表中生成 Excel 字节数组，然后返回 `ActionResult` `File` 类型。请注意，`contentType` 参数设置为完整的 Open XML 格式定义，而不是常用的“`vnd.ms-excel`”。否则，除 IE 以外的浏览器会将文件视为旧版 Excel 2003 格式，并在下载过程中为文件添加额外的“xls”扩展名。
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;
}
包含文件的响应流将被发送回浏览器，然后浏览器将自动提示用户在本地安装的 Excel 应用程序中打开文件，或者将文件保存到任何位置。
单击对话框上的“**Open**”按钮将自动启动 Excel 应用程序，并显示包含通用列表中的数据（使用模型对象类型）的电子表格。
摘要
尽管将数据导出到 Excel 电子表格是一个老话题，但使用 Open XML SDK 从通用列表转换的 Excel 字节数组进行处理正在重振这一领域。`List` 集合及其模型对象类型非常强大、灵活且真正面向对象，作为缓存的数据源可以促进导出任务。此外，本文中描述的动态创建文件以用作电子邮件附件或从网站下载的方法不仅适用于 Excel 文件类型，还可以根据字节数组源和内容类型扩展到其他文件类型，例如 CSV 和 PDF。
历史
- 2014 年 6 月 26 日：初始版本