Lightbend Activator

POST Table Contents with jQuery-Ajax - Convert - Download Excel File

Activator will be EOL-ed on May 24, 2017.

We’re making it easier and simpler for developers to get started with Lightbend technologies. This unfortunately means that future releases of Play, Akka and Scala will no longer include Activator support, and Lightbend’s Activator server will be decommissioned by the end of 2017. Instead of supporting Activator to create and set up development projects, we'll be supporting standard Giter8 templates for sbt users and Maven archetypes for Maven users. So going forward,

To create new Lightbend projects

Instead of using the Activator command, make sure you have sbt 0.13.13 (or higher), and use the “sbt new” command, providing the name of the template. For example, “$ sbt new akka/hello-akka.g8”. You can find a list of templates here.

Also, as a convenience, the Lightbend Project Starter allows you to quickly create a variety of example projects that you just unzip and run.

To create new templates

If you want to create new templates, you can now do that in Giter8.

To migrate templates from Activator to Giter8

If you created Activator templates in the past, please consider migrating them to Giter8 with this simple process.

POST Table Contents with jQuery-Ajax - Convert - Download Excel File

Jesus "Chuy" Lozano
Source
January 20, 2015
playframework scala excel datatables squeryl post jquery recursion apachepoi filedownload javascript

A ready to use Play Framework Application that allows you to search data at the server with Squeryl, download it to your browser, handle it locally with a jQuery Table plug-in, when you decide send it back to the server via a POST executed with jQuery-Ajax, have the server convert it to an Excel File and present it back to you for download

How to get "POST Table Contents with jQuery-Ajax - Convert - Download Excel File" on your computer

There are several ways to get this template.

Option 1: Choose jqueryTableContentsPostAndFileDownload in the Lightbend Activator UI.

Already have Lightbend Activator (get it here)? Launch the UI then search for jqueryTableContentsPostAndFileDownload in the list of templates.

Option 2: Download the jqueryTableContentsPostAndFileDownload project as a zip archive

If you haven't installed Activator, you can get the code by downloading the template bundle for jqueryTableContentsPostAndFileDownload.

  1. Download the Template Bundle for "POST Table Contents with jQuery-Ajax - Convert - Download Excel File"
  2. Extract the downloaded zip file to your system
  3. The bundle includes a small bootstrap script that can start Activator. To start Lightbend Activator's UI:

    In your File Explorer, navigate into the directory that the template was extracted to, right-click on the file named "activator.bat", then select "Open", and if prompted with a warning, click to continue:

    Or from a command line:

     C:\Users\typesafe\jqueryTableContentsPostAndFileDownload> activator ui 
    This will start Lightbend Activator and open this template in your browser.

Option 3: Create a jqueryTableContentsPostAndFileDownload project from the command line

If you have Lightbend Activator, use its command line mode to create a new project from this template. Type activator new PROJECTNAME jqueryTableContentsPostAndFileDownload on the command line.

Option 4: View the template source

The creator of this template maintains it at https://github.com/chuy2008/jqueryTableContentsPostAndFileDownload#master.

Option 5: Preview the tutorial below

We've included the text of this template's tutorial below, but it may work better if you view it inside Activator on your computer. Activator tutorials are often designed to be interactive.

Preview the tutorial

The Goal

Show an implementation of information handling in the Web, executed among the Browser at the Front End and Play Framework at the Back End, while achieving an optimal work distribution balance among these two points. When we say optimal work distribution balance, we refer to the fact of performing operations locally when possible, avoiding the following cycle:

browser transmits to server >>> processing at the server >>> server transmits to browser,

if the browser has the capacity to process the information.

The "information handling in the Web" mentioned above, consists roughly on the following:

  • User requests information from the Server and has it displayed in Table format
  • User arranges the information locally (sorting it the browser according to specific field value)
  • At some point in time, when the user decides it is sorted exactly as desired, she sends it back in JSON format via a POST operation to Play Framework at the Back End
  • Play Framework receives the data in JSON format, process it and inserts it within a new Excel File
  • The Excel File is announced back to the browser, ready for download.

Main Objectives

