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.

Contents

I. Dependency
II. Write Data to Excel File
III. Read Data from Excel File

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>

<groupId>org.jetbrains.kotlin</groupId>

<artifactId>kotlin-stdlib</artifactId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</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 + "]"
	}
}

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<String>("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<String>?) {

	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()
}

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<String>("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<String>?) {

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:

read-write-excel-file-apache-poi-result

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<String>?) {

	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()
}

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<String>?) {

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)

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)

By grokonez | February 26, 2018.

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

I. Dependency

II. Write Data to Excel File

III. Read Data from Excel File

Post Tags

Got Something To Say: Cancel reply

I. Dependency

II. Write Data to Excel File

III. Read Data from Excel File

Related Posts

Post Tags

Got Something To Say: Cancel reply