Download excel with multiple sheets in visualforce page

In this blogpost, i am going to share how to export an excel from visualforce page with multiple sheets in it.

renderAs attribute in the <apex:page> component gives you flexibility to download a visualforce page's output in pdf or xls. In this case, we will use XLS by using following code:

By using renderAs = text/xml, we will get the vf page output as excel, that means we can use excel's code to make Excel compiler understand its syntax. Excel understands data in XML form, so we are going to create XML for excel to understand. 

 - The first thing we have to use is xlsHeader, which is a string that i am setting in controller just so that i can use it in my page. you can hard-code it in the page as well. This should be the first element in your page. This contains the first element of any XML file which is <xml> (similar to <apex:page> in vf page)

  

- The 2nd thing to note is the excel works in workbooks. one instance of excel is one workbook. One workbook may have multiple tabs/sheets, whatever you call it, but it should all belong to one workbook. So after my header, i am defining <workbook> component

What comes after workbook is <styles> tag, which as name suggests is similar to css classes. in this example, i am defining a style with the ID ="s1", which tells that text should be in bold and the formatting will be numerical, alignment will on top. Very similar to css classes.

- After that we have the main area, which is called worksheets or tabs in excel. Excel calls them worksheets. So every worksheet will have a name which is defined in the attribute "ss:Name". in our case Accounts & Contacts. 

- After worksheets, we have <table> which defines a table and <rows> which defines a row. In every row we have <cells> and every cell has <data> in it. 

- you can add style to any cell by using <cell ss:StyleId ="classname">, which will apply the style defined in that style tag, as defined before and you can type to any data by using ss:Type, which is like "Format Cell" option in excel UI.

- in this example, i have created 2 worksheets, one for Account & other for Contacts.

Code is available at github

Using reports to create dynamic columns in pageBlock Table

This is 3rd series from the blogpost where i was explaining how we can show data in visualforce pages with the flexibility for the client/admin to change the fields to display on the page without changing the source code. In this example i am using Reports.

In this example, i am leveraging Salesforce's ReportManager class, which has the flexibility to run reports synchronously and async as well. I am going to use async method in this demonstration, but you can also use async methods, description here.

The source code is available at GitHub.

The code is very simple, all you need is reportId. You can hard-code in your class like i did, or you can also pass it as a parameter in the page. If you want to make it more dynamic, you can query reports from your org and show the report names as select options.

 public ID reportId {get;set;}
    public Reports.ReportResults reportResults{get;set;}
    
    public RG_getDynamicColumnsViaReports(){
        reportId='00O90000009S7X5'; //i am hard-coding the id here, but you can also pass it to parameter in page or query it
        runReport();
    }
    
    public void runReport(){
         
        reportResults = Reports.ReportManager.runReport(reportId, true);
    }

If you notice in the code above, all i am doing is running the report using ReportManager's runReport method. That is all you need. It will return the list of Reports.ReportResults class, which contains all your data.

Page:

<apex:page controller="RG_getDynamicColumnsViaReports" sidebar="false">
    <apex:includeScript value="{!URLFOR($Resource.dataTable, 'js/jquery.js')}"/>
<apex:includeScript value="{!URLFOR($Resource.dataTable, 'js/jquery.dataTables.min.js')}"/>
<apex:stylesheet value="{!URLFOR($Resource.dataTable, 'css/jquery.dataTables.css')}"/>
    <apex:form >
        
        <apex:pageBlock >
           
            <table id="outtable" class="reportResults">
           <thead>
               <apex:repeat value="{!reportResults.reportMetadata.detailColumns}" var="colName">
                   <th><apex:outputText value="{!reportResults.reportExtendedMetadata.detailColumnInfo[colName].label}"/></th>
               </apex:repeat>
           </thead>

           <tbody>
               <apex:repeat value="{!reportResults.factMap['T!T'].rows}" var="row">
                   <tr>
                       <apex:repeat value="{!row.dataCells}" var="cell">
                           <td><apex:outputText value="{!cell.label}"/></td>
                       </apex:repeat>
                   </tr>
               </apex:repeat>
           </tbody>
         </table>
        </apex:pageBlock>
    </apex:form>
<script>
    //to make the table UI look nice, i am using datatables.js
    j$ = jQuery.noConflict();
            j$(document).ready( function () {
                var outTable = j$('[id$="outtable"]').DataTable({
                    
                });
            });
</script>
</apex:page>


I have used 2 repeats here in the page. One for iterating in the reportExtendedMetadata to get header labels and 2nd for getting row data, which is being stored in factMap class of exportResults. 

Here is the output:

Using listViews to create dynamic columns in PageBlock in visualForce page

This is 2nd post from the blogpost that explains how can a developer creates a visualforce page with the flexibility for the client or admin to change fields by themselves without involving a developer. In my previous post, i explained about using field sets to do the same. In this post, i will explain another way i.e. using list views from the object.

In this approach, we can create custom list views on the object and use that list view to get all the fields used in the list view and display them in the visualforce page

Here is what you need to do that:
1) Enable rest API urls in remote Site Settings. Since listView method is only available in rest API, we need to do a callout to get the listView details.

2) In the controller, we will do the callout. I have hard-coded 2 parameters for this example, but you can make them dynamic as per your requirement
     a) objectApiName - This will store the API name of the object which you                need to query
     b) listViewId - This is the Salesforce id of the list view. you can query                    it(shown in the code as commented), or you can find it from the list view            URL (it starts with 00B), image below:

3) In the controller, i am getting 2 things from the response
    a) Query parameter - that contains the whole query from list view. YES,                 salesforce creates a query for you automatically
    b) Columns to Display - in the response, we have columns parameters, that           contains api name of the fields used in the listView. This will be used to             show the columns from the query. You can also split the columns name             from 1st parameter(query)

4) That is it, rest of the things are pretty obvious. A vf page to display the query     results in page block table.
SOURCE CODE AVAILABLE AT : gitHub

Output :