How to Create a Multi-Sheet Excel Workbook from Apex

June 1, 2015 Appirio

By Neeraj Gupta and Rohit Sharma

techbl

We often need to generate reports in Salesforce — sometimes for a single object, sometimes for 2 or more objects. Some organizations needs data analysis calculated using different objects in the same document, separately, along with charts. Of course, the most common way to do this is in using Microsoft Excel. Unfortunately, default Salesforce reporting does not provide that much information in a single Excel file. For a workaround, we can generate an Excel file using XML tags. This allows us to generate different Excel sheets in the same workbook, along with different charts like pie charts, bar charts, etc. By the end, you will be able to generate a Multi-Sheet Excel workbook.

Introduction to Excel Format

Excel files are documented into XML format. Microsoft has introduced XML format to store

Excel spreadsheets and Word documents. This SpreadsheetML format in Excel is fairly easy to work with, as it was designed to be read and edited by people. Using this format, you can create an Excel file by building an XML document with rows, columns, and worksheets. We can also define column width, font color, and font family in XML format.

To get you started, let us build a sample in XML that will illustrate how it all works.

Declare the namespace

Most XML documents have a namespace associated with them. Declaring the namespace of an XML file makes it much easier for users parsing your XML to know what type of XML they are dealing with.

[snippet caption=”Declare the NameSpace”]

<?xml version=”1.0″?>

<?mso-application progid=”Excel.Sheet”?>

<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”>

</Workbook>

[/snippet]

Add a worksheet

Worksheet contains a table which contains all the grid data.

[snippet caption=”Add a WorkSheet”]

<?xml version=”1.0″?>

<ss:Workbook xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”>

<ss:Worksheet ss:Name=”Sheet1″>

<ss:Table>

<ss:Row>

<ss:Cell>

<ss:Data ss:Type=”String”>Name</ss:Data>

</ss:Cell>

<ss:Cell><ss:Data ss:Type=”String”>Phone Number</ss:Data>

</ss:Cell>

</ss:Row>

</ss:Table>

</ss:Worksheet>

</ss:Workbook>

[/snippet]

Creating an Excel workbook using Apex

To create an Excel workbook using Apex we have to define XML format into a Visualforce document with content type as Excel format, so the document will be saved in XLS format. We also need to define the XML header so the Excel reader can read the file.

multisheetexcel

 

Here we have to define the XML version and header using a variable from controller due to some limitations in the Apex document. Once the header is declared, we can start building a workbook in Apex with multiple spreadsheets.

Here is the sample code to build a multi-sheet workbook:

Controller class

[snippet caption=”Sample Controller Class”]

public without sharing class controllerClass{

public string xmlheader {get;set;}

public string endfile{get;set;}

public List<string> listString{get;set;}

 

public controllerClass() {

//VF page gives some issues directly using the below code there, so wrap up in variable

xmlheader ='<?xml version=”1.0″?><?mso-application progid=”Excel.Sheet”?>’;

endfile = ‘</Workbook>’;

listString = new List<string>();

listString.add(‘1’);

listString.add(‘2’);

}

}

[/snippet]

Visualforce page

[snippet caption=”Sample VisualForce Page”]

<apex:page id=”pg” standardStylesheets=”false” controller=”controllerClass” contenttype=”application/vnd.ms-excel#MultiSheetExcel.xls”>

{!xmlheader}

<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″>

<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>

<WindowHeight>8535</WindowHeight>

<WindowWidth>12345</WindowWidth>

<WindowTopX>480</WindowTopX>

<WindowTopY>90</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>

<Author></Author>

<LastAuthor></LastAuthor>

<Company></Company>

<Version>11.6360</Version>

</DocumentProperties>

<Styles>

<Style ss:ID=”Default” ss:Name=”Normal”>

<Alignment ss:Vertical=”Bottom”/>

<Borders/>

<Font ss:bgcolor=”#FF0000″/>

<Interior ss:bgcolor=”#FF0000″/>

<NumberFormat/>

<Protection/>

</Style>

<Style ss:ID=”s16″>

<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#FFFFFF”

ss:Bold=”1″ ss:Italic=”1″/>

<Interior ss:Color=”#92D050″ ss:Pattern=”Solid”/>

</Style>

<Style ss:ID=”s23″>

<Font x:Family=”Swiss” ss:Bold=”0″ ss:bgcolor=”#FF0000″/>

</Style>

<Style ss:ID=”s66″>

<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”16″ ss:Color=”#000000″ ss:Bold=”1″ ss:Italic=”1″/>

</Style>

</Styles>

<Worksheet ss:Name=”Sheet1″>

<Table ss:ExpandedColumnCount=”100″>

<Column ss:Width=”180″/>

<Row>

<Cell ss:StyleID=”s66″><Data ss:Type=”String”>Test Sheet1</Data></Cell>

</Row>

<Row>

<Cell ss:StyleID=”s16″ ><Data ss:Type=”String”>Field1</Data></Cell>

<Cell ss:StyleID=”s16″><Data ss:Type=”String”>Field2</Data></Cell>

</Row>

<!– Repeat and create data rows –>

<apex:repeat value=”{!listString}” var=”row”>

<Row>

<Cell ss:StyleID=”s23″><Data ss:Type=”String”>{!row}</Data></Cell>

<Cell ss:StyleID=”s23″><Data ss:Type=”String”>{!row}</Data></Cell>

</Row>

</apex:repeat>

</Table>

</Worksheet>

<Worksheet ss:Name=”Sheet2″>

<Table ss:ExpandedColumnCount=”100″>

<Column ss:Width=”100″/>

<Column ss:Width=”180″/>

<Row>

<Cell ss:StyleID=”s66″><Data ss:Type=”String”>Test Sheet2</Data></Cell>

</Row>

<Row>

<Cell ss:StyleID=”s16″><Data ss:Type=”String”>Field1</Data></Cell>

</Row>

<apex:repeat value=”{!listString}” var=”row”>

<Row>

<Cell ss:StyleID=”s23″><Data ss:Type=”String”>{!row}</Data></Cell>

</Row>

</apex:repeat>

</Table>

</Worksheet>

</Workbook>

<apex:outputText value=”{!endfile}” escape=”false”/>

</apex:page>

[/snippet]
That’s all for now. Please reference Excel XML format tags to customize your Excel document as per your requirement.

 

Previous Article
11 Integration Tips to Hit Your Cornerstone OnDemand Go-Live Date
11 Integration Tips to Hit Your Cornerstone OnDemand Go-Live Date

by Kevin Churbuck Having had the benefit of being both an Implementation Consultant and an Integration Cons...

Next Article
Quick Guide to Using Apache Ant for Salesforce Deployments
Quick Guide to Using Apache Ant for Salesforce Deployments

By Shailendra Singh Apache Ant is a Java library and command-line tool. Ant was originally used for buildin...