Import Excel File in Spring MVC Framework

Create Spring MVC Project

On the Eclipse, create a Spring MVC project

Enter Project Information:

• Name: LearnSpringMVCWithRealApps
• Group: com.demo
• Artifact: LearnSpringMVCWithRealApps
• Description: Learn Spring MVC with Real Apps
• Package: com.demo

Select the technologies and libraries to be used:

• Web

Click Next button to show Site Information for project

Click Finish button to finish create Spring MVC project

Configure pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.10.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.demo</groupId>
	<artifactId>LearnSpringMVCWithRealApps</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>LearnSpringMVCWithRealApps</name>
	<description>Learn Spring MVC with Real Apps</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.apache.tomcat.embed</groupId>
			<artifactId>tomcat-embed-jasper</artifactId>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>javax.servlet.jsp.jstl</groupId>
			<artifactId>javax.servlet.jsp.jstl-api</artifactId>
			<version>1.2.1</version>
		</dependency>

		<dependency>
			<groupId>taglibs</groupId>
			<artifactId>standard</artifactId>
			<version>1.1.2</version>
		</dependency>

		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.3.3</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Create Excel File

Create new Excel file as photo below:

Configure application.properties

spring.mvc.view.prefix = /WEB-INF/views/
spring.mvc.view.suffix = .jsp
spring.mvc.static-path-pattern=/resources/**

server.port=9596

Resources Folder

Create new folder named resources in webapp folder. In resources folder, create new folder named excels

Entities Class

Create new package, named com.demo.entities. In this package, create entities class as below:

Product Entity

Create new java class, named Product.java as below:

package com.demo.entities;

import java.io.Serializable;
import java.util.Date;

public class Product implements Serializable {

	private String id;
	private String name;
	private double price;
	private int quantity;
	private Date creationDate;
	private boolean status;

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public double getPrice() {
		return price;
	}

	public void setPrice(double price) {
		this.price = price;
	}

	public int getQuantity() {
		return quantity;
	}

	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}

	public Date getCreationDate() {
		return creationDate;
	}

	public void setCreationDate(Date creationDate) {
		this.creationDate = creationDate;
	}

	public boolean isStatus() {
		return status;
	}

	public void setStatus(boolean status) {
		this.status = status;
	}

}

Excel Helper

Create new package named com.demo.helpers. In this package, create new java class named ExcelHelper.java as below:

package com.demo.helpers;

import java.io.*;
import java.lang.reflect.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

public class ExcelHelper {

	private List<String> fieldNames = new ArrayList<String>();
	private Workbook workbook = null;
	private String workbookName = "";

	public ExcelHelper(String workbookName) {
		this.workbookName = workbookName;
		initialize();
	}

	private void initialize() {
		setWorkbook(new HSSFWorkbook());
	}

	public void closeWorksheet() {
		FileOutputStream fileOut;
		try {
			fileOut = new FileOutputStream(getWorkbookName());
			getWorkbook().write(fileOut);
			fileOut.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private boolean setupFieldsForClass(Class clazz) throws Exception {
		Field[] fields = clazz.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			fieldNames.add(fields[i].getName());
		}
		return true;
	}

	private Sheet getSheetWithName(String name) {
		Sheet sheet = null;
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
			if (name.compareTo(workbook.getSheetName(i)) == 0) {
				sheet = workbook.getSheetAt(i);
				break;
			}
		}
		return sheet;
	}

	private void initializeForRead() throws InvalidFormatException, IOException {
		InputStream inp = new FileInputStream(getWorkbookName());
		workbook = WorkbookFactory.create(inp);
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> readData(String classname) throws Exception {
		initializeForRead();
		Sheet sheet = getSheetWithName(classname);
		Class clazz = Class.forName(workbook.getSheetName(0));
		setupFieldsForClass(clazz);
		List<T> result = new ArrayList<T>();
		Row row;
		for (int rowCount = 1; rowCount < 4; rowCount++) {
			T one = (T) clazz.newInstance();
			row = sheet.getRow(rowCount);
			int colCount = 0;
			result.add(one);
			for (Cell cell : row) {
				String fieldName = fieldNames.get(colCount++);
				Method method = constructMethod(clazz, fieldName);
				CellType cellType = cell.getCellTypeEnum();
				if (cellType == CellType.STRING) {
					String value = cell.getStringCellValue();
					Object[] values = new Object[1];
					values[0] = value;
					method.invoke(one, values);
				} else if (cellType == CellType.NUMERIC) {
					Double num = cell.getNumericCellValue();
					Class returnType = getGetterReturnClass(clazz, fieldName);
					if (returnType == int.class || returnType == Integer.class) {
						method.invoke(one, num.intValue());
					} else if (returnType == double.class || returnType == Double.class) {
						method.invoke(one, num);
					} else if (returnType == float.class || returnType == Float.class) {
						method.invoke(one, num.floatValue());
					} else if (returnType == long.class || returnType == Long.class) {
						method.invoke(one, num.longValue());
					} else if (returnType == Date.class) {
						Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
						method.invoke(one, date);
					}
				} else if (cellType == CellType.BOOLEAN) {
					boolean num = cell.getBooleanCellValue();
					Object[] values = new Object[1];
					values[0] = num;
					method.invoke(one, values);
				}
			}
		}
		return result;
	}

	private Class getGetterReturnClass(Class clazz, String fieldName) {
		String methodName = "get" + capitalize(fieldName);
		String methodIsName = "is" + capitalize(fieldName);
		Class returnType = null;
		for (Method method : clazz.getMethods()) {
			if (method.getName().equals(methodName) || method.getName().equals(methodIsName)) {
				returnType = method.getReturnType();
				break;
			}
		}
		return returnType;
	}

	@SuppressWarnings("unchecked")
	private Method constructMethod(Class clazz, String fieldName) throws SecurityException, NoSuchMethodException {
		Class fieldClass = getGetterReturnClass(clazz, fieldName);
		return clazz.getMethod("set" + capitalize(fieldName), fieldClass);
	}

	public <T> void writeData(List<T> data) throws Exception {
		try {
			Sheet sheet = getWorkbook().createSheet(data.get(0).getClass().getName());
			setupFieldsForClass(data.get(0).getClass());
			int rowCount = 0;
			int columnCount = 0;
			Row row = sheet.createRow(rowCount++);
			for (String fieldName : fieldNames) {
				Cell cel = row.createCell(columnCount++);
				cel.setCellValue(fieldName);
			}
			Class<? extends Object> classz = data.get(0).getClass();
			for (T t : data) {
				row = sheet.createRow(rowCount++);
				columnCount = 0;
				for (String fieldName : fieldNames) {
					Cell cel = row.createCell(columnCount);
					Method method = classz.getMethod("get" + capitalize(fieldName));
					Object value = method.invoke(t, (Object[]) null);
					if (value != null) {
						if (value instanceof String) {
							cel.setCellValue((String) value);
						} else if (value instanceof Long) {
							cel.setCellValue((Long) value);
						} else if (value instanceof Integer) {
							cel.setCellValue((Integer) value);
						} else if (value instanceof Double) {
							cel.setCellValue((Double) value);
						} else if (value instanceof Date) {
							cel.setCellValue((Date) value);
							CellStyle styleDate = workbook.createCellStyle();
							DataFormat dataFormatDate = workbook.createDataFormat();
							styleDate.setDataFormat(dataFormatDate.getFormat("m/d/yy"));
							cel.setCellStyle(styleDate);
						} else if (value instanceof Boolean) {
							cel.setCellValue((Boolean) value);
						}
					}
					columnCount++;
				}
			}

			// Autofit
			for (int i = 0; i < fieldNames.size(); i++)
				sheet.autoSizeColumn(i);

			FileOutputStream out = new FileOutputStream(new File(workbookName));
			workbook.write(out);
			out.close();
			workbook.close();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	public String capitalize(String string) {
		String capital = string.substring(0, 1).toUpperCase();
		return capital + string.substring(1);
	}

	public String getWorkbookName() {
		return workbookName;
	}

	public void setWorkbookName(String workbookName) {
		this.workbookName = workbookName;
	}

	void setWorkbook(Workbook workbook) {
		this.workbook = workbook;
	}

	public Workbook getWorkbook() {
		return workbook;
	}

}

Product Controller

Create new package named com.demo.controllers. In this package, create new java class named ProductController.java as below:

package com.demo.controllers;

import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.servlet.ServletContext;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.context.ServletContextAware;
import org.springframework.web.multipart.MultipartFile;

import com.demo.entities.Product;
import com.demo.helpers.ExcelHelper;

@Controller
@RequestMapping(value = { "", "product" })
public class ProductController implements ServletContextAware {

	private ServletContext servletContext;

	@RequestMapping(method = RequestMethod.GET)
	public String index() {
		return "product/index";
	}

	@RequestMapping(value = "process", method = RequestMethod.POST)
	public String process(@RequestParam("file") MultipartFile file) throws Exception {
		String fileName = uploadExcelFile(file);
		System.out.println("File Name: " + fileName);
		String excelPath = servletContext.getRealPath("/resources/excels/" + fileName);
		System.out.println("Excel Path: " + excelPath);
		ExcelHelper excelHelper = new ExcelHelper(excelPath);
		List<Product> products = excelHelper.readData(Product.class.getName());
		System.out.println("Product List");
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
		for (Product product : products) {
			System.out.println("Id: " + product.getId());
			System.out.println("Name: " + product.getName());
			System.out.println("Price: " + product.getPrice());
			System.out.println("Quantity: " + product.getQuantity());
			System.out.println("Status: " + product.isStatus());
			System.out.println("Date: " + simpleDateFormat.format(product.getCreationDate()));
			System.out.println("=========================");
		}
		return "product/index";
	}

	private String uploadExcelFile(MultipartFile multipartFile) {
		try {
			byte[] bytes = multipartFile.getBytes();
			Path path = Paths.get(servletContext.getRealPath("/resources/excels/" + multipartFile.getOriginalFilename()));
			Files.write(path, bytes);
			return multipartFile.getOriginalFilename();
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void setServletContext(ServletContext servletContext) {
		this.servletContext = servletContext;
	}

}

Create View

Create new folders with path webapp\WEB-INF\views in src\main. In views folder, create new folder named product. In product folder, create new views as below:

Index View

Create new jsp file named index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1" isELIgnored="false"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Import Excel File in Spring MVC</title>
</head>
<body>

	<h3>Import Excel File</h3>
	<form method="post" enctype="multipart/form-data" action="${pageContext.request.contextPath }/product/process">
		<input type="file" name="file">
		<br>
		<input type="submit" value="Import">
	</form>

</body>
</html>

Structure of Spring MVC Project

Run Application

Select LearnSpringMVCWithRealAppsApplication.java file in com.demo package, right click and select Run As/Spring Boot App menu

Access index method in product controller with following url: http://localhost:9596

Output

Click Import button to import excel data

Output

Product List
Id: p01
Name: Name 1
Price: 100.0
Quantity: 2
Status: true
Date: 06/07/2015
=========================
Id: p02
Name: Name 2
Price: 200.0
Quantity: 3
Status: false
Date: 20/10/2015
=========================
Id: p03
Name: Name 3
Price: 300.0
Quantity: 4
Status: true
Date: 21/11/2015
=========================