如何创建 Microsoft Excel 2007 文件（在服务器端）

• 下载示例源代码

引言

采用 XML 作为 Excel 2007 的原生文件格式，开启了全新的可能性。现在，我们第一次可以使用标准工具在服务器上创建原生 Excel 文件。不再需要在您漂亮的服务器机房里放置一台没有显示器的 PC，上面运行着 Excel 和一个使用 OLE 自动化来创建报表的旧版 VB6 应用程序。这些东西都可以被扔进 IT 历史的垃圾堆了。

然而，新的 Office Open XML 标准太新了，几乎没有好的代码示例。本文旨在通过提供一套灵活的开源类库来纠正这种情况，您可以使用它们在服务器上创建 Excel 电子表格。

为什么要在服务器上创建 Excel 电子表格？

长期以来，Excel 一直被认为是演示管理报告的事实标准。它结合了出色的计算引擎、卓越的图表功能以及进行“假设分析”的可能性，使其成为“必备”的商业智能工具。

因此，当我们决定替换老旧的管理报告基础设施时，我们设定了一个关键要求：新系统必须是基于 Web 的，并提供“下载到 Excel”选项。对于我们的商业智能项目，我们使用 SQL Server 2005 构建了数据仓库，并通过 SQL Server 2005 Integration Services (SSIS) 从我们的 PeopleSoft 和 Novient 实施中填充数据。OLAP 多维数据集建立在 SQL Server 2005 Analysis Services (SSAS) 上。SQL Server 2005 Reporting Services (SSRS) 提供了基于 Web 的管理报告访问和至关重要的“下载到 Excel”选项。那么，为什么我们还需要在服务器上创建 Excel 呢？

SQL Server 2005 Reporting Services 的问题在于它生成的 Excel 电子表格是“笨拙的”。它们不包含任何公式——只有原始数据。因此，接收者无法通过更改几个值并期望电子表格重新计算来执行“假设分析”。

我们考虑了许多方法来克服这个问题，但迄今为止最有吸引力的方法是从 OLAP 多维数据集数据直接在服务器上创建 Excel 电子表格。因此，我们为 SharePoint Server 2007 创建了一个 Web 部件，用户可以通过 Excel Services 输入他们的条件并在线查看报告。当然，用户可以下载文件以在 Excel 2007 或甚至 Excel 2003 文件格式中离线查看。这个 SharePoint Web 部件及其相关的执行文件格式转换的 Web 服务将是另一篇文章的主题。

开源 ExcelPackage 程序集

ExcelPackage 程序集是一组围绕 .NET 3.0 System.IO.Packaging API 和新的 SpreadsheetML 文件格式的类和包装器。它消除了处理构成新 Excel 2007 文件格式的单个 XML 组件的复杂性。这些类以名为 ExcelPackage 的程序集形式发布，您可以将其安装到 GAC 中，并将其作为您自己应用程序的基础。秉承开源项目的精神，如果您希望帮助扩展 ExcelPackage 程序集提供的功能，请加入 ExcelPackage Open XML 项目的团队。

从头开始创建 Excel 电子表格

示例 1 展示了如何创建一个包含一些基本数据和计算的新 Excel 电子表格。那么，让我们看看这是如何实现的。

using OfficeOpenXml;  // namespace for the ExcelPackage assembly
…
FileInfo newFile = new FileInfo(@"C:\mynewfile.xlsx"); 
using (ExcelPackage xlPackage = new ExcelPackage(newFile)) { … }

这将创建一个非常重要的 ExcelPackage 类的新实例，该实例允许您访问 Excel 工作簿和工作表。如果 mynewfile.xlsx 已经存在，那么 ExcelPackage 将打开现有文件。否则，将从头开始创建 mynewfile.xlsx 。

让我们首先添加一个名为“罐装商品”的新工作表，并添加一些基本数据和一个简单的计算。

ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
// write some titles into column 1
worksheet.Cell(1, 1).Value = "Product";
…
worksheet.Cell(4, 1).Value = "Peas";
worksheet.Cell(5, 1).Value = "Total";
 
// write some values into column 2
worksheet.Cell(1, 2).Value = "Tins Sold";
 
ExcelCell cell = worksheet.Cell(2, 2);
cell.Value = "15"; // tins of Beans sold

string calcStartAddress = cell.CellAddress;  // we want this for the formula
worksheet.Cell(3, 2).Value = "32";  // tins Carrots sold
… 
worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})", 
                                           calcStartAddress, calcEndAddress);

如果这一切看起来太简单了——嗯，确实如此！ExcelPackage 程序集完成了创建表示 Excel 工作表、Excel 行、Excel 单元格等所需的 XML 元素的所有繁重工作。您只需要连接数据即可！ExcelWorksheet 类拥有创建和操作工作表所需的所有属性和方法。许多支持类（如 ExcelCell、ExcelRow、ExcelColumn、ExcelHeaderFooter 等）提供了每个工作表组件的属性和方法。它们还提供了使 Excel 数据易于操作的辅助函数。例如，ExcelCell.GetCellAddress(iRow, iColumn) 方法将您的行和列整数转换为 Excel 样式的单元格地址。

