Dojo Data Grid – Part 16: Exporting Grid Data

Exporter is another useful plugin available to for the Dojo EnhancedGrid. As the name indicates, it gives you the ability to export grid data. In this post, I’ll show how to implement it to export grid data in CSV format.

Dojo Data Grid Series

Exporter Plugin

The Exporter plugin is a bit tough to get a handle on — it requires more work than most plugins to get up and running.

Essentially, what it does is provide a string variable with the data to export. You have to take it from there and actually export the data.

Once you get it up and running, there’s an API full of methods that you can override in order to customize the output, but that’s beyond the scope of this post.

Using the Plugin

This post starts with the assumption that you have already have a working Dojo Data Grid control (with a REST service supplying the data) and that you have followed the instructions in this post to ensure that your Dojo Data Grid control is actually generating an EnhancedGrid, rather than a standard DataGrid.

From that point, there are two more steps to use the Exporter plugin.

1. Add the CSVWriter module

Grid_16_config1

2. Include the plugin on the grid

Grid_16_config2

Export Functions

At this point, there are two functions available to get you started with exporting data: exportGrid() and exportSelected().

The exportGrid function will export the number of records defined in the REST service’s count property. If a count is not defined,it will export 10 rows.

Fortunately, it also takes a fetchArgs property that allows you to define the starting row and number of rows to export. The example below has those values set up as parameters to the function so you can work with them dynamically.

The exportSelected function provides a handle to the selected rows in the grid and makes them available to export.

Export Process

As I mentioned earlier, the Exporter plugin functions really just give you data that’s ready to be exported; you have to take it the rest of the way.

Unfortunately, this is not as trivial a process as it might sound.

I’ll demonstrate how I made it work, but I’m sure there are ways to improve upon the process. (See the end of this post for a list of methods that I tried that were unsuccessful.)

My process includes these steps:

  1. Store the grid data in a hidden input field
  2. Use SSJS to put the data into a scope variable
  3. Launch a separate XAgent page to read the data and export it

To export, the user will click the appropriate ‘Export’ button…

Grid_16_a1

…Click OK on the prompt informing them that the export is underway (more on this below)…

Grid_16_a2

…Click ‘Open’ or ‘Save’ when the data is exported…

Grid_16_a3

…And view the data

Grid_16_a4

To export selected rows, the user will select rows and click on the Export Selected button…

Grid_16_b1

Grid_16_b2

Export Functions

The code below contains an Output Script block that defines all three export functions (export, export selected, export custom).

They all use a built-in method from the Exporter plugin to obtain the data to export and then put that data into a hidden input field on the form.

<xp:scriptBlock id="scriptBlock1">
<xp:this.value><![CDATA[
// Call the plugin's exportGrid function and store the data to export in an unbound hidden input field.
function exportGridData_All() {
  dijit.byId("#{id:djxDataGrid1}").exportGrid("csv", function (gridData) {
    dojo.byId("#{id:csvToExport}").value = gridData;
    }
  );
}

// Call the plugin's exportGrid function and store the data to export in an unbound hidden input field.
function exportGridData_Custom(intStart, intCount) {
  dijit.byId("#{id:djxDataGrid1}").exportGrid("csv", {fetchArgs: {start: intStart, count: intCount}}, function (gridData) {
    dojo.byId("#{id:csvToExport}").value = gridData;
    }
  );
}

// Call the plugin's exportSelected function and store the data to export in an unbound hidden input field.
function exportGridData_Selected() {
  var gridData = dijit.byId("#{id:djxDataGrid1}").exportSelected("csv");
  dojo.byId("#{id:csvToExport}").value = gridData;
}

]]></xp:this.value>
</xp:scriptBlock>

Here is the hidden input field that I’m using to store the grid data:

<xp:inputHidden id="csvToExport"></xp:inputHidden>

Export Buttons

