Is there a way to dynamically fill excel sheets from apex?

Question

Currently in my sandbox I have been working on a component that, upon a button press, updates a record and sends an email with an excel sheet attachment to a given user. The excel sheet needs to have a different value for a name field depending on the sender, so I added a static resource of an excel sheet with different name values that checks record owner and sends the correct one.

All this works as intended, but upon demoing the component, I was asked to extend the functionality to include filling out not just the name, but also 2 other fields that will be drawn directly from the sales force record. Since these fields will not draw from a narrow set of values like the names do, I cannot just upload a different static resource for every different field value, but instead will have to find someway to dynamically fill the fields of the excel sheet in my apex class before sending the email with it attached.

Seeing as there is no real file I/O in apex that I am aware of, I am not sure there is really a way to do this, but would be happy to hear if someone can confirm this either way. If there is not a way to do this, I am hopeful that someone may have an idea of how I can accomplish this functionality in a different manner?

Answer 1

Here's a slightly (well...much) more complex way of generating an Excel document using Visualforce, but it's much easier to get a good looking result that's an actual xls file, including native Excel conditional formatting, frozen header rows, formulas and named ranges! All credit goes to Caspar Harmer. I'm only adding some explanation to his code, which you can find on GitHub here:

Rapsacnz ExcelExport

First, the Apex controller:

public ExcelExportController() {
    xmlheader ='<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
    endfile = '</Workbook>';
    opportunities = [SELECT Id, Name, StageName, Amount FROM Opportunity LIMIT 10];
    oppSize = opportunities.size();
}

The xmlheader and endfile values will be added to the Visualforce page using apex:outputText tags, since Visualforce doesn't play nice with these tags on its own!

And now the beginnings of the Visualforce page:

<apex:page id="pg" standardStylesheets="false" controller="ExcelExportController" contenttype="application/vnd.ms-excel#TestExport_{!TODAY()}.xls">
    <apex:outputText value="{!xmlheader}" escape="false"/>

        <!-- your excel output goes here -->

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

Now for the interesting part: We're going to create our spreadsheet layout directly in Excel, instead of trying to do all the formatting in Visualforce.

• Open Excel and create a new spreadsheet
• Add columns for Opportunity Name, Stage, Amount, and a few more for fun.
• Give it a highlighted frozen column header
• Put in some data rows
• Add a SUM formula at the bottom of the Amount column
• Define named ranges for each of the columns to make them easier to work with in Visualforce.

If you want to get really nutty with it, add a % of total sales column, and use a formula to calculate the percentage based on the total of the Amount column divided by the Amount value for each row. Add a SUM formula to the bottom of that column to be sure it totals 100%

Save the file as an "Excel 2004 XML Spreadsheet (.xml)". Then open the file in a text editor, and paste the contents into the Visualforce page below the "your excel output goes here" comment.

Edit the named ranges to make it simpler to refer to elsewhere in your code. (I'm not sure how to explain that better. It makes sense as you start to read through it...I hope.)

<Names>
    <NamedRange ss:Name="Amount_Column" ss:RefersTo="=Opportunities!R2C3:R{!oppSize + 1}C3"/>
    <NamedRange ss:Name="Name_Column" ss:RefersTo="=Opportunities!R2C1:R{!oppSize + 1}C1"/>
    <NamedRange ss:Name="Percentage_of_Total_Column" ss:RefersTo="=Opportunities!R2C4:R{!oppSize + 1}C4"/>
    <NamedRange ss:Name="Stage_Column" ss:RefersTo="=Opportunities!R2C2:R{!oppSize + 1}C2"/>
    <NamedRange ss:Name="Total_Amount_Cell" ss:RefersTo="=Opportunities!R{!oppSize + 2}C3"/>
</Names>

Caspar has used Visualforce to modify the height of each named range, based on the total number of opportunities (oppSize).

Use one of the rows from the spreadsheet's code as a template to build your table inside an apex:repeat block.

<apex:repeat value="{!opportunities}" var="opp">
    <Row>
        <Cell>
            <Data ss:Type="String">{!opp.Name}</Data>
            <NamedCell ss:Name="Name_Column"/>
        </Cell>

        <Cell >
            <Data ss:Type="String">{!opp.StageName}</Data>
            <NamedCell ss:Name="Stage_Column"/>
        </Cell>

        <Cell >
            <Data ss:Type="Number">{!opp.Amount}</Data>
            <NamedCell ss:Name="Amount_Column"/>
        </Cell>

        <Cell ss:StyleID="s75" ss:Formula="=IFERROR(RC[-1]/Total_Amount_Cell,0)">
            <Data ss:Type="Number"></Data>
            <NamedCell ss:Name="Percentage_of_Total_Column"/>
        </Cell>
    </Row>
</apex:repeat>

Add the "totals" row that includes your SUM formulas to the bottom of the table (below the apex:repeat block).

<Row ss:Height="19">
    <Cell ss:StyleID="s67"/>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Totals</Data></Cell>
    <Cell ss:StyleID="s67" ss:Formula="=SUM(Amount_Column)"><Data ss:Type="Number"></Data></Cell>
    <Cell ss:StyleID="s70" ss:Formula="=SUM(Percentage_of_Total_Column)"> 
       <Data ss:Type="Number"></Data>
    </Cell>
</Row>

...and there you have it: One incredibly complicated but perfectly formatted Excel compatible spreadsheet, generated dynamically from data in your org. (Phew!)

My recommendation: Go with the simple version: Is there a way to dynamically fill excel sheets from apex? or invest in a 3rd party solution like Drawloop or Conga Composer.

Answer 2

There exist third-party AppExchange packages that can perform configurable mail merges into existing Excel documents. This sounds like it could be a core use case for one of those tools.

One such product is DrawLoop, which I've used in the past. I found it effective but with a surprisingly steep learning curve, and it's possible to run the merge in Apex.

If you evaluate one or more solutions in that space (I believe both Conga and Apsona have products that do similar things), make sure to do a trial as sometimes formatting can be a challenge and it sounds like you have a formatting-intensive use case.

Answer 3

One way to do this would be to create a Visualforce page that will render as an xls file. You can do an HTTP call to this page from Apex, and attach the file that's returned to your email. Note the contentType in the apex:page tag.

Sample Visualforce page (from Knowledge Article: Export data in Excel format using Visualforce ).

<apex:page controller="contactquery" contentType="application/vnd.ms-excel#SalesForceExport.xls" cache="true">
<apex:pageBlock title="Export Results" >
    <apex:pageBlockTable value="{!cs}" var="contact">
        <apex:column value="{!contact.ID}"/>
        <apex:column value="{!contact.Name}"/>
    </apex:pageBlockTable>
</apex:pageBlock>

...and controller:

public class contactquery{
    public List<Contact> cs{get; set;}
    public contactquery() {
        cs = new List<Contact>();
        for (Contact c : [Select id, Name from Contact]){       
           cs.add(c);
        }
    }
}

To save this excel document as a Salesforce File

//Set up the page
PageReference pgRef= Page.*YourVFPageName*;

//Create the File
ContentVersion cv = new ContentVersion();
cv.VersionData = pgRef.getContent();
cv.Title = '*YourFileName*.xls';
cv.PathOnClient = '*YourFileName*.xls';
insert cv; 

//Link the File to Parent Record
ContentDocumentLink cdl = new ContentDocumentLink();
cdl.ContentDocumentId = [SELECT Id, ContentDocumentId FROM ContentVersion WHERE Id =: cv.Id].ContentDocumentId;
cdl.LinkedEntityId = *YourRecordId*;
cdl.ShareType = 'V';
insert cdl;