c# - 使用 c# 读取 excel 表并合并单元格以创建 xml

Question

我有一个 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

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

合并的单元格是

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

可以像这样通过 OLEDb 读取没有任何合并单元格的 excel；

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

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

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

例如:

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

等等....

编辑2

@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>

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

<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>

Answer 1

与 Data reader添加 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);
}