Forum Discussion

Prometheus22's avatar
Prometheus22
Copper Contributor
Nov 05, 2021
Solved

Undeclared Prefix Error in one sheet - XML

I have an excel file template that is consistently giving me a XML error on one sheet every time it is opened. I've unzipped the excel file to get the specific sheet prior to excel trying to recover it and validated against an XML online validator. It keeps giving me "error on line 2 at column 11233: Namespace prefix xdr on col is not defined".

 

See below for the specific XML that is referencing xdr. Not sure where xdr should be defined as a namespace, or why excel is leaving it out in the XML definitions. This is a legacy corporate file template so has potentially been through several iterations of Excel.

 

<controls>
    <control shapeId="2049" r:id="rId3" name="Drop Down 1">
        <controlPr defaultSize="0" print="0" uiObject="1" autoLine="0" autoPict="0">
            <anchor sizeWithCells="1">
                <from>
                    <xdr:col>2</xdr:col>
                    <xdr:colOff>0</xdr:colOff>
                    <xdr:row>15</xdr:row>
                    <xdr:rowOff>0</xdr:rowOff>
                </from>
                <to>
                    <xdr:col>6</xdr:col>
                    <xdr:colOff>85725</xdr:colOff>
                    <xdr:row>16</xdr:row>
                    <xdr:rowOff>0</xdr:rowOff>
                </to>
            </anchor>
        </controlPr>
    </control>
</controls>

 

excel
  • Stephane_Zwel's avatar
    Stephane_Zwel
    Nov 08, 2021
    xdr is one of the OfficeOpen XML namespaces. As a namespace, it has to be defined before using it. So in either parent element, just do the following :

    <controls xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">

2 Replies

  • Stephane_Zwel's avatar
    Stephane_Zwel
    Copper Contributor
    Nov 08, 2021
    xdr is one of the OfficeOpen XML namespaces. As a namespace, it has to be defined before using it. So in either parent element, just do the following :

    <controls xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">

    • Prometheus22's avatar
      Prometheus22
      Copper Contributor
      Nov 09, 2021

      Clarifying for any people that find this post in the future and are not XML oriented. Correct code snippet is below. Would add in Stephane_Zwel's 

      xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"

       within the original XML as 

      <controls 
  xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">
    <control shapeId="2049" r:id="rId3" name="Drop Down 1">
        <controlPr defaultSize="0" print="0" uiObject="1" autoLine="0" autoPict="0">
            <anchor sizeWithCells="1">
                <from>
                    <xdr:col>2</xdr:col>
                    <xdr:colOff>0</xdr:colOff>
                    <xdr:row>15</xdr:row>
                    <xdr:rowOff>0</xdr:rowOff>
                </from>
                <to>
                    <xdr:col>6</xdr:col>
                    <xdr:colOff>85725</xdr:colOff>
                    <xdr:row>16</xdr:row>
                    <xdr:rowOff>0</xdr:rowOff>
                </to>
            </anchor>
        </controlPr>
    </control>
</controls>

       

Resources