How to read data from excel sheet using java?

Question

I am a beginner in Java coding and would like to know how to read the following excel sheet data using Java.

Also, I have tried the below code -

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;


public class ReadingFromExcelSheet {

    public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException {
        FileInputStream ip = new FileInputStream("C:\\Users\\Sanjana Rajeev\\Desktop\\Murali_YoutubeLinks.xlsx");
        Workbook wb = WorkbookFactory.create(ip);
        Sheet sheet = wb.getSheet("MySheet1");
        int i,j;
        int rowcount =3,cellcount=2;
        for ( i=0;i<=rowcount;i++){
        for (j=0;j<cellcount;j++){
        Row row = sheet.getRow(i);
        Cell cell = row.getCell(j);
        String cellval = cell.getStringCellValue();
        System.out.println(cellval + "\t\t" );
        }       
        }

        ip.close();
    }


}

And i am getting the below shown output :

Topics      
YouTube Links       
Java Execution
Java and JDK dowload
Eclipse download
Create a Workspace/Project/Package/Class files      
https://youtu.be/Pvcv-V69Vc0        
Java Execution
Java and JDK dowload
Eclipse download
Create a Workspace/Project/Package/Class files
Datatypes
Variables
String Concatenation        
https://youtu.be/Gx0ubuYwTjg        
Global Variables (Static & NonStatic)
Local Variables
Memory Allocation

I am getting the values of the cell but not in their proper order as like the excel sheet. Can anyone please help?

Having the tabs at he end of your output string while using println() doesn't do anything. println() appends a newline. — Tripp Kinetics
– Tripp Kinetics, Commented Apr 4, 2018 at 20:06
I'd say that you get exactly the correct order that you ask for. Either the youtube link is contained in a cell that spans multiple rows - or your left cell spans multiple rows and this is what you see in the output, in multiple rows as well. Add a marker after each cell and each row to your output to see the limits of each cell. — Olaf Kock
– Olaf Kock, Commented Apr 4, 2018 at 20:35

Mohammad · Accepted Answer · 2018-04-04 20:13:34Z

Try this approach

public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException {
        FileInputStream ip = new FileInputStream("C:\\Users\\Sanjana Rajeev\\Desktop\\Murali_YoutubeLinks.xlsx");
        Workbook wb = WorkbookFactory.create(ip);
        Sheet sheet = wb.getSheet("MySheet1");
        Iterator<Row> rowIterator = sheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            //iterate over the columns of the current row
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellValue = dataFormatter.formatCellValue(cell);
                System.out.print(cellValue + "\t");
            }
            //append empty line
            System.out.println();
        }

        ip.close();
    }

Tripp Kinetics · Accepted Answer · 2018-04-04 21:38:21Z

0

Does this improve the formatting?

    for ( i=0;i<=rowcount;i++)
    {
            Row row = sheet.getRow(i);

        for (j=0;j<cellcount;j++)
        {
            Cell cell = row.getCell(j);
            String cellval = cell.getStringCellValue();
            System.out.print(cellval + "\t\t" );
        }
        System.out.println();
    }

edited Apr 4, 2018 at 21:38

answered Apr 4, 2018 at 20:12

Tripp Kinetics

5,4892 gold badges25 silver badges40 bronze badges

2 Comments

Vishrant Over a year ago

Row row = sheet.getRow(i); can be moved out of second for loop

Tripp Kinetics Over a year ago

Done. Thanks for the suggestion.

Collectives™ on Stack Overflow

How to read data from excel sheet using java?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related