采用开放的XML SDK的工作与Excel文件

采用开放的XML SDK的工作与Excel文件
就在布赖恩琼斯的博客有两个最近的文章，也许有兴趣的读者在这里。他们处理使用的Open XML SDK的编程与Excel的基于XML的文件格式。其中涉及“集结号文件” ，这实质上是建立一个档案是从零开始的基础上的其他数据，美联储从一些其他系统（如数据库） 。其他涉及使用的SDK读取数据从Excel文件。如果这些情景你感兴趣，一看见：

大会文件的解决方案SpreadsheetML
我在去年后我给了高级别概述了建筑的SDK 。现在，我们经历了基础，我们准备谈论一些现实世界的解决方案和端到端的情景。意见的基础上从我听到你们，我将开始设想和解决方案的SpreadsheetML 。让我知道如果您有任何具体方案或解决方案，你想我给在今后的职位。

在这一职位，我会向你展示如何填写电子表格与数据从一个真正的SQL数据库，并创建一个凉爽期待的基础上图表，数据，所有没有使用Excel客户端。

情景
今天的情景主题是大会文件，这是所有有关的档案建设的基础上外部数据源，像其他文件或数据库。

想象一个场景，我觉得一个开发工作的一个虚构的公司冒险作品（基于公司的抽样MSDN上，这可以在这里找到） 。如同其他公司，我公司所有商店的销售数据库中的信息。目前，销售团队利用Excel的采用手动方式连接到数据库，以便退出最新的销售资料。我被要求创建一个报告生成工具，它的数据从数据库到Excel ，并创建一个报告，图表。销售团队要执行这一解决方案在我们的报告服务器在一夜之间，使一批有严格要求，我们不能使用Excel客户端。

解决方案
在我进入的细节我解决我想请了几件事：

我创建我的解决方案使用Visual Studio 2008年
我使用免费提供的探险工程数据库建设为SQL Server 2005
请注意，您并不需要使用这两项技术，以建立开放XML的解决方案。随时使用您最喜爱的编辑器/编译器和数据库或数据源。

该解决方案将充分利用开放XML SDK的，所以如果你还没有它，下载的SDK这里。另外，请确保安装工程数据库冒险从上面的连结。

好吧，我们准备好开始。如果您只想跳直接进入代码，感觉免费下载这一解决办法在这里。

第1步-设置项目最多
一旦您已安装的Open XML SDK的，您的第一步是创建一个。网项目，提到了该SDK 。在我而言，我创建了一个C ＃项目要求v2WorkbookFromDB ，并列入参考DocumentFormat.OpenXML ，这是DLL名称的开放XML SDK的。现在我们已经获得了该SDK 。

然后，我们需要添加一个连接到我们的数据库。我是一个巨大的风扇的LINQ ，特别是为使我的生活更容易查询和连接到SQL数据库。如果您不是已经熟悉的技术，你应该看看LINQ到SQL 。在Visual Studio中，您可以添加自动生成LINQ到SQL类我探险工程数据库在几秒钟内。这些类允许你连接和查询数据库，而不需要的时候写的SQL代码。对于那些你从来没有谁试图此功能之前，让我带领您的步骤：

在您的Visual Studio项目中，右键点击该项目，并去购买|新项目
 

选择LINQ到SQL类模板。选择一个名称，然后点击添加
 

现在您已经添加了模板，让我们连接到数据库。选择您的数据库连接在服务器资源管理器窗口并拖动SalesTerritory表的设计窗口中，应该结束了看起来像下面的
 

就是这样。没有多余的编码或任何东西。现在，您可以进入该数据库不仅如此，还有的。 Net自动生成的班级，让您可以轻松地查询数据库通过对象。甜的！

为了使这种情况下，让我们说我只关心在去年和今年的销售额为每个领土。

第2步-创建一个模板文件
这种情况我上面列出的谈论建立一个工作簿内的数据资料库。我可以创建我的工作簿从地面或从现有的模板。我绝对建议选择后者。总是轻松地创建一个开放XML解决方案，从现有的模板。

在这种情况下，我创建了一个工作簿模板Excel中有两个工作表。第一表包含标题行提供的信息，我有兴趣，而在这种情况下是“领土名称” ， “去年销售额”和“今年销售” 。第二张载的总体结构我想我的图表样子。

我的模板看起来如下：

与此模板我留下了以下核心任务：

