How to create a java application that can read and write to an excel's (microsoft office) and calc's (open office) worksheet
Tech-Today

How to create a java application that can read and write to an excel's (microsoft office) and calc's (open office) worksheet


Requirements:
-JOpenDocument: http://www.jopendocument.org/downloads.html (calc worksheet api)
-Excel POI: http://poi.apache.org/download.html (excel worksheet api)
-JUnit: http://sourceforge.net/project/showfiles.php?group_id=15278&package_id=12472 (unit testing)

This article will attempt to explain the development of a java based application that can perform worksheet operations both on microsoft excel and open office calc. The strategy is to create separate packages for each worksheet library, each library will have it's own methods' implementation depending on the poi or jopendocument api. Finally we will create a package with a client class and an openWorkSheet method that we can call, where we will specify the worksheet document and based on its extension the appropriate package will be initialize.

Have a look at our class diagram:



Diagram's Explanation:
Let's first look at the 3 most important items:
-AbstractWorksheet - an abstract class where other worksheet methods aside from reading and writing are defined
- examples are activateReader, activateWriter, etc
/**
 *@author: czetsuya
 *@since: Oct 9, 2009
 **/
package org.irri.cril.jicis.worksheet;

import java.io.IOException;

/**
 * This class is the root of the Worksheet type class.
 * It should be extended if another worksheet class is to be supported.
 * Currently supported worksheets:
 *   Microsoft Office's Excel and
 *   Open Office's Calc
 * @author czetsuya 
 */
public abstract class AbstractWorksheet {
 /**
  * Initialize the AbstractWorksheet reader.
  */
 public abstract void activateReader();
 /**
  * Initialize the AbstractWorksheet writer.
  */
 public abstract void activateWriter();
 /**
  * Sets the active worksheet index.
  * @param x worksheet index
  */
 public abstract void setSheet(int x);
 /**
  * Returns the worksheet instance.
  * @return Object
  */
 public abstract Object getWorksheet();
 /**
  * Saves the modified worksheet.
  * @throws IOException
  */
 public abstract void save() throws IOException;
}
-IWorksheetReader - an interface that defines the methods for reading a worksheet document
/**
 *@author: czetsuya
 *@since: Oct 9, 2009
 **/
package org.irri.cril.jicis.worksheet;

/**
 * A class should implement this interface if it will read a worksheet document.
 * @author czetsuya
 */
public interface IWorksheetReader {
 /**
  * Returns the value of the cell in row col.
  * @param row
  * @param col
  * @return
  */
 Object getCellValueAt(int row, int col);
 /**
  * Returns the value of the cell specified.
  * @param cell
  * @return
  */
 Object getCellValueAt(String cell);
 /**
  * Returns the cell of the current active worksheet.
  * @param row
  * @param col
  * @return
  */
 Object getCellAt(int row, int col);
 /**
  * Read the cells foreground fill color.
  * @param row
  * @param col
  * @return true if the cell foreground fill color is not white.
  */
 boolean isFilledBackgroundColor(int row, int col);
}
-IWorksheetWriter - an interface that defines the methods for writing a worksheet document
/**
 *@author: czetsuya
 *@since: Oct 9, 2009
 **/
package org.irri.cril.jicis.worksheet;

/**
 * Worksheet writer classes should implement this interface.
 * @author czetsuya
 */
public interface IWorksheetWriter {
 /**
  * Sets the value of a cell.
  * @param val value
  * @param row
  * @param col
  */
 void setValueAt(Object val, int row, int col);
 /**
  * Sets the value of a cell.
  * @param val
  * @param cellReference Worksheet row column: A1, A2, etc.
  */
 void setValueAt(Object val, String cellReference);
}

Now that we have defined the above 3 items, these will form the basic structure of our application. Now we have to create the classes that will extend the abstract class and implement the interfaces.

For example, JOpenDocument implementation:
1.) Abstract class
/**
 *@author: czetsuya
 *@since: Oct 9, 2009
 **/
package org.irri.cril.jicis.worksheet.jopendocument;

import java.io.File;
import java.io.IOException;

import org.irri.cril.jicis.worksheet.AbstractWorksheet;
import org.irri.cril.jicis.worksheet.IWorksheetReader;
import org.irri.cril.jicis.worksheet.IWorksheetWriter;
import org.jopendocument.dom.spreadsheet.Sheet;
import org.jopendocument.dom.spreadsheet.SpreadSheet;

/**
 * Handles an Open Office's Calc Worksheet Document.
 * @author czetsuya
 */
public class JOpenDocument extends AbstractWorksheet implements IWorksheetReader, IWorksheetWriter {
 /**
  * Open Office worksheet reader.
  */
 private IWorksheetReader reader;
 /**
  * Open Office worksheet writer.
  */
 private IWorksheetWriter writer;
 /**
  * Open Office spreadsheet. Array of Sheet objects.
  */
 private SpreadSheet spreadSheet;
 /**
  * Open Office sheet.
  */
 private Sheet sheet;
 /**
  * Filename of worksheet. 
  */
 private String workSheetName;
 
