read excel by column name java POI

Question

Good afternoon experts have a problem and I need to read the entire excel file by column name instead of its index,example:

Column1 | Column2 | Column3
data1      data 2    data 3

POI allows me to read the column index by the method getColumnIndex() returning for the Column1 = 0 , Column2= 1 etc, but I need to read it by column name Column1 , Column2 etc , there any way to do this??

I need to read all fields of rows and columns by column name. attach the code which I read my file:

updated code:

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class example {

    DataFormatter fmt = new DataFormatter();

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws FileNotFoundException, IOException {
        example softMarti = new example();
        FileInputStream file = new FileInputStream(new File("C:archive.xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowIndex = row.getRowNum();
            if (rowIndex < 1) {
                continue;
            }
            Iterator cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int columnIndex = cell.getColumnIndex();
                if (columnIndex != 0 && columnIndex != 1 && columnIndex != 4) {
                    continue;
                }
                String columnName = "";
                switch (columnIndex) {
                    case 0:
                        columnName = "column1";
                        break;
                    case 1:
                        columnName = "column2";
                        break;
                    case 4:
                        columnName = "column 4";
                        break;
                }
                String value = example.getValue(cell);
                boolean valid = example.isValid(columnIndex, value);
                if (valid) {
                    continue;
                }
                System.out.print(columnName + rowIndex);
                System.out.println(" -> " + value);

            }

        }

        // TODO code application logic here
    }

    private String getValue(Cell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                return null;
            case Cell.CELL_TYPE_BOOLEAN:
                return "CELL_TYPE_BOOLEAN";
            case Cell.CELL_TYPE_ERROR:
                return "CELL_TYPE_ERROR";
            case Cell.CELL_TYPE_FORMULA:
                return "CELL_TYPE_FORMULA";
            case Cell.CELL_TYPE_NUMERIC:
                return fmt.formatCellValue(cell);
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "none";

        }

    }

    boolean isValid(int column, String value) {
        if (value == null) {
            return false;
        }
        String pattern = "";
        switch (column) {
            case 0:
                pattern = "[A-Za-z0-9_\- ]{1,20}";
                break;
            case 1:
                pattern = "[A-Za-z0-9_\- ]{1,80}";
                break;
            case 4:
                pattern = "[0-9]{1,8}";
                break;
        }
        Pattern pat = Pattern.compile(pattern);
        Matcher mat = pat.matcher(value);
        return mat.matches();
    }
}

This code works but I need to validate column name because for my project columns may change position, that's my goal

user_10271141 · Answer

I wrote a method

public static int columnName(String a) throws EncryptedDocumentException, InvalidFormatException, IOException {

    int coefficient = 0;
    String excelFilePath = ConfigurationReader.getProperty("pathToYourFile"); // or specify the path directly
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
    Workbook wb = WorkbookFactory.create(inputStream);
    Sheet sh = wb.getSheet("Sheet1");
    Row row = sh.getRow(0);
    int cellNum = row.getPhysicalNumberOfCells();
    for (int i = 0; i < cellNum; i++) {
        if ((row.getCell(i).toString()).equals(a)) {
            coefficient = i;
        }
    }

    return coefficient;
}

and then just call it in my code:

Cell anyCellName = row.getCell(columnName("NameOfColumnInMyExcell"));

And like this with any column names. Now I'm able to move my columns in any order and code works.

user_11567596 · Answer

Here is my way, I hope it can help

First of all let us get columns names and put it into a map

 Map requiredHeaders = new HashMap<>();
        FileInputStream file = new FileInputStream(new File("filename.xlsx"));
        Workbook workbook = new XSSFWorkbook(file);
        DataFormatter formatter = new DataFormatter();
        Sheet sheet = workbook.getSheetAt(0);
 for (Cell cell : sheet.getRow(0)) {
        requiredHeaders.put(cell.getStringCellValue(), cell.getColumnIndex());
    }

Then We Can Loop for rows to get the required row using the column index

    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        System.out.println("serial = " + formatter.formatCellValue(row.getCell(requiredHeaders.get("serial"))));
        System.out.println("pin = " + formatter.formatCellValue(row.getCell(requiredHeaders.get("pin"))));
    }

Now Full Code Will be Like :

private void readFile() throws FileNotFoundException, IOException {
    Map requiredHeaders = new HashMap<>();
    FileInputStream file = new FileInputStream(new File("filename.xlsx"));
    Workbook workbook = new XSSFWorkbook(file);
    DataFormatter formatter = new DataFormatter();
    Sheet sheet = workbook.getSheetAt(0);
    for (Cell cell : sheet.getRow(0)) {
        requiredHeaders.put(cell.getStringCellValue(), cell.getColumnIndex());
    }

    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        System.out.println("serial = " + formatter.formatCellValue(row.getCell(requiredHeaders.get("serial"))));
        System.out.println("pin = " + formatter.formatCellValue(row.getCell(requiredHeaders.get("pin"))));
    }
    workbook.close();
}

Now here is the output

read excel by column name java POI

3 Answers