C#解析Excel生的XML文件

[yanyl2001]

目的：要将excel导出的XML文件，解析生成gridview中
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="14" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Row ss:Height="18.75">
<Cell ss:MergeAcross="14" ss:StyleID="s21"><Data ss:Type="String">标题</Data></Cell>
</Row>
<Row>
<Cell ss:MergeDown="2" ss:StyleID="s22"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:MergeDown="2" ss:StyleID="s22"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:MergeAcross="8" ss:StyleID="m70097194"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:MergeAcross="2" ss:StyleID="m70097204"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:MergeDown="2" ss:StyleID="s26"><Data ss:Type="String">标题2</Data></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:MergeDown="1" ss:StyleID="s22"><Data ss:Type="String">合计</Data></Cell>
<Cell ss:MergeAcross="2" ss:StyleID="s22"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:MergeAcross="4" ss:StyleID="s26"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:MergeDown="1" ss:StyleID="s26"><Data ss:Type="String">小计</Data></Cell>
<Cell ss:MergeDown="1" ss:StyleID="s26"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:MergeDown="1" ss:StyleID="s26"><Data ss:Type="String">标题2</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s27"><Data ss:Type="String">小计</Data></Cell>
<Cell ss:StyleID="s27"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:StyleID="s27"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:StyleID="s28"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:StyleID="s28"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:StyleID="s28"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:StyleID="s28"><Data ss:Type="String">标题2</Data></Cell>
<Cell ss:StyleID="s28"><Data ss:Type="String">标题2</Data></Cell>
</Row>
<Row>
<Cell ss:Index="2"><Data ss:Type="String">填表： </Data></Cell>
<Cell ss:Index="7"><Data ss:Type="String">审核：</Data></Cell>
<Cell ss:Index="10"><Data ss:Type="String">审批：</Data></Cell>
</Row>
</Table>
分析函数：
private void initData()
{
Table gvt = new Table();
gv.Attributes.Add("ID", "gv");
gv.Controls.Add(gvt);
string sXml = this.Server.MapPath("XMLHeader/1.xml");
if (!System.IO.File.Exists(sXml))
return;
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.Load(sXml);
System.Xml.XmlNode node = xmlDoc.GetElementsByTagName("Worksheet").Item(0).ChildNodes[0];
int iRows = node.ChildNodes.Count;
for (int i = 0; i < iRows; i++)
{
XmlNode xmlRow = node.ChildNodes[i];
GridViewRow addHeaderRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal);
int iColumns = xmlRow.ChildNodes.Count;
for (int j = 0; j < iColumns; j++)
{
XmlNode xmlColumn = xmlRow.ChildNodes[j];
string sText = "";
sText = xmlColumn.InnerText;
if (sText == "") sText = " ";
int iRowSpan = 1;
XmlAttribute xmlAttrRowSpan = xmlColumn.Attributes["ss:MergeDown"];
if (xmlAttrRowSpan != null && !String.IsNullOrEmpty(xmlAttrRowSpan.Value))
iRowSpan = Convert.ToInt32(xmlAttrRowSpan.Value)+1;

int iColumnSpan = 1;
XmlAttribute xmlAttrColumnSpan = xmlColumn.Attributes["ss:MergeAcross"];
if (xmlAttrColumnSpan != null && !String.IsNullOrEmpty(xmlAttrColumnSpan.Value))
iColumnSpan = Convert.ToInt32(xmlAttrColumnSpan.Value)+1;

int iIndex = 0;
XmlAttribute xmlAttrIndex = xmlColumn.Attributes["ss:Index"];
if (xmlAttrIndex != null && !String.IsNullOrEmpty(xmlAttrIndex.Value))
iIndex = Convert.ToInt32(xmlAttrIndex.Value);
这里获得的了ss:Index但我一直没有下好怎么分析输出这个index的绝对定位，不知道excel本省是如何分析ss:Index这个标记的，请高手指点
//if (iIndex != 0)
//{
// int RowCellCount=addHeaderRow.Cells.Count;
// for (int k = 0; k < iIndex - RowCellCount; k++)
// {
// TableCell Cell = new TableCell();
// Cell.Text = " ";
// Cell.Wrap = false;
// Cell.BorderColor = Color.Black;
// Cell.HorizontalAlign = HorizontalAlign.Center;
// addHeaderRow.Cells.Add(Cell);
// }
// addHeaderRow.Cells[iIndex - 1].Text = sText;
//}
//else
//{
TableCell Cell = new TableCell();
Cell.Text = sText;
Cell.ColumnSpan = iColumnSpan;
Cell.RowSpan = iRowSpan;
Cell.Wrap = false;
Cell.BorderColor = Color.Black;
Cell.HorizontalAlign = HorizontalAlign.Center;
addHeaderRow.Cells.Add(Cell);
//}
}
this.gv.Controls[0].Controls.Add(addHeaderRow);

}
-------------------------------------------------------------------------------------------------
请注意红色部分，问题提问在上面，没有意义的跟帖，我不会给分的。请高手详细说明思路

