Generic way of reading Excel file using Apache POI

Introduction

Generic way of reading Excel file using Apache POI is a way of reading excel file in a dynamic configurable way to read an excel file where in future if you add more columns or remove columns from excel then you don’t need to almost touch the Java source code but you change the configurations that make your life easier.

Here we will see how we can define configurations in JSON file and read the excel file using Apache POI to establish mapping between JSON and Java POJO.

Related Posts:

Obviously whenever you want to read or write to excel file you need to use Java Reflection API though it is not recommended to use Java Reflection API but in rare situations you may need to use Reflection.

You may also like to read:

The Apache POI is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). You can read and write MS Excel files using Java with the help of Apache POI library. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is the Java Excel solution (for Excel 97-2008).

Prerequisites

Java 8 or 12, Eclipse 4.12, Apache POI 4.1.1, Jackson API 2.10.1

Create Project

Create Gradle based project in Eclipse and update the build script as shown below.

We have added Apache POI version 4.1.1 as a dependency. We have also used jackson dependency to convert JSON data type to Java POJO or vice-versa.

Once you create the blank project with below build script please build the Gradle project in order to check whether the project downloads the required libraries and builds successfully.

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.1'
    implementation('com.fasterxml.jackson.core:jackson-databind:2.10.1')
}

Creating JSOn configuration file

Create JSON configuration file called excel.json under classpath directory src/main/resources, where we define the mapping between Excel file column name, column index, column type, column value (initially null) and POJO class’s attribute with which this column will be mapped or excel column’s value will be set to POJO’s attribute.

We have defined the JSON file based on an Excel file’s section-wise.

[
	{
		"Order":
		[
			{
				"excelHeader": "Order Date",
				"excelIndex": 0,
				"excelColType": "Date",
				"excelValue": null,
				"pojoAttribute": "orderDate"
			},
			{
				"excelHeader": "Region",
				"excelIndex": 1,
				"excelColType": "String",
				"excelValue": null,
				"pojoAttribute": "region"
			},
			{
				"excelHeader": "Rep",
				"excelIndex": 2,
				"excelColType": "String",
				"excelValue": null,
				"pojoAttribute": "rep"
			},
			{
				"excelHeader": "Item",
				"excelIndex": 3,
				"excelColType": "String",
				"excelValue": null,
				"pojoAttribute": "item"
			},
			{
				"excelHeader": "Units",
				"excelIndex": 4,
				"excelColType": "Integer",
				"excelValue": null,
				"pojoAttribute": "units"
			},
			{
				"excelHeader": "Unit Cost",
				"excelIndex": 5,
				"excelColType": "Double",
				"excelValue": null,
				"pojoAttribute": "unitCost"
			},
			{
				"excelHeader": "Total",
				"excelIndex": 5,
				"excelColType": "Double",
				"excelValue": null,
				"pojoAttribute": "total"
			}
		]
	},
	{
		"Profit":
		[
			{
				"excelHeader": "Date",
				"excelIndex": 8,
				"excelColType": "Date",
				"excelValue": null,
				"pojoAttribute": "date"
			},
			{
				"excelHeader": "Profit",
				"excelIndex": 9,
				"excelColType": "Double",
				"excelValue": null,
				"pojoAttribute": "profit"
			}
		]
	}
]

Creating POJO configuration

In the above we have defined JSON configuration file for mapping excel data to Java. Here we will create a Java equivalent class that will hold excel file’s column information.

Notice we have exactly the same name as in JSON file configuration. If you have different name between JSOn file and Java class for the same property then you use annotation @JsonProperty("<property name of json file>") above the POJO attribute to indicate with which you want to map.

public class ExcelField {

	private String excelHeader;
	private int excelIndex;
	private String excelColType;
	private String excelValue;
	private String pojoAttribute;

        //getters and setters
}

Creating Enum

Define below enum to declare some constant enum to represent the types of columns or fields in excel file.

package com.roytuts.generic.way.to.read.excel.apache.poi.enums;

public enum FieldType {

	DOUBLE("Double"), //
	INTEGER("Integer"), //
	STRING("String"), //
	DATE("Date");
	
	final String typeValue;
	
	private FieldType(final String typeValue) {
		this.typeValue = typeValue;
	}
	
	public String getName() {
		return name();
	}
	
	public String getValue() {
		return typeValue;
	}
	
	@Override
	public String toString() {
		return name();
	}
	
}

Reading Excel

This below class reads excel file in a generic way. We read the JSON configuration file in method getExcelHeaderFieldSections() and we map them into our POJO class in method getExcelHeaderSections().

We finally read the excel sheet and by iterating each row we put the value into ExcelField[] array and finally return the result.

We start iterating at row number 2 because first two rows contain section name and column headers in excel file.

This class shows Generic way of reading Excel file using Apache POI.

package com.roytuts.generic.way.to.read.excel.apache.poi.reader;

