在上一篇Open XML SDK读取Excel中,初步了解了如何使用Open XML Format SDK 2.0来查询Excel。虽然Open XML SDK已经让我们可以使用LINQ to XML来方便的操作Excel了,但是总觉得不像以对象表示的数据结构(纵然也有RowCell等)那么得心应手。你得了解ExcelOpen XML表示的格式以及命名空间,但这看起来比较麻烦。那么有没有一种方法可以让这些变得简单呢?Excel Table会让这一切变得简单。而今天要介绍的,正式使用Eric提供的对Excel Table的扩展函数库来做的。

首先我们来了解一下Excel Table吧,相信大多数人都不是经常用吧。Excel Table作为2007的一个新Feature,使得我们可以更容易得将一个Excel worksheet作为一个关系型数据库来使用(甚至于你可以关联两个电子表格),过滤,排序,汇总,而且你并不需要编写函数。加入我们有一个如下图所示的Excel Worksheet(Northwind数据库中到处Products)

第一行我们将它作为Header来处理。选择INSERT -> TABLE,弹出Create Table的对话框提示选择要创建Table的数据区域:

点击Create Table对话框中的选择区域图标来选择从A1E32的所有单元格,确定选中My Table has headers,并按OK确定。数据表Table创建完成:

你可以看到对于所有的列头都做了处理,在列头的下拉菜单中你可以选择排序方式,你可以选择对Text的过滤。很轻松吧?很容易的你就实现了一个交互式的报表。我们还可以插入统计列。当你创建完一个Table后,你就有了Design菜单,如下图所示我们将Table Name改成Products,这将会作为Tablekey来标识,需要在程序中用到。如果程序中的key和这里的名字不相同,就会出现Object reference not set to an instance of an object.的经典错误。选中Table Style Options区域中的Total Row选项,可以生成汇总行。对于汇总列你还可以选择按什么样的方式汇总。

当然,你也可以很容易的创建一个汇总列为Price*Quantity.一切都很简单。或许你可以选择在某些时候用Excel Table来替代Reporting Service向你的客户展示一些数据了。

接下来看看如何操作Excel TableEric提供的LtxOpenXml是对OpenXML SDK的一些扩展,使得操作数据表等元素会变得更加容易,就像操作DataTable一样方便。附件中有LtxOpenXml的下载。这里定义了Table, TableRows, Row, Cell等元素并将其附加于worksheet作为不同层次的子元素,你只需要操作Table下的Rows集合,并在Row里对每个Cell/Column进行操作即可。最主要的是,这里的Cell完全是可识别的字段。

下边的代码演示了如何来获取Products表的列,并将各个数据元素打印:

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(strFileName, false))

{

    foreach (var c in doc.Table("Products").TableColumns())

        Console.WriteLine("  {0}", c.Name);

 

    var query = from i in doc.Table("Products").TableRows()

                where (int)i["Quantity"] > 2

                select i;

 

    foreach (var r in query)

    {

        Console.WriteLine(r["ID"]);

        Console.WriteLine(r["ProductName"]);

        Console.WriteLine(r["Description"]);

        Console.WriteLine(r["Price"]);

        Console.WriteLine(r["Quantity"]);

        Console.WriteLine();

    }

}

 

很明显,这节约了不少代码。结果也如你期盼的一样,你不需要去判断cell.CellType是否是ShareStringTable,你也不需要去遍历cell还不知道它到底是哪个列。

ID

  ProductName

  Description

  Price

  Quantity

 

1

Chai

10 boxes x 20 bags

18

39

 

2

Chang

24 - 12 oz bottles

19

17

 

可以使用强类型来将这一切变得更容易使用:还记得我们上次使用传入的数组和反射来赋值吗?太麻烦了,因为这里我们明确的知道每一列的列名,新建强类型变得简单:

var products = from p in doc.Table("Products").TableRows()

               select new Product

               {

                   ID = (int)p["ID"],

                   ProductName = (String)p["ProductName"],

                   Description = (String)p["Description"],

                   Price = (Decimal)p["Price"],

                   Quantity = (int)p["Quantity"]

               };

return products.ToList();

 

下图显示了上边代码执行后的结果展示,没有问题,操纵Excel Table变得很容易了。另一个很熟悉的地方就是Total Row,它并不作为数据表的一部分返回。

这里我们展示了如何使用LtxOpenXml来操纵Excel Table。非常使用的东东。下次我们再一起探讨如何来用OpenXML SDK生成基于模板的报表。

附件:下载LtxOpenXML扩展类库并加入你的工程。

 

 

posted on 2008-12-31 23:54  Allan.  阅读(7029)  评论(4编辑  收藏  举报