Apache poi excel kotlin

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.

I. Dependency

 org.jetbrains.kotlin kotlin-stdlib 1.2.21  org.apache.poi poi-ooxml 3.17 

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

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("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?) < 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

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

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)

Источник

Read & Write Excel in Kotlin | Apache POI

In every Development environment, data is a mandatory part of their development, we put all your data in your program files. But if any change in data results in the editing of the program file, which makes us recompile the code and retest the compiled code.

If data Changes every day, are we going to edit the program file every day?
What happens if the compilation fails ?

Development should happen in such that data do not have any effect on the program files. Placing data outside the program is the only way to do it, like placing the data on excel files, property files, config file, Json Files, Xml files.

Apache POI :

Apache POI helps Kotlin/Java related technologies to read and write Excel files on different platforms, Using apache poi we can do read and write operation of both xls and xlsx file formats. Apache poi is an open-source tool developed by Apache.

Apache POI will be helpful to modify the large content of data. Below is the step by step process to download Apache poi jar files.

Follow below steps to download Apache Poi:
1. Open https://poi.apache.org/download.html
2. Click Downloads section on right side

3. Click on the poi-bin-#####.zip link under binary distribution section

4. Click on the mirror link, apache would be suggesting the nearest location for your mirror link

5. Your poi files will be started to download

6. Extract the downloaded zip file

7. The extracted folder should contain below files.

Steps for adding Apache POI jars in IntelliJ IDEA:

• Click File from the toolbar
• Project Structure (CTRL + SHIFT + ALT + S on Windows/Linux)
• Select Modules at the left panel
• Dependencies tab
• ‘+’ → JARs or directories and add all jar files to IntelliJ

Apache POI famous Terms

Apache POI excel library revolves around four key interfaces which actually represent the items in the excel file.

• Workbook: A workbook represents the excel file
• Sheet: A workbook may contain many sheets. We can access the sheets either with a name or with index.
• Row: As the name suggests, It represents a row in the sheet.
• Cell: A cell represents a column in the sheet.

Write Excel in Kotlin

For writing excel file in kotlin the steps are simple, we would be writing an xlsx file in this example.

• Create an Object for XSSFWorkbook() , which will create excel file in JVM
• Create Sheet object from workbook object(xlWb) using createSheet() function
• Create a row from the Workbook sheet object(xlWb) using createRow(rowNumber) , pass the which row number you want to create
• Create a Cell inside the row using createCell() function, pass the cell number as a parameter
• Set the value of the cell that you have created using the setCellValue(«value to set») function
• Now we have to move the Excel file created inside JVM into local file system using FileOutputStream

Read Excel in Kotlin

, because sometimes you may have xlsx or xls, so to avoid issues we would be using the Workbookfactory.Create() Get the sheet we want to read using
function, we can get the sheet either using the name or by index, the index starts from 0 Get the row using
from the sheet object and pass the row number you want to read s parameter Get the cell number from the row using the

Retrieving Cell values by CellType

We can retrieve cell value using stringCellValue method but it only works for String values. In actual, day to day activities, we may store more types of data in excel sheets like Number, boolean, strings.

We have different properties to retrieve different types of data in apache poi

To retrieve different data, you may check each cell’s type and then retrieve its value using various type-specific methods.

You should understand that below properties will not extract a type of data from the cell when you store a particular data type in the cell then the total cell is of that type

So these properties will fetch total value present in the cell

• booleanCellValue — To fetch boolean data from the excel
• stringCellValue — To fetch String data from the excel
• dateCellValue — fetches date values from the cell
• numericCellValue — fetches numeric value
• cellFormula — fetches the data from the formula cell.

fun main(args: Array) < val filepath = "./test_file.xlsx" val inputStream = FileInputStream(filepath) //Instantiate Excel workbook using existing file: var xlWb = WorkbookFactory.create(inputStream) //Row index specifies the row in the worksheet (starting at 0): val rowNumber = 0 //Cell index specifies the column within the chosen row (starting at 0): val columnNumber = 0 //Get reference to first sheet: val xlWs = xlWb.getSheetAt(0) var cell = xlWs.getRow(rowNumber).getCell(columnNumber) when (cell.getCellTypeEnum()) < CellType.BOOLEAN ->println("Boolean value found : "+cell.booleanCellValue) CellType.STRING -> println("String value found : "+cell.stringCellValue) CellType.NUMERIC -> if (DateUtil.isCellDateFormatted(cell)) < println("Date value found : "+cell.dateCellValue) >else < println("Numeric value found : "+cell.numericCellValue) >CellType.FORMULA -> println("Formula value found : "+cell.getCellFormula()) else -> print("") > >

