How to get columns from Excel files using Apache POI

Question

I need to extract values from a column of an Excel file so that I may perform some statistical analysis. When one has to iterate over rows, the Apache POI module works well for reading from Excel files. However, neither the API (link text) nor a google search turned up anything regarding accessing columns.  Without picking up individual columns, the only other option is to iterate over rows and columns to get the values and compare one at a time, which doesn't sound like it would be very time-efficient. I need to get the maximum and minimum values of various columns so that I can generate random numbers using these values.

Any suggestions on how to solve this issue?

Answer 1

The only way to see all the values in a column in an Excel file is to look at each row individually because Excel files are row oriented rather than column based. The fact that cells in a column are not stored together means that there is no faster way to access the columns.

Change your code like this:

List<Double> values = new ArrayList<Double>();
for(Row r : sheet) {
   Cell c = r.getCell(columnNumber);
   if(c != null) {
      if(c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
         valuesadd(c.getNumericCellValue());
      } else if(c.getCellType() == Cell.CELL_TYPE_FORMULA && c.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
         valuesadd(c.getNumericCellValue());
      }
   }
}