jQuery DataTables using Excel spreadsheets and Dynamic Converter

On a recent project I worked on, we needed to display a calendar on a site with a list of different events.  From the content owner's perspective, authoring and maintaining this calendar in Microsoft Excel was ideal.  So using Dynamic Converter to convert that to HTML fit the bill.  But they wanted the calendar to be more interactive and dynamic then just a static table. Features such as sorting, searching, pagination and such.  So that's where the DataTables jQuery plug-in makes a perfect solution.  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js" type="text/freezescript" charset="utf-8"> </script> <script type="text/freezescript" language="freezescript" src="http://datatables.net/release-datatables/media/js/jquery.dataTables.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js" type="text/freezescript" charset="utf-8"> </script> <script charset="utf-8"> $(document).ready(function() { $("table:contains('TEAM')").attr("id","TeamTable"); $('#TeamTable').prepend($('').append($('#TeamTable tr:first').remove())); $('#TeamTable').attr("class","display"); oTable = $('#TeamTable').dataTable({ 'bJQueryUI': true, 'sPaginationType': 'full_numbers' }) $('#ChangeDivision').appendTo($('#TeamTable_length')); }); function fnFilterType( area ) { oTable.fnFilter( area, 1 ); }</script>

While the default conversion of the Excel document to a HTML table was close, it still needed a bit of manipulation of the table format to fit what DataTables was looking for.  Luckily, jQuery makes that pretty easy to do as well.  

The following are the steps I took to create this conversion.

  1. The first step is to create your Excel document to work from and to check it in.  The first row should be your column headings and the rows below be your data.



  2. Open Internet Explorer and create a new Dynamic Converter  template through Administration -> Dynamic Converter Admin -> Create New Template.  In 11g, for the Template Format, select 'Classic HTML Conversion Template'.  In 10g it should be set as 'GUI Template'
  3. Edit the new template. Be sure to select Classic HTML Conversion Template as the Template Type.

    Note: If you are running Internet Explorer (IE) 8 or newer, you may encounter the error, "Internet Explorer has closed this webpage to help protect your computer.  A malfunctioning or malicious add-on has caused Internet Explorer to close this webpage."   To avoid this error, go to Tools -> Internet Options -> Advanced and uncheck 'Enable memory protection to help mitigate online attacks' near the bottom.  Restart IE and you should be able to bring up the template editor.

  4. Change the preview to point to the document submitted in step 1.
  5. First we'll remove the heading identifying the sheet from Excel.  Click on Element Setup and go to the Styles tab. 
  6. Click New and enter a Name of 'Heading 1'.  For the Associated element, click New and enter a Name of 'Heading 1'.  Click OK and OK.



  7. Go to the Elements tab and double-click on the Heading 1 check mark in the In Body column to change it to a red X.  Click OK.  The sheet heading should now disappear in the preview



  8. Next we'll want to remove all of the formatting to the text.  Click the Formatting button.  Highlight 'Default Paragraph' and for the Font name, Font color, and Font size, choose 'Don't specify'.



  9. Click on the Paragraph tab and for Alignment, choose 'Don't specify'.
  10. Click on the Tables tab and click the Borders and Sizing button.  For Table width and Cell width, choose 'Don't specify'. 



  11. Check the 'Use column headings' box in the Heading section.  Click OK for the Formatting dialog.



  12. Next we need to insert the JavaScript needed to reformat our table into a DataTable. Click on the Globals button and click on the Head tab.
  13. Check the box for 'Include HTML or scripting code in the Head' and insert the code:

    <!-- jQuery-1.4.4.min.js -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js" type="text/javascript" charset="utf-8">
    </script> 

    <!-- jQuery DataTables -->
    <script type="text/javascript" language="javascript" src="http://datatables.net/release-datatables/media/js/jquery.dataTables.js"></script>

    <!-- Link to the jQuery Demotables Stylesheet      -->
    <link href="http://datatables.net/release-datatables/media/css/demo_table_jui.css" type="text/css" rel="stylesheet" />

    <script type="text/javascript" charset="utf-8">
        $(document).ready(function() {
            $("table:contains('TEAM')").attr("id","TeamTable");
            $('#TeamTable').prepend($('<thead></thead>').append($('#TeamTable tr:first').remove()));   
            $('#TeamTable').attr("class","display");       
            $('#TeamTable').dataTable();
        });
    </script>


    Let's take a look at this code. 

    The first script tag is used to load the jQuery JavaScript libary.  Here we're loading it from Google's hosted APIs.  The next script tag is used to load the DataTables plug-in.  And the next link tag is loading a sample stylesheet to be used with the DataTables plug-in. In this example, I'm calling out to the hosted files.  You may want to download, check-in, and reference them locally to ensure they are always available.

    Inside the next script tag, the script waits until the page finished loading and begins it's function.  The first line in the function inserts the ID attribute onto the table with a value of 'TeamTable' so that we can easily reference it in the following actions.  In order to identify the table, it looks for the text 'TEAM'.  Adjust this appropriately for the text in your table.

    The next line inserts the <thead> </thead> tags around the heading row in the table.  There is no way to configure Dynamic Converter to insert this, so jQuery helps us do it after the fact.

    The third line applies the class 'display' to the table to utilize the DataTables stylesheet to help format the table.  Again, there isn't a way to insert this class with Dynamic Converter, so jQuery can do it for us.

    And finally, it runs the function to perform the DataTables function to transform the table.  It's using its basic 'zero configuration' settings without any options applied.

  14. Click OK to save the template.  Now use the Template Section Rules to target the appropriate spreadsheets with the new template.

