Here is a line of XML found in an Excel book I created with a PivotTable/Cache in it:
<pivotCacheDefinition xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1" refreshOnLoad="1" refreshedBy="m" refreshedDate="44873.446783912033" createdVersion="4" refreshedVersion="4" minRefreshableVersion="3" recordCount="4">
I am using XmlWriter (and System.IO.Packaging) to modify this XML to cause it to not use cached values and instead recalculate from the original data every time it's opened (you can do this in Excel, they always forget to). All this needs is an additional attribute in this header, savedata="0".
We use similar code to rewrite the worksheets holding the data, so I simply copypasta it and changed the element names to produce this:
Dim WR As XmlWriter
Dim WRSettings As XmlWriterSettings
WRSettings = New XmlWriterSettings() With {.CloseOutput = False}
pPivotPart.Data = New MemoryStream()
pPivotPart.Data.Position = 0
WR = XmlWriter.Create(pPivotPart.Data, WRSettings)
WR.WriteStartDocument(True)
WR.WriteStartElement("pivotCacheDefinition", cXl07WorksheetSchema)
WR.WriteAttributeString("xmlns", "r", Nothing, cXl07RelationshipSchema)
WR.WriteAttributeString("r", "Id", Nothing, "rId" & RelId)
WR.WriteAttributeString("saveData", "0")
'the rest of the lengthy code creates the other attributes and then copies over the original XML line by line
The r:id attribute is causing a problem. When it is present, XmlWriter adds an alias for the main namespace, and I get this:
<x:pivotCacheDefinition xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" saveData="0" refreshOnLoad="1" refreshedBy="m" refreshedDate="44816.473130671293" createdVersion="4" refreshedVersion="4" minRefreshableVersion="3" recordCount="4" r:Id="rId1" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
This makes regression testing... difficult. If I comment out the single line that inserts that attribute, all the aliasing goes away - even if I leave in the line that manually inserts that namespace for it:
<pivotCacheDefinition xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" saveData="0" refreshOnLoad="1" refreshedBy="m" refreshedDate="44816.473130671293" createdVersion="4" refreshedVersion="4" minRefreshableVersion="3" recordCount="4" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
I thought perhaps that manually adding the r NS was the issue, and tried adding just the attribute and letting XmlWriter add it for me, but that did not do what I expected either, it simply ignored the "r" namespace entirely:
<x:pivotCacheDefinition saveData="0" refreshOnLoad="1" refreshedBy="m" refreshedDate="44816.473130671293" createdVersion="4" refreshedVersion="4" minRefreshableVersion="3" recordCount="4" Id="rId1" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
It is difficult to find this topic because of PHP's similar-name library, but a few threads I found present seemingly similar code.
Can someone who better understands XmlWriter's NS logic explain what's happening here, and how to avoid the renaming?
UPDATE:
I'm still playing with it using the dox on MS and various posts, and in doing so found this problem occurs if these are the only lines:
WR.WriteStartElement("pivotCacheDefinition", cXl07WorksheetSchema)
WR.WriteAttributeString("xmlns", "r", Nothing, cXl07RelationshipSchema)
WR.WriteAttributeString("r", "Id", cXl07RelationshipSchema, "rId" & RelId)
WR.WriteEndElement()
WR.WriteEndDocument()
As soon as it encounters the second NS the first is renamed. I have tried adding the first NS explicitly, I have tried using ns parameters instead of the xmlns tags, every variation seems to have the same effect: as soon as you insert an element on the r namespace the first one gets renamed x.
