As it is the cell's type that you are concerned with, the process should be
quite straightforward.
The first two pieces of information you need to kinow are that the cell's
type is indicated by a simple integer value and that there are constants
defined with in the org.apache.poi.ss.usermodel.Cell interface (and
inherited by the concrete classes HSSFCell and XSSFCell) to define the cells
type - Cell.CELL_TYPE_BLANK, Cell.CELLL_TYPE_BOOLEAN, Cell.CELL_TYPE_ERROR,
Cell.CELL_TYPE_FORMULA, Cell.CELL_TYPE_NUMERIC and Cell.CELL_TYPE_STRING.
Knowing this, it is possible to imagine that you could specify the types you
expect to find on a row using a array if integers, a little like this
int[] rowLayout = new int[]{Cell.CELL_TYPE_STRING, Cell.CELL_TYPE_STRING,
Cell.CELL_TYPE_NUMERIC,Cell.CELL_TYPE_NUMERIC,Cell.CELL_TYPE_NUMERIC,Cell.CELL_TYPE_NUMERIC,
Cell.CELL_TYPE_STRING};
and you could create one of these patterns for each different row you need
to check. Then, it is simply a matter of getting the row from the worksheet
and iterating through the cells, calling the getCellType() for each cell and
comparing it to the pattern.
That assumes of course that the cells are contiguous; i.e. that you want to
get the cells from columns 1, 2, 3, 4, 5, 6, 7, 8, 9, etc with no gaps. If
you do not want to do this, then I would define a small inner class that
allows me to specify both the column number and it's type. Create an array
of this type, add elements to it and then use this to drive the process, a
little like this;
class CellCheck {
public int columnIndex = 0;
public int cellType = 0;
public CellCheck(int columnIndex, int cellType) {
this.columnIndex = columnINdex;
this.cellType = cellType;
}
}
to use this class within code, all you need to do is the folowing;
CellCheck[] cellsToCheck = new CellCheck[3]
cellsToCheck[0] = new CellCheck(0, Cell.CELL_TYPE_STRING);
cellsToCheck[1] = new CellCheck(3, Cell.CELL_TYPE_NUMERIC);
cellsToCheck[2] = new CellCheck(10, Cell.CELL_TYPE_STRING);
Now, simply get the row and then do something like the following;
for(CellCheck cellCheck : cellsToCheck) {
Cell cell = row.getCell(cellCheck.columnIndex);
if(cell.getCellType() != cellCheck.cellType) {
// The cells are of a different type so handle the error here
}
}
Note that the data members of the CellCheck class have been left with public
accessibility as I cannot see any reason to hide them and create accessor
methods in such a simple, application specific class.
You could even extend this further and modify the approach and crate another
class that manages the arrays of cell indices and types and allows you to
add the index of the row, something like this;
class RowCheck {
public int rowIndex = 0;
public CellCheck[] cellsToCheck = null;
public RowCheck(int rowIndex, CellCheck[] cellsToCheck) {
this.rowIndex = rowIndex;
this.cellsToCheck = cellsToCheck;
}
}
This class could be used something like the following;
RowCheck[] rowsToCheck = new RowCheck[2];
CellCheck[] cellsToCheck = new CellCheck[3]
cellsToCheck[0] = new CellCheck(0, Cell.CELL_TYPE_STRING);
cellsToCheck[1] = new CellCheck(3, Cell.CELL_TYPE_NUMERIC);
cellsToCheck[2] = new CellCheck(10, Cell.CELL_TYPE_STRING);
rowsToCheck[0] = new RowCheck(2,cellsToCheck);
// Check the cells in row number 1 (indexes are zero based)
CellCheck[] cellsToCheck = new CellCheck[5]
cellsToCheck[0] = new CellCheck(0, Cell.CELL_TYPE_STRING);
cellsToCheck[1] = new CellCheck(3, Cell.CELL_TYPE_NUMERIC);
cellsToCheck[1] = new CellCheck(7, Cell.CELL_TYPE_NUMERIC);
cellsToCheck[1] = new CellCheck(9, Cell.CELL_TYPE_NUMERIC);
cellsToCheck[2] = new CellCheck(10, Cell.CELL_TYPE_STRING);
// Check these cells in row number 6
rowsToCheck[1] = new RowCheck(5,cellsToCheck);
and to use it, two nested for loops;
for(RowCheck rowCheck : rowsToCheck) {
Row row = sheet.getRow(cellCheck.rowIndex);
CellCheck cellsToCheck = rowCheck.cellsToCheck;
for(CellCheck cellCheck : cellsToCheck) {
Cell cell = row.getCell(cellCheck.columnIndex);
if(cell.getCellType() != cellCheck.cellType) {
// The cells are of a different type so handle the error here
}
}
}
Hope this helps a little. You did not say which file format you are dealing
with and so I have focussed on the SS stream and talked of Rows and Cells
rather than XSSF/HSSFRow and XSSF/HEEFCell. There is also a further wrinkle
you will need to attend to; date cells will also 'tell' you that they are
numeric. So you need to make an additional check in that case, to see if
they are date formatted but it would be better to cross this bridge a little
later in my mind.
If you need any further help, just post to the list.
Yours
Mark B
PS I do not have the api on the PC I am using at the moment so cannot be
certain the above will work but it should.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/How-to-check-if-Excel-values-are-matching-with-my-format-tp3379567p3380786.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
