Code I/O

A topnotch WordPress.com site

5 Minutes on Adobe Flex – A tutorial on getting Flex and Java to process Microsoft Excel files …

4 Comments

In this 5 minute series, lets look into a scenario of uploading a Microsoft excel file to servlet parse it using Apache POI API to determine possible column type by sampling data in each column.

What you need?
Download Apache POI binaries from http://poi.apache.org/ and load it into your eclipse environment. Since this example uses Apache fileupload API’s, you’ll also require them from http://commons.apache.org/fileupload/

In a nutshell, the dependent libraries you need is as follows:
commons-fileupload.jar
commons-io.jar
log4j-1.2.15.jar
poi-3.2-FINAL-20081019.jar

Take an Excel file which has the following content (presuming the first row is always a header):
Sample Input spreadsheet

The following listing of CDS_Demo.mxml is to upload file to servlet and get back the columns and possible data type as result.Β  We’ll try to build more intelligence to find more use of these techniques. For now, the simple basics.

import mx.rpc.http.HTTPService;
			import mx.controls.Alert;
			private var fileReference:FileReference = new FileReference();
			private var FILE_UPLOAD_URL:String = "http://localhost:8080/cdss/upload";

			private function onImportFileButtonClick():void {
				doImportFile();
			}

			private function doImportFile():void
			{
				var fileFilter:FileFilter = new FileFilter("Data Files (*.txt; *.csv; *.xls)","*.txt;*.csv;*.xls");
	  			fileReference.browse([fileFilter]);
	  			fileReference.addEventListener(Event.SELECT,onFileSelect);
	  			fileReference.addEventListener(Event.COMPLETE,onFileComplete);
			}

			private function onFileSelect(event:Event):void
	  		{
	  			fileReference.load();
	  		}

	  		private function onFileComplete(event:Event):void
	  		{
	  			importFileName.text = fileReference.name;
	  		}

			private function onIOError(event:Event):void
			{
				Alert.show("Server is Down/Unreachable, Please try again later"
					, "Connection to Server Failed");
			}
			private function processReponse(event:DataEvent):void
	        {
	            var dataString:String = (event.data);
	            var xmlData:XML = XML(dataString);
	            var xmlList:XMLList = xmlData.children().children();
	            dataGrid.dataProvider = xmlList;
			}

			private function onParseFileButton():void
	  		{
				fileReference.addEventListener(DataEvent.UPLOAD_COMPLETE_DATA, processReponse);
				fileReference.addEventListener(IOErrorEvent.IO_ERROR, onIOError);
	  			fileReference.upload(new URLRequest(FILE_UPLOAD_URL));
	  		}

<mx:VBox height="100%"Β  verticalAlign="top">
 <mx:Spacer />
 <mx:HBox width="100%" verticalAlign="middle">
 <mx:TextΒ  text="Select File" />
 <mx:TextInput id="importFileName" width="485" editable="false"/>
 <mx:Button id="importFileButton" label="..." click="onImportFileButtonClick()" width="36"/>
 <mx:Button id="parseFileButton" label="Suggest" click="onParseFileButton()"/>
 </mx:HBox>

 <mx:DataGrid id="dataGrid" width="672" height="50%">
 <mx:columns>
 <mx:DataGridColumn headerText="Column Name" dataField="@name" width="200"/>
 <mx:DataGridColumn headerText="Data Type" dataField="@type" width="100"/>
 <mx:DataGridColumn headerText="Sample" dataField="@value" width="100"/>
 </mx:columns>
 </mx:DataGrid>
 </mx:VBox>

The above code will get the response XML and display them in a datagrid. Once you get the idea on how to do this, you can play around with the API and explore more.

The following code illustrates how Apache POI, specifically HSSF API – I like the way it’s named “Horrible” πŸ˜‰ Pun aside, I really appreciate how Apache has put this together to help developers work with hetrogenous environments and find solutions that will just work πŸ™‚

This is how the web.xml looks

