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

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

}

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

}

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

}

– 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)

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

1 thought on “Kotlin – How to read/write Excel file with Apache POI”

Got Something To Say: Cancel reply

I. Dependency

II. Write Data to Excel File

III. Read Data from Excel File

Related Posts

Post Tags

1 thought on “Kotlin – How to read/write Excel file with Apache POI”

Got Something To Say: Cancel reply