Read / Write Excel Data Using Apache POI

We can store testing data in excel file for data driven test. But how to read the excel data out in java? In this article we will show you how to use Apache POI to implement this.

Download Apache POI Jar Library

  1. Go to https://poi.apache.org/ , Click Download in left panel.
    apache poi home page
  2. Choose the latest stable build as below picture.
    apache poi download page
  3. Download Apache POI 3.16 zip file, and extract it to a local folder such as C:/WorkSpace/dev2qa.com/Lib/poi-bin-3.16-20170419/poi-3.16apache poi 3.16 binary lib download link

Add Apache POI Jars In Java Project

  1. Right click your Java project name. Click “Build Path —> Configure Build Path” in popup menu.
    configure java build path in java project
  2. Click “Add External JARs” button in the popup dialog. Add all POI jars, POI dependent lib jars and ooxml lib jars.
    add apache poi jars
    add apache poi dependent lib jars
    add apache poi dependent ooxml lib jars

XLS VS XLSX

There are two excel file format, one is .xls which is older format (excel 97 – 2007) the other is xlsx which is the newest format ( excel 2007, 2010, 2013). So we had better use xlsx format.

Apache POI also provide below two ways to manipulate the two file format.

  1. XSSF : Operate .xlsx format file.
  2. HSSF : Operate .xls format file.

Apache POI Interface And Classs

  1. Workbook: interface to represent excel file.
    XSSFWorkbook: a class implement Workbook, operate XLSX file.
    HSSFWorkbook: a class implement Workbook, operate XLS file.
  2. Sheet: interface to represent excel sheet.
    XSSFSheet: a class implement Sheet, operate XLSX file.
    HSSFSheet: a class implement Sheet, operate XLS file.
  3. Row: interface to represent row in sheet.
    XSSFRow: a class implement Row, operate XLSX file.
    HSSFRow: a class implement Row, operate XLS file.
  4. Cell: interface to represent cell in row.
    XSSFCell: a class implement Cell, operate XLSX file.
    HSSFCell: a class implement Cell, operate XLS file.

Code Example

