I have the below java program which is trying too read the excel sheet through java poi , first see below the excel sheet format it would be like as shown below
abcRef 21
ABC 20
ERT 60
FGT 57
abcRef SMS Seal Total
12 45 DRT 3000
23 36 QWE 2000
subtotal 5000
abcRef SMS Seal Total
18 25 hRT 1000
29 16 tWE 2100
subtotal 3100
RTY 57
TDZ 21
YUI 98
so as you can see above i need to catch all the table ranges staring from abcRef and ending at Total column so for that I have added the condition of header cell that first to catch this combination in other words first to find the header cell
abcRef+SMS+Seal+Total
which i am able to achieve so my below program first iterate through all the rows then try to find the header cell of the combination and then print all the values which it is doing
but there are few challenges in which i will be requiring your advise
i want only tables to be printed on console so for that i want to break my program whenever
- 1)any empty row is encountered
- 2)any row having the value of cell as subtotal
- 3) any header cell is encounterd which is a combination of (abcRef+SMS+Seal+Total)
so please advise how can i customize my below code to achieve so that i can print the below thing on my console
abcRef SMS Seal Total
12 45 DRT 3000
23 36 QWE 2000
abcRef SMS Seal Total
18 25 hRT 1000
29 16 tWE 2100
below is my piece of code in below program rite now in which only the header row is printed not as above shown which i want
public class abc{
public static void main(String[] args) throws Exception {
FileInputStream file = null ;
try {
HSSFRow r =findgetRowNo();
getAllColumnFromRow(r);
}
catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally{
file.close();
}
}
public static void getAllColumnFromRow(HSSFRow RowObject){
Iterator<Cell> itr = RowObject.iterator();
HSSFRow headerRow = RowObject.getSheet().getRow(0);
String cellValue = "";
String information = "";
int headerCellCount = 0;
//to avoid first column
while(itr.hasNext()){
Cell cell = itr.next();
Cell headerValue = headerRow.getCell(headerCellCount++);
switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue() +"";
information = information + " " +headerValue+" - "+cellValue+ "; ";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = cell.getNumericCellValue() + "";
information = information + " " +headerValue+" - "+cellValue+ "; ";
break;
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
System.out.println(cellValue);
information = information + " " +headerValue+" - "+cellValue+ "; ";
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
}
}
System.out.println("@@@@");
}
public static int findHeaderCell(HSSFSheet firstSheet) {
final String[] headers1 = { "abcRef", "SMS", "Seal", "Total "};
List<String> listHeader=Arrays.asList(headers1);
for (Row row : firstSheet) {
for (Cell cell : row) {
if(listHeader.contains(cell.getStringCellValue())) {
int row1 = cell.getRowIndex() + 1;
int col = cell.getColumnIndex();
if (firstSheet.getRow(row1) == null)
throw new RuntimeException("Row " + row1 + 1 + " is empty!");
Cell startOfFirstDataRow = firstSheet.getRow(row1).getCell(col);
if (startOfFirstDataRow == null) {
CellReference ref = new CellReference(row1, col);
throw new RuntimeException("Data not found at " + ref.formatAsString());
}
// now we take row from above to be the Row object where we seek our headers
int last = row.getLastCellNum();
for (int c = row.getFirstCellNum(); c < last; c++) {
int h = 0;
// check if the cell at (c + h) has the required value
for (; h < listHeader.size() && c + h < last; h++) {
if (!listHeader.get(h).equals(row.getCell(c + h).getStringCellValue())) {
System.out.println("headers not match as Expected");
break; // if the cell value differs from our header
}
}
int t;
if (h == listHeader.size()) // this means the break was never invoked
System.out.println("headers Matched as Expected");
return row.getRowNum();
// return c; // found it
}
// not found
return -1;
}
}
}
throw new RuntimeException("TradingRef header cell not found!");
}
public static HSSFRow findgetRowNo() throws Exception {
FileInputStream file = null ;
file = new FileInputStream(new File("C:\\abc.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet firstSheet1 = workbook.getSheetAt(0);
Iterator<Row> rowItr = firstSheet1.iterator();
final String[] headers1 = { "abcRef", "SMS", "Seal", "Total "}
List<String> listHeader=Arrays.asList(headers1);
for (Row row : firstSheet1) {
for (Cell cell : row) {
if(listHeader.contains(cell.getStringCellValue())) {
int row1 = cell.getRowIndex() + 1;
int col = cell.getColumnIndex();
if (firstSheet1.getRow(row1) == null)
throw new RuntimeException("Row " + row1 + 1 + " is empty!");
Cell startOfFirstDataRow = firstSheet1.getRow(row1).getCell(col);
if (startOfFirstDataRow == null) {
CellReference ref = new CellReference(row1, col);
throw new RuntimeException("Data not found at " + ref.formatAsString());
}
// now we take row from above to be the Row object where we seek our headers
int last = row.getLastCellNum();
for (int c = row.getFirstCellNum(); c < last; c++) {
int h = 0;
// check if the cell at (c + h) has the required value
for (; h < listHeader.size() && c + h < last; h++) {
if (!listHeader.get(h).equals(row.getCell(c + h).getStringCellValue())) {
System.out.println("headers not match as Expected");
break; // if the cell value differs from our header
}
}
int t;
if (h == listHeader.size()) // this means the break was never invoked
System.out.println("headers Matched as Expected");
row.getPhysicalNumberOfCells();
return (HSSFRow) row;
// return c; // found it
}
// not found
return null;
}
}
}
throw new RuntimeException("abcRef header cell not found!");
}
}
Can some body please advise on this