In this tutorial, we’re gonna look at Kotlin examples that read and write Excel file using Apache POI.
I. Dependency
|
1 2 3 4 5 6 7 8 9 10 11 |
<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> |
II. Write Data to Excel File
– Simple POJO Customer (id, name, address, age):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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:
III. Read Data from Excel File
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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:
|
1 2 3 4 5 6 |
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) |