如何在 Java 中从 Excel 中的列名中读取值

how to read a value from Column Name in Excel in Java

提问by psaikia

How can I read a value related to Specific Column Names with Apache POI in Excel (xlsx).

如何在 Excel (xlsx) 中使用 Apache POI 读取与特定列名称相关的值。

Column Names is my 1st Row of Excel Sheet . value is the row Which have Y as cell value .

列名是我的 Excel Sheet 的第一行。value 是将 Y 作为单元格值的行。

My Input is only Column Name , I need value for the Column name and Row which has value "Y"

我的输入只是 Column Name ，我需要值为 "Y" 的列名和行的值

Below is the Excel Sheet

下面是Excel表格

here I if enter Column names as "Names" , it should return value "Jose" And "Age" it should return 23

在这里我如果输入列名作为 "Names" ，它应该返回值 "Jose" 和 "Age" 它应该返回 23

I have tried and found two codes with different loop system , but getting some error

我已经尝试并找到了两个具有不同循环系统的代码，但出现了一些错误

public static void main(String[] args) throws Exception {

    File file =    new File("D:\xcel.xlsx");
    FileInputStream inputStream = new FileInputStream(file);


     Workbook wb = new XSSFWorkbook(inputStream);
     Sheet sh = wb.getSheet("Sheet1");

    //  Find the Column number Which has column name and row number 0

     Row row1 = sh.getRow(0);       
     int colNum = -1;
     for (int i = 0 ;i<=row1.getLastCellNum();i++){
         Cell cell1 = row1.getCell(i,Row.CREATE_NULL_AS_BLANK);
         String cellValue1 = cell1.getStringCellValue();
         if ("Employee".equals(cellValue1)){
              colNum = i ;
              break;}
        }



    //  Find the Row number Which is "Y" and column number 1

     int rowNum = -1;        
     for (int j = 0 ;j<=sh.getLastRowNum()+1;j++){           
         Row row2 = sh.getRow(j); 
         Cell cell2 = row2.getCell(1,Row.CREATE_NULL_AS_BLANK);       
         String cellValue2 = cell2.getStringCellValue();
            if ("Y".equals(cellValue2))
            { rowNum = j ;
            break;}                     
        }



    Row r = sh.getRow(rowNum);
     String val = r.getCell(colNum).getStringCellValue();

     System.out.println("Row number is "+rowNum);
     System.out.println("Last row number :"+sh.getLastRowNum());
     System.out.println("Column number is "+colNum);
     System.out.println("Last Column number :"+sh.getRow(0).getLastCellNum());
     System.out.println("Value is "+val); 

}

When I run the above code , I get the column number even if there is a blank cell is present before it ,

当我运行上面的代码时，即使前面有一个空白单元格，我也会得到列号，

but for row , if there is a blank cell present before it , i get error Exception in thread "main" java.lang.NullPointerException But it works fine if there is no black cell.

但是对于行，如果在它之前存在一个空白单元格，我会在线程“main”java.lang.NullPointerException 中收到错误异常，但是如果没有黑色单元格，它可以正常工作。

Again in this below code , I don't get any error

再次在下面的代码中，我没有收到任何错误

But if my column name is present in last cell ,then last cell number and my column number should be same , but I getting my column number is one less than the last cell number .

但是如果我的列名出现在最后一个单元格中，那么最后一个单元格号和我的列号应该相同，但是我得到的列号比最后一个单元格号少一个。

But for row number , its fine , I am getting same number .

但是对于行号，很好，我得到了相同的数字。

This problem is applies to the first code also

这个问题也适用于第一个代码

public static void main(String[] args) throws Exception {

    File file =    new File("D:\xcel.xlsx");
    FileInputStream inputStream = new FileInputStream(file);


     Workbook wb = new XSSFWorkbook(inputStream);
     Sheet sh = wb.getSheet("Sheet1");

    //  Find the Column number Which has column name and row number 0

     Row row1 = sh.getRow(0);       

     int colNum = -1;
     for (Cell cell : row1) {
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if (cell.getRichStringCellValue().getString().trim().equals("Employee")) {
                    colNum =  cell.getColumnIndex();                                   }
                                                             }
                                }

    //  Find the Row number Which is "Y" and column number 1


     int rowNum = -1;
     for (Row row : sh) {
            for (Cell cell : row) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getRichStringCellValue().getString().trim().equals("Y")) {
                        rowNum =  row.getRowNum();                                     }
                                                                 }
                                    }
                        }               


 // Get the required value
    Row r = sh.getRow(rowNum);
     String val = r.getCell(colNum).getStringCellValue();

     System.out.println("Row number is "+rowNum);
     System.out.println("Last row number :"+sh.getLastRowNum());
     System.out.println("Column number is "+colNum);
     System.out.println("Last Column number :"+sh.getRow(0).getLastCellNum());
     System.out.println("Value is "+val); 

回答by Rofgar

Apache POI does not support this kind of thing out of the bag. You might need to read your EXCEL file to an abstract structure of your own writting.

I would recommend something like a Document model, that contains your headers and contents.

If your purpose is solely gathering values by column name, with a bit of data redundancy you can store each value as a map entry of {column name : row value} in a list of all rows.

