如何找到在Excel工作表的表格数据 [英] How to find tabular data in Excel sheet

问题描述

我有一个Excel文件，在我的C命名abc.xls：驱动器（本地计算机），现在在Excel文件中的第一个表本身有一个表，如下图所示，

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,

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

现在请各位指教如何通过在Java apachae POI读取Excel工作表这个表。

Now please advise how to read this table from the excel sheet through apachae poi in java.

现在的问题是，此表可以在任何范围与在片材，例如，它可以与A1细胞开始，或者它可以开始与F25，所以换句话说表可以在任何范围中板材。我担心的是，以达到该区间的起点所在的TradeRef是那里第一次，请大家指教怎么去那里，然后打印内容到控制台？

Now the problem is that this table can be at any range with in the sheet, for example, it may be starting with A1 cell or it may be starting with F25, so in other words the table can be at any range in the sheet. My concern is to reach to the starting point of that range where the TradeRef is there first, please advise how to get there and then print the contents into the console?

推荐答案

美的第一条建议是在与谁写的Excel文件中的程序（或人）的起始单元同意 - 或者至少需要一个人打开表，然后用正确的参数调用你的程序像

Mi first advice is agreeing on the starting cell with the program (or the human) who writes to the Excel file - or at least require that a human opens the sheet and then invokes your program with the right parameters like

# F25 is the starting cell, 120 the number of rows
java trade.App F25 120

否则你留下迭代在片材的细胞并假设起点为第一单元的文本内容是TradeRef（或类似的检查所有您所期望的标头更复杂的变体）

Otherwise you are left with the heuristic approach of iterating over the cells of the sheet and assuming the starting point as the first cell whose text content is "TradeRef" (or more sophisticated variants like checking for all the headers you expect)

public Cell findFirstRow(Sheet sheet) {
  for (Row row : sheet) {
    for (Cell cell : row) {
      if (cell.getCellType() == Cell.CELL_TYPE_STRING
          && "TradeRef".equals(cell.getStringCellValue()
      ) {
        int row = cell.getRowIndex() + 1;
        int col = cell.getColumnIndex();
        if (sheet.getRow(row) == null)
          throw new RuntimeException("Row " + row + 1 + " is empty!");
        Cell startOfFirstDataRow = sheet.getRow(row).getCell(col);
        if (startOfFirstDataRow == null) {
          CellReference ref = new CellReference(row, col);
          throw new RuntimeException("Data not found at " + ref.formatAtString());
        }
        return startOfFirstDataRow;
      }
    }
  }
  throw new RuntimeException("TradingRef header cell not found!");
}

这篇关于如何找到在Excel工作表的表格数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！