Working with Excel in Kotlin: Apache POI and Apache POI for Kotlin Libraries

Working with Excel in Kotlin: Apache POI and Kotlin Wrapper Libraries

Kotlin, being a modern programming language for the JVM, provides excellent capabilities for automating work with office documents. In this article, we will look at how to efficiently work with Excel files (XLS and XLSX) in Kotlin using the popular Apache POI libraries and its Kotlin-oriented wrapper Apache POI for Kotlin.

Why Kotlin for working with Excel?

Kotlin offers a more concise and safer syntax compared to Java, which is especially useful when working with bulky Excel APIs. Using Kotlin, you can:

• Reduce the amount of boilerplate code
• Use safe types and null-safety
• Apply extension functions to improve readability
• Easily integrate with existing Java libraries

Main Library: Apache POI

Apache POI is the de facto standard for working with Microsoft Office documents in the JVM ecosystem. It supports:

• Reading and writing XLS (HSSF) and XLSX (XSSF)
• Working with formulas, styles, merged cells
• Creating charts and pivot tables
• Processing large files via SXSSF (streaming)

To add it in Gradle (Kotlin DSL), add to build.gradle.kts:

dependencies {    implementation("org.apache.poi:poi:5.3.0")    implementation("org.apache.poi:poi-ooxml:5.3.0")}

Wrapper Library: Apache POI for Kotlin

There are several Kotlin wrappers that simplify working with POI. One popular one is kotlin-poi (com.github.doyaaaaaken:kotlinpoi). It adds extension functions and a DSL style for creating Excel files.

Adding it:

repositories {    mavenCentral()}

dependencies {    implementation("com.github.doyaaaaaken:kotlinpoi:1.0.0")}

Example 1: Creating a simple XLSX file (pure POI)

Let's create a workbook with one sheet and fill it with data:

import org.apache.poi.xssf.usermodel.XSSFWorkbookimport java.io.FileOutputStream

fun createSimpleExcel() {    val workbook = XSSFWorkbook()    val sheet = workbook.createSheet("Data")        // Create headers    val headerRow = sheet.createRow(0)    headerRow.createCell(0).setCellValue("Name")    headerRow.createCell(1).setCellValue("Age")    headerRow.createCell(2).setCellValue("City")        // Fill data    val data = listOf(        listOf("Anna", 28, "Moscow"),        listOf("Ivan", 35, "Saint Petersburg"),        listOf("Maria", 22, "Kazan")    )        data.forEachIndexed { index, rowData ->        val row = sheet.createRow(index + 1)        rowData.forEachIndexed { cellIndex, value ->            when (value) {                is String -> row.createCell(cellIndex).setCellValue(value)                is Int -> row.createCell(cellIndex).setCellValue(value.toDouble())                else -> row.createCell(cellIndex).setCellValue(value.toString())            }        }    }        // Auto-size columns    for (i in 0..2) {        sheet.autoSizeColumn(i)    }        // Save file    FileOutputStream("output.xlsx").use { fileOut ->        workbook.write(fileOut)    }    workbook.close()    println("File created: output.xlsx")}

Example 2: Reading an Excel file (pure POI)

import org.apache.poi.ss.usermodel.WorkbookFactoryimport java.io.FileInputStream

fun readExcel(filePath: String) {    val file = FileInputStream(filePath)    val workbook = WorkbookFactory.create(file)    val sheet = workbook.getSheetAt(0)        println("Reading sheet: ${sheet.sheetName}")    println("Total rows: ${sheet.physicalNumberOfRows}")        for (row in sheet) {        val r