Read / Write Excel Data Using Apache POI

Apache POI is a popular and easy to use java library to manage excel files. This apache POI example will tell you how to install apache POI library in your java project, how to use apache POI provided java classes to create, load an excel file data.

1. How To Install Apache POI Library In Eclipse Java Project.

There are two ways to install apache POI library jar files in eclipse java project.

1.1  Download Apache POI Jar Library File And Add The Jar Files To Eclipse Java Project Java Build Path.

  1. Go to https://poi.apache.org/ , click Download in the left panel.
  2. Download the version which you need( version 5.0.0 in this example ), and extract the downloaded zip file to a local folder.
  3. Right-click your java project name in eclipse. Click “Build Path —> Configure Build Path” in the popup menu. If you can not find the Build Path menu, then click Properties menu item.
  4. Click Libraries tab on the right panel, then click Add External JARs button. Add all below POI jars ( in poi-5.0.0 folder ), POI dependent lib jars ( in poi-5.0.0/lib folder ), and OOXML lib jars ( in poi-5.0.0/ooxml-lib folder ) to the java project java build path.
    apache poi used jar files

1.2 Install Apache POI Library In Java Maven Project.

  1. If your eclipse java project is a maven project, you can use this method.
  2. If your eclipse java project is not a maven project, you can convert it to a maven java project like below.
  3. Right-click the project name, click Configure —> Convert to Maven Project menu item.
  4. It will popup the Create new POM dialog, check the data in the dialog carefully, click Finish button.
  5. It will add a pom.xml file under the project.
  6. Edit pom.xml file, add below dependency in the dependencies directive. For more poi related dependencies please access https://mvnrepository.com/search?q=apache+poi.
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.0.0</version>
    </dependency>
    
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.0.0</version>
    </dependency>
    
  7. Right-click the maven project, click Maven —> Update Project menu item in the pop-up menu list, then it will download the above maven dependencies jar files to the maven project,

2. Apache POI Classes That Support Excel File Format Xlsx & Xls.

  1. There are two excel file formats, one is .xls which is the older format (excel 97 – 2007) the other is .xlsx which is the newer format ( excel 2007, 2010, 2013). So we had better use the .xlsx format.
  2. Apache POI provides xssf, hssf packages to operate the two file format. Below are some generally used java classes in those packages.
    1. package org.apache.poi.ss.*: contains base interfaces or classes that xssf and hssf pacakge will implement.
    
       org.apache.poi.ss.usermodel.Workbook: base interface to operate excel file workbook.
    
       org.apache.poi.ss.usermodel.Sheet: base interface to operate excel file worksheet in a workbook.
    
       org.apache.poi.ss.usermodel.Row: base interface to operate row in a worksheet.
    
       org.apache.poi.ss.usermodel.Cell: base interface to operate cell in a row.
       
    
    2. package org.apache.poi.xssf.*: contain below classes that operate .xlsx excel file format.
    
       org.apache.poi.xssf.usermodel.XSSFWorkbook: implement org.apache.poi.ss.usermodel.Workbook interface, operate XLSX format excel file workbook.
    
       org.apache.poi.xssf.usermodel.XSSFSheet: implement org.apache.poi.ss.usermodel.Sheet interface, operate XLSX fromat excel file worksheet.
    
       org.apache.poi.xssf.usermodel.XSSFRow: implement org.apache.poi.ss.usermodel.Row interface, operate XLSX format excel file row in a worksheet.
    
       org.apache.poi.xssf.usermodel.XSSFCell: implement org.apache.poi.ss.usermodel.Cell interface, operate XLSX fromat excel file cell in a worksheet row.
    
    
    3. package org.apache.poi.hssf.*: contain classes that operate .xls excel file format.
    
       org.apache.poi.hssf.usermodel.HSSFWorkbook: implement org.apache.poi.ss.usermodel.Workbook interface, operate XLS format excel file workbook.
    
       org.apache.poi.hssf.usermodel.HSSFSheet: implement org.apache.poi.ss.usermodel.Sheet interface, operate XLS fromat excel file worksheet in workbook.
    
       org.apache.poi.hssf.usermodel.HSSFRow: implement org.apache.poi.ss.usermodel.Row interface, operate XLS format excel file row in a worksheet.
    
       org.apache.poi.hssf.usermodel.HSSFCell: implement org.apache.poi.ss.usermodel.Cell interface, operate XLS fromat excel file cell in a worksheet row.

3. Apache POI Read / Write Excel File Code Example.

  1. EmployeeDTO.java : PoJo to save employee info data. One EmployeeDTO object represents one row in a worksheet, each attribute of this object represents one cell. A list of this object represents on worksheet.
    /* 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 method. This method will create an excel file and write the input EmployeeDTO list to a worksheet in the excel file workbook.
    	/* 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. This method will read a worksheet in an excel file and return an EmployeeDTO list.
    	/* 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. This method will invoke the above two methods.
    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");
      }
0 0 votes
Article Rating
Subscribe
Notify of
guest

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

12 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Vladimir

Can someone tell my how to write data in existing Excel file?

happyzhaosong
Reply to  Vladimir

If you want to write data to existing Excel file, you can first read it out to a list, then append new data at the end of the list, then write it back to the existing excel file.

juan
juan

this works for android?

@ngel
@ngel

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. ?

@Di
@Di

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?

@Di
@Di
Reply to  Jerry Zhao

It will be very helpful. Thanks for replying. 🙂

Raushan
Raushan
Reply to  @Di

can you tell me i want to read the data from excel sheet and store into userdto object then save into database

12
0
Would love your thoughts, please comment.x
()
x