Each export button controls all 3 steps of the export process.

  1. With client-side code, the button calls an export method in the output script block to retrieve the data and put it in a hidden input field.
  2. With server-side code, it reads the data from the hidden input field and puts it in a scope variable.
  3. With client-side code on the oncomplete event of the server-side code, it launches a new window with the XAgent page to write out the data.

The ‘Export All’ Button is shown below:

<xp:button value="Export All" id="button1">
  <xp:eventHandler event="onclick" submit="true" refreshMode="partial" refreshId="csvToExport">
    <xp:this.script><![CDATA[
      // Step 1 of 3: Execute the Exporter's export function and use client JS to put the data into an unbound hidden input text field
      exportGridData_All();

      // Pop up an alert box -- this delay provides enough of a delay to let the server-side code read the data from the hidden input field and export it
      alert('Exporting Data...');
]]></xp:this.script>

    <xp:this.action><![CDATA[#{javascript:
      // Step 2 of 3: Read the csv data from the hidden field and put it in a scope variable so the XAgent can retrieve it
      sessionScope.csvExport = getComponent('csvToExport').getValue();}]]>
    </xp:this.action>

    <xp:this.onComplete><![CDATA[
      // Step 3 of 3: oncomplete of the server code that puts the data in the scope variable, open the XAgent page to read and export
      window.open('Grid_16_ExportToCSV_XAgent.xsp');
      dojo.byId("#{id:csvToExport}").value = '';]]>
    </xp:this.onComplete>
  </xp:eventHandler>
</xp:button>

For the ‘Export Selected’ button, the only difference is that the client-side script’s first line (line 5) is this:

exportGridData_Selected();

For the ‘Export Custom’ button, the only difference is that the client-side script’s first line (line 5) is this:

exportGridData_Custom(20, 15);

XAgent Code

The XAgent’s afterRenderResponse event writes out the CSV data. In line 9, it reads the data from the scope variable (line 9) and writes it out in a way that the browser will download it as a CSV file.

// 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("text/csv;charset=utf-8");

response.setHeader("Cache-Control", "no-cache");
response.setHeader("Content-Disposition", "attachment; filename=GridExport.csv");
writer.write(sessionScope.csvExport);
writer.endDocument();

Beware of Popup Blockers

Be aware that users must grant access to your site to display popups, or else the last step will not work. (If that’s impossible to work around in your environment, you could use location.href=XAgentPage.xsp, rather than window.open).

Further Enhancement

CSV files will generally open just fine in Excel, but, if you need to export the data as an Excel file, you can set the content type to application/vnd.ms-excel and write the data out in the format of an HTML table.

You can also customize the format of the data as it is written out by overriding the available API methods, detailed in the dojo documentation:
http://dojotoolkit.org/reference-guide/1.6/dojox/grid/EnhancedGrid/plugins/Exporter.html

Failed Attempts

This section lists some of the other methods that I attempted to make this work.

Attempt #1
My original intent was to only use client-side JavaScript to write the data out directly.

There’s an encodeURI method in JavaScript. You can actually put encoded CSV data into a URL and launch it and it will attempt to download. But it didn’t work in IE. The other problem is that there doesn’t appear to be a way to define the file name/type, so it kept downloading the data with a .part extension.

location.href='data:application/download,' + encodeURIComponent(csvData)

Attempt #2

In HTML5, there’s a new link property called download that can be used to specify a download filename.

I tried to use code like this to add a link to the page with the required properties and force the click of the link but (a) the link.click() only works in IE and (b) the download property does not work in IE.

var encodedUri = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute("href", encodedUri);
link.setAttribute("download", "my_data.csv");
link.click();

Attempts 3-5
Next, I had hoped to use client JS to write the data into a field bound to a scope variable, partially refresh the field to push the data to the scope variable, then launch the XAgent page to pick up the data from the scope variable, but it didn’t work.

It was a timing problem. Both the view.postScript method and the button event handler’s onComplete events both must have started running before the partial refresh was done, because the data was not available to the XAgent. I even updated it to use XSP.partialRefreshPost to try to push the data to the scope variable and execute the XAgent page load with an oncomplete callback, but that still didn’t fix the timing problem.

