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 create a java application that can read and write to an excel's (microsoft office) and calc's (open office) worksheet How to create a java application that can read and write to an excel's (microsoft office) and calc's (open office) worksheet Reviewed by czetsuya on Tuesday, November 17, 2009 Rating: 5

4 comments:

mork said...

Hi
very interesting and well written IMHO

What about to make some money writing an app for Android. It seems there are many users who need it out there...

Anonymous said...

hi,

hmmm, that's a good idea. unfortunately i only have experienced on blackberry, j2me, windows mobile and iphone. maybe i'll right about that one day :-D

zayan said...

Would it be possible to read/write OpenOffice documents without using JDocument. In my case the application's input is a Calc while the output needs to be in Writer

Anonymous said...

@zayan,

Sorry but I haven't tried working with OpenOffice without using a 3rd party library like JDocument (and it is the best that I know that can read/write into an Open Office document).

Powered by Blogger.