The present Work covers several objectives, showing specially:

  • collect the information in the Server h2 Database via Squeryl and in the process utilizing tail recursion in Scala, to have it formatted as a mapped List
  • deliver the information from the Server to the Client (Browser) in a mapped List format, as mentioned in the point above.
  • display and handle the information locally, in the Browser via a jquery table plugin (DataTable)
  • Once the User is happy with the arrangement of the Table contents, gather the existing Table information, within the DOM in the Browser page, with javascript
  • execute a POST with the content in JSON Format (REST Interface), utilizing ajax
  • receive the information at the Back End with Play Framework in JSON format (REST Interface)
  • convert the received information to an Excel File
  • deliver back this Excel File to the Browser, ready for download

Design Criteria and Code implementation

An important design criteria was deciding on an optimal balance of data handling. The decision is to achieve a good balance on when to handle the data remotely, at the Server and when to handle it locally, at the Browser. An initial idea and possible valid implementation was storing and rearranging the table contents in Play Framework as per the Browser orders. In this scheme, the Browser just displays information, in the sequence as it is received from the server. In this scheme, for instance, the User sees the table contents and decides to sort the rows based on column number 5 value, highest at the top. The Browser sends to the Server the column number information and the type of sort needed (highest or lowest at the top). The rows are sorted in the Server and sent back to the Browser. This scheme works, the issues are:

  • delay experienced by the User in seeing his screen refreshed, as he has to wait for all the table contents to be transmitted on to the server and back to the browser.
  • Bandwidth is occupied in shipping table contents back and forth as per the User's desires.

We decided that if you can handle the data locally,- as it is indeed the case,- it makes more sense to do it that way. Why ship the information back and forth if you can process it locally? But there is a trade off, as in all design decisions. After you have sorted locally the table rows as per your liking, the server is completely ignorant of the final arrangement. So you have to send back the table contents, in the right order, to the server for its final processing. Another possible approach would be to keep a version of the table contents at the server, indexed per row. Once the User has sorted the table contents at her browser, instead of sending row by row back to server, she could just send the indexes, ordered as desired. In this case, the server would receive just the indexes and arrange its own copy of the local table contents as instructed, avoiding altogether the transmission of the full Table contents. We decided not to go for this route because it added complexity (in the form of coordinating the server and the router) at a very little benefit. After all, we now live in the era of huge bandwidth everywhere, right? This scheme might be worth it though when bandwidth is scarce or if Table contents are multimedia (images, video).

As in the present example we are handling only text and numbers, the decision was to keep it simple and just send to the server all the Table contents present at the Browser DOM, when instructed to do so.

Below you can find the code that handles in javascript the sending of table contents to the server as a POST. As you can observe, we first have to collect the existing data at the Table and store it in the statCellData Array, to later post it. You can also note that we are doing some string handling below. This is needed in order to extract the URL prefix and thereafter be able to POST to the right address. In this way, this code can work without any modification, not withstanding if you are running it in your own machine or in a public server.


      function postTableData(x)
      {         
      	 var table = document.getElementById('busUnitStatsTable');        
         var item = [];
         var statCellData = [];
         for (var i = 0; i < table.rows.length; i++) 
         {
           item[i] = [];
           for (var j = 0; j < table.rows[i].cells.length; j++)
           {
          	 item[i][j] = table.rows[i].cells[j].innerHTML;
           	 var statCell = {};   
           	 statCell.rowLoc = i.toString();
           	 statCell.colLoc = j.toString();
           	 statCell.val = item[i][j];
             statCellData.push(statCell);     	 	    	               
           }
         }    
         var statCellDataJson = $.toJSON(statCellData);
         var partialURL1 = document.URL;
         var partialURL2 = partialURL1.substring(partialURL1.indexOf("/") + 1, partialURL1.length);
         var partialURL3 = partialURL2.substring(partialURL2.indexOf("/") + 1, partialURL2.length);
         var partialURL4 = partialURL3.substring(partialURL3.indexOf("/") + 1, partialURL3.length);
         var partialURL5 = partialURL1.substring(0, partialURL1.length - partialURL4.length);
         var urll = partialURL5 + 'postJsonDataForExcelConversion/' + x;                        
         $.ajax({
         	url: urll,
            dataType: 'html',
         	type: 'POST', 
         	contentType: 'text/json',
         	data: statCellDataJson})
         	.done(function (response) 
         	{
               $('#insertPostResponse').html(response);
               console.log(response); 
            });
      }      

