Gridx in XPages – 27: Exporting to Excel

No application would be complete without the ability to export all of the data to Excel. </sarcasm> In reality, this request still comes up a lot, so it’s a very handy feature that Gridx provides the ability to export the grid data. In this post, I’ll show what the exporter module provides, the easy way to send the data in CSV format, and how to tie the process together.

Gridx Series

Gridx in XPages — Entire Series

High Level Steps to Export

The Export module doesn’t really do the whole job for you; what it really does is provide you the data in CSV format and then you have to finish the job.

Here are the high-level steps that are required:

  1. Include the Export module in the require statement
  2. Write two functions to get the data to export, then open the page to write it out
  3. Use a JSON RPC method to put that data into a scope variable
  4. Open an XAgent page to actually write out the data
  5. Add a button to call the JavaScript function to run the whole process

1. Including the Export Module

This module doesn’t get added to the grid, but it needs to be available in order to call from JavaScript.

Here’s the require statement from my sample page:

require([ 
  &quot;gridx/Grid&quot;, 
  &quot;dojo/store/Memory&quot;,
  &quot;gridx/core/model/cache/Sync&quot;, 
  &quot;gridx/modules/ColumnResizer&quot;,
  &quot;gridx/modules/NestedSort&quot;, 
  &quot;gridx/modules/Filter&quot;,
  &quot;gridx/modules/filter/FilterBar&quot;,
  &quot;gridx/modules/filter/QuickFilter&quot;,
  &quot;gridx/modules/HiddenColumns&quot;, 
  &quot;gridx/modules/Persist&quot;,
  &quot;gridx/modules/HeaderMenu&quot;,
  &quot;gridx/support/exporter/toCSV&quot;,
  &quot;gridx/modules/VirtualVScroller&quot;,
  &quot;gridx/modules/extendedSelect/Column&quot;,
  &quot;gridx/modules/move/Column&quot;,
  &quot;gridx/modules/dnd/Column&quot;,
  &quot;dojo/date/locale&quot;,
  &quot;dojo/currency&quot;,
  &quot;gridx/modules/Bar&quot;,
  &quot;dijit/Toolbar&quot;,
  &quot;dijit/form/Button&quot;, 
  &quot;dojo/domReady!&quot; 
  ], 
  function(Grid, MemoryStore, Cache, ColumnResizer, NestedSort, Filter, FilterBar, QuickFilter, HiddenColumns, Persist, HeaderMenu, CSV_Export, VirtualVScroller, colSelect, colMove, colDnD, locale, currency, Bar, Toolbar, Button) {    

2. Functions to Retrieve Data and Write it Out

The first function uses the export module to prepare the data for export.

Note: it needs to be within the require() statement so the CSV_Export is within scope.

getGridData = function() { 
  var args = {
    selectedOnly: false,
    omitHeader: false,
    useStoreData: false,
    separator: ','
  };

  CSV_Export(grid, args).then(exportData);
};	

exportData() is a callback to run once the data is ready. (You can also pass in callbacks to run on error and as the export progresses, if you want to update a progress meter).

This function calls a JSON RPC method to put the data into a scope variable. When done, it opens a new window with an XAgent page to actually write out the data.

function exportData(data) {
    rpcExport.exportData(data).addCallback(function() {
    // Open the XAgent page to write out the data	
    window.open('ExportGridx.xsp');	
  })

3.JSON RPC method to put that data into a scope variable

JSON RPC ties everything together and coordinates the timing. When I first worked on exporting with the dojo EnhancedGrid, this was a sticking point, because there was sometimes a timing issue — the data wasn’t ready before the XAgent was opened, so I tried a few workarounds that weren’t always reliable.

With this method, the client-side JavaScript function passes in the data to export and the RPC method puts it into a scope variable so it can be picked up by the XAgent page. (Note: This control is available in the extension library, 8.5.3 Upgrade Pack 1, or Notes 9.)

&lt;xe:jsonRpcService id=&quot;jsonRpcService1&quot; serviceName=&quot;rpcExport&quot;&gt;
  &lt;xe:this.methods&gt;
    &lt;xe:remoteMethod name=&quot;exportData&quot;
      script=&quot;sessionScope.gridxExport = csvData;&quot;&gt;
      &lt;xe:this.arguments&gt;
        &lt;xe:remoteMethodArg name=&quot;csvData&quot; type=&quot;string&quot;&gt;&lt;/xe:remoteMethodArg&gt;
      &lt;/xe:this.arguments&gt;
    &lt;/xe:remoteMethod&gt;
  &lt;/xe:this.methods&gt;
&lt;/xe:jsonRpcService&gt;

On a callback that runs when the RPC method is done, the exportData() function opens the XAgent page. This makes the timing of the whole process flow smoothly.

(Note: if you’re not familiar with JSON RPC and you’ll be at ConnectED, check out my session with Paul Calhoun [BP105], where we’ll dig into the benfits of it.)

4. XAgent to Export Data

Once the data is ready, the code opens a separate window for an XAgent page. The page has rendered set to false and it runs code on afterRenderResponse to write out the data from the scope variable.

If you have Excel installed, you will have the opportunity to open the file directly in Excel. (You can also save it to the machine.)
Gridx 27 - B

Here is the entire source of my XAgent page:

&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;
&lt;xp:view xmlns:xp=&quot;http://www.ibm.com/xsp/core&quot; rendered=&quot;false&quot;&gt;

	&lt;xp:this.afterRenderResponse&gt;&lt;![CDATA[#{javascript:
// This XAgent just writes out the CSV data from the grid that is stored in a scope variable.
var exCon = facesContext.getExternalContext();
var writer = facesContext.getResponseWriter();
var response = exCon.getResponse();
response.setContentType(&quot;text/csv;charset=utf-8&quot;);
 
response.setHeader(&quot;Cache-Control&quot;, &quot;no-cache&quot;);
response.setHeader(&quot;Content-Disposition&quot;, &quot;attachment; filename=GridExport.csv&quot;);
writer.write(sessionScope.gridxExport);
writer.endDocument();}
]]&gt;
	&lt;/xp:this.afterRenderResponse&gt;
&lt;/xp:view&gt;

5. Button to Run the Export

Finally, we just need a button to call the exportGridData() function.

&lt;xp:button value=&quot;Export&quot; id=&quot;button1&quot;&gt;
  &lt;xp:eventHandler event=&quot;onclick&quot; submit=&quot;false&quot;&gt;
    &lt;xp:this.script&gt;&lt;![CDATA[getGridData();]]&gt;&lt;/xp:this.script&gt;
  &lt;/xp:eventHandler&gt;
&lt;/xp:button&gt;

Here’s an example of grid data:

Gridx 27 - A

Here’s the default layout in Excel after exporting:
Gridx 27 - C

Enhancements

In its current form, it will export everything visible in the grid. (If the grid is filtered, it will export the results of the filter.)

There are several things you can do to enhance the process:

  • Export selected rows only
  • Put the button to export the data into a toolbar
  • Add formatters to modify the data as it’s exported
  • Use Apache POI to write a proper Excel spreadsheet

Gridx in XPages – 26: Column and Row Locking

Another handy feature of Gridx is the ability to lock one or more columns or rows in place while you scroll through the grid. It’s similar to the Freeze Panes feature of Excel, although you can only lock one or the other at a time. In this post, I’ll show how to implement column and row locking.

Gridx Series

Gridx in XPages — Entire Series

Column Locking

When you lock one or more columns, they remain in place on the left side of the grid, while the rest of the columns can scroll horizontally.

In this screen shot,the first two columns are locked, so I can see the person’s name as I scroll through the rest of the columns. The horizontal scroll bar does not appear beneath the locked columns.

Gridx 26A

As with most Gridx features, column locking is very simple to implement.

  1. Include the ColumnLock module in your require statement (gridx/modules/ColumnLock)
  2. Add it to the grid’s modules list
  3. Add a property to the grid to define how many columns to lock by default:
grid = new Grid(
{
  id: &quot;my_gridX&quot;,
  cacheClass: Cache,
  store: store,
  structure: columns,
  vScrollerBuffSize:10,
  columnLockCount: 2,
  modules: [
    Resizer,
    VirtualVScroller,
    ColumnLock,
    colSelect,
    colMove,
    colDnD
  ]
});

You can also programmatically change the number of locked columns or clear all column locking.

This statement will lock the first 3 columns in the grid:

grid.columnLock.lock(3)

This statement will clear column locking (Note: There’s an article in the documentation that lists this method name incorrectly):

grid.columnLock.unlock();

If you have column drag and drop in place, you can even rearrange columns and then lock them based on the new arrangement, which gives you a lot of flexibility.

Row Locking

Row locking works pretty similarly, just not nearly as well.

In this example, I’ve set it up to lock the first two rows while the rest of the rows scroll.

Gridx 26B

The implementation is almost identical to column locking:

  1. Include the ColumnLock module in your require statement (gridx/modules/RowLock)
  2. Add it to the grid’s modules list
  3. Add the rowLockCount property to the grid to define how many columns to lock by default

There are similar methods to programmatically lock and unlock rows.

grid.rowLock.lock(3);
grid.rowLock.unlock();

Caveat – Row locking only works propertly when virtual scrolling is not used. When virtual scrolling is used, it ends up leaving blank space at the top of the grid when the locked rows go out of scope as the data scrolls.

Column and Row Locking Together

I tried implementing Column and Row locking together, but it does not work. The grid can’t even load without a JavaScript error saying that a script needs to be stopped. (I tested in current Firefox, Chrome, and IE).

Ultimately, column locking works well, but it seems to me that it’s better to leave the row locking alone.

Gridx in XPages – 25: Customizing the Top and Bottom Bars

In the last post, I showed how to add a button toolbar to the grid. In this post I’ll show a few more ways to customize the top and bottom bars.

Gridx Series

Gridx in XPages — Entire Series

Styling Content

Picking up from where we left off last time with a button toolbar added to the top of the grid, we now have a grid where there’s a button toolbar left justified on the top line.

Gridx 25 A

The Bar contents are added in a table structure, so you can modify it to pass styling to update the layout as desired.

If you’d rather center it you can modify the barTop attribute like this:

barTop: [
  {plugin: toolbar, style: 'text-align: center;'}
],

Gridx 25 B

You can pass other styling information via the style property as well.

If you have multiple items on a bar and/or multiple rows, you can also set rowSpan and colSpan attributes.

Adding plugins

Another useful plugin to add to the bar is Summary. This plugin tells you how many rows are in the data store and how many are currently selected. (If you filter the grid, then the count will be updated to the count of rows matching the filter.)

In this example, I’ve added it to the bottom of the grid (after including "gridx/support/Summary" in the require statement and giving the module the name Sumamary):

barBottom: [
  Summary
],

Gridx 25 C

The other primary use for the bars seems to be for paging plugings. If you decide to use paging rather than infinite scrolling, you can use the bars for paging links, a drop-down page selection, and a page size selection. Here’s an example from the documentation:

Paging Plugins

You can also add custom plugins to the bars, so there’s a lot of flexibility to meet your requirements. Take a look at this article for more information.

Finally, as I mentioned in the last post you can also put custom HTML content in the bars, like this:

barBottom: [
  {content: "<h1>HTML Stuff Here</h1>", style: "color: red;"}  
],

Gridx in XPages – 24: Adding Toolbars to the Grid

Gridx provides the ability to add a toolbar with buttons at the top and/or bottom of the grid. In this post, I’ll show how to implement it.

Gridx Series

Gridx in XPages — Entire Series

Gridx 24

1. Include Required Modules

The Bar module is required for Gridx:
"gridx/modules/Bar",

(Note: There’s a ToolBar module defined, but it’s deprecated, so Bar is the one to use.)

A couple of dijit modules are also required for building the toolbar and adding buttons to it:
"dijit/Toolbar",
"dijit/form/Button",

2. Create An Event Handler

You’ll need to define an event handling function for each button in the toolbar. In this example, we’ll start with a simple logging statement just to see it work.

function myButtonHandler() {
  console.log('Clicked button');
}

3. Create the Toolbar

The next step is to create the Toolbar object and add a button to it.

var toolbar = new Toolbar({}, &quot;toolbar&quot;);
var myButton = new Button({
  label:&quot;Do Something&quot;,
  iconClass:&quot;dijitIconFunction&quot;,
  onClick:myButtonHandler    
});
toolbar.addChild(myButton);
toolbar.startup();

Line 1 creates the Toolbar object.

Lines 2-6 create a Button.

Line 7 adds the button to the toolbar.

Line 8 generates the toolbar.

Dojo Icons

Dojo has a (limited) set of icons built in for use with buttons. In dijit buttons, they are defined by a class name (which uses CSS to get the proper section of an image sprite).

There are two set — common icons and editor icons. I have yet to find documentation with a simple list of images and their class names, but here are links to see the common icon sprite and editor icon sprite

Related CSS files define the area of each image. You can use a resource like this to see the class names in commonIcons.css or editorIcons.css.

4. Add the Toolbar to the grid

The last thing you need to do is add the toolbar to the grid object. This can be done via the barTop or barBottom properties. (Make sure that the Bar module is also in the grid’s modules list.)

In this example, it’s added to the top:

grid = new Grid(
{
  id: &quot;my_gridX&quot;,
  cacheClass: Cache,
  store: store,
  structure: columns,
  vScrollerBuffSize:10,
  barTop: [
    toolbar
  ],
  modules: [
    Resizer,
    NestedSort,
    Filter,
    FilterBar,
    QuickFilter,
    VirtualVScroller,
    Bar
  ]
});

Note: If you try to add the same toolbar to the bottom and top, it seems to only appear at the bottom.

The structure of the barTop and barBottom is pretty flexible, so you can do much more than just add a single toolbar. Take a look at this article for information on how to add multiple items to a bar or even add multiple levels of bars.

Bar Content

Toolbars are not limited to buttons – you can use other dijits as well.

You can also pass through pure HTML like this:

barBottom: [
  { content: &quot;&lt;h1&gt;HTML Stuff Here&lt;/h1&gt;&quot;, style: &quot;color: red;&quot;}  
],

Enter to Win a Free Week of Consulting from PSC!

To celebrate IBM Champion recognition, PSC is offering the opportunity to win a free week of consulting (40 hours), including travel costs, from one of our IBM Champions.

Fill out this form to enter: https://contest.psclistens.com/

The winner will be selected on February 13th.

Gridx in XPages – 23: Rearranging Columns with Drag and Drop

Gridx can provide users the ability to rearrange content via drag and drop. This can be very handy in making a grid more flexible, which can, in turn, cut down the number of views or grids that you need to provide. In this post, I’ll show how to implement column drag and drop.

Gridx Series

Gridx in XPages — Entire Series

Rearranging Columns

To rearrange columns, do the following:

  1. Select 1 or more columns to move. (Use CTRL+Click or SHIFT+Click to select multiple columns.)
  2. Move the cursor over the data in one of the selected columns. (The cursor changes to 4 arrows.)
  3. (Left) click and drag to move the column(s)

When you click to start moving, you’ll see an icon that shows the number of columns to be moved. It includes a green circle with a plus sign (+) if you’re currently hovered over a valid place to drop the column(s).

Gridx 23a - Move a Column

You can select non-contiguous columns to move. If you do, it will make them contiguous at the place where you drop them.

Before:

Gridx 23b - Move Multiple Columns

After:

Gridx 23c - After Moving Multiple Columns

Implementation

Three modules are required to make this happen:

"gridx/modules/extendedSelect/Column",
"gridx/modules/move/Column",
"gridx/modules/dnd/Column",

These modules provide the ability to select columns, move columns (programmatically), and drag and drop to rearrange, respectively.

All you need to do is add the modules to the require statement and to the grid’s modules list.

Gridx in XPages – 22: Row Selection

Gridx in XPages – 22: Row Selection

In order to take action on data in the grid, you need to be able to select one or more rows. In this post, I’ll show 3 different modules that provide grid row selection and highlight the differences between them.

Gridx Series

Gridx in XPages — Entire Series

Simple Selection

The first option is simple selection. It allows you to programmatically select/deselect rows, and check row selection status.

It requries adding the gridx/modules/select/Row module to the grid.

Then, you can use javascript to work with the rows by ID.

Select a row by ID:
grid.select.row.selectById('FCE');

Deselect a row by ID:
grid.select.row.deselectById('FCE');

Clear all selections:
grid.select.row.clear();

Check whether a row is selected:
grid.select.row.isSelected('FCE');

Indirect Selection

Indirect selection is more user-oriented. It adds a checkbox to the left of each row in order to allow selection.

Gridx 22 - Indirect Select

It requires 3 modules in order to work:

"gridx/modules/RowHeader",
"gridx/modules/select/Row",
"gridx/modules/IndirectSelect",

If you want to limit the selection to only 1 row, you can add this property to the grid object:

selectRowMultiple: false

Gridx 22b - Indirect Select

Note: When combined with extended selection, allows for SHIFT+CLICK to select multiple rows.

Row Selection by Clicking on a Cell

By default, you have to click on the checkbox or radio button to select the row. But if you’d like the user to be able to click any cell in the row to goggle selection, you can add a property to the grid:

selectRowTriggerOnCell: true,

Unselectable Rows

If there are any rows in the grid that you do not want to be selectable, you can set a grid attribute that defines the rows by ID (in my example, I’m using

the NoteID for the row ID):

selectRowUnselectable:{
  'FCE': true,
  '9F6': true
},

Gridx 22c - Indirect Select

You can also check whether a row is selectable or and programmatically change whether a row is selectable.

This will get a list of all unselectable rows:
grid.select.row._getUnselectableRows()

Extended Selection

Extended selection allows the user to select one or more rows at a time, using CTRL and SHIFT to span multiple rows for selection.

It requries 2 modules:


"gridx/modules/RowHeader",
"gridx/modules/extendedSelect/Row",

This adds a solid row header to each row where you can click to select that row.

Gridx 22d - Extended Select

You can also SHIFT+CLICK to select a range of rows or CTRL+CLICK to select non-contiguous rows.

There are a number of methods to work with the selection programmatically; take a look at this page for more examples.

Follow

Get every new post delivered to your Inbox.

Join 63 other followers