好的，在我们的示例中，一些数据对于列来说太宽了，所以让我们更改列的大小。

worksheet.Column(1).Width = 15;

接下来，向电子表格添加一些页眉和页脚。请注意我们如何使用 PageNumber 和 NumberOfPages 常量在页脚文本中插入代码。这会导致 Excel 在文档页脚中插入页码和总页数。worksheet.HeaderFooter.oddHeader.CenteredText = "罐头商品销售"；

// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.oddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, 
ExcelHeaderFooter.NumberOfPages);

好的，让我们写一些真正的硬代码。让我们在工作表中插入一行，以便我们可以添加更多数据。这将破坏我们的公式，因为它将引用错误的行集（即，新行将不包含在总计中）。

worksheet.InsertRow(3);

不，公式是正确的。InsertRow 方法不仅更新了底层 XML 中的所有行和单元格引用，还更新了电子表格中的所有公式！好的，我们现在有了报告，但我们希望确保我们的企业搜索引擎稍后能够找到该文件。因此，让我们添加一些标准和自定义文档属性。

xlPackage.Workbook.Properties.Title = "Sample 1";
xlPackage.Workbook.Properties.Author = "John Tunnicliffe"; 
xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");

现在保存文件及其所有组件。

xlPackage.Save();

下面是最终输出的屏幕截图，显示了页眉和文档属性。

从 Excel 电子表格读取数据

示例 2 展示了如何从现有 Excel 电子表格读取数据。我们将使用示例 1 生成的电子表格作为源文档。要将第 2 列的内容输出到控制台，这便足够了。

using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
{
  // get the first worksheet in the workbook
  ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
  int iCol = 2;  // the column to read


  // output the data in column 2
  for (int iRow = 1; iRow < 6; iRow++)
    Console.WriteLine("Cell({0},{1}).Value={2}", iRow, iCol, 
                       worksheet.Cell(iRow, iCol).Value);

  // output the formula in row 6
  Console.WriteLine("Cell({0},{1}).Formula={2}", 6, iCol, 
    
  worksheet.Cell(6, iCol).Formula);
   
} // the using statement calls Dispose() which closes the package.

以下是示例代码的输出

从模板开始

示例 3 展示了如何基于现有文件创建新的 Excel 电子表格，并用数据库中的数据填充它。这是一种更好的方法，因为您可以使用 Excel 2007 快速创建一个具有正确公式和公司“外观”的电子表格。然后，您可以在开始编写任何代码之前，让业务部门验证模板中的计算。这种整体方法节省了大量的编码时间！

在运行代码示例之前，请打开模板并查看其内容。您会看到它已经具有所需的布局以及标题和总计行所需的所有公式和格式。但是，它只有三行“数据行”的空间（即第 5、6 和 7 行）。您稍后将看到我们如何处理这个问题。

因此，让我们从基于模板创建一个新的 Excel 电子表格开始。

using OfficeOpenXml;  // namespace for the ExcelPackage 
assembly
…
FileInfo newFile = new 
FileInfo(@"C:\sample3.xlsx");
FileInfo template = new 
FileInfo(@"C:\sample3template.xlsx");
using (ExcelPackage xlPackage = new 
ExcelPackage(newFile, template)) {…}

在后台，ExcelPackage 构造函数只是复制模板并打开新的包。现在获取对现有工作表的引用并初始化一些变量。

ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sales"];
ExcelCell cell;
const int startRow = 5;
int row = startRow;

接下来，打开数据库连接并运行查询。此示例使用 AdventureWorks 示例数据库中的数据，因此如果要运行该示例，则需要安装它。

while (sqlReader.Read())
{
  int col = 1;
  // we have our total formula on row 7, so push them down so we can insert 
  // more data
  if (row > startRow) worksheet.InsertRow(row);

  // our query has the columns in the right order, so simply
  // iterate through the columns
  for (int i = 0; i < sqlReader.FieldCount; i++)
  {
    // use the email address as a hyperlink for column 1
    if (sqlReader.GetName(i) == "EmailAddress")
    {
      // insert the email address as a hyperlink for the name
      string hyperlink = "mailto:" + sqlReader.GetValue(i).ToString();
      worksheet.Cell(row, 1).Hyperlink = new Uri(hyperlink, UriKind.Absolute);
    }
    else
    {
      // do not bother filling cell with blank data 
      // (also useful if we have a formula in a cell)
      if (sqlReader.GetValue(i) != null)
      worksheet.Cell(row, col).Value = sqlReader.GetValue(i).ToString();
      col++;
    }
  }
  row++;
}

所以现在我们已经用整个数据集填充了我们的工作表。请注意我们如何将电子邮件地址用作超链接。当您想要将一个报告与另一个报告链接起来时，使用超链接非常有用。

纯粹主义者会注意到所有数据都以字符串形式写入单元格。然而，Cell(row, col).Value = "xxx" 属性赋值代码会检查值是数字还是字符串，并相应地设置单元格的数据类型。

