This week at work we were hit with an interesting problem in our development with Pega. We were working on a requirement to import data from an Excel spreadsheet which we originally used to receive in the standard Excel 2003 XLS format. This is a rather simple exercise using the JExcel library which Pega includes in its SI package. (Of course, this is not the first mention of JExcel in my blog. You may recall that I ported it to dotNET and blogged about it in JExcel native port to .NET in C#.) However, true to form of most requirements, there was an unanticipated change in that the new spreadsheets are delivered in the Excel 2007 XLSX format! We are staring down the barrel of a deadline and need to meet this changed requirement.
Of course, we also have a problem in that we cannot develop a new JAR file because we do not have the testing and deployment leeway to support this extensive a change to our Pega environment in such a short time period. This shoots down creation of a bunch of classes because systems like Pega or Tibco BW permit external JARs to be added or some inline Java code to be inserted but there is no way to overtly create classes within the system! Sounds like a bunch of show-stoppers have cropped up.
Format of XLSX files
Unlike the earlier binary-formatted BIFF file, the newer XLSX files are built on XML. This means that, with some astute observation and use of the DOM or SAX parsers, theoretically code can be created to parse the contents of the spreadsheets. The format of the XLSX is that it is a zipped file containing compressed files and folders.
Format of an XLSX file within the Zip envelope
The spreadsheets themselves are carried in internal files under the /xl/worksheets folder within the zip. They are named with xml extensions. Within the file is a tagged group under sheetData which contains the actual data itself. Under sheetData are tagged groups of rows and each row contains cell tagged with a c tag and a reference attribute tagged as r. The value of the cell is contained in a subelement tagged as v.
Sample spreadsheet page in XLSX
The cell values in the v tags either are integers or are floating-point numbers. The latter can be taken at face value but the integers are actually indexes into a string lookup. The string lookup is contained in another xml file called /xl/sharedStrings.xml which must also be parsed and an indexed lookup created to support the cell values. They are organized from index 0 to n.
Sample shared strings file in XLSX
Handling an XLSX file with Java code
The Java approach that I planned to take was to write a parser that would load the file into memory, traverse the files entries in the zip, unzip the ones of interest into memory and then process them. The files of interest to meet the goal above are the sheets and the shared strings. As the sheets are parsed, I would add them to a worksheet object. As rows are parsed they would be added to their respective sheet, and cells would be added to their respective rows. The shared strings would be parsed into a lookup table that would be available to the cells.
With this plan in hand, the code took shape rapidly. Working from outside (low-hanging fruit) in towards the more complex, the cell class was done first, then the row, the sheet, and the workbook classes. This left the parser itself, which represented the most complex object.
The parser logic broke down nicely according to the plan in that the first thing was to open the source file and pass it to the Zip handling. Once there, the file system in the zip could be traversed and the file data uncompressed as needed. Finally, the file data XML would be parsed. The source code for this functionality is located here on Chris Laforet Software’s site. The code is distributed under the Mozilla Public License and is free for your use. Just for clarification, here is the header text for the MPL:
The contents of this file are subject to the Mozilla Public License Version 1.1 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.mozilla.org/MPL/ Software distributed under the License is distributed on an “AS IS” basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for the specific language governing rights and limitations
under the License.
The workhorse in the class hierarchy is the XLSXContentReader. It is here that the Excel xlsx file is opened as a zip object and the files are enumerated. There is no specific guarantee of the order these files will be presented so the design is fluid permitting them to be handled when located in the stream. This fluid design extends to the XLSXCell which points to the shared string cache and only evaluates the contents after the xlsx file has been completely parsed.
ZipInputStream zip = new ZipInputStream(Stream);
ZipEntry entry;
while ((entry = zip.getNextEntry()) != null)
{
if (entry.isDirectory())
continue;
if (entry.getName().compareToIgnoreCase(SHAREDSTRINGPATH) == 0)
{
// handle the shared strings file here
continue;
}
if (!entry.getName().toLowerCase().startsWith(WORKSHEETPATH))
continue;
String filename = entry.getName().substring(WORKSHEETPATH.length());
if (filename.contains("/"))
continue;
// more code here to handle the spreadsheet
}
zip.close();
The other key component to the workhorse of XLSXContentReader is the parsing of the XML files themselves. For this, the DOM model was utilized from within Java. The choice of parsing platforms is entirely up to the developer with several powerful options being available to plug into Java. With the byte-array contents of the file, the DOM is called to do its work. It parses out the XML nodes which can then be located using the getElementsByTagName() method.
DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
Document dom = builder.parse(new ByteArrayInputStream(Contents));
NodeList sheetData = dom.getElementsByTagName("sheetData");
for (int current = 0; current < sheetData.getLength(); current++)
parseSheetData((Element)sheetData.item(current),sheetIndex);
The sheet parsing continues by locating the row nodes, the cell nodes, and finally the values in each of the cells is retrieved. As the cell data is extracted, new cell nodes are created and inserted into the respective spreadsheet. Notice how we located the sheetData node earlier, then now the r nodes, c nodes within them, and the v nodes containing the values.
NodeList rowData = SheetData.getElementsByTagName("row");
for (int curRow = 0; curRow < rowData.getLength(); curRow++)
{
Element row = (Element)rowData.item(curRow);
try
{
int rowIndex = Integer.parseInt(row.getAttribute("r")) - 1; // 0-based row number
NodeList colData = row.getElementsByTagName("c");
for (int curCol = 0; curCol < colData.getLength(); curCol++)
{
Element col = (Element)colData.item(curCol);
int colIndex = DecodeColumnNumber(col.getAttribute("r"));
boolean isSharedString = false;
String typeString = col.getAttribute("t"); // t="s" for shared string
if (typeString != null && typeString.compareToIgnoreCase("s") == 0)
isSharedString = true;
NodeList valueData = col.getElementsByTagName("v");
if (valueData.getLength() > 0)
{
Element el = (Element)valueData.item(0);
String value = el.getFirstChild().getNodeValue();
_workbook.addCell(SheetIndex,rowIndex,
new XLSXCell(_sharedStrings,colIndex,value,isSharedString));
}
}
}
catch (Exception ee)
{
// do something appropriate here
}
}
Java’s Inner Classes: A true Godsend!
Now the XLSX parsing logic was completed, how in the world could it be incorporated into Pega’s inline code? The answer is the wonderful inner class permitted in the Java environment. At any point in an inline run of code, a developer can create a new class and then use it in the subsequent code. There are some rules to this, of course, but it is not difficult to live up to the expectations.
First, and foremost, the classes do not have visibility qualifiers (private or public). They are introduced as class xyz in the code. Second, classes cannot have forward references to other inner classes. This means that all classes must be placed in dependent order in the inline code. Third, classes cannot contain any static elements in the class, neither data nor methods.
The beauty of this construct is that our coding problem is solved. As long as we can create inner classes within one of Pega’s Java activities then reference the functionality those classes bring within the same activity, everything will work nicely. The code is localized and can be tested as a unit and no changes have to be made in the SIT, QA, or Production code environments to accommodate our code. Certainly, if this functionality grows and needs more full-fledged support, it can easily become a library and be worked into the system at a later date and in a structured fashion.
The final solution
Following is a sample of code (wrapped in a main method) which demonstrates the inner class concept in action. The content of the main would be added to the Pega Java activity and then modified and enhanced to capture the data of interest. As you can see, it is an elegant solution to the problem and shows the power of Java’s inner class at work!
Notice the order of the classes. First is the XLSXSharedStrings class which is used by XLSXCell for lookups and in the XLSXContentReader while parsing out data from the file. Next in order is the hierarchy of cell to workbook (XLSXCell, XLSXRow, XLSXSheet, and XLSXWorkbook) since each class depends upon the class(es) before it. Finally, the XLSXContentReader class brings up the rear since it references most of the prior classes.
If you have examined the library code offered above, you would have noticed that there are two static strings in XLSXContentReader (WORKSHEETPATH and SHAREDSTRINGPATH) which were modified in the inner class version below by simply removing the static moniker. This renders them as class members but it does not have any effect on the code whatsoever unless a developer starts monkeying around with the source.
/* ParseXLSXTestbed.java
*
* Copyright (c) 2010, Chris Laforet Software/Christopher Laforet
* All Rights Reserved
*
* Started: Jun 18, 2010
* Revision Information: $Date$
* $Revision$
*
* The contents of this file are subject to the Mozilla Public License
* Version 1.1 (the "License"); you may not use this file except in
* compliance with the License. You may obtain a copy of the License at
* http: * www.mozilla.org/MPL/
*
* Software distributed under the License is distributed on an "AS IS"
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
* License for the specific language governing rights and limitations
* under the License.
*
* The Initial Developer of the Original Code is Chris Laforet from Chris Laforet Software.
* Portions created by Chris Laforet Software are Copyright (C) 2010. All Rights Reserved.
*
* Contributor(s): Chris Laforet Software.
*/
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
public class ParseXLSXTestbed
{
static public void main(String [] args) throws Exception
{
/** Provides the string lookup capabilities for an
* xlsx xml-format excel worksheet.
*
* @author Christopher Laforet
*/
class XLSXSharedStrings
{
private HashMap<Integer,String> _sharedStrings = new HashMap<Integer,String>();
/** Package-private method which inserts a shared string.
*
* @param Index the 0-based key for this string
* @param SharedString the value of the string.
*/
void addSharedString(int Index,String SharedString)
{
_sharedStrings.put(new Integer(Index),SharedString);
}
/** Package-private method which decodes a shared string by its index.
*
* @param Index the 0-based key for this string.
* @return a string value.
*/
String getSharedString(int Index)
{
Integer key = new Integer(Index);
if (_sharedStrings.containsKey(key))
return _sharedStrings.get(key);
return "";
}
}
/** Container for a single cell of data in the spreadsheet.
*
* @author Christopher Laforet
*/
class XLSXCell
{
private XLSXSharedStrings _sharedStringLookup;
private int _columnNumber;
private String _contents;
private int _sharedIndex = -1;
public XLSXCell(XLSXSharedStrings SharedStringLookup,int ColumnNumber,
String Contents,IsSharedString)
{
_sharedStringLookup = SharedStringLookup;
_columnNumber = ColumnNumber;
_contents = Contents == null ? "" : Contents;
if (IsSharedString && _contents.length() > 0)
{
boolean isNumber = true;
for (int count = 0; count < _contents.length(); count++)
{
if (!Character.isDigit(_contents.charAt(count)))
{
isNumber = false;
break;
}
}
if (isNumber)
_sharedIndex = Integer.parseInt(_contents);
}
}
/** Retrieves the 0-based column number of this cell in the row.
*
* @return the 0-based column number.
*/
public int getColumnNumber()
{
return _columnNumber;
}
/** Retrieves the contents string of this cell.
*
* @return the contents of the cell.
*/
public String getContents()
{
if (_sharedIndex >= 0)
return _sharedStringLookup.getSharedString(_sharedIndex);
return _contents;
}
}
/** Container for a row of data
*
* @author Christopher Laforet
*/
class XLSXRow
{
private int _rowNumber;
private HashMap<Integer,XLSXCell> _cells = new HashMap<Integer,XLSXCell>();
private int _totalColumns = 0;
public XLSXRow(int RowNumber)
{
_rowNumber = RowNumber;
}
/** Retrieves the 0-based row number for this row.
*
* @return the number of this row.
*/
public int getRowNumber()
{
return _rowNumber;
}
/** Retrieves the total number of columns in this row.
*
* @return the number of columns.
*/
public int getTotalColumns()
{
return _totalColumns;
}
/** Package-private method which inserts a cell in
* the specific column spot of a row.
*
* @param Cell the cell to insert.
*/
void addCell(XLSXCell Cell)
{
_cells.put(new Integer(Cell.getColumnNumber()),Cell);
if (Cell.getColumnNumber() >= _totalColumns)
_totalColumns = Cell.getColumnNumber() + 1;
}
/** Retrieves the cell from the specific cell location.
*
* @param ColumnNumber the 0-based column number.
* @return the value of a cell.
*/
public XLSXCell getCellAt(int ColumnNumber)
{
if (ColumnNumber < _totalColumns)
{
Integer key = new Integer(ColumnNumber);
if (_cells.containsKey(key))
return _cells.get(key);
}
return new XLSXCell(null,ColumnNumber,"");
}
}
/** Container for the data contained in
* a single worksheet.
*
* @author Christopher Laforet
*/
class XLSXSheet
{
private String _sheetName;
private HashMap<Integer,XLSXRow> _rows = new HashMap<Integer,XLSXRow>();
private int _totalRows = 0;
public XLSXSheet(String SheetName)
{
_sheetName = SheetName;
}
/** Retrieves the name of this sheet.
*
* @return the name of the sheet.
*/
public String getSheetName()
{
return _sheetName;
}
/** Retrieves the maximum number of rows in the sheet.
*
* @return total rows in the sheet.
*/
public int getTotalRows()
{
return _totalRows;
}
/** Package-private method used to add a row to this worksheet.
*
* @param Row the row to add.
*/
void addRow(XLSXRow Row)
{
_rows.put(new Integer(Row.getRowNumber()),Row);
if (Row.getRowNumber() >= _totalRows)
_totalRows = Row.getRowNumber() + 1;
}
/** Package-private method used to add a cell in the specified
* row in this worksheet. If the row does not exist, it is
* added.
*
* @param RowIndex the 0-based index of the row to uinsert into.
* @param Cell the cell to insert.
*/
void addCell(int RowIndex,XLSXCell Cell)
{
Integer key = new Integer(RowIndex);
if (_rows.containsKey(key))
_rows.get(key).addCell(Cell);
else
{
XLSXRow row = new XLSXRow(RowIndex);
addRow(row);
row.addCell(Cell);
}
}
/** Retrieves the contents of the specified row number.
*
* @param RowNumber the 0-based row number.
* @return the contents of the row which could be empty.
*/
public XLSXRow getRowAt(int RowNumber)
{
if (RowNumber < _totalRows)
{
Integer key = new Integer(RowNumber);
if (_rows.containsKey(key))
return _rows.get(key);
}
return new XLSXRow(RowNumber);
}
}
/** Contains a workbook of sheets from an XLSX file.
*
* @author Christopher Laforet
*/
class XLSXWorkbook
{
private List<XLSXSheet> _sheets = new ArrayList<XLSXSheet>();
/** Retrieves the total number of sheets in the workbook.
*
* @return total sheets.
*/
public int getTotalSheets()
{
return _sheets.size();
}
/** Package-private method which inserts a new sheet.
*
* @return the index of the new sheet.
* @param SheetName
*/
int addSheet(String SheetName)
{
int index = _sheets.size();
_sheets.add(new XLSXSheet(SheetName));
return index;
}
/** Retrieves the named sheet if it is in the workbook
* or returns a null. The name is checked in a case-
* insensitive manner.
*
* @param SheetName the name of the sheet to find.
* @return the sheet if gound or null if not.
*/
public XLSXSheet getSheet(String SheetName)
{
for (XLSXSheet sheet : _sheets)
{
if (sheet.getSheetName().compareToIgnoreCase(SheetName) == 0)
return sheet;
}
return null;
}
/** Retrieves the sheet at the specified index of the
* sheets in the workbook. If the index is out of range,
* an exception is thrown.
*
* @param SheetIndex the 0-based index for the sheet to fetch.
* @throws IndexOutOfBoundsException if the sheet index is invalid.
* @return the sheet in question.
*/
public XLSXSheet getSheetAt(int SheetIndex) throws IndexOutOfBoundsException
{
if (SheetIndex < 0 || SheetIndex >= _sheets.size())
throw new IndexOutOfBoundsException("Sheet index is invalid");
return _sheets.get(SheetIndex);
}
/** Package-private method which adds a new cell to the spreadsheet.
*
* @param SheetIndex the index of the sheet to locate.
* @param RowNumber the 0-based number of the row to insert in to.
* @param Cell the cell to insert.
* @throws IndexOutOfBoundsException if the sheet index does not exist.
*/
void addCell(int SheetIndex,int RowIndex,XLSXCell Cell) throws IndexOutOfBoundsException
{
if (SheetIndex < 0 || SheetIndex >= _sheets.size())
throw new IndexOutOfBoundsException("Sheet index is invalid");
_sheets.get(SheetIndex).addCell(RowIndex,Cell);
}
}
/** Permits reading the basic contents of an
* xlsx xml-format excel worksheet.
*
* @author Christopher Laforet
*/
class XLSXContentReader
{
private String WORKSHEETPATH = "xl/worksheets/";
private String SHAREDSTRINGPATH = "xl/sharedstrings.xml";
private XLSXWorkbook _workbook = new XLSXWorkbook();
private XLSXSharedStrings _sharedStrings = new XLSXSharedStrings();
/** Attempts to load the contents of the named XLSX
* Excel file.
*
* @param Filename
* @throws IOException if an error occurs opening or reading the file.
*/
public XLSXContentReader(String Filename) throws IOException
{
this(new File(Filename));
}
/** Attempts to load the contents of the XLSX Excel
* file in the specified file object.
*
* @param File the file object pointing to the xlsx file.
* @throws IOException if an error occurs opening or reading the file.
*/
public XLSXContentReader(File File) throws IOException
{
this(new FileInputStream(File));
}
/** Retrieves the workbook object for traversal.
*
* @return a workbook containing 0 or more sheets.
*/
public XLSXWorkbook getWorkbook()
{
return _workbook;
}
/** Attempts to load the contents of the XLSX Excel
* file that has been opened for input.
*
* @param Stream the input stream of the newly open file.
* @throws IOException if an error occurs opening or reading the file.
*/
public XLSXContentReader(InputStream Stream) throws IOException
{
ZipInputStream zip = new ZipInputStream(Stream);
ZipEntry entry;
while ((entry = zip.getNextEntry()) != null)
{
if (entry.isDirectory())
continue;
if (entry.getName().compareToIgnoreCase(SHAREDSTRINGPATH) == 0)
{
// read the contents of the entry
ByteArrayOutputStream buffer = new ByteArrayOutputStream(8192);
while (true)
{
int b = zip.read();
if (b == -1)
break;
buffer.write(b);
}
parseSharedStrings(buffer.toByteArray());
continue;
}
if (!entry.getName().toLowerCase().startsWith(WORKSHEETPATH))
continue;
String filename = entry.getName().substring(WORKSHEETPATH.length());
if (filename.contains("/"))
continue;
// read the contents of the entry
ByteArrayOutputStream buffer = new ByteArrayOutputStream(8192);
while (true)
{
int b = zip.read();
if (b == -1)
break;
buffer.write(b);
}
// uncomment to see the xml itself: String contents = new String(buffer.toByteArray(),Charset.forName("UTF-8"));
parseSpreadsheet(filename,buffer.toByteArray());
}
zip.close();
}
/** Parses out the shared strings used in the encoded sheets.
*
* @param Contents the contents of the shared strings xml file.
*/
private void parseSharedStrings(byte [] Contents)
{
try
{
DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
Document dom = builder.parse(new ByteArrayInputStream(Contents));
int index = 0;
NodeList shared = dom.getElementsByTagName("si");
for (int current = 0; current < shared.getLength(); current++)
{
Element si = (Element)shared.item(current);
NodeList textData = si.getElementsByTagName("t");
if (textData.getLength() > 0)
{
Element el = (Element)textData.item(0);
String value = el.getFirstChild().getNodeValue();
_sharedStrings.addSharedString(index++,value);
}
}
}
catch (Exception ee)
{
// do something appropriate here
}
}
/** Handles parsing the contents of the spreadsheet
* from the file and attempts to create a new spreadsheet.
*/
private void parseSpreadsheet(String Filename,byte [] Contents)
{
String sheetName = Filename;
if (Filename.contains("."))
sheetName = Filename.substring(0,Filename.lastIndexOf("."));
int sheetIndex = _workbook.addSheet(sheetName);
try
{
DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
Document dom = builder.parse(new ByteArrayInputStream(Contents));
NodeList sheetData = dom.getElementsByTagName("sheetData");
for (int current = 0; current < sheetData.getLength(); current++)
parseSheetData((Element)sheetData.item(current),sheetIndex);
}
catch (Exception ee)
{
// do something appropriate here
}
}
/** Handles parsing the contents of a sheet data node in
* the spreadsheet data.
*
* @param SheetData the DOM node of the sheet data tree.
* @param SheetIndex the index of this sheet in the workbook.
*/
private void parseSheetData(Element SheetData,int SheetIndex) throws Exception
{
NodeList rowData = SheetData.getElementsByTagName("row");
for (int curRow = 0; curRow < rowData.getLength(); curRow++)
{
Element row = (Element)rowData.item(curRow);
try
{
int rowIndex = Integer.parseInt(row.getAttribute("r")) - 1; // 0-based row number
NodeList colData = row.getElementsByTagName("c");
for (int curCol = 0; curCol < colData.getLength(); curCol++)
{
Element col = (Element)colData.item(curCol);
int colIndex = DecodeColumnNumber(col.getAttribute("r"));
boolean isSharedString = false;
String typeString = col.getAttribute("t"); // t="s" for shared string
if (typeString != null && typeString.compareToIgnoreCase("s") == 0)
isSharedString = true;
NodeList valueData = col.getElementsByTagName("v");
if (valueData.getLength() > 0)
{
Element el = (Element)valueData.item(0);
String value = el.getFirstChild().getNodeValue();
_workbook.addCell(SheetIndex,rowIndex,
new XLSXCell(_sharedStrings,colIndex,value,isSharedString));
}
}
}
catch (Exception ee)
{
// do something appropriate here
}
}
}
/** Decodes a column-row attribute into a valid 0-based
* column number. Column-row attributes are like A5, AB10,
* or HZ1091. Columns number from A-Z, then AA-AZ, then BA-BZ, etc.
*
* @param Attribute the letter-number column/row.
* @return a 0-based index.
*/
private int DecodeColumnNumber(String Attribute)
{
if (Character.isLetter(Attribute.charAt(1)))
{
int multiplier = (Character.toUpperCase(Attribute.charAt(0)) - 'A') + 1;
int column = Character.toUpperCase(Attribute.charAt(1)) - 'A';
return multiplier * 26 + column;
}
return Character.toUpperCase(Attribute.charAt(0)) - 'A';
}
}
// now for the code itself proper -- see how the inline code
// can use the classes and interact with them.
XLSXWorkbook workbook = (new XLSXContentReader("d:/temp/TestingXLSX.xlsx")).getWorkbook();
for (int curSheet = 0; curSheet < workbook.getTotalSheets(); curSheet++)
{
XLSXSheet sheet = workbook.getSheetAt(curSheet);
System.out.println("Sheet name is " + sheet.getSheetName());
System.out.println("Sheet has " + sheet.getTotalRows() + " rows");
for (int curRow = 0; curRow < sheet.getTotalRows(); curRow++)
{
XLSXRow row = sheet.getRowAt(curRow);
System.out.println(" Row " + curRow + " has " + row.getTotalColumns() + " columns");
for (int curCol = 0; curCol < row.getTotalColumns(); curCol++)
{
XLSXCell cell = row.getCellAt(curCol);
System.out.println(" Col " + curCol + " contains value of \"" + cell.getContents() + "\"");
}
}
}
}
}
Chris' Creative Musings 
Pingback: Getting Started with Pega Development | Chris' Creative Musings
Pingback: Programmers Showing Initiative: Never allow problems stop development | Chris' Creative Musings
Pingback: Chris’ Creative Musings: 2010 in review | Chris' Creative Musings