Read from Excel (multiple cells) using Java

Question

I have an Excel sheet and I used Apache POI library to read that sheet, but I want the user to choose which row & (multiple selected cells) to show its data. for example:

Name | ID | Password | date

john | 1001 | 1234 | 2-9-1997

James | 1002 |4567 | 24-6-1980

Liza | 1003 | 7890 | 18 -11-1990

I want to read the first row but only name, id & data cells, not all of the row. How can I perform this?

Here’s my attempt:

package javaapplication;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Scanner;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class JavaApplication {
    public static void main(String[] args)  {
        try {
            // Specify the path of file
            File path=new File("/Users/monasaad1/Desktop/emp.xlsx");

            // load file
            FileInputStream file=new FileInputStream(path);

            // load workbook
            XSSFWorkbook workbook=new XSSFWorkbook(file);

            // Load sheet- Here we are loading first sheetonly
            XSSFSheet sheet1= workbook.getSheetAt(0);
            int  rowCounter = sheet1.getLastRowNum();

            // user input row & cell
            Scanner input = new Scanner(System.in);
            System.out.print("Please choose row");
            int choosenRow = input.nextInt();
            System.out.print("Please choose cell");
            int choosenCell = input.nextInt();

            for (int i=0; i<rowCounter+1; i++){
                String data =sheet1.getRow(choosenRow).getCell(choosenCell).getStringCellValue();
                System.out.println("Data from Row"+i+" is "+data);
            }
            } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}

Mustapha Belmokhtar · Accepted Answer · 2018-07-11 14:43:55Z

1

First you don't have to loop over the cells and the rows, knowing that if you choose a row and a cell you are expecting a single value. Second you have to verify the type of the cell before you perform getStringCellValue() because if the value is not a String an exception may occure.

Here's an attempt I came up with, I hope it may help.

 public static void main(String[] args) {
    try {
        // Specify the path of file
        File path=new File("/Users/monasaad1/Desktop/emp.xlsx");

        // load file
        FileInputStream file = new FileInputStream(path);

        // load workbook
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        // Load sheet- Here we are loading first sheetonly
        XSSFSheet sheet1 = workbook.getSheetAt(0);
        // user input row & cell
        Scanner input = new Scanner(System.in);
        System.out.print("Please choose row");
        int choosenRow = input.nextInt();
        System.out.print("Please choose cell");
        int choosenCell = input.nextInt();

        String data = sheet1.getRow(choosenRow).getCell(choosenCell).toString();// may return null if the choosen row and/or cell goes beyond
        // the rows count and/or the cells count
        System.out.println(String.format("Data from Row %d and cell %d is %s", choosenRow, choosenCell, data));


    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

EDIT : (based on the comments below) :

When you want to print the whole row, except for the password, use this:

public static void main(String[] args) {
//.... Skipped identical lines as above 
        System.out.print("Please choose row");
        int chosenRow = input.nextInt();

        StringBuilder stringBuilder = new StringBuilder();
        XSSFRow row = sheet1.getRow(chosenRow);
        int i = 0;
        for (; i < row.getPhysicalNumberOfCells() - 1; i++) {
            if (i == 2) {
                continue;// skipping the password cell
            }
            stringBuilder.append(row.getCell(i))
                    .append(" | ");
        }
        stringBuilder.append(row.getCell(i));

        String data = stringBuilder.toString();

        System.out.println(String.format("Data from Row %d are :%s", chosenRow, data));
  //.... Skipped identical lines as above 
}

edited Jul 11, 2018 at 14:43

answered Jul 11, 2018 at 10:34

Mustapha Belmokhtar

1,2198 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LaLa Over a year ago

Thanks for your info! but I want my answer to be like: the 2nd row, selected cells (0,1,3). Output shall be James | 1002 | 24-6-1980.

Mustapha Belmokhtar Over a year ago

ok @LaLa I understand now, because in your question you said that you wanted to provide the celle number and the row number, then you will get the specified celle value, if you want to do as you mentionned in your comment, just loop over the given row and then skip the cell number 2, I will edit my answer based on your comment.

LaLa Over a year ago

Thanks @мυѕτавєւмo, can I have your email? I'd like to send you my attempt, I implement it with GUI but it has some error.

Collectives™ on Stack Overflow

Read from Excel (multiple cells) using Java

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related