Excel的便捷使得其在非开发人员的办公中非常流行,而Excel确实也提供了很多有用的功能。很多时候我们还需要以Excel为数据源来进行处理或者将Excel作为模板来生成一些报表。在Open XML SDK没出来之前,我们大多采用引用Office类库的方法来做处理,但这样的操作显得很麻烦。而Open XML SDK的出现,让我们可以以一个很自然的方式来处理这些数据,本文我们就一起了解一下如何使用Open XML SDK来通过LINQ to XML的方式操作Excel。
这些代码是建立在Open XML SDK CTP 2基础上的,使用前请先下载Open XML Format SDK 2.0。点击这里下载。SDK默认会安装在C:/Program Files (x86)/Open XML Format SDK/V2.0 (64bit)目录下,lib子目录下的DocumentFormat.OpenXml.dll必须被引用到项目中。
Excel也罢,word也罢,他们都是通过Open XML的标准来组织特定标记的。其实,你只要理解这些标记的含义,你可以不用任何工具自己来解析XML得到你想要的内容和格式。而Open XML SDK提供给我们的是更统一的解析方式。通过下边传统的DOM解析,你可以得到一个Excel的worksheet集合。
public static List<String> GetSheets(String strFileName)
{
// Fill this collection with a list of all the sheets.
List<String> sheets = new List<String>();
using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(strFileName, false))
{
WorkbookPart workbook = xlPackage.WorkbookPart;
Stream workbookstr = workbook.GetStream();
XmlDocument doc = new XmlDocument();
doc.Load(workbookstr);
XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI);
XmlNodeList nodelist = doc.SelectNodes("//default:sheets/default:sheet", nsManager);
foreach (XmlNode node in nodelist)
{
String sheetName = String.Empty;
sheetName = node.Attributes["name"].Value;
sheets.Add(sheetName);
}
}
return sheets;
}
对于每一个工作簿而言,Open XML Format SDK将它以不同对象之间的关系来组织。在下边的图中你可以看到这些内嵌的对象间的关系。需要说明的是,他们都代表在Excel中不同的应用,例如Columns,你可以通过它得到你的工作簿中的列。TablePart则列举了在工作簿中以Table(可以理解为以数据表,具有过滤,排序,汇总等功能)方式展现的数据区域。
而我们可能更关心的是如何来得到行、列单元格内的值。当然,worksheet除了这些对象(关系)集合外,它也通过直观的行(Row)、列(Cell)来组织内容区域。通过LINQ to XML我们可以很容易的通过Descendents来得到。
IEnumerable
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return null;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
Worksheet worksheet = worksheetPart.Worksheet;
//Ignore row header
IEnumerable<Row> rows = worksheet.Descendants<Row>();
foreach (Row row in rows)
{
foreach (Cell cell in row)
{
……
}
}
能够对Row和Cell进行遍历,这就容易多了。你可以很容易的取出某一行某一列,或者所有列的数据并输出(当然,同等的道理,你也可以写入),操作起来是不是很方便呢?但不要忘记了,你很难得到cell的值:cell.InnerText并不是在任何时候都有效的。对于s类型的列(SharedStringTable类型),它的值是存储在子元素内的。
public static String GetValue(Cell cell, SharedStringTablePart stringTablePart)
{
if (cell.ChildElements.Count == 0)
return null;
//get cell value
String value = cell.CellValue.InnerText;
//Look up real value from shared string table
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
value = stringTablePart.SharedStringTable
.ChildElements[Int32.Parse(value)]
.InnerText;
return value;
}
这时,你可以通过String columnValue = GetValue(cell, tablePart);的方式来取得单元格值。tablePart就是WorkbookPart.SharedStringTablePart,它存储了所有以SharedStringTable方式存储数据的子元素。
再扩展一下如果你想将一个Excel的工作簿以强类型展示,那该如何做呢?列明,每一个行代表一个对象元素,通过反射来来对对象赋值。请注意在工作簿中并不是每个列都能和你的类属性对应的,所以必须判断。当然,你也可以通过中间元素来产生映射扩展。
//get SharedStringTablePart to get the cell value.
SharedStringTablePart tablePart = document.WorkbookPart.SharedStringTablePart;
//Column headers
String[] cellHeaders = null;
String[] cellValues = null;
//Ignore row header
IEnumerable<Row> rows = worksheet.Descendants<Row>();
foreach (Row row in rows)
{
if (row.RowIndex == 1)
{
cellHeaders = new String[row.Count()];
}
cellValues = new String[row.Count()];
int i = 0;
foreach (Cell cell in row)
{
String columnValue = GetValue(cell, tablePart);
//The first row is header
if (row.RowIndex == 1)
{
cellHeaders[i] = columnValue;
}
else
{
cellValues[i] = columnValue;
}
i++;
}
if (row.RowIndex > 1)
{
products.Add(ProductConverter.Convert(cellValues, cellHeaders));
}
}
对最终的单元格值集合到Product对象的转换,我们通过ProductConverter类来完成。在这里,你可以通过反射来完成,但枚举出所有你可能用到的类型是你不得不面对的问题。
foreach (PropertyInfo pi in product.GetType().GetProperties())
{
for (int i = 0; i < cellHeader.Length; i++)
{
if (pi.Name.Equals(cellHeader[i], StringComparison.OrdinalIgnoreCase))
{
//get property type
String propertyType = pi.PropertyType.Name;
switch (propertyType)
{
case "Int32":
pi.SetValue(product, int.Parse(cellValues[i]), null);
break;
case "DateTime":
pi.SetValue(product, System.DateTime.Parse(cellValues[i]), null);
break;
case "Decimal":
pi.SetValue(product, Decimal.Parse(cellValues[i]), null);
break;
case "Double":
pi.SetValue(product, Double.Parse(cellValues[i]), null);
break;
case "String":
pi.SetValue(product, cellValues[i], null);
break;
}
break;
}
}
}
通过将数据展现到UI上,你可以验证你的工作 是否成功:
List
this.dataGridView1.DataSource = products;
其实对于Excel中的Table可能更有意思。因为你可以通过它来实现过滤,排序,汇总,你会感觉它特别方便(起码比Reporting Service来得快多了)。我们会再介绍如何通过更简单的办法来对Excel Table操作。
手机扫一扫
移动阅读更方便
你可能感兴趣的文章