Reading Excel Files with Apache POI HSSF

• facebook
• twitter
• in
• Email

To represent values from a spreadsheet, I find a Map of ArrayLists handy for the rows of data, with the key being the column name, and then dropping these Maps into an ArrayList. This makes for simple code to read the values for multiple purposes without having to create new objects or expose the HSSF APIs to client applications. This is definitely not the only approach, just the one I found most useful the first time I implemented the HSSF APIs (see the User-Friendly Updates section of "Open Source-Based Portal-Lite").

/**
 * @param sheetIndex
 * @return ArrayList<Map> where the key is the field names.
 * Assumes first row contains field names
 */
public ArrayList<Map> getMappedValues(int sheetIndex)
{
   ArrayList<String>    colNames    = null;
   ArrayList<Map>       mapArray    = null;
   HSSFRow              row         = null;
   HSSFSheet            sheet       = null;
   int                  sheetRows   = 0;
   int                  rowCols     = 0;
   Map<String, Object>  rowMap      = null;

   sheet     = this.workbook.getSheetAt(sheetIndex);
   sheetRows = sheet.getPhysicalNumberOfRows();
   mapArray  = new ArrayList<Map>(sheetRows - 1);
   colNames  = getColNames(sheetIndex);

   colNames.trimToSize();

   rowCols = colNames.size();

   for (int i = 1; i < sheetRows; i++)
   {
      row    = sheet.getRow(i);
      rowMap = new HashMap<String, Object>(rowCols);
      for (int c = 0; c < rowCols; c++)
      {
         rowMap.put(colNames.get(c), getCellValue(row.getCell(c)));
      }
      mapArray.add(rowMap);
   }
   return mapArray;
}

Again, you can overload to provide the convenience of a sheet name instead of an index:

public ArrayList<Map> getMappedValues(String sheetName)
{
   return getMappedValues(this.workbook.getSheetIndex(sheetName));
}

Using What You Read

The following example prints out my collection of series recordings to the console:

public static void main(String args[])
{
   ArrayList<String>                colNames      = null;
   ArrayList<Map<String, Object>>   columnMaps    = null;
   ExcelReader                      excelReader   = null;
   Iterator<String>                 colNamesIt    = null;
   Iterator<Map<String, Object>>    columnMapsIt  = null;
   Map<String, Object>              columnMap     = null;
   String                           colKey        = null;
   String[]                         myRecordings  = new
      String[]{"NCIS", "DoctorWho"};

   try
   {
      excelReader = getInstance("demo_data.xls");

      for(int i=0; i < myRecordings.length; i++)
      {
         colNames     = excelReader.getColNames(myRecordings[i]);
         colNamesIt   = colNames.iterator();
         columnMaps   = excelReader.getMappedValues(myRecordings[i]);
         columnMapsIt = columnMaps.iterator();

         while(columnMapsIt.hasNext())
         {
            columnMap     = columnMapsIt.next();

            while(colNamesIt.hasNext())
            {
               colKey = colNamesIt.next();

               System.out.println(colKey+"\t"+
                  ((columnMap.get(colKey)!=null)?
                    columnMap.get(colKey):""));
            }
            System.out.println("---------------------------------");
            colNamesIt = colNames.iterator();
         }
      }
   }
   catch (Exception any)
   {
      any.printStackTrace();
   }
}

With some minor changes, you can present this as an HTML table:

<tr><% while(colNamesIt.hasNext()){%>
   <th><%=colNamesIt.next() %></th>
   <%}colNamesIt  = colNames.iterator();%></tr>
<% while(columnMapsIt.hasNext()){ %><tr>
<% columnMap = columnMapsIt.next(); while(colNamesIt.hasNext()){
   colKey = colNamesIt.next();
   isNeed = colKey.equals("Status")
            && columnMap.get(colKey)==null;%>
<td<%if(isNeed){ %> bgcolor="red"<%} %>>
   <%=((columnMap.get(colKey)!=null)?columnMap.get(colKey):"Need") %>
</td>
<%}colNamesIt  = colNames.iterator();%></tr>
<%} %></table></td><%}%></tr></table><%}catch
   (Exception any){any.printStackTrace();}%>

Click here for a larger image.

Figure 1: Web Table from HSSF

Or, you could take the field names and values and create a SQL insert or update statement.

Conclusion

Providing non-technical users the ability to submit data in the form of Microsoft Excel files is a win-win solution for both developers and users. The POI project makes this a practical approach for any J2EE web project with their HSSF and XSSF APIs.

For the curious, HSSF stands for "Horrible Spread Sheet Format", and XSSF for "XML Spread Sheet Format". The former is for Excel 2007 and earlier, whereas the latter is for Excel 2007 and forward. Because the majority of users still use the older Excel format, this article contained only HSSF examples. That, and no one has offered me a free copy of the MS Office upgrade to try out the newer APIs.

About the Author

Scott Nelson provides optimization services designing, developing, and maintaining web-based applications for manufacturing, pharmaceutical, financial services, non-profits, and real estate agencies for use by employees, customers, vendors, franchisees, executive management, and others who use a browser. For information on how he can help with your web applications, please visit http://www.fywservices.com/. He also blogs all of the humorous emails forwarded to him at Frequently Unasked Questions.

Page 2 of 2