Now when you view the HTML conversion of the spreadsheet, you should see it as a DataTable.  You can do things like sort columns, search, and have pagination.



But now that we have it as a DataTable, we can use the different options it offers to give it a different look and experience.

We can first add an additional JavaScript library and stylesheet from the jQuery UI project.  Edit the template again and modify the code being added to the Head section.

<!-- jquery-ui-1.8.6.custom.min.js -->
<script src='https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js' type='text/javascript' charset='utf-8'>
</script>

<!-- jQuery smoothness -->
<link href='http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/themes/smoothness/jquery-ui.css' type='text/css' rel='stylesheet' />

Then we can add some additional options to the DataTable:

oTable = $('#TeamTable').dataTable({
                'bJQueryUI': true,
                'sPaginationType': 'full_numbers'
})

So the bjQueryUI will use the UI library we included above.  And the pagination will show page numbers instead of just arrows.

Then we'll add an option list to do filtering on the table.  Add this line within the $(document).ready(function():

$('#ChangeDivision').appendTo($('#TeamTable_length'));

Then add an additional function to call when the option list changes:

 function fnFilterType( area )
    {
        oTable.fnFilter( area, 1 );
    }

Finally, we'll add the HTML option list to the page.  Click on the HTML tab and add this code in the 'Include HTML or scripting code before the content'. 

<span id="ChangeDivision"><br />
<span class="style6">Show</span> <select onchange="fnFilterType (value)" name="Division">
<option value="" selected="selected">All types</option>
<option value="NFC">only NFC</option>
<option value="AFC">only AFC</option>
</select>
</span>

When you make these additional additions to the editor, it will complain about a runtime error on the page.  This only occurs in the preview window and can be ignored. 

Now we have our updated DataTable:


You can download the completed GUI template for 11g here. The 10g version is here.  If using 11g, be sure to submit it as a "Classic  HTML Conversion Template" and as a "GUI Template" in 10g.  

Special thanks to Paul Thaden for the code on this example!

Comments:

if only it had the drag-and-fill feature that Excel does, and I'd be able to replace the RemoteMetadataUpdater with a jQuery plugin. *sigh!*

Posted by bex on October 17, 2011 at 07:48 AM CDT #

@bex - Did you see this? http://datatables.net/extras/autofill/

Posted by guest on October 18, 2011 at 08:12 AM CDT #

Jquery’s solution did not worked for me. I wanted to do something like excel. Where the top row is fixed and cells are moved.
And also wanted to edit the cells with data by users. But Jquery has very less option to allow this and was giving all kind of errors.
I also used the AUtocomplete in few fields in the excels like view.
So I think to just present data using jquery this way is okay, but achieving excel like functionality using jquery is a dream.
I invented my own method to do that now
And soon will be posting the solution to http://www.eddmpostcardprinting.com
Ravi

Posted by EDDM Printing on June 24, 2012 at 02:15 AM CDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Kyle Hatlestad is a Solution Architect in the WebCenter Architecture group (A-Team) who works with WebCenter Content and other products in the WebCenter & Fusion Middleware portfolios. The WebCenter A-Team blog can be found at: https://blogs.oracle.com/ ateam_webcenter/

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today