<servlet>
 <display-name>CDS upload service</display-name>
 <servlet-name>upload</servlet-name>
 <servlet-class>net.foss.cdss.service.FileUploaderServlet</servlet-class>

 <init-param>
 <param-name>net.foss.cdss.filestore</param-name>
 <param-value>C:/DEV/Temp/CDSS/FileStore</param-value>
 </init-param>

 <load-on-startup>1</load-on-startup>
 </servlet>

 <servlet-mapping>
 <servlet-name>upload</servlet-name>
 <url-pattern>/upload</url-pattern>
 </servlet-mapping>

Here is the full source listings of servlet code:
FileUploaderServlet.java


package net.foss.cdss.service;

import java.io.IOException;
import java.util.Properties;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.foss.cdss.domain.Constants;
import net.foss.cdss.utils.FileUploader;
import net.foss.cdss.utils.FileUtils;

import org.apache.log4j.PropertyConfigurator;

public class FileUploaderServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private static String fileStorePath = null;

public FileUploaderServlet() {
super();
}

public void init(ServletConfig config) throws ServletException {
super.init(config);

Properties loggingProperties = new Properties();
loggingProperties.setProperty(“log4j.rootLogger”, “debug, A1”);
loggingProperties.setProperty(“log4j.appender.A1”, “org.apache.log4j.ConsoleAppender”);
loggingProperties.setProperty(“log4j.appender.A1.layout”, “org.apache.log4j.PatternLayout”);
loggingProperties.setProperty(“log4j.appender.A1.layout.ConversionPattern”, “%d{ABSOLUTE} %5p %c{1}:%L – %m%n”);
PropertyConfigurator.configure(loggingProperties);

fileStorePath = (config.getInitParameter(Constants.FILESTORE_PATH));
if(null == fileStorePath) {
throw new ServletException(“Check web.xml init-param property for ” + Constants.FILESTORE_PATH + ” it is either missing or is not defined correctly”);
}

FileUtils.createNonExistantDirs(fileStorePath);
}

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
FileUploader fileUploader = new FileUploader(request, response);
fileUploader.uploadFilesTo(fileStorePath);
}
}

Constants.java


package net.foss.cdss.domain;

public class Constants {
public static final String FILESTORE_PATH = “net.foss.cdss.filestore”;
}

MyFileUtils.java


package net.foss.cdss.utils;

import java.io.File;

public class MyFileUtils {
synchronized
public static boolean fileExists(String fileName){
File simpleFile = new File(fileName);
return(simpleFile.exists());
}

synchronized
public static void createNonExistantDirs(String dirName){
if(!fileExists(dirName)){
File dir = new File(dirName);
dir.mkdirs();
}
}

synchronized
public static void deleteDirectory(String dirName){
try
{
FileUtils.deleteDirectory(new File(dirName));
}
catch( Exception e ) {
}
}
}

FileUploader.java

