如何读取 Excel XML (C#) [英] How to read Excel XML (C#)
本文介绍了如何读取 Excel XML (C#)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
不知道是否有一种简单的方法可以读取 Excel 2010 XML?
此 XML 的结构与我以前阅读的结构不同.
I wonder if there is an easy way to read Excel 2010 XML?
This XML has a different structure than I used to read.
特别是 ss:index 属性 (ss:Index**="7") 使事情变得有点复杂
Especially ss:index attribute (ss:Index**="7") makes things a bit more complicated
编辑:更好地解释:
- 我有可以在 Excel 中轻松打开的带有 XML 扩展名的文件
- 我正在寻找一种以编程方式阅读此工作表的方法(例如复制到 DataTable)
- 注意到这不是我以前工作的常用 XML
- XML 在开头定义字段，而不是使用 ROW、CELL 和 DATA 标签
- 令我惊讶的是，当有 3 个字段(单元格)但第二个字段没有值时，该字段在 XML 中被跳过"，但第三个字段有一些额外的索引"属性，例如:ss:Index**="3" (这表示即使它在第 2 位右索引也应该是 "3"
示例 XML 的片段
<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String">Johny</Data></Cell>
<Cell ss:Index="3"><Data ss:Type="String">NY</Data></Cell>
推荐答案
好的，我终于从这里找到了解决方案:http://www.codeproject.com/Articles/32370/Import-Excel-File-to-DataSet#xx
OK I've finally found solution from here: http://www.codeproject.com/Articles/32370/Import-Excel-File-to-DataSet#xx
以下示例代码很少满足我的需求.
Below sample code little adopted to my needs.
public static class XMLtoDataTable {
private static ColumnType getDefaultType() {
return new ColumnType(typeof(String));
}
struct ColumnType {
public Type type;
private string name;
public ColumnType(Type type) { this.type = type; this.name = type.ToString().ToLower(); }
public object ParseString(string input) {
if (String.IsNullOrEmpty(input))
return DBNull.Value;
switch (type.ToString()) {
case "system.datetime":
return DateTime.Parse(input);
case "system.decimal":
return decimal.Parse(input);
case "system.boolean":
return bool.Parse(input);
default:
return input;
}
}
}
private static ColumnType getType(XmlNode data) {
string type = null;
if (data.Attributes["ss:Type"] == null || data.Attributes["ss:Type"].Value == null)
type = "";
else
type = data.Attributes["ss:Type"].Value;
switch (type) {
case "DateTime":
return new ColumnType(typeof(DateTime));
case "Boolean":
return new ColumnType(typeof(Boolean));
case "Number":
return new ColumnType(typeof(Decimal));
case "":
decimal test2;
if (data == null || String.IsNullOrEmpty(data.InnerText) || decimal.TryParse(data.InnerText, out test2)) {
return new ColumnType(typeof(Decimal));
} else {
return new ColumnType(typeof(String));
}
default://"String"
return new ColumnType(typeof(String));
}
}
public static DataSet ImportExcelXML (string fileName, bool hasHeaders, bool autoDetectColumnType) {
StreamReader sr = new StreamReader( fileName);
Stream st = (Stream) sr.BaseStream;
return ImportExcelXML( st, hasHeaders, autoDetectColumnType);
}
private static DataSet ImportExcelXML(Stream inputFileStream, bool hasHeaders, bool autoDetectColumnType) {
XmlDocument doc = new XmlDocument();
doc.Load(new XmlTextReader(inputFileStream));
XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace("o", "urn:schemas-microsoft-com:office:office");
nsmgr.AddNamespace("x", "urn:schemas-microsoft-com:office:excel");
nsmgr.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
DataSet ds = new DataSet();
foreach (XmlNode node in
doc.DocumentElement.SelectNodes("//ss:Worksheet", nsmgr)) {
DataTable dt = new DataTable(node.Attributes["ss:Name"].Value);
ds.Tables.Add(dt);
XmlNodeList rows = node.SelectNodes("ss:Table/ss:Row", nsmgr);
if (rows.Count > 0) {
//*************************
//Add Columns To Table from header row
//*************************
List<ColumnType> columns = new List<ColumnType>();
int startIndex = 0;
if (hasHeaders) {
foreach (XmlNode data in rows[0].SelectNodes("ss:Cell/ss:Data", nsmgr)) {
columns.Add(new ColumnType(typeof(string)));//default to text
dt.Columns.Add(data.InnerText, typeof(string));
}
startIndex++;
}
//*************************
//Update Data-Types of columns if Auto-Detecting
//*************************
if (autoDetectColumnType && rows.Count > 0) {
XmlNodeList cells = rows[startIndex].SelectNodes("ss:Cell", nsmgr);
int actualCellIndex = 0;
for (int cellIndex = 0; cellIndex < cells.Count; cellIndex++) {
XmlNode cell = cells[cellIndex];
if (cell.Attributes["ss:Index"] != null)
actualCellIndex =
int.Parse(cell.Attributes["ss:Index"].Value) - 1;
ColumnType autoDetectType =
getType(cell.SelectSingleNode("ss:Data", nsmgr));
if (actualCellIndex >= dt.Columns.Count) {
dt.Columns.Add("Column" +
actualCellIndex.ToString(), autoDetectType.type);
columns.Add(autoDetectType);
} else {
dt.Columns[actualCellIndex].DataType = autoDetectType.type;
columns[actualCellIndex] = autoDetectType;
}
actualCellIndex++;
}
}
//*************************
//Load Data
//*************************
for (int i = startIndex; i < rows.Count; i++) {
DataRow row = dt.NewRow();
XmlNodeList cells = rows[i].SelectNodes("ss:Cell", nsmgr);
int actualCellIndex = 0;
for (int cellIndex = 0; cellIndex < cells.Count; cellIndex++) {
XmlNode cell = cells[cellIndex];
if (cell.Attributes["ss:Index"] != null)
actualCellIndex = int.Parse(cell.Attributes["ss:Index"].Value) - 1;
XmlNode data = cell.SelectSingleNode("ss:Data", nsmgr);
if (actualCellIndex >= dt.Columns.Count) {
for (int ii = dt.Columns.Count; ii < actualCellIndex; ii++) {
dt.Columns.Add("Column" + actualCellIndex.ToString(), typeof(string));columns.Add(getDefaultType());
} // ii
ColumnType autoDetectType =
getType(cell.SelectSingleNode("ss:Data", nsmgr));
dt.Columns.Add("Column" + actualCellIndex.ToString(),
typeof(string));
columns.Add(autoDetectType);
}
if (data != null)
row[actualCellIndex] = data.InnerText;
actualCellIndex++;
}
dt.Rows.Add(row);
}
}
}
return ds;
}
}
这篇关于如何读取 Excel XML (C#)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