Read all values from a specific Column

We can fetch all the data from a specific column in apache poi, here we should not change the column number. The column is specified by the cell in apache POI. We have our column number ready but we have to get the number of rows present in the excel sheet so that we would know how much we have to iterate.

We have all rows now, let’s get the data based on the cell/Column number. In ‘E’ column I have store months, let’s retrieve it. E column would have an index number as 4.

fun main(args: Array) < val filepath = "./test_file.xlsx" val inputStream = FileInputStream(filepath) //Instantiate Excel workbook using existing file: var xlWb = WorkbookFactory.create(inputStream) //Row index specifies the row in the worksheet (starting at 0): val rowNumber = 0 //Cell index specifies the column within the chosen row (starting at 0): val columnNumber = 0 //Get reference to first sheet: val xlWs = xlWb.getSheetAt(0) val xlRows = xlWs.rowIterator() // go row by row to get the values and print them xlRows.forEach < row ->println(row.getCell(4))> >

Using While Loop

I hope you know that the last row number would give us the number of rows present in the excel.

We can get number of rows using the lastRowNumber property from Cell object and we can use the while loop to get all the values

Источник

Apache POI Excel in Kotlin

The current Apache POI Excel official document (https://poi.apache.org/components/spreadsheet/index.html) explains a mixture of old and new methods, and there are many parts that are difficult to understand, so here is a frequently used description. I summarized it. Language kotlin

setup

gradle installation

build.gradle

basic operation

Creating a sheet

sample.kt

 //Create XSSF Workbook entity val workBook = XSSFWorkbook() //Create an excel sheet val sheet = workBook.createSheet() 

Enter the value in the specified cell

sample.kt

 //Specify the cell to enter the value //Specify a column with createRow val row = sheet.createRow(0) //Specify a row with createCell val cell = row.createCell(0) //Fill in the value cell.setCellValue("test") 

Save sheet

sample.kt

 val fileOutputStream = FileOutputStream("test.xlsx") workBook.write(fileOutputStream) fileOutputStream.close() 

result



Applied operation

Cell color specification

sample.kt

 //Create a style instance val style = workBook.createCellStyle() //Set cell to yellow style.fillForegroundColor = (XSSFColor(byteArrayOf(255.toByte(), 255.toByte(), 204.toByte()), null) //Specify fill style.fillPattern = FillPatternType.SOLID_FOREGROUND //Style the cell cell.setCellStyle(style) 

result



Font settings

sample.kt

 //Create a font instance val font = workBook.createFont() //Set font size font.setFontHeightInPoints(16.toShort()) //Set character type font.fontName = "MS Gothic" val style = workBook.createCellStyle() //Add font information to style style.setFont(font) 

Conditional formatting

List of conditions that can be set https://github.com/apache/poi/blob/trunk/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java

sample.kt

 val sheet = workBook.createSheet() //Create an instance for conditional formatting val sheetCF: SheetConditionalFormatting = sheet.sheetConditionalFormatting //Set conditions: In this case, the value is 90%The following val rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "90%") //Set the font to be used under the above conditions(Various elements other than fonts can be set) val font = rule1.createFontFormatting() //Set font to red font.fontColor = XSSFColor(byteArrayOf(255.toByte(), 0.toByte(), 0.toByte()), null) //Set the range of cells for which the above conditions are valid(In the following cases, from cell A1 to cell A5) val range = "A1:A5" //Multiple ranges can be set in the array val regions = arrayOf( CellRangeAddress.valueOf(range) ) //Enable condition sheetCF.addConditionalFormatting(regions, rule1) 

Set formula in cell

sample.kt

 val sheet = workBook.createSheet() val row = sheet.createRow(0) val cell = row.createCell(0) //A1 cell/Create a formula that gives the percentage of C1 cells val formula = "A1/C1" //Set formula in cell cell.cellFormula = formula //Enable formulas sheet.setForceFormulaRecalculation(true) 

Источник