使用 ASP.NET MVC 和 OpenXML API 流式传输 Excel 文件。
0/5 (0投票)
我过去几周一直埋头于 Scrum，有一段时间没有更新我的博客了，所以我想如果我想保持你的
我过去几周一直埋头于 Scrum，有一段时间没有更新我的博客了，所以我想如果我要保持你的兴趣，我应该给你一些真正有用的东西。我们一直在从 Web Forms 过渡到 ASP.NET MVC，我们有一个需求，需要将网格内容从我们的一个 ASP.NET MVC 页面导出到 Excel 电子表格，并提示用户打开或保存。
经过一番挖掘，我对移植旧解决方案的想法并不满意，所以我做了一些研究。打开 Excel 电子表格并不是什么新鲜事，但我自己有一些要求。
- 我想从我的 ASP.NET MVC 控制器调用该函数。
- 我想从我的模型中发送一个数据集合。
- 我想尽量减少硬盘 I/O。
- 我想添加自定义标题。
- 我想给我的 Excel 文件一个有意义的名称。
- 我想给我的电子表格一个有意义的名称，比如与用户从中导出的页面名称匹配。
- 我希望能够扩展该类以添加自定义样式。
- 我想为特定类型的数据格式化列
- 我希望 Excel 立即识别文件。我们不能让任何“无法识别的文件类型”提示屏幕困扰我们的客户。
- 最后，我想将它与 Web Forms 一起使用。
很明显，通过将文件保存到内存流可以最大限度地减少 I/O，但我的其他要求呢？
我将引导您完成以下各节中的步骤并提供源代码。
第 1 步 扩展控制器。 Steve Sanderson 的博客有一篇关于扩展控制器并将 Excel 文件作为动作结果返回的优秀文章。 Phil Haack 也有一个关于该主题的优秀帖子，你可以在此处找到： Steve 的示例使用 DataContext 和 XML 流式传输文件，但 Excel 不识别文件并提示您一个恼人的屏幕。 Excel 最终会打开文件，看起来很完美，但我真的想消除这个提示。我还想能够从模型中传入自己的数据并本地化标题和数据。我大量借鉴了 Steve 的教程，并稍作修改。那么，我如何让 Excel 正常工作呢？
我必须学习一些 Excel 兼容性才能使事情正常运行，所以我从 Office Developer's Kit 开始，追溯到 Excel 97。如果你有几个小时或一个空闲的周末，我建议你阅读 BIFF8。虽然 BIFF8 在 Office Developer's SDK 中有很好的文档，而且我最终确实成功地以 BIFF8 流式传输了文件，但我发现它在添加附加功能时几乎无法理解，坦率地说，我不想成为我小组中 Excel 电子表格知识的守护者，这最终会发生。这个解决方案需要扩展，因为我与许多其他开发人员合作，随着项目的增长，他们将有更复杂的需求。我偶然发现了 Microsoft 的 OpenXML API API 和 James Westgate 的 OOXML 扩展，然后我就开始运行了。有关 OpenXml 和文件格式的更多信息此处。
所以总结一下，我将扩展我的 ASP.NET MVC 控制器，以便我的所有控制器都可以访问该功能，我将从我的模型中发送数据，接下来我将创建一个带样式标题的自定义电子表格和工作簿，最后我将为 Excel 文件和工作表指定一个有意义的名称。
步骤 1。 扩展控制器。
如果你读过 Steve 或 Phil 的博客（我鼓励你先阅读这些博客），这看起来像是很标准的东西，没有什么神奇的事情发生。
namespace YourNamespaceGoesHere.Controllers.Extensions
{
using System.Linq;
using System.Web.Mvc;
using YourNamespaceGoesHere.Controllers.Results;
/// <summary>
/// Excel 控制器扩展类。
/// </summary>
public static class ExcelControllerExtensions
{
/// <summary>
/// 控制器扩展：返回一个 Excel 结果构造函数，用于从行返回值。
/// </summary>
/// <param>此控制器。</param>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>Excel 行值。</param>
/// <returns>动作结果。</returns>
public static ActionResult Excel(this Controller controller, string fileName, string excelWorkSheetName, IQueryable rows)
{
return new ExcelResult(fileName, excelWorkSheetName, rows);
}
/// <summary>
/// 控制器扩展：Excel 结果构造函数，用于从行和标题返回值。
/// </summary>
/// <param>此控制器。</param>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>Excel 行值。</param>
/// <param>Excel 标题值。</param>
/// <returns>动作结果。</returns>
public static ActionResult Excel(this Controller controller, string fileName, string excelWorkSheetName, IQueryable rows, string[] headers)
{
return new ExcelResult(fileName, excelWorkSheetName, rows, headers);
}
/// <summary>
/// 控制器扩展：Excel 结果构造函数，用于从行、标题和行键返回值。
/// </summary>
/// <param>此控制器。</param>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>Excel 行值。</param>
/// <param>Excel 标题值。</param>
/// <param>行集合的键值。</param>
/// <returns>动作结果。</returns>
public static ActionResult Excel(this Controller controller, string fileName, string excelWorkSheetName, IQueryable rows, string[] headers, string[] rowKeys)
{
return new ExcelResult(fileName, excelWorkSheetName, rows, headers, rowKeys);
}
}
}
我有 3 个重载。
- 重载 1 接受 Excel 文件名和一个 IQueryable 行集合。
- 重载 2 接受 Excel 文件名、工作表名、IQueryable 行集合和标题数组。
- 此方法允许单独传入标题，但它们必须与行的键匹配。
- 重载 3 接受 Excel 文件名、工作表名、IQueryable 行集合、标题数组和行键集合。
- 此方法允许您为行使用不同的标题名称。
步骤 3 扩展动作结果。此类存储构造时传入的值。如果您检查此类，您会发现它覆盖了 ExcecuteResult 函数，并添加了创建 Excel 文件并将其保存到内存流的功能。这是一个非常巧妙的技术，我特意将文件流式传输的功能与文件创建的功能分开，以便我的 Excel 类可以在非 MVC 应用程序中重用。
namespace YourNamespaceGoesHere.Controllers.Results
{
using System;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using YourNamespaceGoesHere.Controllers.ControllerExtensions;
/// <summary>
/// Excel 结果类
/// </summary>
public class ExcelResult : ActionResult
{
/// <summary>
/// 文件名。
/// </summary>
private string excelFileName;
/// <summary>
/// 工作表名称。
/// </summary>
private string excelWorkSheetName;
/// <summary>
/// Excel 行数据。
/// </summary>
private IQueryable rowData;
/// <summary>
/// Excel 标题数据。
/// </summary>
private string[] headerData = null;
/// <summary>
/// 行数据键。
/// </summary>
private string[] rowPointers = null;
/// <summary>
/// 动作结果：Excel 结果构造函数，用于从行返回值。
/// </summary>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>Excel 行值。</param>
public ExcelResult(string fileName, string workSheetName, IQueryable rows)
: this(fileName, workSheetName, rows, null, null)
{
}
/// <summary>
namespace YourNamespaceGoesHere.Controllers.Results
{
using System;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using YourNamespaceGoesHere.Controllers.ControllerExtensions;
/// <summary>
/// Excel 结果类
/// </summary>
public class ExcelResult : ActionResult
{
/// <summary>
/// 文件名。
/// </summary>
private string excelFileName;
/// <summary>
/// 工作表名称。
/// </summary>
private string excelWorkSheetName;
/// <summary>
/// Excel 行数据。
/// </summary>
private IQueryable rowData;
/// <summary>
/// Excel 标题数据。
/// </summary>
private string[] headerData = null;
/// <summary>
/// 行数据键。
/// </summary>
private string[] rowPointers = null;
/// <summary>
/// 动作结果：Excel 结果构造函数，用于从行返回值。
/// </summary>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>Excel 行值。</param>
public ExcelResult(string fileName, string workSheetName, IQueryable rows)
: this(fileName, workSheetName, rows, null, null)
{
}
/// <summary>
/// 动作结果：Excel 结果构造函数，用于从行和标题返回值。
/// </summary>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>Excel 行值。</param>
/// <param>Excel 标题值。</param>
public ExcelResult(string fileName, string workSheetName, IQueryable rows, string[] headers)
: this(fileName, workSheetName, rows, headers, null)
{
}
/// <summary>
/// 动作结果：Excel 结果构造函数，用于从行、标题和行键返回值。
/// </summary>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>Excel 行值。</param>
/// <param>Excel 标题值。</param>
/// <param>行集合的键值。</param>
public ExcelResult(string fileName, string workSheetName, IQueryable rows, string[] headers, string[] rowKeys)
{
this.rowData = rows;
this.excelFileName = fileName;
this.excelWorkSheetName = workSheetName;
this.headerData = headers;
this.rowPointers = rowKeys;
}
/// <summary>
/// 获取文件名的值。
/// </summary>
public string ExcelFileName
{
get { return this.excelFileName; }
}
/// <summary>
/// 获取文件名的值。
/// </summary>
public string ExcelWorkSheetName
{
get { return this.excelWorkSheetName; }
}
/// <summary>
/// 获取行的值。
/// </summary>
public IQueryable ExcelRowData
{
get { return this.rowData; }
}
/// <summary>
/// 执行 Excel 结果。
/// </summary>
/// <param>控制器上下文。</param>
public override void ExecuteResult(ControllerContext context)
{
MemoryStream stream = ExcelDocument.Create(this.excelFileName, this.excelWorkSheetName, this.rowData, this.headerData, this.rowPointers);
WriteStream(stream, this.excelFileName);
}
/// <summary>
/// 将内存流写入浏览器。
/// </summary>
/// <param>内存流。</param>
/// <param>Excel 文件名。</param>
private static void WriteStream(MemoryStream memoryStream, string excelFileName)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", excelFileName));
memoryStream.WriteTo(context.Response.OutputStream);
memoryStream.Close();
context.Response.End();
}
}
}
最后创建 Excel 文件。关于静态方法的一点说明。我想确保我的 Excel 文档是线程安全的，所以我创建了静态方法，代价是使其不容易模拟。您需要自己权衡这个决定。
此类型的任何公共静态（Visual Basic 中的 **Shared**）成员都是线程安全的。不保证任何实例成员都是线程安全的。
namespace YourNamespaceGoesHere.Controllers.ControllerExtensions
{
using System;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Extensions;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
/// <summary>
/// Excel 文档。
/// </summary>
public static class ExcelDocument
{
/// <summary>
/// 默认电子表格名称。
/// </summary>
private const string DefaultSheetName = "Sheet1";
/// <summary>
/// 创建要流式传输的 Excel 文档。
/// </summary>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>要写入的行数据。</param>
/// <param>标题数据。</param>
/// <param>行指针。</param>
/// <returns>内存流。</returns>
public static MemoryStream Create(string documentName, string excelWorkSheetName, IQueryable rowData, string[] headerData, string[] rowPointers)
{
return CreateSpreadSheet(documentName, excelWorkSheetName, rowData, headerData, rowPointers, null);
}
/// <summary>
/// 创建电子表格。
/// </summary>
/// <param>Excel 文件名。</param>
/// <param>Excel 工作表名称：默认：sheet1。</param>
/// <param>要写入的行数据。</param>
/// <param>标题数据。</param>
/// <param>行指针。</param>
/// <param>样式表。</param>
/// <returns>内存流。</returns>
private static MemoryStream CreateSpreadSheet(string documentName, string excelWorkSheetName, IQueryable rowData, string[] headerData, string[] rowPointers, Stylesheet styleSheet)
{
int rowNum = 0;
int colNum = 0;
int maxWidth = 0;
int minCol = 1;
int maxCol = rowPointers == null ? minCol : rowPointers.Length;
maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length;
MemoryStream xmlStream = SpreadsheetReader.Create();
SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true);
SetSheetName(excelWorkSheetName, spreadSheet);
if (styleSheet == null)
{
SetStyleSheet(spreadSheet);
}
else
{
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(spreadSheet, excelWorkSheetName);
WriteHeaders(headerData, out rowNum, out colNum, out maxWidth, spreadSheet, worksheetPart);
AddCellWidthStyles(Convert.ToUInt32(minCol), Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart);
if (rowPointers == null || rowPointers.Length == 0)
{
WriteRowsFromHeaders(rowData, headerData, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
else
{
WriteRowsFromKeys(rowData, rowPointers, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
// 保存到内存流
SpreadsheetWriter.Save(spreadSheet);
spreadSheet.Close();
spreadSheet.Dispose();
return xmlStream;
}
/// <summary>
/// 设置电子表格的名称。
/// </summary>
/// <param>电子表格名称。</param>
/// <param>电子表格。</param>
private static void SetSheetName(string excelSpreadSheetName, SpreadsheetDocument spreadSheet)
{
excelSpreadSheetName = excelSpreadSheetName ?? DefaultSheetName;
Sheet ss = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == DefaultSheetName).SingleOrDefault<Sheet>();
ss.Name = excelSpreadSheetName;
}
/// <summary>
/// 添加单元格宽度样式。
/// </summary>
/// <param>最小列索引。</param>
/// <param>最大列索引。</param>
/// <param>最大列宽。</param>
/// <param>电子表格。</param>
/// <param>工作表。</param>
private static void AddCellWidthStyles(uint minCol, uint maxCol, int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
{
Columns cols = new Columns(new Column() { CustomWidth = true, Min = minCol, Max = maxCol, Width = maxWidth, BestFit = false });
workSheetPart.Worksheet.InsertBefore<Columns>(cols, workSheetPart.Worksheet.GetFirstChild<SheetData>());
}
/// <summary>
/// 设置样式表。
// 注意：此处设置样式而不是传入样式，可确保所有工作表都具有共同的用户界面设计。
/// </summary>
/// <param>要更改的电子表格。</param>
private static void SetStyleSheet(SpreadsheetDocument spreadSheet)
{
// 注意：此处设置样式而不是传入样式，可确保所有工作表都具有共同的用户界面设计。
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
styleSheet.Fonts.AppendChild(
new Font(new FontSize() { Val = 11 }, new Color() { Rgb = "FFFFFF" }, new FontName() { Val = "Arial" }));
styleSheet.Fills.AppendChild(new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Solid,
BackgroundColor = new BackgroundColor() { Rgb = "D8D8D8" }
}
});
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
/// <summary>
/// 保存工作表标题的样式。
/// </summary>
/// <param>单元格位置。</param>
/// <param>要更改的电子表格。</param>
/// <param>要更改的工作表。</param>
private static void SeatHeaderStyle(string cellLocation, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
{
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
Cell cell = workSheetPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == cellLocation).FirstOrDefault();
if (cell == null)
{
throw new ArgumentNullException("Cell not found");
}
cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1"));
OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", "");
CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
// 选择第一个单元格格式。
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0);
CellFormat cf = new CellFormat(cellFormat.OuterXml);
cf.FontId = styleSheet.Fonts.Count;
cf.FillId = styleSheet.Fills.Count;
cellFormats.AppendChild(cf);
int a = (int)styleSheet.CellFormats.Count.Value;
cell.SetAttribute(cellStyleAttribute);
cell.StyleIndex = styleSheet.CellFormats.Count;
workSheetPart.Worksheet.Save();
}
/// <summary>
/// 替换特殊字符。
/// </summary>
/// <param>要输入的值。</param>
/// <returns>替换特殊字符后的值。</returns>
private static string ReplaceSpecialCharacters(string value)
{
value = value.Replace("’", "'");
value = value.Replace("“", "\"");
value = value.Replace("”", "\"");
value = value.Replace("–", "-");
value = value.Replace("…", "...");
return value;
}
/// <summary>
/// 将值写入电子表格。
/// </summary>
/// <param>行列表值。</param>
/// <param>要写入的值。</param>
/// <param>要写入的电子表格。</param>
/// <param>要写入的工作表。</param>
private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
int intValue = 0;
if (strValue.Contains("$"))
{
strValue = strValue.Replace("$", "");
strValue = strValue.Replace(",", "");
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (int.TryParse(strValue, out intValue))
{
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (string.IsNullOrEmpty(strValue))
{
workSheetWriter.PasteText(cellLocation, strValue);
}
else
{
workSheetWriter.PasteText(cellLocation, strValue);
}
}
/// <summary>
/// 写入电子表格的 Excel 行。
/// </summary>
/// <param>Excel 行值。</param>
/// <param>Excel 行键值。</param>
/// <param>行号。</param>
/// <param>最大宽度。</param>
/// <param>要写入的电子表格。</param>
/// <param>要写入的工作表。</param>
private static void WriteRowsFromKeys(IQueryable rowData, string[] rowDataKeys, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
maxWidth = 0;
foreach (object row in rowData)
{
int colNum = 0;
foreach (string rowKey in rowDataKeys)
{
string strValue = row.GetType().GetProperty(rowKey).GetValue(row, null).ToString();
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
/// <summary>
/// 将列号转换为字母数字值。
/// </summary>
/// <param>列号。</param>
/// <returns>数字的 ASCII 值。</returns>
private static string GetColumnLetter(string colNumber)
{
if (string.IsNullOrEmpty(colNumber))
{
throw new ArgumentNullException(colNumber);
}
string colName = null;
try
{
for (int i = 0; i < colNumber.Length; i++)
{
string colValue = colNumber.Substring(i, 1);
int asc = Convert.ToInt16(colValue) + 65;
colName += Convert.ToChar(asc);
}
}
finally
{
colName = colName ?? "A";
}
return colName;
}
/// <summary>
/// 从标题写入行的值。
/// </summary>
/// <param>Excel 行值。</param>
/// <param>Excel 标题值。</param>
/// <param>行号。</param>
/// <param>最大宽度。</param>
/// <param>要写入的电子表格。</param>
/// <param>要写入的工作表。</param>
private static void WriteRowsFromHeaders(IQueryable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
maxWidth = 0;
foreach (object row in rowData)
{
int colNum = 0;
foreach (string header in headerData)
{
string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
/// <summary>
/// 写入电子表格的 Excel 标题。
/// </summary>
/// <param>Excel 标题值。</param>
/// <param>行号。</param>
/// <param>列号。</param>
/// <param>最大列宽</param>
/// <param>要写入的最大列宽。</param>
/// <param>要写入的工作表。</param>
private static void WriteHeaders(string[] headerData, out int rowNum, out int colNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
rowNum = 1;
colNum = 0;
maxWidth = 0;
foreach (string header in headerData)
{
string strValue = ReplaceSpecialCharacters(header);
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
SeatHeaderStyle(cellLocation, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
}
我希望你觉得这很有用。我不会在这里详细介绍 OpenXml API。这都是可运行的代码，所以我实际上建议你自己逐步调试代码并熟悉 Microsoft 的 OpenXml 开发人员网站。 http://msdn.microsoft.com/en-us/office/bb738430.aspx
此致，
-c