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, Apr 04 '18 at 20:06
Possible duplicate of [Read XLSX file in Java](https://stackoverflow.com/questions/267025/read-xlsx-file-in-java) — xtratic, Apr 04 '18 at 20:10
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, Apr 04 '18 at 20:35

score 2 · Answer 1 · answered Apr 04 '18 at 20:13

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-04T21:38:21.897

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 04 '18 at 21:38

answered Apr 04 '18 at 20:12

Tripp Kinetics

5,178
2
23
37

`Row row = sheet.getRow(i);` can be moved out of second `for` loop – Vishrant Apr 04 '18 at 21:35
Done. Thanks for the suggestion. – Tripp Kinetics Apr 04 '18 at 21:38

How to read data from excel sheet using java?

2 Answers2