It’s a common request – create a nice looking Excel spreadsheet from a Salesforce Visualforce page.
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.
https://gist.github.com/rapsacnz/6c9142023a4f0f055a8d
Notice the requirement of xmlHeader variable and the endFile variable – Salesforce 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:
https://gist.github.com/rapsacnz/a073aa360c5d51011061
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.
https://gist.github.com/rapsacnz/d09e51debd6fc3abd067
Define Repeating Data Section
Now, define your repeating data section using a standard apex:repeat tag:
https://gist.github.com/rapsacnz/8b06de9ae5e96efa0eb8
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.
https://gist.github.com/rapsacnz/6c9d2f059cba132855e6
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.
Happy coding!
I am trying to generate a excel sheet when vf page is loaded.
My question is I need to do sum of amounts. In the above post you show how to perform a sum – I am trying to do same but I am not getting results.
below is the code. Do you have any idea of what is going wrong?
{!VPKey}
{!managerName}
{!priceValue}
{!VPKey}
Hi Aruna, can you please host the code on a github gist or on pastebin so I can see it properly – the tags have been swallowed by WordPress.
Hi, I am sorry for the late response. I am able to figure out the sum .
But I am unable to do below tasks
1) Unable to put image . I need to put company logo on xml sheet .
2) When I am using currency number format amount is defaulted to two decimal . my users doesn’t want decimal place so I have I have used . But when I use this format if the amount is in -ve it’s not displaying in red color.
my requirement is I need to display amount with out decimal places and if the amount is -ve display in red.
Can you please send me sample code which display image and number according my requirement.
Thank you
Aruna.
Unfortunately, SpreadsheetML does not provide a way for you to embed images.
What you’d need to do is save the output as an xlsx or xls and add the image afterwards.
For formatting within xls, you can probably do that with some sort of formatting formula – but that is not within the scope of this blog.
Hey Caspar. We are trying to replicate this for our use case, we have the custom controller built and when we save in excel we only have the option of XML Data (.xml) and XML Spreadsheet 2003 (.xml). Everything seems correct when we add the XML to the VF page, but we are getting an error when the exported spreadsheet opens of the file is corrupt and cannot be opened. It doesn’t matter if we have a blank spreadsheet or a formatted one we get the same error.
This is because the file contents is XML, while the file extension is XLS – that is the mismatch. Excel is smart enough to recognize that you are using an XML spreadsheet, and renders it as such, but it will continue giving you this error because of the file extension. There is no way around this.
Update – just went to test the code you had posted on Git and I am getting the exact same error when the file downloads and attempts to open in excel.
Hi Tim, I have a fix for you. It seems that the line:
<apex:outputText value="{!endfile}" escape="false"/>
Is not required anymore – and is causing errors to occur – remove or comment this and the file will work properly.
Hey Casper,
When I tried to open the generated Excel it says it’s corrupted and I had a look on the error log in Windows. It says invalid tag “SfdcApp.Visualforce.viewstate.ViewstateSender.sendViewstate(my_instance, my_vf_page);”
I opened the generated excel file in notepad and verified the above text was rendered and due to that it’s not compatible for Excel. Any idea?
Madhura
I found the issue here. When you try to open the page from developer console, it appends this script tag. If you try to open directly from the URL it works properly
Looks like you found the issue. Also if you turn off your viewstate viewer, this should go away all the time.
This is great, but when I open the Excel file I get an error “The file format and extension of ‘TestExport_8_22_2018.xls’ don’t match”.
I can open the file and everything looks ok, but I can’t use it with that error. Do you have a workaround?
Hi Mairead, can you email me a copy of one of the spreadsheets? You can edit it by removing any critical data if you like – I really just want to see the surrounding structure. my email is charmer at soliantconsulting dot com
I have a same error !!! you can help me??
did yu find the error?? and the solution??
Any solution found here?
hi Caspar, thank you for sharing this, really helpful 🙂
Glad it was useful for you!
Hi, Am trying pull company logo on to excel output from VF page, can you please let me know if you have approach
Unfortunately, I have never managed to get images into an Excel page. Really sorry.
I have used the HTML Code in the vf page Now the images are displaying for me
That’s good to know! I didn’t think that would work. Can you leave a quick example perhaps?
on Vf page i am displaying list of products in html table and for image i have used the
apex:image tag on vf page
on Vf page i am displaying list of products in html table and for image i have used the apex:image tag
That’s great – I never tried an apex:image. Thanks for the tip!
Ho to change the font type from Arial to something else?
You need to change your default styles xml block to something like this:
<Style ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Helvetica" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
You can also specifiy a new style and reference it in a specific cell like this:
<Styles><Style ss:ID="s62">
<Font ss:FontName="Helvetica" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
</Style>
</Styles>
And you’d reference your custom style in a cell like this:
<Cell ss:StyleID="s62"><Data ss:Type="String">Opportunity Name</Data></Cell>How do I have multiple tables in one sheet? I am not able to do it. If I could get help on it!
Thanks
Hi, How to hide a column in excel based on a boolean from my page controller ?
Hi Caspar,
I tried to open the spreadsheet, and I got “The file is corrupt and cannot be opened.” Looks seem my code is fine. Please help me with this.
Here is my code,
Row 1
Row 2
Data
Data
Unfortunately, it’s very difficult to paste xml here – how about you try posting a bit of your code on https://salesforce.stackexchange.com/ and I’ll see if I can see what’s wrong from there.
Hey Caspar,
Nice work! Can you explain the following snipped please, or link to docs? What is the significance the ‘3’? I notice you have C1,C2,C3,C4 in your named ranges but they aren’t in order and C3 is used twice.
R2C3:R{!oppSize + 1}C3
Cheers!
Feel free to re-arrange the order of named ranges. However, you’ll see, that while C3 is used twice, it’s used once to define a range (assuming there are 10 opportunities) `Opportunities!R2C3:R11C3` and a cell `Opportunities!R12C3` (note the last one is the same column, but 1 lower)
Hi Caspar,
I have been successful in getting an output from my VF page. However, I can’t seem to use the data type = “Number” on any cell. When I do, I get a table error from Excel. However, If I use String, it works. But, the numbers in my output are messy.
Because I am gathering data from a few sources, I am using a custom class called “excelResponse” to generate the output. In that class I have the column in question setup as a Decimal type.
Have you ever used a custom class to generate output using this solution? Just curious if I need to tweak something on my side.
Custom Class Example:
public class excelResponse {
public Decimal stmtItemId {get;set;}
public String type {get;set;}
public String itemName {get;set;}
public Decimal numTrans {get;set;}
public Decimal itemAmt {get;set;}
}
I would think decimal would be fine as an output. Have you tried outputting this an a regular visualforce page to see what the output is?
Also, what’s the error you are getting from excel? I’d also like to see a raw output of the page, if you have a sample.
Hello Caspar, first of all, thank you so much for sharing this solution.
I saw someone else in this comment section could insert an image in the XLS file.
Any luck in the answer?
Unfortunately I have never managed to do this. You will have to try to get the commenter to expand on their solution if possible
Hi Caspar,
Kindly help with the following issue that i am currently facing.
I am having trouble with the below formula(the ampersand and quotation between two strings) is not showing when excel file is downloaded.
ss:Formula=”=RC[16]&"-"&RC[17]”.
Eg :- string1 – String2(This is what i would like to show in my cell), however excel is unable to load/translate & and ".
I got this error
XML Spreadsheet Warning in Table
REASON: Bad Value
FILE: TestExport_9_12_2022 (13).xls
GROUP: Row
TAG: Cell
ATTRIB: Formula
VALUE: =RC[16]&&&"-&"&&RC[17]
Regards
Pardha.
Because an XML spreadsheet is an XML document, any reserved characters in XML must be replaced by the appropriate entities (see appendix 7). The formula
= RC[16] & "-" & RC[17]
should be written like this:
= RC[16] & "-" & RC[17]
Refer to the guide for more info:
http://m8y.org/Microsoft_Office_2003_XML_Reference_Schemas/Help/html/spreadsheetml_HV01151864.htm
Hi Caspar,
My Formula in Visual force page is breaking when excel is getting downloaded. It says bad formula found in the log where it shows in place of &"=>&" getting displayed.
Regards
Venkat.
I’m sorry, that’s not enough information for me to help you out. Try submitting to https://salesforce.stackexchange.com/ – you can tag me there. Make sure you include the relevant parts of your code that you think are causing the error.
Hi Casper,
This solution is not working anymore I think, because when I tried this and after downloading when you will open the file in excel it throws an error that file format and extension does not match and file is corrupted.
Can we solve this error?
Hi Ajay, This solution should work fine – can you post your code here on on stackoverflow as a question? In the meantime, try reducing your file to the absolute bare minimum to generate an Excel spreadsheet and see if that opens.
Hi CASPAR,
Would you kindly share the hyperlink that is code-populated and show the company logo in the Excel file that is created from the Salesforce Vf Page?
Hi, I want to know if this solution can be used together with spreadsheetML and html? Because my table needs to be rendered with html.