How to read values from an excel file using Apache POI

Question

I am currently working on a project for my computer science class, and I was trying to figure out how to retrieve and print certain values from a file in excel. Such as, how would I go about printing the integer in column J, row 6?

Better yet, is there a way for me to return the row number of a string in column 1? Such as, if I had a string "Phone" in column 1, could I use a command to return the row number of the first instance of "phone"?

I have looked at other questions, none of which sufficiently answered my own.

The proper way to go about this is to read the Apache POI user guide, try the tutorials, and run a few experiments. When you have a program that you *think* should be getting the correct value but isn't, then you ask a question about that program on StackOverflow. An answer would not be meaningful if you don't have the basic knowledge of your problem domain. — RealSkeptic, Nov 25 '19 at 16:35
The underlying point of a project is that you will be able to learn something new. It might take some time, but it's worth the investment. I would suggest to read the Apache POI documentation, which also has examples (http://poi.apache.org/components/spreadsheet/examples.html). Like @RealSkeptic said if you have issues, come back with the code which has the error. Happy learning :) — Vinnie, Nov 25 '19 at 16:44
Does this answer your question? [How to read Excel cell having Date with Apache POI?](https://stackoverflow.com/questions/3148535/how-to-read-excel-cell-having-date-with-apache-poi) — Prasad, Dec 07 '19 at 10:30

score 0 · Accepted Answer · answered Nov 25 '19 at 17:59

Here you go refer to this class file for iterating over an excel file

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.util.Iterator;

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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {

    private static final String FILE_NAME = "/users/developer/Documents/myFile.xlsx";

    public void employeesUpload() {
        String fName = "";
        String lName = "";
        String phoneNumber = "";
        String email = "";
        String gender = "";
        String employeeCode = "";

        try {

            FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();

            int rowIndex = 0;
            DataFormatter formatter = new DataFormatter();
            while (iterator.hasNext()) {
                Row currentRow = iterator.next();
                if (rowIndex > 0) {
                    Iterator<Cell> cellIterator = currentRow.iterator();

                    employeeCode = fName = lName = phoneNumber = email = gender = "";

                    int cellIndex = 0;
                    while (cellIndex <= 5) {

                        Cell currentCell = currentRow.getCell(cellIndex);
                        if (cellIndex == 4) {
                            employeeCode = formatter.formatCellValue(currentCell).trim();
                        }

                        if (cellIndex == 1) {
                            fName = formatter.formatCellValue(currentCell).trim();
                        }

                        if (cellIndex == 2) {
                            lName = formatter.formatCellValue(currentCell).trim();
                        }

                        if (cellIndex == 0) {
                            email = formatter.formatCellValue(currentCell);
                            email = email.trim().toLowerCase();
                        }

                        if (cellIndex == 3) {
                            phoneNumber = formatter.formatCellValue(currentCell).trim();
                        }

                        cellIndex++;
                    }
                    Cell resultCell = currentRow.getCell(7);

                    if (resultCell == null) {
                        resultCell = currentRow.createCell(7);
                    }

                    Cell employementIdCell = currentRow.getCell(8);
                    if (employementIdCell == null) {
                        employementIdCell = currentRow.createCell(8);
                    }

                    if (true) {
                        resultCell.setCellType(Cell.CELL_TYPE_STRING);
                        employementIdCell.setCellValue("Success");
                        resultCell.setCellValue(email);

                    } else {
                        resultCell.setCellType(Cell.CELL_TYPE_STRING);
                        resultCell.setCellValue("Error");
                    }

                }
                rowIndex++;
            }

            FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) throws ParseException, UnsupportedEncodingException {

        Test employeesBulkUpload = new Test();
        employeesBulkUpload.employeesUpload();

    }
}

Hope this helps :)

score 0 · Answer 2 · answered Nov 26 '19 at 07:42

user https://github.com/jueyue/easypoi this jar

use annotion to easy read excel

public class ExcelImportNewDateTest {

@Test
public void importTest() {
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    params.setHeadRows(1);
    long start = new Date().getTime();
    List<NewDateEntity> list = ExcelImportUtil.importExcel(
            new File(FileUtilTest.getWebRootPath("import/ExcelNewDateTest.xlsx")), NewDateEntity.class, params);
    System.out.println(new Date().getTime() - start);
    Assert.assertEquals(list.size(), 100);
    System.out.println(list.size());
    System.out.println(ReflectionToStringBuilder.toString(list.get(1)));

}

}

How to read values from an excel file using Apache POI

2 Answers2