Please note that you are sending the table contents at a very specific URL. The server needs to be ready to receive this POST, with the information formated as JSON. Below you can find the code at the server that can handle this task:


  def processTableJsonDataForExcelConversion(fileName: String) = Action(parse.json)
  {
     request =>
       request.session.get("fileCleanUpNeeded") match
       {
         case Some(x) => x match
           {
              case "yes" => {FileExplorer.cleanUpFilesWhoseNameIncludes(
                                  request.session.get("fileId"), mainPathForFiles)}
              case _     => {}    
           }
         case None    => {}
       }           
       val salesDataInTable = request.body.as[List[Map[String, String]]]
       val fileNameAndPath  = ExcelConverter.excelConv(fileName + "-" + request.session.get("fileId").getOrElse("noFileIdInSesion"), 
                                                         mainPathForFiles, 
                                                         salesDataInTable)
        Ok(views.html.showDownloadFileLink(fileNameAndPath)).withSession(
            "fileId" -> request.session.get("fileId").getOrElse("11"), "fileCleanUpNeeded" -> "yes")
                
  }

Of course, you need to add a line at the routes file (within the conf folder) to direct the incoming request to the correct function. This is the line you need to add in routes:


POST  /postJsonDataForExcelConversion/:fileName  controllers.Application.processTableJsonDataForExcelConversion(fileName: String)

Path Binding

As you might have noted, when you select to display the data of a particular Business Unit, you send as a parameter a specific Business Unit Object. As it turns out, Play Framework can handle pretty transparently both Strings and Ints in its route file, but it is not the case with Objects, when passed via the URL. You need to add a Path Binding function to process Objects as Parameters when handled through the routes file. This represents some work, but after you do it the first time it is pretty straightforward. Below is the code to handle the Path Binding:


   implicit def pathBinder(implicit strBinder: PathBindable[String]) = new PathBindable[BusinessUnitId] 
    {
       override def bind(key: String, value: String): Either[String, BusinessUnitId] =
        {           
           AppBusinessUnitIdDB.findByKey(value.toInt) match
           {
              case Some(x) => Right(new BusinessUnitId(x.name, x.keyy))
              case None    => Left("for some strange reason, Business Unit is not in database, " +
                                    "or at least the following key = " + value + "does not appear " +
                   		            "to exist == BusinessUnitId scala routine, within pathBinder function")
           }
        }        
        override def unbind(key: String, busUnitId: BusinessUnitId): String = 
        {
            strBinder.unbind(key, busUnitId.keyy.toString)
        }
     }  

File Cleaning

The process to send an Excel File to the User is to first produce and store it at the server and then present it to the User for download. At some point in time, however, you need to erase these files at the server, otherwise outdated files are going to be stored at the server. We decided to do that by reviewing per session if there are any remaining files produced during the session and erasing them. In a production Application, ideally you would need to review at logout if there are any files left and erase them. You can even add an actor that periodically erases files that are too old. For our purposes, our scheme works fine. Here is the code:

request.session.get("fileCleanUpNeeded") match
       {
         case Some(x) => x match
           {
              case "yes" => {FileExplorer.cleanUpFilesWhoseNameIncludes(
                             request.session.get("fileId"),mainPathForFiles)}
              case _     => {}    
           }
         case None    => {}
       }

tail recursion

