I want the number of columns of a particular row in excel. How is that possible? I used POI API
but I could get only columns count to 7 .
try
{
fileInputStream = new FileInputStream(file);
workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheet("0");
int numberOfCells = 0;
Iterator rowIterator = sheet.rowIterator();
/**
* Escape the header row *
*/
if (rowIterator.hasNext())
{
Row headerRow = (Row) rowIterator.next();
//get the number of cells in the header row
numberOfCells = headerRow.getPhysicalNumberOfCells();
}
System.out.println("number of cells "+numberOfCells);
}
I want the number of columns at a particular row number say 10 . The excel columns are not same
This question is related to
java
excel
apache-poi
Sometimes using row.getLastCellNum()
gives you a higher value than what is actually filled in the file.
I used the method below to get the last column index that contains an actual value.
private int getLastFilledCellPosition(Row row) {
int columnIndex = -1;
for (int i = row.getLastCellNum() - 1; i >= 0; i--) {
Cell cell = row.getCell(i);
if (cell == null || CellType.BLANK.equals(cell.getCellType()) || StringUtils.isBlank(cell.getStringCellValue())) {
continue;
} else {
columnIndex = cell.getColumnIndex();
break;
}
}
return columnIndex;
}
/** Count max number of nonempty cells in sheet rows */
private int getColumnsCount(XSSFSheet xssfSheet) {
int result = 0;
Iterator<Row> rowIterator = xssfSheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
List<Cell> cells = new ArrayList<>();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
cells.add(cellIterator.next());
}
for (int i = cells.size(); i >= 0; i--) {
Cell cell = cells.get(i-1);
if (cell.toString().trim().isEmpty()) {
cells.remove(i-1);
} else {
result = cells.size() > result ? cells.size() : result;
break;
}
}
}
return result;
}
Source: Stackoverflow.com