Kotlin – How to read/write Excel file with Apache POI

In this tutorial, we’re gonna look at Kotlin examples that read and write Excel file using Apache POI.

I. Dependency

<dependency>
	<groupId>org.jetbrains.kotlin</groupId>
	<artifactId>kotlin-stdlib</artifactId>
	<version>1.2.21</version>
</dependency>

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

II. Write Data to Excel File

– Simple POJO Customer (id, name, address, age):

package com.javasampleapproach.kotlin.apachecsv

class Customer {
	var id: String? = null
	var name: String? = null
	var address: String? = null
	var age: Int = 0

	constructor() {}
	constructor(id: String?, name: String?, address: String?, age: Int) {
		this.id = id
		this.name = name
		this.address = address
		this.age = age
	}

	override fun toString(): String {
		return "Customer [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]"
	}
}

– Write to Excel file:

package com.javasampleapproach.kotlin.apachecsv

import java.io.FileOutputStream
import java.io.IOException
import java.util.Arrays
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellStyle
import org.apache.poi.ss.usermodel.CreationHelper
import org.apache.poi.ss.usermodel.Font
import org.apache.poi.ss.usermodel.IndexedColors
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.xssf.usermodel.XSSFWorkbook

private val COLUMNs = arrayOf("Id", "Name", "Address", "Age")
private val customers = Arrays.asList(
		Customer("1", "Jack Smith", "Massachusetts", 23),
		Customer("2", "Adam Johnson", "New York", 27),
		Customer("3", "Katherin Carter", "Washington DC", 26),
		Customer("4", "Jack London", "Nevada", 33),
		Customer("5", "Jason Bourne", "California", 36))

@Throws(IOException::class)
fun main(args: Array?) {

	val workbook = XSSFWorkbook()
	val createHelper = workbook.getCreationHelper()

	val sheet = workbook.createSheet("Customers")

	val headerFont = workbook.createFont()
	headerFont.setBold(true)
	headerFont.setColor(IndexedColors.BLUE.getIndex())

	val headerCellStyle = workbook.createCellStyle()
	headerCellStyle.setFont(headerFont)

	// Row for Header
	val headerRow = sheet.createRow(0)

	// Header
	for (col in COLUMNs.indices) {
		val cell = headerRow.createCell(col)
		cell.setCellValue(COLUMNs[col])
		cell.setCellStyle(headerCellStyle)
	}
	
	// CellStyle for Age
	val ageCellStyle = workbook.createCellStyle()
	ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"))
	
	var rowIdx = 1
	for (customer in customers) {
		val row = sheet.createRow(rowIdx++)
		row.createCell(0).setCellValue(customer.id)
		row.createCell(1).setCellValue(customer.name)
		row.createCell(2).setCellValue(customer.address)
		val ageCell = row.createCell(3)
		ageCell.setCellValue(customer.age.toDouble())
		ageCell.setCellStyle(ageCellStyle)
	}
	
	val fileOut = FileOutputStream("customers.xlsx")
	workbook.write(fileOut)
	fileOut.close()
	workbook.close()
}

– Check results in customers.xlsx:

III. Read Data from Excel File

package com.javasampleapproach.kotlin.apachecsv

import java.io.File
import java.io.FileInputStream
import java.io.IOException
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellType
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.xssf.usermodel.XSSFWorkbook

@Throws(IOException::class)
fun main(args: Array?) {

	val excelFile = FileInputStream(File("customers.xlsx"))
	val workbook = XSSFWorkbook(excelFile)

	val sheet = workbook.getSheet("Customers")
	val rows = sheet.iterator()
	while (rows.hasNext()) {
		val currentRow = rows.next()
		val cellsInRow = currentRow.iterator()
		while (cellsInRow.hasNext()) {
			val currentCell = cellsInRow.next()
			if (currentCell.getCellTypeEnum() === CellType.STRING) {
				print(currentCell.getStringCellValue() + " | ")
			} else if (currentCell.getCellTypeEnum() === CellType.NUMERIC) {
				print(currentCell.getNumericCellValue().toString() + "(numeric)")
			}
		}

		println()
	}

	workbook.close()
	excelFile.close()
}

– Check Result in Console:

Id | Name | Address | Age | 
1 | Jack Smith | Massachusetts | 23.0(numeric)
2 | Adam Johnson | New York | 27.0(numeric)
3 | Katherin Carter | Washington DC | 26.0(numeric)
4 | Jack London | Nevada | 33.0(numeric)
5 | Jason Bourne | California | 36.0(numeric)

Last updated on May 7, 2021.