to get columns from Excel files using Apache POI?
In order to do some statistical analysis I need to extract values in a column of an Excel sheet. I have been using the Apache POI package to read from Excel files, and it works fine when one needs to iterate over rows. However I couldn't find anything about getting columns neither in the API (link text) nor through google searching.
As I need to get max and min values of different columns and generate random numbers using these values, so without picking up individual columns, the only other option is to iterate over rows and columns to get the values and compare one by one, which doesn't sound all that time-efficient.
Any ideas on how to tackle this problem?
Thanks,
Excel files are row based rather than column based, so the only way to get all the values in a column is to look at each row in turn. There's no quicker way to get at the columns, because cells in a column aren't stored together.
Your code probably wants to be something like:
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());
}
}
}
That'll then give you all the numeric cell values in that column.
I know it's an old question but I had the same problem as presented and had to solve it differently.
My code could not be easily adapted and would have gained alot of unnecessary complexity. So I decided to change the excel sheet instead by inversing columns and rows like explained here: (http://www.howtogeek.com/howto/12366/)
You can also inverse it by VBA like shown here:
Convert row with columns of data into column with multiple rows in Excel 2007
Hope it helps somebody out there
只是想补充一下，如果你的文件中有标题，但你不确定列索引，但想为特定的标题（列名）选择列，例如，你可以尝试这样的事情
for(Row r : datatypeSheet)
{
Iterator<Cell> headerIterator = r.cellIterator();
Cell header = null;
// table header row
if(r.getRowNum() == 0)
{
// getting specific column's index
while(headerIterator.hasNext())
{
header = headerIterator.next();
if(header.getStringCellValue().equalsIgnoreCase("column1Index"))
{
column1Index = header.getColumnIndex();
}
}
}
else
{
Cell column1Cells = r.getCell(column1);
if(column1Cells != null)
{
if(column1Cells.getCellType() == Cell.CELL_TYPE_NUMERIC)
{
// adding to a list
column1Data.add(column1Cells.getNumericCellValue());
}
else if(column1Cells.getCellType() == Cell.CELL_TYPE_FORMULA && column1Cells.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC)
{
// adding to a list
column1Data.add(column1Cells.getNumericCellValue());
}
}
}
}