When having such a structure you can easily search by column values.

Apache POI 不支持这种现成的东西。您可能需要将您的 EXCEL 文件读取到您自己写作的抽象结构。

我会推荐类似 Document 模型的东西，其中包含您的标题和内容。

如果您的目的只是按列名收集值，那么您可以将每个值存储为所有行列表中 {column name : row value} 的映射条目。

拥有这样的结构时，您可以轻松地按列值进行搜索。

For example:

例如：

import java.util.ArrayList;
import java.util.List;

public class Document {

    private List<String> headers;

    private List<DataRow> contents;

    public Document(List<String> headers, List<DataRow> contents) {
        this.headers = headers;
        this.contents = contents;
    }

    public List<DataRow> findAllWhere(String column, String value) {
        List<DataRow> result = new ArrayList<>();
        for (DataRow content : contents) {
            if (content.get(column).equals(value)) {
                result.add(content);
            }
        }
        return result;
    }

    public List<DataRow> getContents() {
        return contents;
    }

    public void setContents(List<DataRow> contents) {
        this.contents = contents;
    }

    public List<String> getHeaders() {
        return headers;
    }

    public void setHeaders(List<String> headers) {
        this.headers = headers;
    }

    @Override
    public String toString() {
        return "Document{" +
                "headers=" + headers +
                ", contents=" + contents +
                '}';
    }

}

For rows we can have something like this:

对于行，我们可以有这样的东西：

import java.util.HashMap;
import java.util.Map;

public class DataRow {

    private Map<String, String> values = new HashMap<>();
    private Integer index;

    public String put(String columnName, String value) {
        return values.put(columnName, value);
    }

    public String get(String columnName) {
        return values.get(columnName);
    }

    public Integer getIndex() {
        return index;
    }

    public void setIndex(Integer index) {
        this.index = index;
    }


    @Override
    public String toString() {
        return "DataRow{" +
                "values=" + values +
                ", index=" + index +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        DataRow dataRow = (DataRow) o;

        if (values != null ? !values.equals(dataRow.values) : dataRow.values != null) return false;
        return !(index != null ? !index.equals(dataRow.index) : dataRow.index != null);

    }

    @Override
    public int hashCode() {
        int result = values != null ? values.hashCode() : 0;
        result = 31 * result + (index != null ? index.hashCode() : 0);
        return result;
    }

}

Example how to parse it:

示例如何解析它：

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class Parser {

    private InputStream stream;

    public Parser(InputStream stream) {
        this.stream = stream;
    }

    public Document parse() {
        try {
            Workbook wb = WorkbookFactory.create(stream);

            //TODO better sheet searching
            Sheet sheet = wb.getSheetAt(0);
            Iterator<Row> rows = sheet.rowIterator();

            List<String> headers = new ArrayList<>();
            if (rows.hasNext()) {
                Row row = rows.next();
                for (Cell cell : row) {
                    headers.add(cell.getStringCellValue());
                }
            }

            List<DataRow> contents = new ArrayList<>();
            while (rows.hasNext()) {
                Row row = rows.next();
                DataRow dataRow = new DataRow();
                dataRow.setIndex(row.getRowNum());
                for (Cell cell : row) {
                    //TODO safeguard for header resolving, cell column index might be out of bound of header array
                    dataRow.put(
                            headers.get(cell.getColumnIndex()),
                            cell.getStringCellValue()
                    );
                }
                contents.add(dataRow);
            }

            return new Document(headers, contents);
        } catch (IOException | InvalidFormatException e) {
            throw new RuntimeException(e);
        }
    }

}

Example how to use:

使用示例：

 Parser parser = new Parser(getClass().getResourceAsStream("resource.xlsx"));
 List<DataRow> data = parser.parse().findAllWhere("FLAG", "Y");

Full mock up is available here

完整模型可在此处获得

Is this what you are looking for? I must warn you, this is not a good idea to read the whole excel each time you need to search on it.

这是你想要的？我必须警告你，每次需要搜索时都阅读整个 excel 并不是一个好主意。

public static String findFirstExecute(String excelPath, String sheetName, String columnName) {
    return findFirstValue(excelPath, sheetName, columnName, "Execute", "Y");
}

public static String findFirstValue(String excelPath, String sheetName, String columnName, String filterColumnName, String filterColumnValue) {
    try {
        Workbook wb = WorkbookFactory.create(new FileInputStream(new File(excelPath)));

        Sheet sheet = wb.getSheet(sheetName);
        Iterator<Row> rows = sheet.rowIterator();

        Map<String, Integer> headers = new HashMap<>();
        if (rows.hasNext()) {
            Row row = rows.next();
            for (Cell cell : row) {
                headers.put(cell.getStringCellValue(), cell.getColumnIndex());
            }
        }

        while (rows.hasNext()) {
            Row row = rows.next();
            String executeValue = row.getCell(headers.get(filterColumnName)).getStringCellValue();
            if (executeValue.equals(filterColumnValue)) {
                return row.getCell(headers.get(columnName)).getStringCellValue();
            }
        }
        return null;
    } catch (IOException | InvalidFormatException e) {
        throw new RuntimeException(e);
    }
}