BlogSalesforce

Stylish Spreadsheets From a Visualforce Page

By March 1, 2016 September 6th, 2019 24 Comments
It’s a common request – create a nice looking Excel spreadsheet from a 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:

Excel Output

Excel Output

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 – 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!

Caspar Harmer

Caspar Harmer

Caspar is a New Zealander working for Soliant from far-off Wellington. He loves exploring new technologies and solving problems. Caspar also loves getting into the outdoors; he runs, mountain bikes and does a lot of orienteering when he can fit it in.

24 Comments

  • Avatar Aruna Rapole says:

    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}

    • Avatar Caspar Harmer says:

      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.

  • Avatar Aruna Rapolu says:

    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.

    • Avatar Caspar Harmer says:

      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.

  • Avatar Tim Wiech says:

    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.

    • Avatar Kirill Yunussov says:

      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.

  • Avatar Tim Wiech says:

    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.

    • Avatar Caspar Harmer says:

      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.

  • Avatar Madhura says:

    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

    • Avatar Madhura says:

      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

    • Avatar Caspar Harmer says:

      Looks like you found the issue. Also if you turn off your viewstate viewer, this should go away all the time.

  • Avatar Mairead Kennedy says:

    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?

    • Avatar Caspar Harmer says:

      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

    • Avatar jade maimon says:

      I have a same error !!! you can help me??
      did yu find the error?? and the solution??

  • Avatar Dmitry says:

    hi Caspar, thank you for sharing this, really helpful 🙂

  • Avatar Shanmukh says:

    Hi, Am trying pull company logo on to excel output from VF page, can you please let me know if you have approach

  • Avatar sai venkat prakash says:

    I have used the HTML Code in the vf page Now the images are displaying for me

  • Avatar sai venkat prakash says:

    on Vf page i am displaying list of products in html table and for image i have used the

  • Avatar sai venkat prakash says:

    on Vf page i am displaying list of products in html table and for image i have used the apex:image tag

Leave a Reply