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
Steps involved in WaveMaker-Excel integrations:
/*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
}
}
}
}
