Skip to main content

How to read and write Excel files in Kotlin

How to read and write Excel files in Kotlin.

Here's a detailed step-by-step tutorial on how to read and write Excel files in Kotlin.

Introduction​

Working with Excel files is a common task in many applications. Kotlin provides several libraries that can be used to read and write Excel files. In this tutorial, we will explore two popular libraries: Apache POI and JExcelApi.

Prerequisites​

Before we begin, make sure you have the following prerequisites:

  • Kotlin installed on your machine
  • A text editor or an integrated development environment (IDE) to write Kotlin code
  • Apache POI and JExcelApi libraries added to your project

Reading Excel Files​

Let's start by learning how to read Excel files in Kotlin.

Using Apache POI​

Apache POI is a widely used Java library for working with Microsoft Office files. To read an Excel file using Apache POI, follow these steps:

  1. Import the required classes from the Apache POI library:
import org.apache.poi.ss.usermodel.WorkbookFactory
  1. Create a File object representing the Excel file:
val file = File("path/to/excel/file.xlsx")
  1. Open the Excel file and create a Workbook object:
val workbook = WorkbookFactory.create(file)
  1. Get the desired sheet from the workbook:
val sheet = workbook.getSheetAt(0) // Index of the sheet (zero-based)
  1. Iterate over the rows and columns of the sheet to read the data:
for (row in sheet) {
    for (cell in row) {
        val cellValue = cell.toString()
        // Process the cell value as needed
    }
}

Using JExcelApi​

JExcelApi is another popular Java library for working with Excel files. To read an Excel file using JExcelApi, follow these steps:

  1. Import the required classes from the JExcelApi library:
import jxl.Workbook
import jxl.CellType
  1. Create a Workbook object representing the Excel file:
val workbook = Workbook.getWorkbook(File("path/to/excel/file.xls"))
  1. Get the desired sheet from the workbook:
val sheet = workbook.getSheet(0) // Index of the sheet (zero-based)
  1. Iterate over the rows and columns of the sheet to read the data:
for (row in 0 until sheet.rows) {
    for (column in 0 until sheet.columns) {
        val cell = sheet.getCell(column, row)
        val cellValue = when (cell.type) {
            CellType.NUMBER -> cell.contents.toDouble()
            CellType.BOOLEAN -> cell.contents.toBoolean()
            else -> cell.contents
        }
        // Process the cell value as needed
    }
}

Writing Excel Files​

Now, let's learn how to write Excel files in Kotlin.

Using Apache POI​

To write an Excel file using Apache POI, follow these steps:

  1. Import the required classes from the Apache POI library:
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Row
  1. Create a new Workbook object:
val workbook: Workbook = WorkbookFactory.create(true)
  1. Create a new sheet in the workbook:
val sheet: Sheet = workbook.createSheet("Sheet1")
  1. Create rows and cells in the sheet to write the data:
val row: Row = sheet.createRow(0) // Index of the row (zero-based)
val cell = row.createCell(0) // Index of the cell (zero-based)
cell.setCellValue("Hello, World!")
  1. Save the workbook to a file:
val file = File("path/to/output/file.xlsx")
workbook.write(FileOutputStream(file))

Using JExcelApi​

To write an Excel file using JExcelApi, follow these steps:

  1. Import the required classes from the JExcelApi library:
import jxl.Workbook
import jxl.write.Label
import jxl.write.WritableWorkbook
  1. Create a new WritableWorkbook object:
val workbook: WritableWorkbook = Workbook.createWorkbook(File("path/to/output/file.xls"))
  1. Create a new sheet in the workbook:
val sheet = workbook.createSheet("Sheet1", 0) // Index of the sheet (zero-based)
  1. Create labels and add them to the sheet to write the data:
val label = Label(0, 0, "Hello, World!") // Column, Row, Text
sheet.addCell(label)
  1. Save the workbook:
workbook.write()
workbook.close()

Conclusion​

In this tutorial, we have learned how to read and write Excel files in Kotlin using two popular libraries: Apache POI and JExcelApi. You can now apply this knowledge to handle Excel files in your Kotlin applications.