插入数据转化为资产负债表的第一个，我活
修改的范围在我的图表所以拿起数据
第3步-插入数据到我的工作簿
在我们可以使用的SDK ，我们需要添加相应的名称我们的C ＃中的档案。在这种情况下，我们需要添加我的包装和Spreadhseet API的组成部分如下：
using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml;
我知道考虑看看我们的模板文件，第一次工作将需要包含所有必要的数据。首先，我们需要做的是找到这个工作范围内的工作簿。下面的代码将告诉您如何打开一个现有工作簿文件，并获得的第一个工作。一旦你有机会获得我们的工作可以访问SheetData对象，这是所有细胞内所载资料的工作。
//Make a copy of the template file

File.Copy("template.xlsx", "generated.xlsx", true);

//Open up the copied template workbook

using (SpreadsheetDocument myWorkbook =

SpreadsheetDocument.Open("generated.xlsx", true))

{

//Access the main Workbook part, which contains all references

WorkbookPart workbookPart = myWorkbook.WorkbookPart;

//Grab the first worksheet

WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

//SheetData will contain all the data

SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

...

}
 
要添加数据的工作，我们将需要附加更多的行的SheetData基于对象的数据资料库。下面的代码将告诉您如何查询探险工程数据库的所有销售信息SalesTerriroty就座。对于每一行的查询那么我们需要创建并添加一个新行的工作基础上的数据查询。
//Connect to database

AdventureWorksDataContext db = new AdventureWorksDataContext();

//My data starts at row 2

int index = 2;

//Select * from SalesTerritoties table

var territoryQuery =

from t in db.SalesTerritories

select t;

//For each row in my database add a row to my spreadsheeet

foreach (var item in territoryQuery)

{

string territoryName = item.Name;

decimal salesLastYear = Math.Round(item.SalesLastYear, 2);

decimal salesThisYear = Math.Round(item.SalesYTD, 2);

//Add a new row

Row contentRow = CreateContentRow(index, territoryName, salesLastYear, salesThisYear);

index++;

//Append new row to sheet data

sheetData.AppendChild(contentRow);

}
我知道我们的情况下，我们要创建一个连续三个细胞：名称的领土，销售额从去年同期，销售为今年。第一个细胞是一种文字为基础的细胞，而其他两个细胞是细胞的价值基础。下面的代码创建了一个连续三个细胞：
string[] headerColumns = new string[] { "A", "B", "C" };

Row CreateContentRow(int index, string territory, decimal salesLastYear, decimal salesThisYear)

{

//Create new row

Row r = new Row();

r.RowIndex = (UInt32)index;

//First cell is a text cell, so create it and append it

Cell firstCell = CreateTextCell(headerColumns[0], territory, index);

r.AppendChild(firstCell);

//Create cells that contain data

for (int i=1; i<headerColumns.Length; i++)

{

Cell c = new Cell();

c.CellReference = headerColumns[i] + index;

CellValue v = new CellValue();

if (i == 1)

v.Text = salesLastYear.ToString();

else

v.Text = salesThisYear.ToString();

c.AppendChild(v);

r.AppendChild(c);

}

return r;

}
文字为基础的细胞和细胞为基础的价值是储存不同的SpreadsheetML 。看看过去后以获取更多信息的差异。基于这些差异，我们有以下代码来创建文字为基础的细胞：
Cell CreateTextCell(string header, string text, int index)

{

//Create new inline string cell

Cell c = new Cell();

c.DataType = CellValues.InlineString;

c.CellReference = header + index;

//Add text to text cell

InlineString inlineString = new InlineString();

Text t = new Text();

t.Text = text;

inlineString.AppendChild(t);

c.AppendChild(inlineString);

return c;

}
在这一点上我们有一个功能齐全的数据簿人口从我们的数据库中。

第4步-调整图表数据
如果你看看图表数据在我们的模板您会注意到范围的数据是固定的，参照的数据到第二排的试算表如下：
<c:f>'My Data'!$B$2</c:f>

为了确定我们的图表数据，我们需要扩大这个范围内，包括我们所有的新行。下面的代码完成这个任务：
void FixChartData(WorkbookPart workbookPart, int totalCount)

{

//Grab the appropriate chart part from template file

ChartPart chartPart = workbookPart.ChartsheetParts.First().DrawingsPart.ChartParts.First();

//Change the ranges to accomodate for newly inserted data

foreach (Charts.Formula formula in chartPart.ChartSpace.Descendants<Charts.Formula>())

{

if (formula.Text.Contains("$2"))

{

string s = formula.Text.Split('$')[1];

formula.Text += ":$" + s + "$" + totalCount;

}

}

chartPart.ChartSpace.Save();

}
最终结果
最终的结果是我们刚刚创造了一个新的工作簿，其中包含所有的数据从我们的数据库以及一个冷静寻找图表，代表了数据。生成的文件看起来是这样的：