 /**
  * Creates an instance of the JOpenDocument.
  * @param worksheet
  * @throws IOException
  */
 public JOpenDocument(String worksheet) throws IOException {
  workSheetName = worksheet;
  spreadSheet = SpreadSheet.createFromFile(new File(worksheet));
  sheet = spreadSheet.getSheet(0);
 }
 
 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.AbstractWorksheet#activateReader()
  */
 @Override
 public final void activateReader() {
  reader = new JOpenDocumentReader(sheet);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.AbstractWorksheet#activateWriter()
  */
 @Override
 public final void activateWriter() {
  writer = new JOpenDocumentWriter(sheet);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.AbstractWorksheet#setSheet(int)
  */
 @Override
 public final void setSheet(int x) {
  sheet = spreadSheet.getSheet(x - 1);
  ((JOpenDocumentReader)reader).setSheet(sheet);
  ((JOpenDocumentWriter)writer).setSheet(sheet);
 } 

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.AbstractWorksheet#save()
  */
 @Override
 public final void save() throws IOException {
  spreadSheet.saveAs(new File(workSheetName));
 }
 
 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(int, int)
  */
 @Override
 public final Object getCellValueAt(int row, int col) {
  return reader.getCellValueAt(row, col);
 }
 
 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(java.lang.String)
  */
 @Override
 public final Object getCellValueAt(String cell) {
  return reader.getCellValueAt(cell);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.AbstractWorksheet#getWorksheet()
  */
 @Override
 public final Object getWorksheet() {
  return sheet;
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetWriter#setValueAt(java.lang.Object, int, int)
  */
 @Override
 public final void setValueAt(Object val, int row, int col) {
  writer.setValueAt(val, row, col);  
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetWriter#setValueAt(java.lang.Object, java.lang.String)
  */
 @Override
 public final void setValueAt(Object val, String cellReference) {
  writer.setValueAt(val, cellReference);
 }
 
 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getFillBackgroundColor(int, int)
  */
 @Override
 public final boolean isFilledBackgroundColor(int row, int col) {
  return ((IWorksheetReader)reader).isFilledBackgroundColor(row, col);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(int, int)
  */
 @Override
 public final Object getCellAt(int row, int col) {
  return ((IWorksheetReader)reader).getCellAt(row, col);
 }
}
Note: Both the reader and writer class are not initialize by default.
2.) Reader
package org.irri.cril.jicis.worksheet.jopendocument;
/**
 *@author: czetsuya
 *@since: Oct 9, 2009
 **/

import org.irri.cril.jicis.worksheet.IWorksheetReader;
import org.jopendocument.dom.spreadsheet.Sheet;

/**
 * Worksheet reader implementation for Open Office's Calc.
 * @author czetsuya
 */
public class JOpenDocumentReader implements IWorksheetReader {
 /**
  * Open Office worksheet.
  */
 private Sheet sheet;

 /**
  * Creates an instance of JOpenDocumentReader.
  * @param sheet
  */
 public JOpenDocumentReader(Sheet sheet) {
  this.sheet = sheet;
 }

 /**
  * Sets the sheet for reading.
  * @param sheet
  */
 public final void setSheet(Sheet sheet) {
  this.sheet = sheet;
 }

 /*
  * (non-Javadoc)
  * 
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(int, int)
  */
 @Override
 public final Object getCellValueAt(int row, int col) {
  return sheet.getCellAt(col - 1, row - 1).getValue();
 }

 /*
  * (non-Javadoc)
  * 
  * @see
  * org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(java.lang.String
  * )
  */
 @Override
 public final Object getCellValueAt(String cell) {
  return sheet.getCellAt(cell).getValue();
 }

 /*
  * (non-Javadoc)
  * 
  * @see
  * org.irri.cril.jicis.worksheet.IWorksheetReader#getFillBackgroundColor
  * (int, int)
  */
 @Override
 public final boolean isFilledBackgroundColor(int row, int col) {
  throw new UnsupportedOperationException();
 }

 /*
  * (non-Javadoc)
  * 
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(int, int)
  */
 @Override
 public final Object getCellAt(int row, int col) {
  return sheet.getCellAt(col, row);
 }
}
3.) Writer
/**
 *@author: czetsuya
 *@since: Oct 9, 2009
 **/
package org.irri.cril.jicis.worksheet.jopendocument;

import org.irri.cril.jicis.worksheet.IWorksheetWriter;
import org.jopendocument.dom.spreadsheet.Sheet;

/**
 * Worksheet writer implementation for Open Office's Calc.
 * @author czetsuya
 */
public class JOpenDocumentWriter implements IWorksheetWriter {
 /**
  * Open office worksheet.
  */
 private Sheet sheet;
 
 /**
  * Creates an instance of JOpenDocumentWriter.
  * @param sheet
  */
 public JOpenDocumentWriter(Sheet sheet) {
  this.sheet = sheet;
 }
 
 /**
  * Sets the sheet for writing.
  * @param sheet
  */
 public final void setSheet(Sheet sheet) {
  this.sheet = sheet;
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetWriter#setValueAt(java.lang.Object, int, int)
  */
 @Override
 public final void setValueAt(Object val, int row, int col) {
  sheet.ensureRowCount(row);
  sheet.ensureColumnCount(col);
  sheet.setValueAt(val, col - 1, row - 1);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetWriter#setValueAt(java.lang.Object, java.lang.String)
  */
 @Override
 public final void setValueAt(Object val, String cellReference) {
  sheet.getCellAt(cellReference).setValue(val);  
 }
}

The same is true for Microsoft Excel Worksheet, just a different implementation based on POI API.

Finally create a class that will instantiate the worksheet class and will call the higher level methods which encapsulate our newly created reader and writer class.
/**
 *@author: czetsuya
 *@since: Oct 9, 2009
 **/
package org.irri.cril.jicis.worksheet;

import java.io.IOException;

import org.irri.cril.jicis.worksheet.jopendocument.JOpenDocument;
import org.irri.cril.jicis.worksheet.msexcel.ExcelDocument;

/**
 * This class serves as the controller which instantiate the appropriate class 
 * for the specified file. It instantiate an AbstractWorksheet class base on the file extension.
 * ods - @see org.irri.cril.jicis.worksheet.jopendocument
 * xls/xlsx - org.irri.cril.jicis.worksheet.msexcel  
 * @author czetsuya
 */
public class WorksheetClient implements IWorksheetReader, IWorksheetWriter {
 /**
  * AbstractWorksheet. 
  */
 private AbstractWorksheet workSheet;
 /**
  * Extension length. .xls, .ods  
  */
 private static final int EXT_LENGTH = 3;
 
 /**
  * Parse a string, determine its type and creates an abstract worksheet. 
  * @param worksheet
  */
 public final void openWorksheet(String worksheet) throws IOException {
  String type = worksheet.substring(worksheet.length() - EXT_LENGTH);
  
  if(type.equals("ods")) { //open office calc
   workSheet = new JOpenDocument(worksheet);
  } else if(type.equals("xls") || type.equals("xlsx")) { //excel
   workSheet = new ExcelDocument(worksheet);
  }
  workSheet.activateReader();
  workSheet.activateWriter();
 }
 
 /**
  * Sets the active sheet number.
  * @param x - worksheet number
  */
 public final void setSheet(int x) {
  workSheet.setSheet(x);
 }
 
 /**
  * Saves the active worksheet.
  * @throws IOException
  */
 public final void save() throws IOException {
  workSheet.save();
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(int, int)
  */
 @Override
 public final Object getCellValueAt(int row, int col) {
  return ((IWorksheetReader)workSheet).getCellValueAt(row, col);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(java.lang.String)
  */
 @Override
 public final Object getCellValueAt(String cell) {
  return ((IWorksheetReader)workSheet).getCellValueAt(cell);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetWriter#setValueAt(java.lang.Object, int, int)
  */
 @Override
 public final void setValueAt(Object val, int row, int col) {
  ((IWorksheetWriter)workSheet).setValueAt(val, row, col);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetWriter#setValueAt(java.lang.Object, java.lang.String)
  */
 @Override
 public final void setValueAt(Object val, String cellReference) {
  ((IWorksheetWriter)workSheet).setValueAt(val, cellReference);  
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getFillBackgroundColor(int, int)
  */
 @Override
 public final boolean isFilledBackgroundColor(int row, int col) {
  return ((IWorksheetReader)workSheet).isFilledBackgroundColor(row, col);
 }

 /* (non-Javadoc)
  * @see org.irri.cril.jicis.worksheet.IWorksheetReader#getCellAt(int, int)
  */
 @Override
 public final Object getCellAt(int row, int col) {
  return ((IWorksheetReader)workSheet).getCellAt(row, col);
 }
}




- How To Protect Your Page Using Webfilter In Javaee
This tutorial is to be use in conjunction with picketlink. Normally we want some pages to be accessible only after a user has logged in. In this case we need a real protection filter. The class below filters a url path and check if there's a logged...

- How To Load Property File From Glassfish Config's Folder
In seam you can define a component class in components.xml that will load the properties from the JBOSS_CONFIG folder. Add the ff lines: <component name="paramBean" class="com.ipil.PropertyBean" scope="application" auto-create="true" startup="true">...

- Android Bundle Sqlite Database On Application. Create Sqlite Database On First Application Invoke.
How to setup a default sqlite database structure with data in an android application. Add this class and initialize it in your main class that extends Activity. import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase;...

- Eclipse: !message Missing Required Bundle Org.eclipse.ui.forms_3.4.1
What I want to do: To add a reference to the ui.forms plugin so that I can use the eclipse-rcp's FormToolkit object. Problem: I have encountered this problem and spent more than an hour finding a solution so I think it's better to share what I've...

- Create A New Eclipse-rcp Preference Page By Code When A Listener Is Invoked
Objective: -To create an eclipse-rcp preference page without using the preference extension, all is done in code. This is done by creating a customized button with a SelectionListener, and eventually that action will call a customized preference page....



Tech-Today








.