使用具有合并单元格的c＃读取excel表以创建xml

i have an excel in the format :

我有一个excel格式:

       col1 col2    col3    col4    col5
Row 1:  1   Head    data1   r11   r12   
Row 2:  1   Head    data2   r21   r22
Row 3:  1   Head    data3   r31   r32
Row 4:  1   Head    data4   r41   r42   
Row 5:  1   Head    data5   r51   r52
Row 6:  1   Head2   data6   r61   r62
Row 7:  1   Head2   data7   r71   r72
Row 8:  1   Head2   data7   r81   r82
Row 9:  1   Head2   data8   r91   r92
Row 10: 1   Head2   data9   r101  r102
Row 11: 1   Head2   data10  r111  r112
Row 12: 1   Head2   data11  r121  r122
Row 13: 1   Head2   data12  r131  r132
Row 14: 1   Head2   data13  r141  r142

the above excel is such that the consecutive same values means they are merged.

上面的excel是这样的,连续相同的值意味着它们被合并。

The merged cells are

合并的单元格是

 " Row1col1 to Row14col1"  with value :"1"
 " Row1col2 to Row5col2"   with value : "Head"
 "Row6Col2 to Row14col2"   with value : "Head2"

reading an excel without any merged cells can be done through OLEDb like this;

在没有任何合并单元的情况下读取excel可以通过这样的OLEDb完成;

  OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$] ", connection);
  connection.Open();
  XmlDocument doc = new XmlDocument();

But how to read merged cells and to format it into an xml like this;

但是如何读取合并的单元格并将其格式化为像这样的xml;

A node Head with subnodes data1 to data6 and each subnodes have the concatenation of col4 and col5.

具有子节点data1到data6的节点Head和每个子节点具有col4和col5的串联。

Eg:

<node name="Head">
<subnode name="data1" sum="r11+r12"></subnode>
<subnode name="data2" sum="r21+r22"></subnode>

and so on....

等等....

EDIT2

Output as per @lloydm is this:

根据@lloydm的输出是这样的:

 <node>
  <subnode name="Head" sum="data1r11" />
  <subnode name="" sum="data2r21" />
  <subnode name="" sum="data3r31" />
  <subnode name="" sum="data4r41" />
  <subnode name="" sum="data5r51" />
  <subnode name="Head2" sum="data6r61" />
  <subnode name="" sum="data7r71" />
  <subnode name="" sum="data8r81" />
  <subnode name="" sum="data9r91" />
 </node>

So, how shall i get an output like:

那么,我该如何获得如下输出:

<node>
  <subnode name="Head"  />
       <subsubnode name="data2" sum="data2r21" />
       <subsubnode name="data3" sum="data3r31" />
       <subsubnode name="data4" sum="data4r41" />
       etc...
  </subnode>
  <subnode name="Head2"  />
       <subsubnode name="data2" sum="data2r21" />
       <subsubnode name="data3" sum="data3r31" />
       <subsubnode name="data4" sum="data4r41" />
       etc...
  </subnode>
</node>

1 个解决方案

#1

With Data reader add a attribute

使用数据阅读器添加属性

using System;
using System.Data.OleDb;
using System.Xml;                   
using System.Data.Common;
public class Program
{

        public static void Main()
        {
             string connectionString ="";
            XmlDocument doc = new XmlDocument();
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$] ", connection);

             XmlElement root = doc.CreateElement("node");
             doc.AppendChild(root);
            using (DbDataReader dr = command.ExecuteReader())
            {
                while (dr.Read())
                {

            #region Field Matrices
            // Field Matrix
            string r01  = dr.GetValue(0).ToString();
            string r11  = dr.GetValue(1).ToString();
            string r21  = dr.GetValue(2).ToString();


            XmlElement subnode = doc.CreateElement("subnode");
            root.AppendChild(subnode);
            XmlAttribute name = doc.CreateAttribute("name");
            name.Value = r01;        
            subnode.Attributes.Append(name);
            XmlAttribute sum = doc.CreateAttribute("sum");
            sum.Value = r11  + r21;        
            subnode.Attributes.Append(sum);
            }
        }
    }
    doc.Save(Console.Out);
}

}