如前所述，模板只有三个数据行的空间。我们通过简单地将行插入模板来处理这个问题——从而将“总计”行向下推到表格中。InsertRow 方法会自动更新“总计”行中的公式，以便它们考虑额外的行。

由于我们已将一整套行插入到电子表格中，因此它们将没有正确的样式。我们通过简单地遍历新行并将样式从第一行复制到所有其他行来纠正此问题。

// First copy the styles from startRow to the new rows.     
for (int iCol = 1; iCol <= 7; iCol++)
{
  cell = worksheet.Cell(startRow, iCol);
  for (int iRow = startRow; iRow <= row; iRow++)
  {
    worksheet.Cell(iRow, iCol).StyleID = cell.StyleID;
  }
}

命名样式的力量

任何熟悉使用 CSS 样式化 HTML 的人都会理解使用命名样式而不是更新每个独立元素的样式所带来的强大功能和灵活性。使用命名样式，只需更改一个样式定义，即可更改整个电子表格的外观。此功能在 Excel 2003 中引入，但 Excel 2007 更进一步，使其成为使用命名样式创建模板的梦想。

我们应用了两种内置命名样式来突出显示表现最佳和表现最差的销售代表。

// style the first row as they are the top achiever
worksheet.Cell(startRow, 6).Style = "Good";
// style the last row as they are the worst performer
worksheet.Cell(row, 6).Style = "Bad";

Excel 2007 中命名样式最大的问题是，如果它们未在模板中使用，则保存文件时 Excel 会删除定义。这是一个真正的麻烦。有两种方法可以解决这个问题：（1）添加应用了样式的额外行，然后删除它们（这是本示例中使用的技术）；或（2）加载您自己的 style.xml 文件，其中包含您要使用的所有定义。

共享公式

Excel 2007 有一个巧妙的功能，可以在将相同公式应用于一系列单元格时节省大量编码。一个单元格中的公式可以标记为“共享”，并且所有被共享公式引用的单元格都会获得自己版本的公式。因此，如果单元格 E5 的公式是 D5*12，那么单元格 E6 的公式将是 D6*12 等等。要设置共享公式，只需调用 CreateSharedFormula 方法。在以下示例中，单元格 E5 中的公式被标记为“共享”，并且范围 E5:E21 中的所有其他单元格都分配了它们自己的公式变体。

worksheet.CreateSharedFormula(worksheet.Cell(5, 5), worksheet.Cell(21, 5));

确保您的公式在文件打开时重新计算

我们在 Excel 2007 中遇到的一个问题是，它在重新打开时不会自动重新计算电子表格——即使计算选项设置为自动！这是因为模板中的现有单元格既包含公式又包含值。所以 Excel 只是假定该值是正确的，并且不尝试重新计算公式。当然，我们刚刚添加了二十行数据并更新了 XML 中的公式引用——但 Excel 没有办法知道这一点，所以假定值一定是正确的！

强制重新计算的唯一方法是确保单元格没有值——只有公式。因此，RemoveValue() 方法对于工作表中的所有公式都变得非常有用。因此

worksheet.Cell(22, 5).RemoveValue();

由于这种现象，我们修改了 ExcelCell.Formula 属性赋值代码，使其在为单元格分配公式时删除单元格的值。

示例 3 代码的最终输出应类似于此——比从头开始创建的任何内容都更专业。

完整性问题

一旦您开始从包中删除行甚至工作表，就可能出现完整性问题。您的公式将引用不再存在的单元格（或工作表）。ExcelPackage 程序集在您完成操作后会很好地清理，但无法处理复杂情况。如果您遇到完整性问题，您很快就会知道——Excel 在打开新创建的文件时会大声抱怨。

一个典型的问题是 calcChain.xml 文件。它告诉 Excel 计算应该按什么顺序处理。因此，如果您删除 calcChain 引用的行，Excel 会抱怨。但是，如果您只是从包中删除 calcChain.xml ，Excel 会在文件打开时重新创建它——并且不会抱怨！所以这是一个简单的修复。ExcelPackage 程序集正是这样做的——从模板中删除 calcChain.xml 文件，以便 Excel 在文件打开时简单地重新创建它。

ExcelPackage 程序集还为您提供了对组成包的每个 XML 文档的直接访问。因此，您可以编写自己的代码直接操作 XML。但是，如果您选择这样做，请务必确保 XML 符合新的 Office Open XML 标准。否则，Excel 将简单地将您的所有辛勤工作作为“格式错误”删除。

调试您的应用程序

如果您想准确了解 ExcelPackage 程序集写入每个组件 XML 文件的内容，只需添加以下代码行即可：

xlPackage.DebugMode = true;

这将使程序集在输出文件所在的同一位置输出原始 XML 文件。您将看到一个名为“xl”的子文件夹，另一个名为“docProps”。

摘要

本文演示了使用开源 ExcelPackage 程序集在服务器上创建基于 Excel 的报告是多么容易。我们希望您能加入我们，在 ExcelPackage Open XML 项目中扩展程序集的功能。还有很多工作要做；图表、条件格式、插入注释，这只是其中一部分！

祝您的项目好运！