Please see below java code comments for more detail.

  1. EmployeeDTO.java : PoJo to save employee info.
    /* This is the class to save employee information. */
    public class EmployeeDTO {
    	
    	private String eName;
    	
    	private String ePasswd;
    	
    	private String eEmail;
    	
    	private String eAge;
    	
    	private String eDepartment;
    	
    	private String eSkill;
    
    	public String geteName() {
    		return eName;
    	}
    
    	public void seteName(String eName) {
    		this.eName = eName;
    	}
    
    	public String getePasswd() {
    		return ePasswd;
    	}
    
    	public void setePasswd(String ePasswd) {
    		this.ePasswd = ePasswd;
    	}
    
    	public String geteEmail() {
    		return eEmail;
    	}
    
    	public void seteEmail(String eEmail) {
    		this.eEmail = eEmail;
    	}
    
    	public String geteAge() {
    		return eAge;
    	}
    
    	public void seteAge(String eAge) {
    		this.eAge = eAge;
    	}
    
    	public String geteDepartment() {
    		return eDepartment;
    	}
    
    	public void seteDepartment(String eDepartment) {
    		this.eDepartment = eDepartment;
    	}
    
    	public String geteSkill() {
    		return eSkill;
    	}
    
    	public void seteSkill(String eSkill) {
    		this.eSkill = eSkill;
    	}
    
    	public EmployeeDTO(String eName, String ePasswd, String eEmail, String eAge, String eDepartment, String eSkill) {
    		super();
    		this.eName = eName;
    		this.ePasswd = ePasswd;
    		this.eEmail = eEmail;
    		this.eAge = eAge;
    		this.eDepartment = eDepartment;
    		this.eSkill = eSkill;
    	}
    
    	@Override
    	public String toString() {
    		StringBuffer retBuf = new StringBuffer();
    		retBuf.append("Employee Info : Name = ");
    		retBuf.append(this.geteName());
    		retBuf.append(" , Password = ");
    		retBuf.append(this.getePasswd());
    		retBuf.append(" , Email = ");
    		retBuf.append(this.geteEmail());
    		retBuf.append(" , Age = ");
    		retBuf.append(this.geteAge());
    		retBuf.append(" , Department = ");
    		retBuf.append(this.geteDepartment());
    		retBuf.append(" , Skill = ");
    		retBuf.append(this.geteSkill());
    		return retBuf.toString();
    	}
  2. Create excel xslx file.
    	/* Use Apacje POI to create a xlsx format excel file. 
    	 * filePath : The excel file save path.
    	 * employeeDtoList : A list of employee info that need to save.
    	 * */
    	private void createExcel(String filePath, List<EmployeeDTO> employeeDtoList)
    	{
    		if(filePath!=null && !"".equals(filePath.trim()))
    		{
    			try
    			{
    				/* Create excel workbook. */
    				Workbook excelWookBook = new XSSFWorkbook();
    				
    				/* */
    				CreationHelper createHelper = excelWookBook.getCreationHelper();
    				
    				/* Create employee info sheet. */
    				Sheet employeeSheet = excelWookBook.createSheet("Employee Info");
    	
    				/* Create employee info row. Row number start with 0.
    				 * The input parameter for method createRow(int rowNumber) is the row number that will be created.
    				 * */
    				
    				/* First create header row. */
    				Row headerRow = employeeSheet.createRow(0);
    				
    				headerRow.createCell(0).setCellValue("Name");
    				headerRow.createCell(1).setCellValue("Password");
    				headerRow.createCell(2).setCellValue("Email");
    				headerRow.createCell(3).setCellValue("Age");
    				headerRow.createCell(4).setCellValue("Department");
    				headerRow.createCell(5).setCellValue("Skill");
    				
    	
    				/* Loop for the employee dto list, add each employee data info into one row. */
    				if(employeeDtoList!=null)
    				{
    					int size = employeeDtoList.size();
    					for(int i=0;i<size;i++)
    					{
    						EmployeeDTO eDto = employeeDtoList.get(i);
    						
    						/* Create row to save employee info. */
    						Row row = employeeSheet.createRow(i+1);
    						
    						row.createCell(0).setCellValue(eDto.geteName());
    						row.createCell(1).setCellValue(eDto.getePasswd());
    						row.createCell(2).setCellValue(eDto.geteEmail());
    						row.createCell(3).setCellValue(eDto.geteAge());
    						row.createCell(4).setCellValue(eDto.geteDepartment());
    						row.createCell(5).setCellValue(eDto.geteSkill());
    					}
    				}
    				
    				/* Write to excel file */
    				FileOutputStream fOut = new FileOutputStream(filePath);
    				excelWookBook.write(fOut);
    				fOut.close();
    				
    				System.out.println("File " + filePath + " is created successfully. ");
    			}catch(Exception ex)
    			{
    				ex.printStackTrace();
    			}
    		}
    	}
  3. Read data from excel xlsx file.
    	/* Read data from an excel file. 
    	 * Return: a 2 dimension list that contain all rows data in the file.
    	 * filePath :  The excel file saved path.
    	 * sheetName : The sheetName that need to read data.
    	 * */
    	private List<List<String>> readExcel(String filePath, String sheetName)
    	{
    		List<List<String>> ret = new ArrayList();
    		if(filePath!=null && !"".equals(filePath.trim()) && sheetName!=null && !"".equals(sheetName.trim()))
    		{
    			try{
    				/* First need to open the file. */
    				FileInputStream fInputStream = new FileInputStream(filePath.trim());
    	
    				/* Create the workbook object. */
    				Workbook excelWookBook = new XSSFWorkbook(fInputStream);
    	
    				/* Get the sheet by name. */
    				Sheet employeeSheet = excelWookBook.getSheet(sheetName);
    				
    				int firstRowNum = employeeSheet.getFirstRowNum();
    				int lastRowNum = employeeSheet.getLastRowNum();
    				
    				System.out.println("firstRowNum = " + firstRowNum);
    				System.out.println("lastRowNum = " + lastRowNum);
    				
    				/* Because first row is header row, so we read data from second row. */
    				for(int i=firstRowNum+1; i<lastRowNum+1; i++)
    				{
    					Row row = employeeSheet.getRow(i);
    					
    					int firstCellNum = row.getFirstCellNum();
    					int lastCellNum = row.getLastCellNum();
    					
    					System.out.println("firstCellNum = " + firstCellNum);
    					System.out.println("lastCellNum = " + lastCellNum);
    					
    					List<String> rowDataList = new ArrayList<String>();
    					for(int j = firstCellNum; j < lastCellNum; j++)
    					{
    						String cellValue = row.getCell(j).getStringCellValue();
    						rowDataList.add(cellValue);
    					}
    					
    					ret.add(rowDataList);
    				}
    				
    			}catch(Exception ex){
    				ex.printStackTrace();
    			}
    		}
    		return ret;
    	}

    If you want to translate the returned 2D data list into a 2D array. Please read below article Save 2D List Data In 2D Array, Vice Versa

  4. Main method.
    	public static void main(String[] args) {
    		
    		String excelFilePath = "C:/Workspace/EmployeeInfo.xlsx";
    		
    		/* Create six employee data DTO object, add them all to a list. */
    		List<EmployeeDTO> employeeDtoList = new ArrayList<EmployeeDTO>();
    		
    		EmployeeDTO employee1 = new EmployeeDTO("jack", "jack1232123", "[email protected]", "35", "Dev" , "Java, Solr, Selenium.");
    		EmployeeDTO employee2 = new EmployeeDTO("jackie", "jackie888888", "[email protected]", "35", "QA" , "Javascript, C++, Selenium.");
    		EmployeeDTO employee3 = new EmployeeDTO("tom", "tom666666", "[email protected]", "35", "Dev" , "C++, Sybase, Objective C.");
    		EmployeeDTO employee4 = new EmployeeDTO("richard", "richard9898", "[email protected]", "38", "Dev" , "Java, J2EE, Spring.");
    		EmployeeDTO employee5 = new EmployeeDTO("lily", "lily998889", "[email protected]", "35", "QA" , "Hibernate, Struts, Webwork.");
    		EmployeeDTO employee6 = new EmployeeDTO("steven", "steven9999999", "[email protected]", "30", "Dev" , "MFC, .net, Objective C.");
    		employeeDtoList.add(employee1);
    		employeeDtoList.add(employee2);
    		employeeDtoList.add(employee3);
    		employeeDtoList.add(employee4);
    		employeeDtoList.add(employee5);
    		employeeDtoList.add(employee6);
    		
    		/* Save above employee data in to excel file.*/
    		ApachePOIExcel excelObj = new ApachePOIExcel();
    		excelObj.createExcel(excelFilePath, employeeDtoList);
    		
    		/* Read excel file data out, return a 2D list. */
    		ApachePOIExcel excelObj1 = new ApachePOIExcel();
    		List<List<String>> dataList = excelObj1.readExcel(excelFilePath, "Employee Info");
    	}

Download “Read-Excel-Data-Using-Apache-POI-Example-Code.zip” Read-Excel-Data-Using-Apache-POI-Example-Code.zip – Downloaded 237 times – 3 KB

(Visited 4,152 times, 32 visits today)
READ :   Execute jQuery With Selenium WebDriver Example

9 Comments


  1. your article is very much useful. thank you for sharing. now I want to convert these excel data in json format. guide me how i can do it. also in my pojo there are many no of fields like 90 fields hence 90 getter and setters. is there any other better way?

    Reply

    1. 1. If you want to convert the excel data in JSON format, you can translate the excel data list into JSON format with a method.

      2. If you have so many fields in your excel table sheet, Maybe you can get the columns information in code, and use java reflection to get those excel data, if i have time, i will write the code and create another article.

      Reply

      1. It will be very helpful. Thanks for replying. 🙂

        Reply

  2. If I want to make it more advance by generating output txt file for each row in excel sheet then how can do it? Means for each row individual text file generation. Please suggest. Thanks in advance. 🙂

    Reply

    1. When you get the excel sheet rows list, then you can loop in the list and write each row data in text file. Can this method resolve your concern?

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.