+ Start a Discussion
TadeasBTadeasB 

Visualforce - XML - tags not nested properly

Hi,

I'm trying to generate XML via Visualforce, but visualforce is not nesting tags properly. I have got following VF page:

<apex:page standardStylesheets="false" controller="DController" contentType="application/xml#D.xls">{!xmlheader}
<apex:variable value="" var="n"/>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>SF</Author>
  <Version>15.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    ...
 </ExcelWorkbook>

<Styles>
    ...
</Styles>

<Worksheet ss:Name="Objects">
    <Names>
        ...
    </Names>
    <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="{!objectList.size+1}" x:FullColumns="1"
   x:FullRows="1">
        
      <apex:repeat value="{!objectList}" var="o">
        <Row>
            <Cell ss:StyleID="s22"><Data ss:Type="String"><apex:outputText escape="false" value="{!HTMLENCODE(o.Name)}"/></Data>
                <NamedCell ss:Name="_FilterDatabase"/></Cell>
            <Cell ss:StyleID="s22"><Data ss:Type="Number"><apex:outputText escape="false" value="{!o.R_Count__c}"/></Data>
                <NamedCell ss:Name="_FilterDatabase"/></Cell>
            <Cell ss:StyleID="s22"><Data ss:Type="Number"><apex:outputText escape="false" value="{!o.F_size__c}"/></Data>
                <NamedCell ss:Name="_FilterDatabase"/></Cell>
        </Row>
      </apex:repeat>
    </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">   ...
 </WorksheetOptions>
</Worksheet>


<apex:outputPanel layout="none" rendered="true">
<Worksheet ss:Name="Fields">
    ...
</Worksheet>
</apex:outputPanel>

</Workbook>{!endFile}
</apex:page>

 The thing is, that outputed XML is damaged. Sometimes tags are missing and almost always some tags are ended too late. This code produces following structure:

...
<Styles>
    ...
</Styles>
<Worksheet ss:Name="Objects">
	...
	<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
	</WorksheetOptions>
  
<Worksheet ss:Name="Fields">
	...
	<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
	...
	<WorksheetOptions>
</Worksheet></Workbook>
</Worksheet>

 You can see </Worksheet> tag at end of file. I've tried to surround it with some text to see, what happens. This VF:

</WorksheetOptions>
asdf</Worksheet>asdf

 produces this in output XML:

asdf</WorksheetOptions>asdf

...

</Workbook>
</Worksheet>

 

Sometimes it helps if I surround whole section with <apex:ouputPanel layout="none"> but mostly it has no effect. Does someone has any idea?

Douglas AyersDouglas Ayers
Did you ever find a solution for this? I'm having the exact same problem. The generated output when running the visualforce page is re-ordering the tags such that the </Worksheet> tags are appearing out of order and the </Workbook> tag doesn't even show up.  Very frustrating...
TadeasBTadeasB
No, and it seems that no one is going to repair this. I hvae found a workaround, you can assign problematic tags into variables, for example:

String wStart = '<Workbook><Worksheet>';
String wEnd = '</Worksheet></Workbook>';

Then you can use it like this: 

<apex:outputText value="{!wStart}" escape="false" />
...
<apex:outputText value="{!wEndt}" escape="false" />

This usually helps, but you will have to play with it, it's not working everytime.
Vaib ParaVaib Para
Visualforce to XML workbook declaration error.. Pls helpHi all, I am getting an error when i use xmlns="urn:schemas-microsoft-com:office:excel" in two worksheetoptions, like it works for only sheet3 and not for sheet1 and sheet2.

It says cannot load content when i add xmlns tag in other two sheets...Pls Help

<apex:page controller="ExportToExcelMultipleSheets" contentType="application/vnd.ms-excel#Candidate.xls" cache="true">
 <apex:outputText value="{!xlsHeader}"/>
  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">
<Styles>
      <Style ss:ID="Default" ss:Name="Normal">
         <Alignment ss:Vertical="Bottom" />
         <Borders />
         <Font />
         <Interior />
         <NumberFormat />
         <Protection />
      </Style>
   </Styles>
   <Worksheet ss:Name="Sheet1">
      <Table ss:ExpandedColumnCount="100">
         <Row>
            <Cell>
               <Data ss:Type="String">cell a1</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">cell b2</Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss:Type="String">cell a2</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">cell b3</Data>
            </Cell>
         </Row>
      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Layout x:Orientation="Landscape"/>
    <Header x:Data="&amp;A"/>
    <Footer x:Data="Page &amp;P of &amp;N"/>
   </PageSetup>
   <FitToPage/>
   <Print>
    <FitHeight>50</FitHeight>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <Scale>35</Scale>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <ShowPageBreakZoom/>
   <PageBreakZoom>100</PageBreakZoom>
   <FreezePanes/>
   <FrozenNoSplit/>
   <SplitHorizontal>1</SplitHorizontal>
   <TopRowBottomPane>1</TopRowBottomPane>
   <SplitVertical>2</SplitVertical>
   <LeftColumnRightPane>2</LeftColumnRightPane>
   <ActivePane>0</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
    </Pane>
    <Pane>
     <Number>1</Number>
    </Pane>
    <Pane>
     <Number>2</Number>
    </Pane>
    <Pane>
     <Number>0</Number>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
   </Worksheet>
   <Worksheet ss:Name="Sheet2">
      <Table ss:ExpandedColumnCount="100">
         <Row>
            <Cell>
               <Data ss:Type="String">cell a1</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">cell b2</Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss:Type="String">cell a2</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">cell b3</Data>
            </Cell>
         </Row>
      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   
  </WorksheetOptions>
   </Worksheet>
<Worksheet ss:Name="Sheet3">
      <Table ss:ExpandedColumnCount="100">
<Row>
            <Cell>
               <Data ss:Type="String"></Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">cell b2</Data>
            </Cell>
         </Row>
         
         <Row>
            <Cell>
               <Data ss:Type="String">cell a2</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">cell b3</Data>
            </Cell>
         </Row>
      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   
  </WorksheetOptions>
   </Worksheet>
</Workbook>
</apex:page>