Categories
Enterprise Application Development

Import data from MS-Excel documents into WaveMaker

Microsoft Excel is the most widely used spreadsheet application around the globe and in this post, we will see how to import data from an Excel file into a grid widget of WaveMaker Studio.  For reading the Excel file we will be using Apache POI libraries.

Scope of this blog

  1. We will be creating a Prefab to upload an Excel file and display it using the grid widget in an application.
  2. The Excel file is assumed to have a particular structure with a heading in its top row as shown below.
  3. This post covers only the basic Excel integration, restricted to the reading and displaying the spreadsheet data in a grid widget.  We use a Java service for reading the Excel file.
  4. By modifying the provided Java service, one can implement more complex Excel integration using Apache POI libraries and methods.

Steps involved in WaveMaker-Excel integrations:

  1. Download the latest version of the Apache POI library from HERE.
  2. Create a new “Prefab” Project.  I call it ReadWriteExcel (though I just demonstrate only Excel reading 🙂 ).  I will have another post for exporting Excel documents in the future.
    1. Import the following jars into WaveMaker
      Note: The JAR names could be different depending on the POI release you had downloaded.  I had downloaded Apache POI 3.11.

      1. poi-3.11-20141221.jar
      2. poi-ooxml-3.11-20141221.jar
      3. poi-ooxml-schemas-3.11-20141221.jar
      4. commons-codec-1.9.jar  (in $POI_HOME/lib directory)
      5. xmlbeans-2.6.0.jar (in $POI_HOME/ooxml-lib directory)
    2. Create a Java Service (I call it XLSReaderWriter). This java service uses the APIs provided by the Apache POI libraries, reads the excel file, and outputs the content as a JSON object.  The Java service code is given below..
      /*Copyright (c) 2015-2016 wavemaker-com All Rights Reserved.This software is the confidential and proprietary information of wavemaker-com You shall not disclose such Confidential Information and shall use it only in accordance with the terms of the source code license agreement you entered into with wavemaker-com*/
      
      package com.readwriteexcel.xlsxreaderwriter;
      
      import com.wavemaker.runtime.javaservice.JavaServiceSuperClass;
      import com.wavemaker.runtime.service.annotations.ExposeToClient;
      
      
      import java.io.InputStream; 
      import java.io.FileNotFoundException;
      import java.io.IOException; 
      import java.util.Iterator;
      import java.util.*; 
      
      import org.apache.poi.ss.usermodel.Cell;
      import org.apache.poi.ss.usermodel.Row;
      import org.apache.poi.ss.usermodel.Workbook;
      import org.apache.poi.ss.usermodel.Sheet;
      
      import org.json.JSONArray;
      import org.json.JSONObject;
      import org.json.JSONException;
      
      import org.springframework.beans.factory.annotation.Autowired;
      import com.wavemaker.runtime.server.upload.FileUploadDownload;
      import com.wavemaker.runtime.server.*;
      import org.apache.poi.ss.usermodel.WorkbookFactory;
      import com.wavemaker.runtime.*;
      import javax.servlet.http.*;
      
      /**
       * This is a client-facing service class.  All
       * public methods will be exposed to the client.  Their return
       * values and parameters will be passed to the client or taken
       * from the client, respectively.  This will be a singleton
       * instance, shared between all requests. 
       * 
       * To log, call the superclass method log(LOG_LEVEL, String) or log(LOG_LEVEL, String, Exception).
       * LOG_LEVEL is one of FATAL, ERROR, WARN, INFO and DEBUG to modify your log level.
       * For info on these levels, look for tomcat/log4j documentation
       */
      @ExposeToClient
      public class XLSXReaderWriter extends JavaServiceSuperClass {
          @Autowired
          FileUploadDownload fileUpload;
      
          /* Pass in one of FATAL, ERROR, WARN,  INFO and DEBUG to modify your log level;
           *  recommend changing this to FATAL or ERROR before deploying.  For info on these levels, look for tomcat/log4j documentation
           */
          public XLSXReaderWriter() {
             super(INFO);
          }
      
          public void readExcel(String fileName, HttpServletResponse response ) {
              String resultString = null;
              Workbook book = null;
              InputStream fis = null;
              try {
      
                  log(INFO, "inside readExcel"); 
                  
                  DownloadResponse dresponse = fileUpload.downloadFile(fileName,fileName);
                  fis = dresponse.getContents();
                  
                  book = WorkbookFactory.create(fis);
                  Sheet sheet = book.getSheetAt(0);
                  
      			// Start constructing JSON.
      			JSONObject json = new JSONObject();
      			
                  Iterator<Row> itr = sheet.iterator();
      
      			// Iterate through the rows.
      			JSONArray rows = new JSONArray();
      
                  // Iterating over Excel file in Java
      			int rowCount = 0;
      			ArrayList<String> headerList = new ArrayList<String>();
                  while (itr.hasNext()) {
                      Row row = itr.next();
      				JSONObject jRow = new JSONObject();
                      log(INFO, "inside row iterator");
      				// Iterate through the cells.
                      // Iterating over each column of Excel file
                      Iterator<Cell> cellIterator = row.cellIterator();
                      int cellCount = 0;
      				while (cellIterator.hasNext()) {
                          log(INFO, "inside cell iterator");
      
                          Cell cell = cellIterator.next();
                          JSONObject jCellObj = new JSONObject();
                          switch (cell.getCellType()) {
                          case Cell.CELL_TYPE_STRING:
      						if(rowCount == 0) 
      							headerList.add(cell.getStringCellValue());
      						else{
      							jRow.put((String)headerList.get(cellCount),cell.getStringCellValue()); 
      							log(INFO,cell.getStringCellValue() + "t");
      						}
                              break;
                          case Cell.CELL_TYPE_NUMERIC:
      						jRow.put((String)headerList.get(cellCount),cell.getNumericCellValue()); 
      						log(INFO,cell.getNumericCellValue() + "t");
                              break;
                          case Cell.CELL_TYPE_BOOLEAN:
      						jRow.put((String)headerList.get(cellCount),cell.getBooleanCellValue()); 
      						log(INFO,cell.getBooleanCellValue() + "t");
                              break;
                          default:
                              jRow.put((String)headerList.get(cellCount),""); 
          
                          }
      					cellCount++;
                      }
      				if(rowCount == 0) {
      					rowCount++;
      					continue;
      				}
      				rows.put( jRow );
                      
                  }
      	        // Get the JSON text.
      	        resultString = rows.toString();
      			response.setContentType("application/json");
      			response.getOutputStream().write(resultString.getBytes("UTF-8"));
      			response.getOutputStream().flush();
          
              } catch (FileNotFoundException fe) {
                  fe.printStackTrace();
                  log(ERROR, "The sample java service operation has failed", fe);
              } catch (IOException ie) {
                  ie.printStackTrace();
                  log(ERROR, "The sample java service operation has failed", ie);
              } catch (JSONException je) {
                  je.printStackTrace();
                  log(ERROR, "The sample java service operation has failed", je);
              } catch (Exception ex) {
                  ex.printStackTrace();
                  log(ERROR, "The sample java service operation has failed", ex);
              }finally{
                  try{
                      if(book != null){
                      book.close();
                      }
                      if(fis != null){
                        fis.close();
                      }
                      
                  }catch(IOException e){
                      //swallow this
                  }
              }
              
      		
          }    
      
      
      }
      
    3. Design UI to consume the JSON object from the java service and show it as a grid.
      1. Use the File upload widget to allow the user to select the excel file.  I call it “Select File & Load Grid”
        Select the required upload directory.  The file will be saved in this directory in runtime.  You can check out the uploaded file “Employee.xlsx” – in the /resources/uploads directory after using the file-upload widget in runtime.
      2. Edit the java service variable (XLSReaderWriterReadExcel) and bind the filename variable to the filename from the file upload widget, as shown below. Also, make sure to enable the “Auto Update” property for this variable
      3. Drag and drop a  grid and bind it to the Java service variable created from the previous step.(i.e. XLSReaderWriterReadExcel)
    4. This is a fully functional Prefab and can be tested like any other application. Go ahead, run the application, and test it.
    5. Export prefab as a zip file, to use in various applications.
  3. You can use this prefab in any other project by importing the prefab zip file into any project.  Try it out.