[yagebu1983]

没搞过！！
关注+学习！！

[yanyl2001]

我已经解决了这个问题，现在把调整后的程序帖出来，给大家参考

感谢

lye2000000_super
我自横刀香甜笑 费心了
private void initData2()
{
Table gvt = new Table();
gv.Attributes.Add("ID", "gv");
gv.Controls.Add(gvt);
string sXml = this.Server.MapPath("XMLHeader/1.xml");
if (!System.IO.File.Exists(sXml))
return;
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.Load(sXml);
System.Xml.XmlNode node = xmlDoc.GetElementsByTagName("Worksheet").Item(0).ChildNodes[0];
int iRows = Convert.ToInt32(node.Attributes["ss:ExpandedRowCount"].Value);
int iCols = Convert.ToInt32(node.Attributes["ss:ExpandedColumnCount"].Value);
List<GridViewRow> headRows = new List<GridViewRow>();
for (int i = 0; i < iRows; i++)
{
GridViewRow addHeaderRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal);
for (int j = 0; j < iCols; j++)
{
TableCell Cell = new TableCell();
Cell.Text = " ";
Cell.ColumnSpan = 1;
Cell.RowSpan = 1;
Cell.Wrap = false;
Cell.BorderColor = Color.Black;
Cell.HorizontalAlign = HorizontalAlign.Center;
addHeaderRow.Cells.Add(Cell);
}
headRows.Add(addHeaderRow);
}
for (int i = 0; i < iRows; i++)
{
XmlNode xmlRow = node.ChildNodes[i];
int iColumns = xmlRow.ChildNodes.Count;
int iiIndex = 0;
for (int j = 0; j < iColumns; j++)
{
XmlNode xmlColumn = xmlRow.ChildNodes[j];
string sText = "";
//sText = xmlColumn.InnerXml;
sText = xmlColumn.InnerText;
if (sText == "") sText = " ";
int iRowSpan = 1;
XmlAttribute xmlAttrRowSpan = xmlColumn.Attributes["ss:MergeDown"];
if (xmlAttrRowSpan != null && !String.IsNullOrEmpty(xmlAttrRowSpan.Value))
iRowSpan = Convert.ToInt32(xmlAttrRowSpan.Value) + 1;

int iColumnSpan = 1;
XmlAttribute xmlAttrColumnSpan = xmlColumn.Attributes["ss:MergeAcross"];
if (xmlAttrColumnSpan != null && !String.IsNullOrEmpty(xmlAttrColumnSpan.Value))
iColumnSpan = Convert.ToInt32(xmlAttrColumnSpan.Value) + 1;

int iIndex = 0;
XmlAttribute xmlAttrIndex = xmlColumn.Attributes["ss:Index"];
if (xmlAttrIndex != null && !String.IsNullOrEmpty(xmlAttrIndex.Value))
iIndex = Convert.ToInt32(xmlAttrIndex.Value);

System.Xml.XmlNode node = xmlDoc.GetElementsByTagName();

if (iIndex != 0) iiIndex = iIndex-1 ;
// else iiIndex ;
headRows[i].Cells[iiIndex].Text = sText;
headRows[i].Cells[iiIndex].ColumnSpan = iColumnSpan;
headRows[i].Cells[iiIndex].RowSpan = iRowSpan;
for (int ii = 0; ii < iRowSpan; ii++)
{
for (int jj = 0; jj < iColumnSpan; jj++)
{
if (ii == 0 && jj == 0)
{
}
else
{
headRows[i+ii].Cells[iiIndex+jj].Visible = false;
}
}
}
iiIndex = iiIndex + iColumnSpan;
}
}
foreach (GridViewRow row in headRows)
{
this.gv.Controls[0].Controls.Add(row);
}
}

[lizhengnan]

你直接用datatable.ReadXml方法不可以吗，把ＸＭＬ读到DataTable中去，再把DataTable添冲到GridViewk中

[lizhengnan]

不好意思，回答错了．你的ＸＭＬ文档好象是一个模板，而不是数据集

[格拉]

[Quote=引用 1 楼 yagebu1983 的回复:]
没搞过！！
关注+学习！！
[/Quote]

[yanyl2001]

上面几位都没有分，不看清问题者。

[viki117]

ss:Index是什么啊，确定行的？
不知道啊。。

[yanyl2001]

确定列

[viki117]

居然是绝对定位，你多试几次就知道了，自己做一个EXECL例子。。在导出XML。。看看着ss:Index到底是怎么绝对定位的。。找到规律就好了

[yanyl2001]

我当然知道是怎么定位了，就是要机上绝对定位到流的输出中，你要是不太懂就别回答了，我工作5年多了，一般问题就不问了

