Download excel with multiple sheets in visualforce page

In this blogpost, i am going to share how to export an excel from visualforce page with multiple sheets in it.

renderAs attribute in the <apex:page> component gives you flexibility to download a visualforce page's output in pdf or xls. In this case, we will use XLS by using following code:

By using renderAs = text/xml, we will get the vf page output as excel, that means we can use excel's code to make Excel compiler understand its syntax. Excel understands data in XML form, so we are going to create XML for excel to understand. 

 - The first thing we have to use is xlsHeader, which is a string that i am setting in controller just so that i can use it in my page. you can hard-code it in the page as well. This should be the first element in your page. This contains the first element of any XML file which is <xml> (similar to <apex:page> in vf page)

  

- The 2nd thing to note is the excel works in workbooks. one instance of excel is one workbook. One workbook may have multiple tabs/sheets, whatever you call it, but it should all belong to one workbook. So after my header, i am defining <workbook> component

What comes after workbook is <styles> tag, which as name suggests is similar to css classes. in this example, i am defining a style with the ID ="s1", which tells that text should be in bold and the formatting will be numerical, alignment will on top. Very similar to css classes.

- After that we have the main area, which is called worksheets or tabs in excel. Excel calls them worksheets. So every worksheet will have a name which is defined in the attribute "ss:Name". in our case Accounts & Contacts. 

- After worksheets, we have <table> which defines a table and <rows> which defines a row. In every row we have <cells> and every cell has <data> in it. 

- you can add style to any cell by using <cell ss:StyleId ="classname">, which will apply the style defined in that style tag, as defined before and you can type to any data by using ss:Type, which is like "Format Cell" option in excel UI.

- in this example, i have created 2 worksheets, one for Account & other for Contacts.

Code is available at github