Spring Boot + Microsoft Excel Hello World Example (2023)

There are many cases where we need to integrate our Spring Boot Application with Excel. With the help of Apache POI, it becomes easy to integrate our application with Excel. There are various interfaces present in this dependency like Sheet, Row, Cell as well as Workbook by which excel file can be modelled.

• Project structure

  This will be the standard directory layout for maven project structure-
  
  
  We need to start by creating a Maven pom.xml(Project Object Model) file. The pom.xml file contains the project configuration details.
  We add the following dependency to integrate Spring Boot with Excel File.

  <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
  
    <groupId>com.codeusingjava</groupId>
    <artifactId>ExcelFileUpdateApplication</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
  
    <name>ExcelFileUpdateApplication</name>
    <url>http://maven.apache.org</url>
  
    <properties>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
  
    <dependencies>
       		<dependency>
  			<groupId>org.apache.poi</groupId>
  			<artifactId>poi</artifactId>
  			<version>3.15</version>
  		</dependency>
  		 <dependency>
  			<groupId>org.apache.poi</groupId>
  			<artifactId>poi-ooxml</artifactId>
  			<version>3.15</version>
  		</dependency>  
    
      <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>3.8.1</version>
        <scope>test</scope>
      </dependency>
    </dependencies>
  </project>
  
  

  The model class for the Person can be described here:-

  		package com.codeusingjava;
  		public class Person {
  			private String personId ;
  			private String personName ;
  			private String email;
  			
  			public String getPersonId() {
  				return personId;
  			}
  			public void setPersonId(String personId) {
  				this.personId = personId;
  			}
  			public String getPersonName() {
  				return personName;
  			}
  			public void setPersonName(String personName) {
  				this.personName = personName;
  			}
  			public Person() {
  				super();
  			}
  			
  				public String getEmail() {
  				return email;
  			}
  			public void setEmail(String email) {
  				this.email = email;
  			}
  			@Override
  			public String toString() {
  				return "Person [personId=" + personId + ", personName=" + personName + ", email=" + email + "]";
  			}
  			public Person(String personId, String personName, String email) {
  				super();
  				this.personId = personId;
  				this.personName = personName;
  				this.email = email;
  			}
  			
  			
  			
  			
  		}
  		
  	

  
  The Service class for the application can be described as follows-
  The main logic for updating the excel file and adding the data can be written in this class.
  The path where the excel file is declared in the variable excelFilePath .
  Workbook is the collection of all the Sheets.
  Sheet represent a single sheet.
  Row represent a single row.
  Cell represent a single cell.
  Finally the code to write the data in the excel file is written as follows-

      
  
  	package com.codeusingjava;
  
  	import java.io.FileInputStream;
  	import java.io.FileOutputStream;
  	import java.util.ArrayList;
  	import java.util.List;
  	
  	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 ExcelService {
  		
  		public void updateExcelFile() {
  			
  			System.out.println("Integrating excel with Spring boot");
  			
  			String excelFilePath = "C:\\Users\\MyPC\\Documents\\names.xlsx";  
  			
  			try {
  				
  				FileInputStream fileInputStream = new FileInputStream(excelFilePath);
  				
  				Workbook workbook = WorkbookFactory.create(fileInputStream);		
  				Sheet sheet = workbook.getSheetAt(0);
  				
  				int lastRowCount = sheet.getLastRowNum();
  				System.out.println("lastRowCount.. "  +  lastRowCount);
  				
  				List<Person> personList = getPersonList();
  				for (int i = 0; i < personList.size(); i++) {
  					
  					Row dataRow = sheet.createRow(++lastRowCount);
  					dataRow.createCell(0).setCellValue(personList.get(i).getPersonId());
  					dataRow.createCell(1).setCellValue(personList.get(i).getPersonName());       	        
  					
  				}
  				
  				System.out.println("lastRowCount after excel sheet modified.. "  +  lastRowCount);
  				fileInputStream.close();
  				
  				FileOutputStream fileOutputStream = new FileOutputStream(excelFilePath);
  				workbook.write(fileOutputStream);
  				fileOutputStream.close();
  				System.out.println("Updated details successfully........");
  				
  				
  			} catch (Exception e) {
  				e.printStackTrace();
  			}
  			
  			
  		}
  		
  		
  		public List<Person> getPersonList(){
  			
  			List<Person> list = new ArrayList<Person>();
  			
  			list.add(new Person("1" ,"abc","abc@gmail.com"));
  			list.add(new Person("2" ,"def","def@gmail.com"));
  			list.add(new Person("3" ,"ghi","ghi@gmail.com"));
  			
  			
  			return list;
  			
  		}
  		
  	}
  	
  

  The main class for the Spring Boot application can be shown as below-

  package com.codeusingjava;
  public class App 
  {
  	public static void main( String[] args )
  	{
  		
  		ExcelService excelService = new ExcelService();
  		
  		excelService.updateExcelFile();
  	   
  	}
  }
  

If we now run the application we get the output as follows-

The updated excel file can be shown as follows-

Download the code-

Spring Boot + Excel Example