At times when a custom Visualforce page is required and data needs to be presented in a table with multiple columns, it could be useful for users to be able to lock or freeze columns in order to scroll through the data without losing sight of key data points. This type of functionality is of course familiar to many of us who have used any spreadsheet application, however, it would be great to be able to do this from a table in a Visualforce page. I would like to use this post to present a method of doing so that does not involve many steps. In order to do so I will be using the following components.
- jquery
- Datatables – a table plug-in for jquery
- FixedColumns – an extension of Datatables to facilitate column freezing.
To keep this example simple I will not be using any Apex controller classes with my Visualforce page. Instead I will be the recordvarset attribute from the apex:page component to get Account records. If records need to pulled via an Apex controller you can add that to your own project and can adapt it to the below example. Here is a great blog post on how to work with Datatables and Apex.
Initial Setup
Below snippet is what is needed to start. As mentioned above I will be using Account records in the example. I have included all the libraries and css files needed for the components I will be using. I recommend saving those included files and storing them as Static Resources and referencing the files via that method instead for your own project.
<apex:page sidebar="false" standardController="Account" recordSetVar="accounts">
<!--All JS Libraries and CSS required for this example-->;
<apex:includeScript value="https://code.jquery.com/jquery-1.11.1.min.js"/>
<apex:includeScript value="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"/>
<apex:includeScript value="https://www.datatables.net/release-datatables/extensions/FixedColumns/js/dataTables.fixedColumns.js"/>
<apex:stylesheet value="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.css"/>
<apex:stylesheet value="https://www.datatables.net/release-datatables/extensions/FixedColumns/css/dataTables.fixedColumns.css"/>
<html>
<body>
</body>
</html>
</apex:page>
Building the Table
Instead of using a pageblocktable component I will be using a html table to render the account records onto the page. In order to so I will be iterating through the account records using an apex:repeat component and writing all the rows of data for the table. Note that I am using thead and th to specify the column headers and tbody to specify the rows and cells of the table. Datatables requires these tags in order for it to render the formatted table.
<apex:page sidebar="false" standardController="Account" recordSetVar="accounts">
<!--All JS Libraries and CSS required for this example-->
<apex:includeScript value="https://code.jquery.com/jquery-1.11.1.min.js"/>
<apex:includeScript value="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"/>
<apex:includeScript value="https://www.datatables.net/release-datatables/extensions/FixedColumns/js/dataTables.fixedColumns.js"/>
<apex:stylesheet value="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.css"/>
<apex:stylesheet value="https://www.datatables.net/release-datatables/extensions/FixedColumns/css/dataTables.fixedColumns.css"/>
<html>
<body>
<table id="accTable" class="display" cellspacing="0" width="100%">
<thead>
<th>Account Id</th>
<th>Account Name</th>
<th>Account Owner</th>
<th>Site</th>
<th>Number of Employees</th>
<th>Website</th>
<th>Phone</th>
<th>Annual Revenue</th>
<th>Industry</th>
<th>Type</th>
<th>SLA</th>
<th>Number of Locations</th>
</thead>
<tbody>
<!-- Use apex:repeat to render each account record as a row and cell for the table-->
<apex:repeat value="{!accounts}" var="acc">
<tr>
<td>{!acc.Id}</td>
<td>{!acc.Name}</td>
<td>{!acc.Owner.Name}</td>
<td>{!acc.Site}</td>
<td>{!acc.NumberOfEmployees}</td>
<td>{!acc.Website}</td>
<td>{!acc.Phone}</td>
<td>{!acc.AnnualRevenue}</td>
<td>{!acc.Industry}</td>
<td>{!acc.Type}</td>
<td>{!acc.SLA__c}</td>
<td>{!acc.NumberofLocations__c}</td>
</tr>
</apex:repeat>
</tbody>
</table>
</body>
</html>
</apex:page>
So now we have our table created onto the page, however, it has no styling or formatting so we will have to initialize Datatables. Note that the table has an id attribute value of “accTable”, we will be using this in the next section.
Initialize Datatable
At this point we will need to introduce Javascript and jQuery into the mix and in order to format the plain html table and give it a better visual. Right below the closing html tag I have added the following script.
- A variable, table, to store the return value of the Datatable initialization.
- A javascript function (initializeDatatable) to facilitate initialization and set any type of settings needed. Notice that I am using the the id attribute of the table to initialize Datatable.
- initializeDataTable is called when the document page is ready.
Here is the example I followed from the Datatable.net site.
............................
</apex:repeat>
</tbody>
</table>
</body>
</html>
<script type="text/javascript">
var table;
$(document).ready( initializeDataTable());
function initializeDataTable(){
table = $('#accTable').DataTable( {
scrollY: "300px",
scrollX: true,
scrollCollapse: true,
paging: false
} );
}
</script>
</apex:page>
We now have Datatable initialized. The table can be filtered via the search function in real-time and sortable without any further configuration. 
Now onto the main goal of the example!
Applying FixedColumns Extension
This is relatively easy as you only need to add a few more lines to the intializeDataTable function. When we apply FixedColumns the table variable and additional settings are passed as parameters. We are using the leftColumns settings which specifies how many columns starting from the left needs to be locked. Visit the FixedColumn page to see additional examples on how to use FixedColumns.
....................................
</table>
</body>
</html>
<script type="text/javascript">
var table;
$(document).ready( initializeDataTable());
function initializeDataTable(){
table = $('#accTable').DataTable( {
scrollY: "300px",
scrollX: true,
scrollCollapse: true,
paging: false
} );
new $.fn.dataTable.FixedColumns( table, {
leftColumns: 1
} );
}
</script>
Currently we are only specifying the first column from the left to be locked during horizontal scrolling, however, we can add a bit more functionality by allowing the user to choose which column to start freezing. 
Column Freezing Options
To allow users to choose which column to start freezing for horizontal scroll we will add html select options right above the table tag. The select options will comprise of the column header labels specified along with a “None” option which will be selected by default. Each option will have a numerical value which will correspond to the position of the column in the table from the left with 1 being the left-most column. The select tag also has an id attribute called “freezeColSel”. The select tag has an onchange attribute which calls a function called freezeColChange.
<apex:page sidebar="false" standardController="Account" recordSetVar="accounts">
<apex:includeScript value="https://code.jquery.com/jquery-1.11.1.min.js"/>
<apex:includeScript value="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"/>
<apex:includeScript value="https://www.datatables.net/release-datatables/extensions/FixedColumns/js/dataTables.fixedColumns.js"/>
<apex:stylesheet value="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.css"/>
<apex:stylesheet value="https://www.datatables.net/release-datatables/extensions/FixedColumns/css/dataTables.fixedColumns.css"/>
<html>
<body>
<apex:outputText >Freeze Columns Starting From: </apex:outputText>
<select onchange="freezeColChange();" id="freezeColSel">
<option value="0" selected="selected">None</option>
<option value="1" >Account Id</option>
<option value="2" >Account Name</option>
<option value="3" >Account Owner</option>
<option value="4" >Site</option>
<option value="5" >Number of Employees</option>
</select>
<table id="accTable" class="display" cellspacing="0" width="100%">
<thead>
<th>Account Id</th>
.....................................................
A few changes are made to the javascript functions. In the initializeDataTable function, the value of the selected column option is stored in the freezeColIdx variable (at initial page load None is selected to the value will be zero so no columns are locked). Instead of specifying an explicit number for the leftColumns parameter will be using the freezeColIdx variable.
<script type="text/javascript">
var table;
$(document).ready( initializeDataTable());
function initializeDataTable(){
var freezeColIdx = $( "#freezeColSel" ).val();
table = $('#accTable').DataTable( {
scrollY: "300px",
scrollX: true,
scrollCollapse: true,
paging: false
} );
new $.fn.dataTable.FixedColumns( table, {
leftColumns: freezeColIdx
} );
}
function freezeColChange(){
table.destroy();
initializeDataTable();
}
</script>
The new function added freezeColChange (which is called when the select option is changed) removes all Datatable enhancements and reverts the table back to its state before Datatable initialization by calling the destroy function. The Datatable is then immediately re-initialized by calling initializeDataTable function. The initializeDataTable function will then use the select option value to apply the correct left columns freeze.
That concludes this example. I hope you find this useful and can use it in your Salesforce projects. Below is the completed visualforce page along with some screen capture examples of the page in action. Thank you for reading and hope to add more soon!
<apex:page sidebar="false" standardController="Account" recordSetVar="accounts">
<apex:includeScript value="https://code.jquery.com/jquery-1.11.1.min.js"/>
<apex:includeScript value="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"/>
<apex:includeScript value="https://www.datatables.net/release-datatables/extensions/FixedColumns/js/dataTables.fixedColumns.js"/>
<apex:stylesheet value="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.css"/>
<apex:stylesheet value="https://www.datatables.net/release-datatables/extensions/FixedColumns/css/dataTables.fixedColumns.css"/>
<html>
<body>
<apex:outputText >Freeze Columns Starting From: </apex:outputText>
<select onchange="freezeColChange();" id="freezeColSel">
<option value="0" selected="selected">None</option>
<option value="1" >Account Id</option>
<option value="2" >Account Name</option>
<option value="3" >Account Owner</option>
<option value="4" >Site</option>
<option value="5" >Number of Employees</option>
</select>
<table id="accTable" class="display" cellspacing="0" width="100%">
<thead>
<th>Account Id</th>
<th>Account Name</th>
<th>Account Owner</th>
<th>Site</th>
<th>Number of Employees</th>
<th>Website</th>
<th>Phone</th>
<th>Annual Revenue</th>
<th>Industry</th>
<th>Type</th>
<th>SLA</th>
<th>Number of Locations</th>
</thead>
<tbody>
<apex:repeat value="{!accounts}" var="acc">
<tr>
<td>{!acc.Id}</td>
<td>{!acc.Name}</td>
<td>{!acc.Owner.Name}</td>
<td>{!acc.Site}</td>
<td>{!acc.NumberOfEmployees}</td>
<td>{!acc.Website}</td>
<td>{!acc.Phone}</td>
<td>{!acc.AnnualRevenue}</td>
<td>{!acc.Industry}</td>
<td>{!acc.Type}</td>
<td>{!acc.SLA__c}</td>
<td>{!acc.NumberofLocations__c}</td>
</tr>
</apex:repeat>
</tbody>
</table>
</body>
</html>
<script type="text/javascript">
var table;
$(document).ready( initializeDataTable());
function initializeDataTable(){
var freezeColIdx = $( "#freezeColSel" ).val();
table = $('#accTable').DataTable( {
scrollY: "300px",
scrollX: true,
scrollCollapse: true,
paging: false
} );
new $.fn.dataTable.FixedColumns( table, {
leftColumns: freezeColIdx
} );
}
function freezeColChange(){
table.destroy();
initializeDataTable();
}
</script>
<style>
th, td { white-space: nowrap; }
div.dataTables_wrapper {
width: 900px;
margin: 0 auto;
}
</style>
</apex:page>
tccloud 