[nullpassword]

把xml先换成Excel导入不行啊

[我自横刀香甜笑]

先定好位。然后再设置相应行和列的rowspan和columnspan

[我自横刀香甜笑]

private void initData()
{
Table gvt = new Table();
gv.Attributes.Add( "ID", "gv" );
gv.Controls.Add( gvt );
string sXml = @"c:\Sheet.xml";
if( !System.IO.File.Exists( sXml ) )
return;
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.Load( sXml );
System.Xml.XmlNode node = xmlDoc.GetElementsByTagName( "Worksheet" ).Item( 0 ).ChildNodes[ 0 ];
int iRows = node.ChildNodes.Count;
List<DataClass> list = new List<DataClass>();
List<GridViewRow> headRows = new List<GridViewRow>();
for( int i = 0; i < iRows; i++ )
{
XmlNode xmlRow = node.ChildNodes[ i ];
GridViewRow addHeaderRow = new GridViewRow( 0, 0, DataControlRowType.Header, DataControlRowState.Normal );
int iColumns = xmlRow.ChildNodes.Count;
for( int j = 0; j < iColumns; j++ )
{
XmlNode xmlColumn = xmlRow.ChildNodes[ j ];
string sText = "";
sText = xmlColumn.InnerText;
if( sText == "" )
sText = " ";
int iRowSpan = 1;
XmlAttribute xmlAttrRowSpan = xmlColumn.Attributes[ "ss:MergeDown" ];
if( xmlAttrRowSpan != null && !String.IsNullOrEmpty( xmlAttrRowSpan.Value ) )
iRowSpan = Convert.ToInt32( xmlAttrRowSpan.Value ) + 1;

int iColumnSpan = 1;
XmlAttribute xmlAttrColumnSpan = xmlColumn.Attributes[ "ss:MergeAcross" ];
if( xmlAttrColumnSpan != null && !String.IsNullOrEmpty( xmlAttrColumnSpan.Value ) )
iColumnSpan = Convert.ToInt32( xmlAttrColumnSpan.Value ) + 1;

int iIndex = 0;
XmlAttribute xmlAttrIndex = xmlColumn.Attributes[ "ss:Index" ];
if( xmlAttrIndex != null && !String.IsNullOrEmpty( xmlAttrIndex.Value ) )
iIndex = Convert.ToInt32( xmlAttrIndex.Value );
if( iIndex != 0 )
{
int RowCellCount = addHeaderRow.Cells.Count;
for( int k = 0; k < iIndex - RowCellCount; k++ )
{
TableCell Cell = new TableCell();
Cell.Text = " ";
Cell.Wrap = false;
Cell.BorderColor = Color.Black;
Cell.HorizontalAlign = HorizontalAlign.Center;
addHeaderRow.Cells.Add( Cell );
}
addHeaderRow.Cells[ iIndex - 1 ].Text = sText;
}
else
{
TableCell Cell = new TableCell();
Cell.Text = sText;
//Cell.ColumnSpan = iColumnSpan;
//Cell.RowSpan = iRowSpan;
Cell.Wrap = false;
Cell.BorderColor = Color.Black;
Cell.HorizontalAlign = HorizontalAlign.Center;
addHeaderRow.Cells.Add( Cell );
}
DataClass test = new DataClass();
test.RowIndex = i;
test.ColumnIndex = j;
test.RowSpan = iRowSpan;
test.ColumnSpan = iColumnSpan;
list.Add( test );
}
headRows.Add( addHeaderRow );

}
foreach( DataClass data in list )
{
headRows[ data.RowIndex ].Cells[ data.ColumnIndex ].RowSpan = data.RowSpan;
headRows[ data.RowIndex ].Cells[ data.ColumnIndex ].ColumnSpan = data.ColumnSpan;
}
foreach( GridViewRow row in headRows )
{
this.gv.Controls[ 0 ].Controls.Add( row );
}
}


[Serializable]
public class DataClass
{
private int iColIndex = 0;
private int iRowIndex = 0;
private int iRowSpan = 1;
private int iCloSpan = 1;
public int ColumnIndex
{
set
{
iColIndex = value;
}
get
{
return iColIndex;
}
}
public int RowIndex
{
set
{
iRowIndex = value;
}
get
{
return iRowIndex;
}
}
public int RowSpan
{
set
{
iRowSpan = value;
}
get
{
return iRowSpan;
}
}
public int ColumnSpan
{
set
{
iCloSpan = value;
}
get
{
return iCloSpan;
}
}
}
看看是否符合要求？

[我自横刀香甜笑]

跨列的时候还得把需要把多余的列移走。跨行业需要移别的行的相应列。

[feiyuan0204]

你的程序有问题吧？一个函数里面有两个node相同的局部变量！还能编译过去？