Categories
Enterprise Application Development

Why a Docker architected PaaS platform is superior?

Over the last year and a half, if there is one thing that the world of IT, DevOps & Cloud would not have missed discussing about, that would be Docker.  Docker is an app containerization technology that was developed by the erstwhile PaaS vendor DotCloud.  In fact, DotCloud was so enamored about the discovery that they remodeled their entire business strategy around Docker, renamed themselves Docker, and sold off their PaaS business.  And boy were they right!!!  Docker is nothing short of an epidemic now and literally every company with any kind of interest in the world of IT, DevOps & Cloud have embraced it with open arms and it includes Google, Microsoft, Amazon, VMWare, and more.

In this blog post, I will take you through those points that throw light on why Docker indeed has created so much buzz and how a Docker architected platform is superior, offering tremendous cost and performance benefits to the end-user, over traditional cloud platforms using hypervisor-based virtualization.

I am not going to explain “What is Docker” here.  There are a lot of articles on the web to explain that in more detail.  In fact, docker has an “Understanding Docker” section that does the job pretty nicely.  This post is just going to answer the question “Why a Docker architected PaaS platform is more superior?”

 

More bang for the buck:
Docker containers are lightweight compared to hypervisor-based VMs.  They don’t have the concept of a guest OS for every virtual machine(VM) that is created and shares the OS resources.  This means that more containers (2-3 times more) can be packed into the same host machine compared to packing VMs.

