Import data from MS-Excel documents into WaveMaker
May 29

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 heading in its top row as shown below.
    Excel Snippet

    Fig 0: Excel Snippet

  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 Apache POI library from HERE.
    Download Apache POI

    Fig 1: Download Apache POI

  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)
        Import POI Jars

        Fig 2: Imported Apahce POI Jars

         

    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 java service and show it as a grid.
      1. Use File upload widget to allow user to select the excel file.  I call it “Select File & Load Grid”.
        Select Excel file and load grid

        Fig 3: Select Excel file and load grid

        Select the required upload directory.  The file will be saved in this directory in runtime.  You can checkout the uploaded file “Employee.xlsx” – in the /resources/uploads directory after using the file-upload widget in runtime.

        Uploaded Employee.xlsx

        Fig 4: Uploaded Employee.xlsx

      2. Edit the java service variable (XLSReaderWriterReadExcel) and bind the filename variable to the filename from the fileupload widget, as shown below..
        Bind fileName parameter

        Fig 5: Bind fileName parameter

        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 zip file, to use it in various applications.
      Export Prefab

      Fig 6: Export Prefab

  3. You can use this prefab in any other project by importing the prefab zip file into any project.  Try it out.

 

About The Author

Karthick drives the Products and Marketing efforts for WaveMaker. He is happy to discuss in-depth, code-level details and can just as easily shift focus to business and go-to-market strategies. Prior to WaveMaker, Karthick has occupied various roles in the world of engineering and product management at PegaSystems, IBM and Oracle – giving him the ability to take decisions that consider both business and technology aspects.

1 Comment

  1. Chendil
    October 26, 2015 at 6:36 am ·

    Hi Karthick,

    Now that i have the excel file on the server, how do i update the database from within the JAVA service itself?

    Regards,
    Chendil