However, it’s not immediately clear how to do this. It’s also reasonably easy to output a very badly formatted Excel output based on hacking an existing example found on the net.
What is not commonly provided, however, is how this example was first generated. As it happens, you can do this quickly and easily.
Excel has an output format called Excel 2004 XML Spreadsheet which creates the exact format that you want. This is also called spreadsheetML. This format includes a lot of existing Excel functionality – many formulas, formatting, external references, row freezing and more.
For example, the spreadsheet below was easily generated using this format:
This example contains styles, frozen rows, named ranges and calculations.
How to do this:
Define Your Controller
First, define your controller. I’ve put the code up on GitHub here for your reference.
Notice the requirement of xmlHeader variable and the endFile variable – Visualforce doesn’t tolerate these in the page markup.
The last two variables define the data and the size of the data.
Define Your Visualforce Page
From here, you will want to define the Visualforce page. Create an Excel page similar to the image above and save it as Excel 2004 XML Spreadsheet (.xml). Then open this file in your text editor (not Excel).
Now, make a basic Visualforce page with this markup:
Paste in the text of the spreadsheet that you just saved. Now test your page by visiting /apex/YourPageName to make sure that it is working properly. It should download a page very similar the file that you just saved from Excel.
Modify Named Ranges
Now, modify your named ranges (if you don’t have them, add some – they make it much easier to define calculations within apex). As you can see below, I have used the length of the list of data + some hard coded values. You may want to define these hard coded values in the controller.
Define Repeating Data Section
Now, define your repeating data section using a standard apex:repeat tag:
As you can see, I have a formula in there that refers to a previously defined NamedRange. This makes the code a lot more readable.
Add Total Row
Finally, add the total row, with the styled cells and percentage calculations. Note, you can actually define calculations that are much more complex than shown here. The format is very powerful.
You now have a completed Visualforce page and Controller that will export nicely formatted data to Excel!
Get the Full Code
Remember the full code (putting these pieces together) is hosted on github here. Note, the prefix on the files is *.xml and *.java to get better syntax highlighting.
Make sure you edit the Excel file very carefully and in small increments – Excel is very picky about the format and won’t open the file if it thinks something is not right.