java - 使用 Java 程序从 Excel 文件中获取第 X 列

Question

我有一个 Excel 工作表，其中有近 150 列。我正在编写一个实用程序，其中将获取列名称为 X 的列。在另一个线程中，我看到了如何读取 XL 工作簿和工作表。已编写以下代码。

        HSSFSheet sheet = workbook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();


        while (rows.hasNext()) 
        {
            HSSFRow row = (HSSFRow) rows.next();
            Iterator cells = row.cellIterator();

            List data = new ArrayList();
            while (cells.hasNext()) 
            {
                HSSFCell cell = (HSSFCell) cells.next();
                data.add(cell);
            }

            sheetData.add(data);
        }
    } 

那么如何从给定列名称的 XL 工作表中获取确切的列..？

Answer 1

Apache POI API HSSFSheet 是基于行的，您需要通过迭代提取列数据，下面的链接可能会回答您的问题:

Extracting data in spreadsheet columns in Apache POI API

修改代码以搜索第一个工作表第 1 行中的字符串

package projectTest.test;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class Poi {


    public static void main(String[] args) throws Exception {  
    //test file is located in your project path         
    FileInputStream fileIn = new FileInputStream("test.xls");
    //read file 
    POIFSFileSystem fs = new POIFSFileSystem(fileIn); 
    HSSFWorkbook filename = new HSSFWorkbook(fs);
    //open sheet 0 which is first sheet of your worksheet
    HSSFSheet sheet = filename.getSheetAt(0);

    //we will search for column index containing string "Your Column Name" in the row 0 (which is first row of a worksheet
    String columnWanted = "Your Column Name";
    Integer columnNo = null;
    //output all not null values to the list
    List<Cell> cells = new ArrayList<Cell>();

    Row firstRow = sheet.getRow(0);

    for(Cell cell:firstRow){
        if (cell.getStringCellValue().equals(columnWanted)){
            columnNo = cell.getColumnIndex();
        }
    }


    if (columnNo != null){
    for (Row row : sheet) {
       Cell c = row.getCell(columnNo);
       if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
          // Nothing in the cell in this row, skip it
       } else {
          cells.add(c);
       }
    }
    }else{
        System.out.println("could not find column " + columnWanted + " in first row of " + fileIn.toString());
    }

    }
}