May 29, 2015

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.