import java.io.File;
import java.io.IOException;
import java.net.URISyntaxException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.roytuts.generic.way.to.read.excel.apache.poi.enums.FieldType;
import com.roytuts.generic.way.to.read.excel.apache.poi.model.ExcelField;

public final class ExcelFileReader {

	final static SimpleDateFormat dtf = new SimpleDateFormat("dd-MM-yyyy");

	private ExcelFileReader() {
	}

	public static Workbook readExcel(final String fullFilePath)
			throws EncryptedDocumentException, InvalidFormatException, IOException {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(new File(fullFilePath));
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}
		return wb;
	}

	public static Map<String, List<ExcelField[]>> getExcelRowValues(final Sheet sheet) {
		Map<String, List<ExcelField[]>> excelMap = new HashMap<>();
		Map<String, ExcelField[]> excelSectionHeaders = getExcelHeaderSections();
		int totalRows = sheet.getLastRowNum();
		excelSectionHeaders.forEach((section, excelFields) -> {
			List<ExcelField[]> excelFieldList = new ArrayList<>();
			for (int i = 2; i <= totalRows; i++) {
				Row row = sheet.getRow(i);
				ExcelField[] excelFieldArr = new ExcelField[excelFields.length];
				int k = 0;
				for (ExcelField ehc : excelFields) {
					int cellIndex = ehc.getExcelIndex();
					String cellType = ehc.getExcelColType();
					Cell cell = row.getCell(cellIndex);
					ExcelField excelField = new ExcelField();
					excelField.setExcelColType(ehc.getExcelColType());
					excelField.setExcelHeader(ehc.getExcelHeader());
					excelField.setExcelIndex(ehc.getExcelIndex());
					excelField.setPojoAttribute(ehc.getPojoAttribute());
					if (FieldType.STRING.getValue().equalsIgnoreCase(cellType)) {
						excelField.setExcelValue(cell.getStringCellValue());
					} else if (FieldType.DOUBLE.getValue().equalsIgnoreCase(cellType)
							|| FieldType.INTEGER.getValue().equalsIgnoreCase(cellType)) {
						excelField.setExcelValue(String.valueOf(cell.getNumericCellValue()));
					} else if (DateUtil.isCellDateFormatted(cell)) {
						excelField.setExcelValue(String.valueOf(dtf.format(cell.getDateCellValue())));
					}
					excelFieldArr[k++] = excelField;
				}
				excelFieldList.add(excelFieldArr);
			}
			excelMap.put(section, excelFieldList);
		});
		return excelMap;
	}

	private static Map<String, ExcelField[]> getExcelHeaderSections() {
		List<Map<String, List<ExcelField>>> jsonConfigMap = getExcelHeaderFieldSections();
		Map<String, ExcelField[]> jsonMap = new HashMap<>();
		jsonConfigMap.forEach(jps -> {
			jps.forEach((section, values) -> {
				ExcelField[] excelFields = new ExcelField[values.size()];
				jsonMap.put(section, values.toArray(excelFields));
			});
		});
		return jsonMap;
	}

	private static List<Map<String, List<ExcelField>>> getExcelHeaderFieldSections() {
		List<Map<String, List<ExcelField>>> jsonMap = null;
		ObjectMapper objectMapper = new ObjectMapper();
		try {
			String jsonConfig = new String(
					Files.readAllBytes(Paths.get(ClassLoader.getSystemResource("excel.json").toURI())));

			jsonMap = objectMapper.readValue(jsonConfig, new TypeReference<List<Map<String, List<ExcelField>>>>() {
			});
		} catch (IOException | URISyntaxException e) {
			e.printStackTrace();
		}
		return jsonMap;
	}

}

Creating POJO Classes

We have to create below POJO files which will hold actual excel file’s data. These POJO class will represent excel section’s data. The Order class will hold all data from Order section and Profit class will hold all data from Profit section.

Order

public class Order {

	private LocalDate orderDate;
	private String region;
	private String rep;
	private String item;
	private int units;
	private double unitCost;
	private double total;

        //getters and setters
}

Profit

public class Profit {

	private LocalDate date;
	private double profit;

        //getters and setters
}

Excel to POJO Mapper

This class will map excel value to our POJO classes. We use here Java’s Reflection API to set excel value value to Java’s attribute. We are setting value to class’s attribute in Generic way using Reflection API. So you don’t need to know the name of the attribute of Java class. So we first saw Generic way of reading Excel file using Apache POI and then we are writing those excel column values into Java class attributes.

Here you can also notice how to return a list for any kind of object. So it also shows generic way of returning list of generic objects.

package com.roytuts.generic.way.to.read.excel.apache.poi.mapper;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

import com.roytuts.generic.way.to.read.excel.apache.poi.enums.FieldType;
import com.roytuts.generic.way.to.read.excel.apache.poi.model.ExcelField;

public final class ExcelFieldMapper {

	final static DateTimeFormatter dtf = DateTimeFormatter.ofPattern("dd-MM-yyyy");

