Read excel sheet using c# having merged cells to create a xml

Question

i have an excel in the format :

       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.

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;

  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;

A node Head with subnodes data1 to data6 and each subnodes have the concatenation of col4 and 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:

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

[Creating a document](http://stackoverflow.com/questions/11492705/how-to-create-xml-document-using-xmldocument) and [Reading in excel](http://stackoverflow.com/questions/657131/how-to-read-data-of-an-excel-file-using-c) — lloyd, May 14 '15 at 11:56
thanks @lloydm Merged cells is my issue here- can you give a help on it — , May 14 '15 at 11:59

score 1 · Answer 1 · edited May 23 '17 at 12:23

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);
}

}

edited May 23 '17 at 12:23

Community

1
1

answered May 14 '15 at 12:19

lloyd

1,683
2
19
23

variables : InsCount and ns is not clear . can u help @lloydm . consider me a slow learner – May 14 '15 at 12:28
lnsCount not required. [namespace](https://msdn.microsoft.com/en-us/library/system.xml.xmldocument.createattribute%28v=vs.110%29.aspx) is optional to avoid [duplicates](http://www.w3schools.com/xml/xml_namespaces.asp). so for simplicity I've removed them. – lloyd May 14 '15 at 12:36
`Error 1 'System.Xml.XmlElement' does not contain a definition for 'CreateAttribute' and no extension method 'CreateAttribute' accepting a first argument of type 'System.Xml.XmlElement' could be found (are you missing a using directive or an assembly reference?) ` am i missing something – May 14 '15 at 12:43
using System.Globalization; using System.Xml; – lloyd May 14 '15 at 12:45
Hy @lloydm ; i have added System.Globalization and still error persists – May 14 '15 at 12:54
thanks for that help. Just now only i got it understood . A problem is there : `getting empty values . like this: ` – May 14 '15 at 14:25
exact output as per my Question data is this : ` ` – May 14 '15 at 14:28
Tony please post this as a separate question. I believe the original question is answered. Perhaps rename the nodes to something more distinct than subnode, subsubnode. – lloyd May 14 '15 at 15:14

Read excel sheet using c# having merged cells to create a xml

EDIT2

1 Answers1