I want to make a special mention of tail recursion, because it is really fun. It is actually liberating to have an option to for loops and very handy when you do not know in advance the length of the data you are going to work on. It is one of the reasons I enjoy Scala over Java. Be careful of using tail recursion, it is addictive :-). Below you can find the code used in one of the recursion functions. In this function, I gather all the contents of a Directory, inspect if it is a File or a Directory. If is a Directory, I let it go untouched, if it is a File, I review if I have to erase it or not. Here is the code, it is contained on the loop function. The session Id parameter is passed as a String Option, it is the variable received under strOption:


  def cleanUpFilesWhoseNameIncludes(strOption: Option[String], presentDirectory: String) =
  {
    strOption match
    {
      case Some(x) => 
        {
           def loop(filesRemaining: List[java.io.File], sessionId: String): Boolean =
           {
              if (!filesRemaining.headOption.isEmpty)
              {
                 filesRemaining.head.isFile match
                 {
                   case true  => 
                     {
                       filesRemaining.head.getName() contains sessionId match
                       {
                         case true => 
                           {
                              filesRemaining.head.delete()
                              loop(filesRemaining.tail, sessionId)
                           }
                         case _ => {loop(filesRemaining.tail, sessionId)}
                       }
                     }
                   case false => loop(filesRemaining.tail, sessionId) 
                 }
              }
              else {true}
           }
           loop(new java.io.File(presentDirectory).listFiles().toList, x)
        }
      case None    => {}
    }
    
  }

jquery Table plug in: DataTables

The plug-in employed for handling the data in the browser, using jQuery, is DataTables,

url: http://www.datatables.net/

Hats off for this guys, they really handle the data beautifully.

Excel Conversion

For doing the Excel Conversion, I employ Apache POI Project, the Java API for Microsoft Documents, below you have the URL:

http://poi.apache.org/

File Download

Below you can find the code employed for downloading files, I am using the available Play Framework tools for this.


      Ok.sendFile(
        content = new java.io.File(fileNameAndPath),
        fileName = _ => "SalesStatsForYourAnalysis.xlsx").withSession(
           "fileId" -> request.session.get("fileId").getOrElse("11"), "fileCleanUpNeeded" -> "yes")                   

Session Key Value Pairs

The Program handles a couple of Session Key Value Pairs (String, String), they are:

  • "fileId" -> some String value
  • "fileCleanUpNeeded" -> some String value

The first pair with the key value "fileId" is used to store a Random Number generated at the beginning of the Session. This Random Number will be used as part of the name of the file that will be eventually downloaded. Here is the code where initially this value is set:


Ok(views.html.index("Please select one option")("")(lOfBusUnits)).withSession(
"fileId" -> RandomCode.generateRandomCode(5), "fileCleanUpNeeded" -> "no")

This Random Number is generated with the objective of handling different users at the same time and avoiding conflict at the time of downloading their respective files. I guess in a full fledged application, the standard way to avoid conflict would be to take the users name,- which must be unique,- and use it to assign to name the file. In this particular template, since handling users, passwords and associated security is out of its scope, we are employing this Random Number Generator to avoid conflict. The code below shows how we are integrating the "fileId" value to the file name, before its creation


val fileNameAndPath  = ExcelConverter.excelConv(fileName + "-" + request.session.get("fileId").getOrElse("noFileIdInSesion"), 
                                                         mainPathForFiles, 
                                                         salesDataInTable)

Please note that the variable "fileName" is the specific region selected by the Customer on her screen, the region whose sale stats she is particularly interested in.

When reading the File Session value mentioned above, whose key is "fileName" I employ the following command:


request.session.get("fileId").getOrElse("11")

Since the get("fileId") command returns a String Option, I am appending a getOrElse("11") to convert it to a String. I cannot think of any reason why this "getOrElse" command executed on the String Option should go to the "Else" alternative (instead of "get"), as the "fileId" value is assigned at the beginning of the Session and kept there all the time. But if for some strange, unknown reason this happened, then the number integrated in the file name would be 11

The other Session key value pair, with the key "fileCleanUpNeeded", is used to perform file erasure after the file has been produced and downloaded, to free disk space. Please take a look above to the "File Cleaning" Section if you want to know more detail on this.

×

Welcome to the Lightbend Enterprise Suite


You are excited about Reactive applications. And you want to build, manage and monitor them easily and reliably in your environment.
We get it. So we built Lightbend Enterprise Suite to help you do that, and more, with a range of powerful Application Management, Intelligent Monitoring, Enterprise Integration and Advanced Tooling features.