If I put that code on a separate button and manually clicked it, then that provided enough time for the partial refresh to get the data into the scope variable. But that wasn’t good enough. I wanted this to be a one-click process.

Attempt 6

My next idea was to move past the dependency upon a partial refresh and just use SSJS to put the data directly into a scope variable.

Client JS to put data into a text field (NOT BOUND, BUT MUST BE AN XPAGE CONTROL)

Server JS on button to take data from component and put in scope var immediately

Client JS on complete to open XAgent page

However, the timing was still problematic. It just didn’t update fast enough for the data to be available to SSJS when the CSJS had put it in the field immediately prior. If I click the button a second time, it works fine, but it’s probably picking up the previously-stored data.

Attempt 7

I tried a few different methods (client JS and SSJS) to add delays and timers to the process (wrapped in loops to pause and then check), but none of it worked.

Attempt 8

Tweaking attempt 6 a bit, I decided to split steps 2 and 3 out to a separate hidden button and have the first button trigger the second button to execute.

The timing issue was still apparent.

The Solution

Ultimately, this is why the alert box is there in step 1. Waiting for the user to click it provides the required delay for the data to be available to SSJS.

It’s mildly annoying, but the process works.

However, if you have suggestions on how to improve this process, I’d love to hear them!

Update

The timing issue is still prevalent in some browsers. It appears to be due to the time it takes the plugin function to retrieve the data. The code doesn’t wait for that function to complete, it just moves right along, so the data isn’t available by the time the server-side code runs.

To mitigate this problem, the code can be split into two separate buttons. The first button runs the client-side function to retrieve the data to export. It then waits one second (by setting a timeout for 1,000 milliseconds) and then triggers a ‘click’ on the second button, which is hidden, but has the code to retrieve the data to a scope variable and then launch the XAgent page to write it out.

The logic is virtually the same, just split into separate buttons. The differences are that the delay is added and the user prompt is not needed. In this case, the second button could be used by all 3 of the other export buttons, because steps 2 and 3 in the process are the same.

Here is the updated code for an Export Custom button (with hard-coded parameters) and the second hidden button to finish the export. If you’re still seeing a timing issue, you can increase the time of the delay before triggering the second button.

<xp:button value="1 Export Custom" id="btnExportStep1">
  <xp:eventHandler event="onclick" submit="false">
    <xp:this.script><![CDATA[// Step 1 of 3: Execute the Exporter's export function and use client JS to put the data into an unbound hidden input text field
      exportGridData_Custom(20, 15);
      int=window.setTimeout(function(){dojo.byId('#{id:btnExportStep2}').click()},1000);]]>
    </xp:this.script>
  </xp:eventHandler>
</xp:button>

<xp:button value="2 Export Custom" id="btnExportStep2" style="display:none">
  <xp:eventHandler event="onclick" submit="true"
    refreshMode="partial" refreshId="dummyTarget">
    <xp:this.onComplete><![CDATA[// Step 3 of 3: oncomplete of the server code that puts the data in the scope variable, open the XAgent page to read and export
      window.open('Grid_16_ExportToCSV_XAgent.xsp');
      dojo.byId("#{id:csvToExport}").value = '';]]>
    </xp:this.onComplete>
    <xp:this.action><![CDATA[#{javascript:// Step 2 of 3: Read the csv data from the hidden field and put it in a scope variable so the XAgent can retrieve it
      sessionScope.csvExport = getComponent('csvToExport').getValue();}]]>
    </xp:this.action>
  </xp:eventHandler>
</xp:button>
Advertisements

