none
Programmatically Update Excel File's SQL 'command' Attribute RRS feed

  • Question

  • Greetings, using c# openxml - I am attempting to open an excel file, bind to its connection.xml stream, and update the embedded SQL query. I am able to successfully replace individual character sequences withing the connection/command node, but attempting to explicitly set the command attribute (i.e. node.Attribute["command"].Value = select * from ....) is resulting in a corrupted, poorly formed connection.xml file.

                            xmlDoc.Load(wkb.WorkbookPart.ConnectionsPart.GetStream());

                            csNode = xmlDoc.SelectSingleNode("*/*/*[@connection]");

                            csNode.Attributes["command"].Value = newQry;

                            xmlDoc.Save(wkb.WorkbookPart.ConnectionsPart.GetStream()); 

                            wkb.Close();

    Although, I'm POSITIVE it worked at one time - it seems to be consistently failing now. Does it require a special format - example documentation would be ideal!

    Any assistance would be greatly appreciated!

    Tuesday, November 13, 2012 10:04 PM

Answers

  • Thanks for you response, but csNode is reflecting the proper node according to the IDE: 

    {Element, Name="dbPr"}

    Not sure if this is the only way to solve this issue, but I was able to correct it by deleting the original connections.xml stream and creating/attaching a new one with the correct value to the workbook

    //load xmldoc with stream from selected Excel doc

    xmlDoc.Load(wkb.WorkbookPart.ConnectionsPart.GetStream());

    //select connections node from loaded xml Excel csNode = xmlDoc.SelectSingleNode("*/*/*[@connection]"); //store original node values oldConnValue = csNode.Attributes["connection"].Value; oldCommValue = csNode.Attributes["command"].Value; //delete existing ConnectionsPart - to ensure that bleed-over data is not present wkb.WorkbookPart.DeletePart(wkb.WorkbookPart.ConnectionsPart); //create a replacement ConnectionsPart

    wkb.WorkbookPart.AddNewPart<ConnectionsPart>(); //reassign existing connection value csNode.Attributes["connection"].Value = oldConnValue; //assign new query csNode.Attributes["command"].Value = baseQry; //save changes to stream

    xmlDoc.Save(wkb.WorkbookPart.ConnectionsPart.GetStream());



    • Marked as answer by disgruntleddev Wednesday, November 14, 2012 3:48 PM
    Wednesday, November 14, 2012 3:47 PM

All replies

  • Hi disgrunteddev,

    Thanks for posting in the MSDN Forum.

    It's seems that your XPath is incorrect. It must be x:connnection/x:dbPr if the x:connections is the root.

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, November 14, 2012 6:52 AM
    Moderator
  • Thanks for you response, but csNode is reflecting the proper node according to the IDE: 

    {Element, Name="dbPr"}

    Not sure if this is the only way to solve this issue, but I was able to correct it by deleting the original connections.xml stream and creating/attaching a new one with the correct value to the workbook

    //load xmldoc with stream from selected Excel doc

    xmlDoc.Load(wkb.WorkbookPart.ConnectionsPart.GetStream());

    //select connections node from loaded xml Excel csNode = xmlDoc.SelectSingleNode("*/*/*[@connection]"); //store original node values oldConnValue = csNode.Attributes["connection"].Value; oldCommValue = csNode.Attributes["command"].Value; //delete existing ConnectionsPart - to ensure that bleed-over data is not present wkb.WorkbookPart.DeletePart(wkb.WorkbookPart.ConnectionsPart); //create a replacement ConnectionsPart

    wkb.WorkbookPart.AddNewPart<ConnectionsPart>(); //reassign existing connection value csNode.Attributes["connection"].Value = oldConnValue; //assign new query csNode.Attributes["command"].Value = baseQry; //save changes to stream

    xmlDoc.Save(wkb.WorkbookPart.ConnectionsPart.GetStream());



    • Marked as answer by disgruntleddev Wednesday, November 14, 2012 3:48 PM
    Wednesday, November 14, 2012 3:47 PM