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

We have also seen Generic way of Excel file writing using Apache POI and Generic way of writing data to multiple sheets in excel using apache poi

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:

Instantiate object from a class which contains private constructor

Read excel file using Python

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

Knowledge of Java

Softwares

Have JDK 1.8 installed and configured
Apache POI dependencies

Preparing and Setting Up Project

Create Gradle based project in Eclipse and update the build script as shown below. We have added Apache POI version 3.15 as a dependency. You may use higher version as well. 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.

allprojects {
	apply plugin: 'java'
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

repositories {
	mavenCentral()   
}
    
dependencies {
	compile("org.apache.poi:poi-ooxml:3.15")
	compile("com.fasterxml.jackson.core:jackson-databind:2.9.5")
}

Creating JSOn configuration file

Create below JSON configuration file 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.

[
	{
		"Orders":
		[
			{
				"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 equivalent 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.

package com.jeejava.poi.excel;

public class ExcelValueConfig {
	private String excelHeader;
	private int excelIndex;
	private String excelColType;
	private String excelValue;
	private String pojoAttribute;

	//getters and setters
}

Creating Enum for constants

Define below enum to declare some constant enum to avoid any value altering.

package com.jeejava.poi.excel;

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

Create class for reading excel file

This below class reads excel file in a generic way. We read the JSON configuration file in method getExcelHeaderConfigSections() and we map them into our POJO class in method getExcelHeaderSections(). We finally read the excel sheet and by iteratin each row we put the value into ExcelValueConfig[] 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.jeejava.poi.excel;

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.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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;

public final class ExcelReader {
	
	final static SimpleDateFormat dtf = new SimpleDateFormat("dd-MM-yyyy");
	
	private ExcelReader() {
	}
	
	public static Workbook readExcel(final String fullFilePath)
		throws EncryptedDocumentException, InvalidFormatException, IOException {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(new File(fullFilePath));
		} catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
			e.printStackTrace();
		}
		return wb;
	}
	
	public static Map<String, List<ExcelValueConfig[]>> getExcelRowValues(final Sheet sheet) {
		Map<String, List<ExcelValueConfig[]>> excelMap = new HashMap<>();
		Map<String, ExcelValueConfig[]> excelSectionHeaders = getExcelHeaderSections();
		int totalRows = sheet.getLastRowNum();
		excelSectionHeaders.forEach((section, excelValueConfigs) -> {
			List<ExcelValueConfig[]> excelValueConfigList = new ArrayList<>();
			for (int i = 2; i <= totalRows; i++) {
				Row row = sheet.getRow(i);
				ExcelValueConfig[] excelValueConfigArr = new ExcelValueConfig[excelValueConfigs.length];
				int k = 0;
				for (ExcelValueConfig ehc : excelValueConfigs) {
					int cellIndex = ehc.getExcelIndex();
					String cellType = ehc.getExcelColType();
					Cell cell = row.getCell(cellIndex);
					ExcelValueConfig config = new ExcelValueConfig();
					config.setExcelColType(ehc.getExcelColType());
					config.setExcelHeader(ehc.getExcelHeader());
					config.setExcelIndex(ehc.getExcelIndex());
					config.setPojoAttribute(ehc.getPojoAttribute());
					if (DataTypeEnum.STRING.getValue().equalsIgnoreCase(cellType)) {
						config.setExcelValue(cell.getStringCellValue());
					} else if (DataTypeEnum.DOUBLE.getValue().equalsIgnoreCase(cellType)
							|| DataTypeEnum.INTEGER.getValue().equalsIgnoreCase(cellType)) {
						config.setExcelValue(String.valueOf(cell.getNumericCellValue()));
					} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
						config.setExcelValue(String.valueOf(dtf.format(cell.getDateCellValue())));
					}
					excelValueConfigArr[k++] = config;
				}
				excelValueConfigList.add(excelValueConfigArr);
			}
			excelMap.put(section, excelValueConfigList);
		});
		return excelMap;
	}
	
	private static Map<String, ExcelValueConfig[]> getExcelHeaderSections() {
		List<Map<String, List<ExcelValueConfig>>> jsonConfigMap = getExcelHeaderConfigSections();
		Map<String, ExcelValueConfig[]> jsonMap = new HashMap<>();
		jsonConfigMap.forEach(jps -> {
			jps.forEach((section, values) -> {
				ExcelValueConfig[] excelValueConfigs = new ExcelValueConfig[values.size()];
				jsonMap.put(section, values.toArray(excelValueConfigs));
			});
		});
		return jsonMap;
	}
	
	private static List<Map<String, List<ExcelValueConfig>>> getExcelHeaderConfigSections() {
		List<Map<String, List<ExcelValueConfig>>> jsonMap = null;
		ObjectMapper objectMapper = new ObjectMapper();
		try {
			String jsonConfig = new String(
											Files.readAllBytes(Paths.get(ClassLoader.getSystemResource("excelConfig.json").toURI())));
			jsonMap = objectMapper.readValue(jsonConfig,
				new TypeReference<List<HashMap<String, List<ExcelValueConfig>>>>() {
				});
		} catch (IOException | URISyntaxException e) {
			e.printStackTrace();
		}
		return jsonMap;
	}
}

Creating POJO classes

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

package com.jeejava.poi.excel;

import java.time.LocalDate;

public class Orders {
	
	private LocalDate orderDate;
	private String region;
	private String rep;
	private String item;
	private int units;
	private double unitCost;
	private double total;
	
	//getters and setters
}

 

package com.jeejava.poi.excel;

import java.time.LocalDate;

public class Profit {
	private LocalDate date;
	private double profit;
	
	//getters and setters
}

Creating excel 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. Here also 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 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.jeejava.poi.excel;

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;

public class ExcelPojoMapper {

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

	public static <T> List<T> getPojos(List<ExcelValueConfig[]> excelValueConfigs, Class<T> clazz) {
		List<T> list = new ArrayList<>();
		excelValueConfigs.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 (DataTypeEnum.STRING.getValue().equalsIgnoreCase(evc[i].getExcelColType())) {
								field.set(t, evc[i].getExcelValue());
							} else if (DataTypeEnum.DOUBLE.getValue().equalsIgnoreCase(evc[i].getExcelColType())) {
								field.set(t, Double.valueOf(evc[i].getExcelValue()));
							} else if (DataTypeEnum.INTEGER.getValue().equalsIgnoreCase(evc[i].getExcelColType())) {
								field.set(t, Double.valueOf(evc[i].getExcelValue()).intValue());
							} else if (DataTypeEnum.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;
	}
}

Creating enum for section name. This will help us to retrieve object from map.

package com.jeejava.poi.excel;

public enum ExcelSectionEnum {
	ORDERS("Orders"), //
	PROFIT("Profit");

	final String typeValue;

	private ExcelSectionEnum(final String typeValue) {
		this.typeValue = typeValue;
	}
	
	public String getName() {
		return name();
	}
	
	public String getValue() {
		return typeValue;
	}
	
	@Override
	public String toString() {
		return name();
	}
}

Creating main class for testing

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.jeejava.poi.excel;

import java.io.IOException;
import java.util.List;
import java.util.Map;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class TestApplication {
	public static void main(String[] args) throws InvalidFormatException {
		try {
			Workbook workbook = ExcelReader.readExcel("C:/Users/workspace/OrderData.xlsx");
			Sheet sheet = workbook.getSheetAt(0);
			Map<String, List<ExcelValueConfig[]>> excelRowValuesMap = ExcelReader.getExcelRowValues(sheet);
			excelRowValuesMap.forEach((section, rows) -> {
				// System.out.println(section);
				// System.out.println("==============");
				boolean headerPrint = true;
				for (ExcelValueConfig[] 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<Orders> orders = ExcelPojoMapper.getPojos(excelRowValuesMap.get(ExcelSectionEnum.ORDERS.getValue()),
									Orders.class);
			List<Profit> profits = ExcelPojoMapper.getPojos(excelRowValuesMap.get(ExcelSectionEnum.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.

Recommended reading Generic way of Excel file writing using Apache POI and Generic way of writing data to multiple sheets in excel using apache poi

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit | Email Me

Leave a Reply

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