2

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?

  • What kind of "Excel sheet" are we talking about here? There's several possible ways one might be able to work through this depending on the situation. It's also relatively easy to write binary data in JavaScript for client-side rendering. – sfdcfox Oct 9 '18 at 18:06
  • @sfdcfox Not sure I am understanding exactly what you are talking asking, but essentially it is a single sheet that is highly formatted to act as a form rather than a traditional spreadsheet. Normally I would just output it to a .csv file, but since it does not use any pattern of row and column output, it makes it very tough to lay out this way. – Frank Evers Oct 9 '18 at 19:04
  • For anyone coming back to this question later-- If conga is an option for you, that is probably the easiest way to go. While Shanes solutions are quite excellent, for most use cases it seems that conga can do the job as it did for me with far less effort. – Frank Evers Oct 10 '18 at 16:16
5

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.

  • This is awesome. (Maybe crazy, but definitely awesome). +1. – David Reed Oct 10 '18 at 0:46
  • Completely crazy. I can't wait to try it! ;) – Shane Steinfeld Oct 10 '18 at 1:43
  • 1
    I ve done that in past. Only issue I got that once you open that downloaded excel file, you get message like 'this file version is different or not correct, do you still want to open it' ...however once you accept ..You can view the excel..I tried to create that excel file in version 2007 and latest but could not find any way! – Ayub Oct 10 '18 at 2:15
  • This is fantastic. I think you may be right in that it is a little bit much as far as complexity for the case we are trying to solve but awesome to see some of the tricks of the platform in action like this. I will certainly be playing with this quite a bit whether I actually use it in my implementation or not. Thank you again for your thoroughness in attempting to help me find a solution. – Frank Evers Oct 10 '18 at 12:31
  • I didn't see Excel 2004 xml in Office365, but it did work with regular Excel xml format. Thanks!! – Dean M. Nov 3 '18 at 16:12
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.

1

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);
        }
    }
}
  • Very interesting! I like the thought, but a couple follow up questions for you. Mainly, once sent to the user, is there an easy way to take this from a visual force file into excel as a xlsx or similar file for further editing, or does this basically just give them an excel like table for viewing on a visual force page that they won't be able to save and use in excel on their end? – Frank Evers Oct 9 '18 at 19:06
  • 1
    Yes, it gives you an actual Excel document, not just a page. In fact, they never see the page. The page is just there to let you handle formatting and filling in information. When you load the page, it gives you back an Excel file, not HTML. – Shane Steinfeld Oct 9 '18 at 20:22
  • 1
    It gives you a sort of Excel file, though, right? Excel loads this file in the same manner it loads Salesforce report exports - it "knows" that this isn't a real Excel file but an HTML table, and not all of its presentation/behavior is the same as with a native .xlsx file. – David Reed Oct 9 '18 at 21:17
  • 1
    @DavidReed -- I think so, but I haven't had a reason to try it yet!. I know that for PDFs it gives you a legit pdf file. (I think there's a new way to get pages via HTTP in and have them converted to pdf in Apex now. Even better.) Since we're just changing the contentType, it probably is just plain o'l HTML. – Shane Steinfeld Oct 9 '18 at 21:27
  • 1
    I just put this together in my dev org and @DavidReed is right -- it downloads an xls file that Excel does open, but it's HTML content that Excel is nice enough to render for us. It does have editable fields, but the formatting is exactly like it was on the Visualforce page -- all white background, no gridlines, etc. It would take some work to get it looking good. – Shane Steinfeld Oct 9 '18 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.