Read xls and xlsx files in Java using apache poi

Introduction

There are many situations where you need to read xls and xlsx files in Java or write to .xlsx files. In this example, we'll show how to read xls and xlsx files in Java data using apache-poi from file with data in the following format:

If you're using Maven, it will be good to add it to your pom.xml file. Here I use version 3.9.

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

How to read xls and xlsx files in Java in practice?

Following code example reads data from file and returns Map where customerId is key and locationId is value. 

package com.test.cloud.tenants.utils;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class DataImporter {

	public Map<String, String> getCustomerLocationIdMap() throws IOException {
		ClassLoader classloader = Thread.currentThread().getContextClassLoader();
		InputStream is = classloader.getResourceAsStream("importdata/customers.xlsx");
		XSSFWorkbook workbook = new XSSFWorkbook(is);
		// pick sheet
		XSSFSheet sheet = workbook.getSheetAt(1);

		Map<String, String> customerLocationIdMap = new HashMap<String, String>();
		// iterate over rows
		for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			String customerId = null;
			String locationId = null;
			// iterate over cells over row
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				Cell cell = row.getCell(j);
				if (j == 0) {
					customerId = cell.getStringCellValue();
				} else if (j == 1) {
					locationId = cell.getStringCellValue();
				}
			}
			customerLocationIdMap.put(customerId, locationId);
		}
		return customerLocationIdMap;
	}

}

Cells types can be different:

• CELL_TYPE_BLANK
• CELL_TYPE_BOOLEAN
• CELL_TYPE_ERROR
• CELL_TYPE_FORMULA
• CELL_TYPE_NUMERIC
• CELL_TYPE_STRING 

In case you're getting the error "cannot get a text value from a numeric cell" or similar, it means that poi recognized the cell as a different type than you expect, and it can be solved by explicitly stating cell type before you read it.

ce.setCellType(Cell.CELL_TYPE_STRING);
customerId = cell.getStringCellValue();

Code could also be found on github.