Not able to print the values of cell with in a range through poi

162 views Asked by At

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

0

There are 0 answers