读取 Excel 标题和行值 [英] Read Excel header and row value

问题描述

我需要阅读具有标题和 N.of 行的 excel 内容.基于列标题输入，需要在JAVA中提取行.

I would need to read the excel content which has header and N.of rows. Based on column header input,the row needs to be extracted in JAVA.

我读取了完整的 excel 内容的 java 代码.

the java code which i has reads the full excel content.

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);
        int totalRows = sheet.getPhysicalNumberOfRows();


        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) 
            {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                }
            }
            System.out.println("");

Excel 内容

Coln1 Coln2 Coln3 Coln4 
A        12   nice  3e
A        23   talk  s2
A        43   res   23
B        11   xl    34
B        88   out   r45
C        45    tr    h5

预期结果

if (Coln1==B)
{

Loop the list of B rows (here its 2 rows)
Coln1 Coln2 Coln3 Coln4 
B        11   xl    34
B        88   out   r45

if i need , r45 , How to pass the row cell to get the value?

}

有人可以帮忙吗?谢谢

推荐答案

如果标题是第 1 行，也许你可以这样做.

If header is row 1, maybe you can do this.

Row headerRow = sheet.getRow(0);   

您可以为索引定义一些常量.例如

You can define some constant for index. e.g

int n1ColIndex = 0;    // 0-based.    this is the index of row in excel.
int n2ColIndex = 1;    // 
int n3ColIndex = 2;
int n4ColIndex = 3;

// then you can read cell by Row.getCell() method.

// below code, you can put in loop.
XSSFCell n1Cell = row.getCell(n1ColIndex);
if ( n1Cell == null ){
    continue;
}

String n1Col = n1Cell.getStringCellValue();
if( "B".equals(n1Col) ) {
    // get r45 value.

    XSSFCell n4Cell = row.getCell(n4ColIndex);
    if ( n4Cell == null ){
        continue;
    }

    String val = n4Cell.getStringCellValue();
    // this value will be 34 or r45
    // Do you understand ?

}

// update 1. how to get a row.

// method 1.
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
    Row row = rowIterator.next();

    // do something with the row var.
}

// method 2.
for ( int index=0; index < sheet.getLastRowNum(); index++){
    Row row = sheet.getRow(index);

    // check null.
    if ( row == null ){
        continue;
    }

    // do something with the row var.

}

API 文档 XSSFSheet

都是手写的，没有测试.有任何问题，回复我.

All write by hand, no test. Any question, reply me .

这篇关于读取 Excel 标题和行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！