package net.foss.cdss.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class FileUploader {
private static final Logger logger = Logger.getLogger(FileUploader.class.getName());

private String uuid = null;
private HttpServletRequest request = null;
private HttpServletResponse response = null;

public class ResponseData{
public String name;
public String type;
public String value;

public ResponseData(String name, String type, String value){
this.name = name;
this.type = type;
this.value = value;
}
}

public FileUploader(HttpServletRequest request, HttpServletResponse response) {
this.request = request;
this.response = response;

this.uuid = UUID.randomUUID().toString();
}

@SuppressWarnings(“unchecked”)
public void uploadFilesTo(final String fileStorePath) throws ServletException, IOException {
try
{
List fileItems = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
if(fileItems == null){
logger.debug(“uploadFilesTo: No fileitems from client!”);

return;
}

Iterator fileIterator = fileItems.iterator();

while(fileIterator.hasNext())
{
FileItem fileItem = (FileItem)fileIterator.next();
if(fileItem == null){
logger.debug(“uploadFilesTo: null fileItem cannot be processed”);
return;
}

// definitely not a form item
if(!fileItem.isFormField()) {
String uploadPath = fileStorePath + File.separator + uuid;
MyFileUtils.createNonExistantDirs(uploadPath);
File fileWorkingOn = new File(uploadPath + File.separator + fileItem.getName());

if(fileWorkingOn == null){
logger.debug(“uploadFilesTo: File Path null!”);
}
else {
logger.debug(“uploadFilesTo: fileStorePath = ” + fileWorkingOn.getAbsolutePath());
fileItem.write(fileWorkingOn);

processFile(fileWorkingOn.getAbsolutePath());

MyFileUtils.deleteDirectory(uploadPath);
}
}
}
}
catch( Exception e ) {
logger.error(“uploadFilesTo: Exception during uploadFilesTo”);
e.printStackTrace();
}
}

private void sendResponse(HashMap hm){
Iterator iterator = hm.keySet().iterator();

StringBuffer buff = new StringBuffer();
buff.append(“”);

while(iterator.hasNext()) {
String key = iterator.next().toString();
ResponseData rd = hm.get(key);

buff.append(“” +
“”
+ “”);
}

buff.append(“”);
logger.debug(“xml = ” + buff);

response.setHeader(“Cache-Control”,”no-cache”);
response.setHeader(“Pragma”,”no-cache”);
response.setDateHeader(“Expires”, 0);
response.setContentType(“text/xml”);
try {
ServletOutputStream out = response.getOutputStream();
out.write(buff.toString().getBytes());
} catch (IOException e) {
logger.debug(“Exception obtaining outputstream”);
e.printStackTrace();
}

}
private void processFile(String fileName){
try {
FileInputStream fis = new FileInputStream(fileName);

// Treat input as Excel File
HSSFWorkbook wb = new HSSFWorkbook(fis);

// Take first sheet
HSSFSheet sheet = wb.getSheetAt(0);

// Read first row to sample data
HSSFRow headerRow = sheet.getRow(0);
HSSFRow dataRow = sheet.getRow(1); // Assuming data exists for sampling

HashMap pairs = new HashMap();

// Read each cell content and print it out.
for(int cellnum = 0; cellnum < headerRow.getLastCellNum(); cellnum++){
HSSFCell headerCell = headerRow.getCell(cellnum);
HSSFCell dataCell = dataRow.getCell(cellnum);

// Assume first row is always a header row
String colHeader = headerCell.getRichStringCellValue().toString();
logger.debug("row = " + headerRow.getRowNum() + " col = " + cellnum + " content = " + colHeader);

// Try different types
if(dataCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
// Uses column header to determine if it’s date
if(colHeader.toLowerCase().contains(“date”)) {
String DATE_FORMAT = “dd-MM-yyyy”;
SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
pairs.put(colHeader, new ResponseData(colHeader, “DATE”, “” +
sdf.format(dataCell.getDateCellValue())));
}
else {
pairs.put(colHeader, new ResponseData(colHeader, “NUMERIC”, “” + dataCell.getNumericCellValue()));
}
}
else if(dataCell.getCellType() == HSSFCell.CELL_TYPE_STRING){
pairs.put(colHeader, new ResponseData(colHeader, "STRING", dataCell.getRichStringCellValue().toString()));
}
else if(dataCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
pairs.put(colHeader, new ResponseData(colHeader, "BOOLEAN", "" + dataCell.getBooleanCellValue()));
}
else {
pairs.put(colHeader, new ResponseData(colHeader, "UNDETERMINED", ""));
}
}

sendResponse(pairs);
} catch (FileNotFoundException e) {
logger.debug("File " + fileName + " does not exist!");
e.printStackTrace();
} catch (IOException e) {
logger.debug("IOException while reading " + fileName);
e.printStackTrace();
}
}
}

Once everything is working, here is the output that will be generated, hope this was fun for you …
Sample output

Advertisements

4 thoughts on “5 Minutes on Adobe Flex – A tutorial on getting Flex and Java to process Microsoft Excel files …

  1. private function onFileSelect(event:Event):void
    {
    fileReference.load();
    }
    throws an error in Flex Builder 3
    1119: Access of possibly undefined property load through a reference with static type flash.net:FileReference.
    Can you please help.

  2. As on the contrary, I try using load() at my end it works. FYI, I’m using Flex 3.4 SDK, how about you?

  3. i can’t understands this codes

    my server output :
    xml =

    ===========================================

    buff.append(β€œβ€);

    while(iterator.hasNext()) {
    String key = iterator.next().toString();
    ResponseData rd = hm.get(key);

    buff.append(β€œβ€ +
    β€œβ€
    + β€œβ€);
    }

    buff.append(β€œβ€);
    logger.debug(β€œxml = ” + buff);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s