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
- We will be creating a Prefab to upload an Excel file and display it using the grid widget in an application.
- The Excel file is assumed to have a particular structure with a heading in its top row as shown below.
- 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.
- 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:
- Download the latest version of the Apache POI library from HERE.
- 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.
- 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.
- poi-3.11-20141221.jar
- poi-ooxml-3.11-20141221.jar
- poi-ooxml-schemas-3.11-20141221.jar
- commons-codec-1.9.jar (in $POI_HOME/lib directory)
- xmlbeans-2.6.0.jar (in $POI_HOME/ooxml-lib directory)
- 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
}
}
}
}
- Design UI to consume the JSON object from the java service and show it as a grid.
- 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.
- 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
- Drag and drop a grid and bind it to the Java service variable created from the previous step.(i.e. XLSReaderWriterReadExcel)
- This is a fully functional Prefab and can be tested like any other application. Go ahead, run the application, and test it.
- Export prefab as a zip file, to use in various applications.
- You can use this prefab in any other project by importing the prefab zip file into any project. Try it out.
A common request among WaveMaker Studio developers is to integrate third-party UI widgets inside WaveMaker Studio and make them available as drag-and-drop components. This task becomes especially challenging when we consider the fact that the WaveMaker Studio uses AngularJS technology for defining its widgets and the third-party widget may be using another technology like jQuery. In this blog post, we will take a use case-based approach to see how a jQuery-based third-party widget is integrated with WaveMaker Studio. For achieving this, we will leverage Studio’s very own superstar component - Prefab - and demonstrate its power of customization.
Use case
Lets take Lightbox image viewer widget and see how users can integrate this widget into WaveMaker Studio using Prefabs. This widget is jQuery-based and this link gives the details about how to use the Lightbox widget in general.
Steps to integrate Lightbox widget into WaveMaker Studio
The detailed steps to create this Prefab, export it and use it any WaveMaker Studio project are given below.
1. Get the widget library source code: Download the Lightbox resource files from here and unzip it into a folder
/integrating-third-party-ui-widgets-using-prefabs/
2. Import the relevant js, css and image files into WaveMaker Studio. In case of Lightbox widget, the details are given in the step-1 of “How to use” section in the Lightbox project home page.
The imported files can be seen under the folders js, css and image.
3. Set the Bindable Properties for the Prefab: Go to the newly introduced “Prefabs Properties” section under the project “Settings” and add references to the imported js under script section, css under the style section and an icon file. Add the bindable properties that are needed for this Prefab. In this example, we have added “image” and “title” properties that represent the location and title of the image to be displayed.
These properties will appear in the properties panel of the Prefab, once it is dragged and dropped into the WYSIWYG canvas.
Check out the WaveMaker Prefabs documentation to get complete details about this new feature.
4. Design the Prefab UI: Drag and drop the “picture” widget to display the image thumbnail and bind the “Hint” and “Source” attributes to the “title” and “image” properties respectively that you had defined in the previous step.
And finally, step-2 of “How to use” section of Lightbox documentation instructs the user to embed the images inside an anchor tag(<a>...</a>) & also add a “data-lightbox” attribute, to activate the widget. In the Studio we can do this by adding the above details in the “Markup” section, as shown below.
5. Export the Prefab using the Export menu item in Studio (see Fig-7). Make sure you have tested the Prefab before you export it.
6. Import the Prefab and use it as any other component inside an app. To demonstrate the Lightbox Prefab usage, lets create a demo app called “ImageViewer” and import the Lightbox Prefab. Once it is imported you can spot the LightboxPrefab in the Prefabs accordion section.
7. Perform the following customary steps in the Studio to integrate the LightboxPrefab:
a. Import the sample DB provided with WaveMaker Studio
b. Drag and drop a Live-List widget into the canvas and bind it to the dataset of “HRdbEmployeeData” live variable that got created automatically after import of sample DB.
c. Delete the “panel1” created under the “listtemplate1”.
d. Drag and drop the lightbox prefab into the “listtemplate1” and bind the image “Source” property to “Variables.HrdbEmployeeData.dataSet.picurl” and the title to “Variables.HrdbEmployeeData.dataSet.firstName”
We are all set now. Run the application and see how the Lightbox image viewer functionality executes successfully.
The above approach of using Prefabs can be applied successfully to integrate any third-party UI widgets into WaveMaker Studio instantly, providing the needed customization inside Studio.
Try it out with your widget of choice and let us know how it went. Send your experience of integrating your custom widget to feedback@wavemaker.com.
References
Lightbox: lokesh dhakar projects - lightbox2
Karthick Viswanathan
Product & Customer-Success Manager
