通过java的POI遍历一个行的列 [英] iterating over the columns of a row through poi in java

问题描述

我有一个Excel文件，在我的C命名abc.xls：驱动器（本地计算机），现在在Excel文件中的第一个表本身有如下一个表，这个表下方可以在任何范围在于用片，所以我开发了下面的Java程序将由一行首先根据扫描整个表，然后列基础上，会发现那里的细胞
TradeRef有

I have a excel file named abc.xls in my c: drive (local computer) , now in that excel file in the first sheet itself there is a table as shown below and this below table can lie in any range with in the sheet so i have developed the below java program which will scan the entire sheet first by row basis and then on column basis and will find the cell where TradeRef is there

 TradeRef   TMS  Deal     Date        
    12      45   DRT    23/97/2014      
    23      36   QWE    21/07/2015  

现在在我的下面程序的问题是，它捕获细胞，其中TradeRef是存在的，那么它遍历的列，然后以类似的方式它捕获下一行并遍历列

now the problem in my below program is that it captures the cell where TradeRef is there and then it iterates over the columns and then in similar fashion it captures the next row and iterating over the columns

但我想申请的逻辑是，当它抓住了TradeRef细胞并遍历列，并达到该表的最后一栏是日期在上表中就应该进一步扫描在未来20列在同一行，如果在未来的20列中存在具有任何值无细胞那么它应该移动到下一行，并且如果20列内它螨是任何细胞可以有值，则在这种情况下，它应该读该单元值

but the logic that i want to apply is that when it captures the TradeRef cell and iterating over the columns and reached to the last column of the table which is Date in the above table then it should further scan the next 20 columns within the same row and if within the next 20 columns there is no cell having any value then it should move to the next row and if within the 20 columns it mite be that any cell can have value then in that case it should read that cell value

所以它会像

 TradeRef   TMS  Deal     Date          <----- scan next 20 columns is there is no value in next 20 cells then move to next row else include that cell value also------->
    12      45   DRT    23/97/2014        
    23      36   QWE    21/07/2015  

所以请各位指教如何实现行内扫描下一个20列以上逻辑下面是我早期的实现，它

so please advise how to implement the above logic of scanning the next 20 columns within the row below is my earlier implementation that is

public class AAA {
    public static void main(String[] args) throws IOException {


        FileInputStream file = null ;
         try {

                 file = new FileInputStream(new File("C:\\abc.xls"));
                HSSFWorkbook workbook = new HSSFWorkbook(file);
                HSSFSheet firstSheet = workbook.getSheetAt(0);
                Iterator<Row> iterator = firstSheet.iterator();

                Cell c = findFirstRow(firstSheet);
             }




             catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally{
                file.close();
            }

    }

    public static Cell findFirstRow(HSSFSheet firstSheet) {
          for (Row row : firstSheet) {
            for (Cell cell : row) {
                  cell.setCellType(cell.CELL_TYPE_STRING);
              if ("TradeRef".equals(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());
                }
                return startOfFirstDataRow;
              }
            }
          }
          throw new RuntimeException("TradingRef header cell not found!");
        }


}

所以请告知我如何可以实现扫描接下来的20列以上逻辑

so please advise how can i implement the above logic of scanning next 20 columns

推荐答案

首先，你应该使用 org.apache.poi.ss.usermodel API，它的工作所有的Excel文件，而 HSSF * 类只用的.xls 文件。

First, you should probably use the org.apache.poi.ss.usermodel API, which work with all Excel files while the HSSF* classes only work with .xls files.

的 org.apache.poi.ss.usermodel.Sheet 类有一个的 getFirstRow（） 方法，你可以用它来启动搜索。接下来，你要找到包含给定的字符串，电池的序列，如：

The org.apache.poi.ss.usermodel.Sheet class has a getFirstRow() method that you can use to start your search. Next you want to find a sequence of Cells containing the given Strings, like:

// This is an array containing the headers
final String[] headers = { "TradeRef", "TMS", "Deal", "Date" };

// 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 < headers.length && c + h < last; h++) {
        if (!headers[h].equals(row.getCell(c + h).getStringCellValue())) {
            break; // if the cell value differs from our header
        }
    }
    if (h == headers.length) // this means the break was never invoked 
        return c; // found it
}
return -1; // not found

这篇关于通过java的POI遍历一个行的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！