	public static <T> List<T> getPojos(List<ExcelField[]> excelFields, Class<T> clazz) {

		List<T> list = new ArrayList<>();
		excelFields.forEach(evc -> {

			T t = null;

			try {
				t = clazz.getConstructor().newInstance();
			} catch (InstantiationException | IllegalAccessException | IllegalArgumentException
					| InvocationTargetException | NoSuchMethodException | SecurityException e1) {
				e1.printStackTrace();
			}

			Class<? extends Object> classz = t.getClass();

			for (int i = 0; i < evc.length; i++) {

				for (Field field : classz.getDeclaredFields()) {
					field.setAccessible(true);

					if (evc[i].getPojoAttribute().equalsIgnoreCase(field.getName())) {

						try {
							if (FieldType.STRING.getValue().equalsIgnoreCase(evc[i].getExcelColType())) {
								field.set(t, evc[i].getExcelValue());
							} else if (FieldType.DOUBLE.getValue().equalsIgnoreCase(evc[i].getExcelColType())) {
								field.set(t, Double.valueOf(evc[i].getExcelValue()));
							} else if (FieldType.INTEGER.getValue().equalsIgnoreCase(evc[i].getExcelColType())) {
								field.set(t, Double.valueOf(evc[i].getExcelValue()).intValue());
							} else if (FieldType.DATE.getValue().equalsIgnoreCase(evc[i].getExcelColType())) {
								field.set(t, LocalDate.parse(evc[i].getExcelValue(), dtf));
							}
						} catch (IllegalArgumentException | IllegalAccessException e) {
							e.printStackTrace();
						}

						break;
					}
				}
			}

			list.add(t);
		});

		return list;
	}

}

Excel Sheet Section

Creating enum for excel sheet’s section name. This will help us to retrieve object from map.

package com.roytuts.generic.way.to.read.excel.apache.poi.enums;

public enum ExcelSection {

	ORDERS("Order"), //
	PROFIT("Profit");

	final String typeValue;

	private ExcelSection(final String typeValue) {
		this.typeValue = typeValue;
	}

	public String getName() {
		return name();
	}

	public String getValue() {
		return typeValue;
	}

	@Override
	public String toString() {
		return name();
	}

}

Create Main Class

Create below main class to test the example on Generic way of reading Excel file using Apache POI.

We read first sample excel file which is under a directory. Then we iterate through the excel file data and simply print those data into console.

package com.roytuts.generic.way.to.read.excel.apache.poi;

import java.io.IOException;
import java.util.List;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.roytuts.generic.way.to.read.excel.apache.poi.enums.ExcelSection;
import com.roytuts.generic.way.to.read.excel.apache.poi.mapper.ExcelFieldMapper;
import com.roytuts.generic.way.to.read.excel.apache.poi.model.ExcelField;
import com.roytuts.generic.way.to.read.excel.apache.poi.model.Order;
import com.roytuts.generic.way.to.read.excel.apache.poi.model.Profit;
import com.roytuts.generic.way.to.read.excel.apache.poi.reader.ExcelFileReader;

public class ReadExcelGenericWay {

	public static void main(String[] args) throws InvalidFormatException {
		try {
			Workbook workbook = ExcelFileReader.readExcel("C:/jee_workspace/order-profit.xlsx");
			Sheet sheet = workbook.getSheetAt(0);
			Map<String, List<ExcelField[]>> excelRowValuesMap = ExcelFileReader.getExcelRowValues(sheet);
			excelRowValuesMap.forEach((section, rows) -> {
				// System.out.println(section);
				// System.out.println("==============");
				boolean headerPrint = true;
				for (ExcelField[] evc : rows) {
					if (headerPrint) {
						for (int j = 0; j < evc.length; j++) {
							// System.out.print(evc[j].getExcelHeader() + "t");
						}
						// System.out.println();
						// System.out.println(
						// "------------------------------------------------------------------------------------");
						// System.out.println();
						headerPrint = false;
					}
					for (int j = 0; j < evc.length; j++) {
						// System.out.print(evc[j].getExcelValue() + "t");
					}
					// System.out.println();
				}
				// System.out.println();
			});
			List<Order> orders = ExcelFieldMapper.getPojos(excelRowValuesMap.get(ExcelSection.ORDERS.getValue()),
					Order.class);
			
			List<Profit> profits = ExcelFieldMapper.getPojos(excelRowValuesMap.get(ExcelSection.PROFIT.getValue()),
					Profit.class);
			
			orders.forEach(o -> {
				System.out.println(o.getItem());
			});
			
			profits.forEach(p -> {
				System.out.println(p.getProfit());
				System.out.println(p.getDate());
			});
		} catch (EncryptedDocumentException | IOException e) {
			e.printStackTrace();
		}
	}

}

Testing the Application

Here is the excel file snapshot that you need to create in order to test it.

Generic way of reading Excel file using Apache POI

Here is the output of the application when main class is run.

Generic way of reading Excel file using Apache POI

That’s all. Hope you got idea on Generic way of reading Excel file using Apache POI.

Source Code

Download

Thanks for reading.

Leave a Reply

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