Create Excel files with multiple tabs. - El Toro - Find articles about Visualforce, Apex, Force.com and Salesforce in general

Print Preview

Create Excel files with multiple tabs.

In Excel create a spreasheet and save it as “XML Spreadsheet 2003”, then you can create this VF page that generates that XML file.

<apex:page standardController="Account" extensions="Y01" contentType="txt/xml#myTest.xml" cache="true">
<apex:outputText value="{!xlsHeader}" />
<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">
    <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
            <Alignment/>
            <Borders/>
            <Font/>
            <Interior/>
            <NumberFormat/>
            <Protection/>
        </Style>
    </Styles>
    <Worksheet ss:Name="Contacts">
        <Table x:FullColumns="1" x:FullRows="1">
            <apex:repeat value="{!Account.Contacts}" var="c">
                <Row>
                    <Cell><Data ss:Type="String">{!c.Id}</Data></Cell>
                    <Cell><Data ss:Type="String">{!c.Name}</Data></Cell>
                    <Cell><Data ss:Type="String">{!c.Email}</Data></Cell>
                </Row>
            </apex:repeat>
        </Table>
    </Worksheet>
    <Worksheet ss:Name="Cases">
        <Table x:FullColumns="1" x:FullRows="1">
            <apex:repeat value="{!Account.Cases}" var="c">
                <Row>
                    <Cell><Data ss:Type="String">{!c.Id}</Data></Cell>
                    <Cell><Data ss:Type="String">{!c.CaseNumber}</Data></Cell>
                </Row>
            </apex:repeat>
        </Table>
    </Worksheet>
</Workbook>
</apex:page>

The genered XML must have some special lines, but unfortunately those lines can not be placed in the Visualforce page directly because they will not compile. You must use apex to generate those lines ;-)

public class Y01 {
    public String xlsHeader {
        get {
            String strHeader = '';
            strHeader += '<?xml version="1.0"?>';
            strHeader += '<?mso-application progid="Excel.Sheet"?>';
            return strHeader;
        }
    }
    public Y01(ApexPages.StandardController controller) {
    }
}

comments powered by Disqus

© El Toro . IT @ 2013
Andrés Pérez