16 responses to “Dojo Data Grid – Part 16: Exporting Grid Data”

  1. Alan Hurt says :

    I use the Enhanced grid in a non-XPage application and basically had to take the same approach. Export all or Export selected put the data in a textarea within a hidden iframe, which is submitted and a WQS agent picks up the field value and generates the export via content/type.

    I don’t have the timing issue you are encountering, so maybe investigating the use of an iframe approach (yeah I know, iframes sound hackish) might help.

    • Brad Balassaitis says :

      Thanks for you feedback — I appreciate it. I’m sure the timing issue was related to my need to execute a partial refresh.

      I saw some info with people trying to use dojo io frames as the temporary storage area, but I couldn’t find anywhere that someone had a cleaner solution. Hopefully, it’ll be easier with HTML5 and more browser consistency down the road!

  2. Patrick Kwinten says :

    Impressive writing! Maybe I have missed something but have you provided somewhere, somehow a downloadable demo nsf?

  3. Aaron Brake (@AaronDBrake) says :

    Hi Brad. I tried implementing your solution in Chrome but I’m still getting the timing issue. Do you have any recommendations for Chrome? Additionally, I’m having another issue where I’m getting a large amount of HTML printed in addition to my CSV values. Have you encountered that in any of your testing?

    Tested in Chrome, IE, and Firefox

    • Brad Balassaitis says :

      Very interesting — I tend to develop and test initially with Firefox. I just went back to my grid and I see that it works in Firefox, but not IE9 or Chrome. Did it work for you in IE?

      I’ll make a note to look into it further and I’ll post something when I have some time to work on it.

    • Brad Balassaitis says :

      It’s definitely still a timing issue. I was able to get it to work by splitting the code up into 2 separate buttons and adding a 1-second delay. That provides enough time for the data to be retrieved and put in the hidden input field.

      Here’s code for two buttons for the Export Custom logic. The user just needs to click the first one. It will call the function to retrieve the data and put it in the hidden field. It will wait one second and then automatically trigger a click on the second button, which is hidden. That button will read the data into the scope variable and trigger the XAgent page that downloads the data. I tested this in IE and Chrome and they both work. (If it doesn’t work for some reason, increase the length of the delay.)

      All three versions of the export buttons can be modified to look like this first one. They can all call the same button to read the data and trigger the download.

      • Brad Balassaitis says :

        The good news is that this update doesn’t require the user to click a popup.

      • Aaron Brake (@AaronDBrake) says :

        Thanks for looking into it, Brad. I didn’t have the timing issue in IE 9 (Maybe due to data set sizes), but I’m not surprised it came up there too. I see that you came up with a workaround, but I don’t seem to see the code you’re referring to. I’d be very interested to check it out. I had thought to try another approach where I pass the grid data into a lotus agent and then try to manipulate it from there but that also led to some dead ends due to security issues.

      • Brad Balassaitis says :

        It keeps removing the code I’m adding in a reply, so I’ll update the original post with the new code at the very end.

  4. Hank, Yang says :

    Hi Brad,

    Thank you for the post related to the export function in EnhancedGrid. I have spent days looking for this “Export to CSV file” and found your post. However, I have a question about how to write a file in .xsp file. I am using the NotePad++ and can not find a .xsp format file to save as. Could you please let me know how to figure out this question?

    Many thanks,
    Hank

    • Brad Balassaitis says :

      The .xsp file is an XPage (part of an IBM Notes web application). If you’re not using XPages, then you won’t need that part of it — just skip all of the tags and take just the JavaScript out of the examples.

  5. Hank, Yang says :

    Hi Grad,

    I really appreciate your quick updating. I am new to JavaScript and then I just followed your instructions. I found there is something wrong with my export button. In addition, I wonder how I have an alternative server code to replace the .xsp file for the server side script(‘Grid_16_ExportToCSV_XAgent.xsp’).

    Could you please take at look at my script and point out the error?
    The web map application is listed below.
    http://jsbin.com/vujon/3/edit

    Many thanks,
    Hank

  6. Hank says :

    Hi Brad,

    Thank you for previous reply. I followed the example and found the button “Export Selected Rows to CSV” not working. Could you please let me know how to fix this problem.

    Thanks,
    Hank

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: