Apache POI famous words

Apache POI excel library revolves around four key interfaces which actually represent the items in the excel file.

  • Workbook: A workbook represents the excel file
  • Sheet: A workbook may contain many sheets. We can access the sheets either with name or with index.
  • Row: As the name suggests, It represents a row in the sheet.
  • Cell: A cell represents a column in the sheet.

Read Excel with Apache POI

In this tutorial we are going to see how to read data from excel sheet, and to print the value on the screen step by step. 1. Create a Java class on Eclipse apache-poi-java-excel-read

2. Create excel sheet and place "Hello" in the top-right corner cell and save the file as data.xlsx data-excel-apache-poi-selenium-webdriver

3. Create Object for FileInputStream with excel file path, it makes the file into stream of input item

// create file input stream object for the excel sheet
FileInputStream fis = new FileInputStream("C:\\pathX\\data.xlsx");


4. Create Work book object by using create() method present in WorkbookFactory class, and pass the file Input stream object as parameter.

// create object for work book
Workbook wb = WorkbookFactory.create(fis);


5. Create Sheet object from workbook object(wb), we have to pass sheet name(Sheet1) as parameter

//create object for sheet present in excel using Workbook object 'wb'
Sheet sheet = wb.getSheet("Sheet1");


6. Create Row object from Sheet object(sheet), we have to pass Row number as parameter

//create object for row present in sheet using Sheet object 'sheet'
Row row = sheet.getRow(0);


7. Create Cell object from Row object(row), we have to pass Cell number as parameter

//create object for cell present in row using Row object 'row'
Cell cell = row.getCell(0);


8. There is method called getStringCellValue() in Cell Class, with help of this method we can retrieve string cell value from the Cell,

//print the value present in the excel sheet
System.out.println(cell.getStringCellValue());


Complete Program to retieve Cell value in Excel


import java.io.FileInputStream;
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 ReadExcel {
	public static void main(String[] args) throws Exception{
		// create file input stream object for the excel sheet
		FileInputStream fis = new FileInputStream("C:\\pathXX\\data.xlsx");
		// create object for work book
		Workbook wb = WorkbookFactory.create(fis);
		//create object for sheet present in excel using Workbook object 'wb'
		Sheet sheet = wb.getSheet("Sheet1");
		//create object for row present in sheet using Sheet object 'sheet'
		Row row = sheet.getRow(0);
		//create object for cell present in row using Row object 'row'
		Cell cell = row.getCell(0);
		//print the value present in the excel sheet
		System.out.println(cell.getStringCellValue());
	}
}

Output : Hello

Write Excel with Apache POI

In this tutorial we are going to see how to write data to excel sheet step by step detail. 1. Create a Java class on Eclipse apache-poi-write-excel

2. Create a excel file on your local machine ( you can find system created file bottom section ). apche-poi-data-write-file

3. Create Object for FileInputStream with excel file path, it makes the file into stream of input item

// create file input stream object for the excel sheet
FileInputStream fis = new FileInputStream("C:\\pathX\\data-write.xlsx");


4. Create Work book object by using create() method present in WorkbookFactory class, and pass the file Input stream object as parameter.

// create object for work book
Workbook wb = WorkbookFactory.create(fis);


5. Create Sheet object from workbook object(wb), we have to pass sheet name(Sheet1) as parameter

//create object for sheet present in excel using Workbook object 'wb'
Sheet sheet = wb.getSheet("Sheet1");


6. Create Row object from Sheet object(sheet), we have to pass Row number as parameter

//create object for row present in sheet using Sheet object 'sheet'
Row row = sheet.getRow(0);


7. Create Cell object from Row object(row), here we have to user createCell() method to create cell in particular row, we have to pass Cell number as parameter

//create object for cell present in row using Row object 'row'
Cell cell = row.createCell(0);


8. There is method called setCellValue() in Cell Class, with help of this method we can write cell value to the Cell in excel file,

//print the value present in the excel sheet
cell.setCellValue("Written by apache poi");


9. create object for FileOutputStream which makes the content of excdel into output stream items, so that we can write a excel,

//creates file output stream
FileOutputStream fos = new FileOutputStream("C:\\pathX\\data-write.xlsx");


10.With help of write() present in the Workbook class we can write the excel into local system, this method accepts FileOutputStream object as parameter

//writes excecl sheet into local file system
wb.write(fos);


Complete Program to Write Cell value in Excel


import java.io.FileInputStream;
import java.io.FileOutputStream;
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 WriteExcel {
	public static void main(String[] args) throws Exception {
		// create file input stream object for the excel sheet
		FileInputStream fis = new FileInputStream("C:\\path\\data-write.xlsx");
		// create object for work book
		Workbook wb = WorkbookFactory.create(fis);
		//create object for sheet present in excel using Workbook object 'wb'
		Sheet sheet = wb.getSheet("Sheet1");
		//create object for row present in sheet using Sheet object 'sheet'
		Row row = sheet.getRow(0);
		//create object for cell present in row using Row object 'row'
		Cell cell = row.createCell(0);
		cell.setCellValue("Written by apache poi");
		FileOutputStream fos = new FileOutputStream("C:\\path\\data-write.xlsx");
		wb.write(fos);
	}
}


apache-poi-after-execution-write-file

Retrieving Cell values by CellType

We can retrieve cell value using getStringCellValue method but it only works for String values. In acutal ;day to day activities we may store more types of data in excel sheets like Number, boolean, strings.

We have different methods to retrieve diffret types of data. in apche poi

To retrieve different data, You may check each cell's type and then retrieve its value using various type-specific methods.

You should understand that below methods will not extract a type of data from the cell, when you store a particular data type in a cell then the total cell is of that type

So these methods will fetch total value present in the cell

  • getBooleanCellValue() - To fetch boolean data from the excel
  • getDateCellValue() - fetches date values from the cell
  • getNumericCellValue() - fetches numeric value
  • getCellFormula() - fetches the data from the formula cell.

							public static void printCellValue(Cell cell) {
								switch (cell.getCellTypeEnum()) {
									case BOOLEAN:
										System.out.print(cell.getBooleanCellValue());
										break;
									case STRING:
										System.out.print(cell.getRichStringCellValue().getString());
										break;
									case NUMERIC:
										if (DateUtil.isCellDateFormatted(cell)) {
											System.out.print(cell.getDateCellValue());
										} else {
											System.out.print(cell.getNumericCellValue());
										}
										break;
									case FORMULA:
										System.out.print(cell.getCellFormula());
										break;
									case BLANK:
										System.out.print("");
										break;
									default:
										System.out.print("");
								}
							}			
							

 
Join My Facebook Group
Join Group
 

About Author

Myself KarthiQ, I am the author of this blog, I know ways to write a good article but some how I donot have the skills to make it to reach people, would you like help me to reach more people By sharing this Article in the social media.

Share this Article Facebook
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions
  • vamsi
    this code is wrong 
    iam getting an error
    Exception in thread "main" java.io.FileNotFoundException: F:java121.xlsx (The system cannot find the file specified)
    	at java.io.FileInputStream.open0(Native Method)
    	at java.io.FileInputStream.open(FileInputStream.java:195)
    	at java.io.FileInputStream.(FileInputStream.java:138)
    	at java.io.FileInputStream.(FileInputStream.java:93)
    	at killer.Fb1.main(Fb1.java:14)
    
    Reply
    • Karthiq [admin]
      Hi vamsi, 
      Please make sure you have the excel file in given path. 
      As the Exception suggest the file is not present in the given path
      
      
      Sometimes you might have created the xls file but you may searching for xlsx file
      
      Let me if this works
      Reply
  •  
    Selenium-Webdriver.Com [Group]
    Facebook Group · 801 members
    Join Group
    Learn Selenium Webdriver Concepts and Interview Questions Learn and code
     
    Copyright © CherCher Tech