This higher container density aspect has a tremendous impact on the pricing front and offers the user more bang for the buck.  In fact, in WaveMaker, we have achieved almost 80% savings on operational costs of wavemaker online by using Docker architected WaveMaker Cloud platform on top of AWS.   I have to add, however, that the savings also includes benefits from WaveMaker Cloud’s additional optimizations on top of Docker containers.  But how is it possible to achieve so many savings? Let me try to explain that with an example.
Every time you provision a new virtual machine instance on EC2 you’ll need to pay for it. Imagine 1500 users are simultaneously logged onto wavemaker online and that would mean I need to have around 1500 EC2 instances provisioned.  That is a tremendous waste of resources, especially if the application is light.  But with containerization, we provide a container for every logged-in user and a lot of containers can be packed inside a single EC2 instance giving us this cost-benefit.

Easy updates to higher app versions:
With the lightweight Docker containers, release management of applications especially upgrading to newer app versions has changed.  App version upgrades using old school approaches are not possible without server downtime and hence business continuity is lost.
With containers, a newer version of the app is provisioned in separate containers alongside the containers containing the current version of the app.  Once the new version of the app has stabilized, the older version is phased out and its container de-provisioned.  This approach is called the Rolling Upgrades.  Rolling Upgrades would not be possible with such ease if not for Docker images (see Fig 2), a concept of snapshotting the container with the application and its dependencies.

Faster start-up times for horizontal scalability:
Lightweight Docker containers can be provisioned in a matter of milliseconds compared to a few minutes that are needed to provision a hypervisor VM instance.  This is because Docker containers use a layered approach to the mounting file system.  So instead of having to make full copies of whatever files comprise a container, Docker references back to existing files in a read-only layer,

This commoditization and instant availability of hardware resources have brought back the idea of horizontal scalability (see Fig 4) into focus again, where a new container can be provisioned instantly as the app load increases.  This on-demand scalability is achieved in style and forever will change the way applications are architected for scalability.

Faster app delivery through continuous deployment:
Docker images are a way to snapshot the app and all its dependencies.  Images are templates based on which containers are provisioned.  These images are extremely lightweight and can be easily pushed to different app life cycle stages like development, testing, and production (See Fig 5).  This facility along with the guaranteed reproduce-ability (explained in the next section) is a huge deal for release management because significant time goes towards dependency resolution in traditional development approaches.

Guaranteed re­pro­duce-abil­i­ty:
A typical scenario in enterprise systems is to have scripts to deploy apps to a server.  However, the script executions will vary across different environments that include parameters like time, hardware, software versions, etc. But by packaging your ap­pli­ca­tions into containers you can be sure that they will run as tested wherever they are deployed. In summary, there is a guarantee of reproducing the same behavior wherever it is executed.

Enforces certified software usage:
Docker registries (see Fig 6) are components that hold Docker images. These are public or private stores from which you can upload or download images. The public Docker registry is called Docker Hub. Enterprise IT teams have the control to make available only IT-certified software components (as of Docker images) through these registries.  This enforces certified software use across the organization.

Better error detection and recovery:
Isolated containers running individual apps also offer a targeted error detection and correction, without affecting other parts of the application.  This becomes especially valuable considering that the lightweight containers offer you quicker error recovery options.
Consider that container C1 is up and running with image I1.  A new version of the image I2 is created and C1 is provisioned again.  However, due to an error that got introduced into the system, the app is down.  Now the user can quickly snapshot the current state of the container that has the error and create a new image, say I3.  Now C1 is re-provisioned with image I1, which used to work correctly before.  I3 is sent across to the development team with all the logs and other details to be examined for issues and corrective measures.  This is amazing since the developer has a snapshot of the problem from a live environment, allowing faster debugging of the issue.

With containerization providing a wide array of benefits covering cost, effort, and time, it is just a matter of time that cloud platform vendors are going to adopt containerization as the default architectural style.

WaveMaker recently released its PaaS software – WaveMaker Cloud – which is architected using Docker containerization technology.  If you like to know more about the newly released product contact us or request a demo at wavemaker.