Adding an XElement with and specify a namespace

Question

I'm trying to use linq to read and modify some Excel XML, first I grab the Worksheet named Datagrid From there I select the first row (using Index) And now I want to find the cell in this row whose property is atrHeader

    Dim dg = From item In root...<ss:Worksheet> Select item Where item.@ss:Name = "Datagrid"
    Dim r = From item In dg...<Table>...<Row> Select item Where item.@ss:Index = 1 row
    Dim cell = From item In r...<Cell> Select item Where item.@ecProperty = "atrHeader"

Everything is good at this point. Now what I wish to do is create a new cell and insert it before the found cell.

    Dim newCell As New XElement("cell", 123)
    cell.First.AddBeforeSelf(newCell)

And after I save the file, I see the new value where it should be

    <cell xmlns="">123</cell>

However I need it to be like

   <ss:Cell>123</ss:Cell>

If I try to use "ss:cell" when creating the new element, I get an RTE saying ":" can't be included

I do have the following imports statement at the top of my code already:

Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

Initial XML

  <ss:Worksheet ss:Name="Datagrid">
    <ss:Table ss:ExpandedColumnCount="17" ss:ExpandedRowCount="5" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
      <ss:Row ss:Index="1" ss:AutoFitHeight="0">
        <ss:Cell ecProperty="model_name" ss:StyleID="styleReadOnly">
          <ss:Data ss:Type="String">Joe's Project</ss:Data>
        </ss:Cell>
        <ss:Cell ss:Index="6" ss:StyleID="styleColumnDescription">
          <ss:Data ss:Type="String">Type</ss:Data>
        </ss:Cell>
        <ss:Cell ecProperty="atrHeader" ss:Index="7">
          <ss:Data ss:Type="String">blank</ss:Data>
        </ss:Cell>
      </ss:Row>
      <ss:Row ss:AutoFitHeight="0" ss:Index="2">
        <ss:Cell ecProperty="email_address" ss:StyleID="styleBiDirectional">
          <ss:Data ss:Type="String">joe@ec.com</ss:Data>
        </ss:Cell>
        <ss:Cell ss:Index="6" ss:StyleID="styleColumnDescription">
          <ss:Data ss:Type="String">Full Path</ss:Data>
        </ss:Cell>
        <ss:Cell ecProperty="atrHeader" ss:Index="7">
          <ss:Data ss:Type="String">blank</ss:Data>
        </ss:Cell>
      </ss:Row>
    </ss:Table>
  </ss:Worksheet>

score 1 · Answer 1 · answered Aug 18 '12 at 19:28

Importing the namespace just allows you to get design-time support when working XML literals and queries (AFAIK). This allows you to write the queries as you did and create literal elements. You could create your newCell as a literal too.

Dim newCell = <ss:cell>123</ss:cell>

Otherwise, you'd need an XNamespace to create your element.

Dim ss As XNamespace = "urn:schemas-microsoft-com:office:spreadsheet"
Dim newCell As New XElement(ss + "cell", 123)

Adding an XElement with and specify a namespace

1 Answers1