Working with Excel in Kotlin: Apache POI and Kotlin…

Working with Excel in Kotlin: Library Overview and Practical Examples

Kotlin, as a modern JVM language, has excellent compatibility with the Java ecosystem. For working with Excel files (.xls and .xlsx), the most powerful and widespread solution is the Apache POI library. It allows you to create, read, and modify Microsoft Office documents. In this article, we will look at how to use Apache POI in Kotlin, and also get acquainted with specialized Kotlin wrappers that simplify the code.

1. Adding Apache POI to a Kotlin Project

Add dependencies to the build.gradle.kts file (for Gradle Kotlin DSL):

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

For Maven, use the corresponding poi and poi-ooxml artifacts.

Important: poi-ooxml is required for working with the .xlsx format (Office Open XML), while poi is for the older .xls format (HSSF).

2. Creating and Writing an Excel File (XLSX) in Kotlin

Let's create a simple workbook with one sheet and write data.

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

fun createExcel() {    val workbook = XSSFWorkbook()    val sheet = workbook.createSheet("Employees")

    // Headers    val headerRow = sheet.createRow(0)    val headers = listOf("ID", "Name", "Position", "Salary")    headers.forEachIndexed { index, header ->        headerRow.createCell(index).setCellValue(header)    }

    // Data    val data = listOf(        listOf(1, "Ivan Petrov", "Developer", 120000.0),        listOf(2, "Maria Smirnova", "Analyst", 95000.0),        listOf(3, "Alexey Kuznetsov", "Tester", 85000.0)    )

    data.forEachIndexed { rowIndex, rowData ->        val row = sheet.createRow(rowIndex + 1)        rowData.forEachIndexed { colIndex, value ->            when (value) {                is Int -> row.createCell(colIndex).setCellValue(value.toDouble())                is Double -> row.createCell(colIndex).setCellValue(value)                is String -> row.createCell(colIndex).setCellValue(value)            }        }    }

    // Auto-size columns    for (i in headers.indices) {        sheet.autoSizeColumn(i)    }

    // Saving    FileOutputStream("employees.xlsx").use { stream ->        workbook.write(stream)    }    println("File created: employees.xlsx")}

Key points:

XSSFWorkbook — class for working with .xlsx (XSSF). For .xls, use HSSFWorkbook.
Rows and cells are indexed starting from 0.
autoSizeColumn automatically adjusts column width to fit the content.
In Kotlin, it's convenient to use use for automatic stream closing.

3. Reading an Excel File in Kotlin

Let's read the previously created file and output the data to the console.

import org.apache.poi.ss.usermodel.*import org.apache.poi.xssf.usermodel.XSSFWorkbookimport java.io.FileInputStream

fun readExcel() {    FileInputStream("employees.xlsx").use { stream ->        val workbook = XSSFWorkbook(stream)        val sheet = workbook.getSheetAt(0)

        for (row in sheet) {            for (cell in row) {                when (cell.cellType) {                    CellType.STRING -> print("${cell.stringCellValue}\\t")                    CellType.NUMERIC -> print("${cell.numericCellValue}\\t")                    CellType.BOOLEAN -> print("${cell.booleanCellValue}\\t")                    else -> print("?\\t")                }            }            println()        }    }}

Note the pro