C# Excel XML error when cloning row

Question

I have a strange problem, and I am new to this topic of XML.
I am adding rows to an XML file that should be "openable" with Excel.
Now, I copy a single table row, and edit it, and then add it in that table again. The catch is that if I, when getting that row, use line:

XmlNode row = table.ChildNodes[6].CloneNode(true);

I cannot open the .xml file, but, If I use it like this:

XmlNode row = table.ChildNodes[6];

then I can open the .xml file, but the row is actually edited and moved to bottom, that is, no new row is added. The main "problem" that I have is understanding what is happening here because when I try to open the non working .xml file (that is created when I use the first row mentioned), I get error in excel

Also, the Table and WorksheetOptions places are switched (Table was before WorksheetOptions, so now its after WorksheetOptions). Here is the whole testing code

    using System;
    using System.Collections;
    using System.Xml;

    namespace TIS
    {
        class Program
        {
            static void Main(string[] args)
            {
                string loadPath = @"C:\Export Template.xml";
                string savePath = @"C:\Users\Ilija.DESKTOP-5Q1E02D\Desktop\Export Template Export.xml";


                XmlDocument xml = new XmlDocument();
                xml.Load(loadPath);

                XmlNamespaceManager nsmgr = new xmlNamespaceManager(xml.NameTable);
                nsmgr.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
                XmlElement root = xml.DocumentElement;
                XmlNodeList nodeList = root.SelectNodes("//ss:Worksheet", nsmgr);

                //FIND "PL" SHEET
                XmlNode nSheet = xml.DocumentElement;
                foreach (XmlNode n in nodeList)
                    if (n.Attributes["ss:Name"].Value == "PL")
                        nSheet = n;

                //FIND TABLE NODE & CREATE OTHER NODES
                XmlNode table = nSheet.ChildNodes[0];
                XmlNode row = table.ChildNodes[6].CloneNode(true);
                XmlNode cell = row.ChildNodes[0];

                XmlNode lastRow = table.LastChild;
                table.RemoveChild(table.LastChild);

                //POPULATE NODES
                row.ChildNodes[0].ChildNodes[0].InnerText = "tID";
                row.ChildNodes[1].ChildNodes[0].InnerText = "tENG";
                row.ChildNodes[2].ChildNodes[0].InnerText = "tSRB";
                row.ChildNodes[3].ChildNodes[0].InnerText = "tItem Code";
                row.ChildNodes[4].ChildNodes[0].InnerText = "tAmount";

                table.AppendChild(row);
                table.AppendChild(lastRow);
                nSheet.AppendChild(table);

                //xml.DocumentElement.RemoveChild(xml.DocumentElement.LastChild);
                //xml.DocumentElement.AppendChild(nSheet);

                xml.Save(savePath);

                /*
                IEnumerator ienum = nodeList.GetEnumerator();
                while (ienum.MoveNext())
                {
                    XmlNode title = (XmlNode)ienum.Current;
                    Console.WriteLine(title.InnerText);
                }
                //*///

                wl("end");
                wait();
            }

            static void wl(string message = "")
            {
                Console.WriteLine(message);
            }
            static void wait()
            {
                Console.ReadLine();
            }
        }
    }

So, my question is: Why is this error poppin up all of a sudden and what can I do to prevent it/work around it? I tried switching places with Table and WorksheetOptions and it didn't work. I also added WorksheetOptions from the original tamplate, and the error presisted. Have in mind: I am new to xml in c#. Any suggestions of how to add rows to Workbook/Worksheet/Table are welcome.

Monset · Accepted Answer · 2016-03-01 14:07:31Z

Answering my own question here.
The error was a wierd one.

I had to try editing the .xml file manualy, and figured that the excel wont work because I had a < cell > that had < data > witch type was numeric (< Cell>< Data ss:Type="Numeric">< /Data>< /Cell>) and I set the value of that data to ="", so it produced an error. Also, an empty row (lastRow in code) was not the last row in table (I know, right...).

Anyway, if you had any errors like this, follow the path that Excel logged, in my case it was "C:\Users\Ilija.DESKTOP-5Q1E02D\AppData\Local\Microsoft\Windows\INetCache\Content.MSO\BA6D17AD.log". In start search "Run", type %appData%, and continue following the excel's log path. I personally wasn't able to see the folder \INetCache\ , so I had to type the path manualy. The log file will look something like this

XML ERROR in Table
REASON: Bad Value
FILE:   C:\Users\Ilija.DESKTOP-5Q1E02D\Desktop\Export Template Export.xml
GROUP:  Cell
TAG:    Data
VALUE:             

XML ERROR in Table
REASON: Bad Value
FILE:   C:\Users\Ilija.DESKTOP-5Q1E02D\Desktop\Export Template Export.xml
GROUP:  Cell
TAG:    Data
VALUE:

As mentioned, error is that the VALUE is an empty string. Populate it and it should work fine.

Collectives™ on Stack Overflow

C# Excel XML error when cloning row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related