[JAVA] 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


gradle installation


dependencies {
/*Stable version as of August 2020 4.1.2*/


  'When creating an xlsx file, the following poi-ooxml required'


basic operation

Creating a sheet


//Create XSSF Workbook entity
val workBook = XSSFWorkbook()

//Create an excel sheet
val sheet = workBook.createSheet()
Enter the value in the specified cell


//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
Save sheet


val fileOutputStream = FileOutputStream("test.xlsx")



スクリーンショット 2020-08-15 22.44.33.png

Applied operation

Cell color specification


//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

スクリーンショット 2020-08-16 16.25.52.png

Font settings


//Create a font instance
val font = workBook.createFont()
//Set font size
//Set character type
font.fontName = "MS Gothic"

val style = workBook.createCellStyle()
//Add font information to style

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


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(

//Enable condition
sheetCF.addConditionalFormatting(regions, rule1)

Set formula in cell


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

Recommended Posts

Apache POI Excel in Kotlin
[Apache POI] Corresponding Excel version
Excel operation using Apache POI
Manipulate Excel with Apache POI
Excel output using Apache POI!
Replace text in Excel file autoshapes with Apache POI
HMAC in Kotlin
[Java] Handle Excel files with Apache POI
[Java] Creating an Excel file using Apache POI
Big Decimal in Kotlin
Apache POI Addictive Point List
Save Java PDF in Excel
Importing Excel data in Java 2
[Java] Get KClass in Java [Kotlin]
apache POI personal notes crossfish21
What's new in Apache Camel 2.19.0
Importing Excel data in Java 3
Implementation of HashMap in kotlin
Screen transition using Intent in Kotlin
Apache Camel in the cloud-native era
common export to excel using poi
Create an excel file with poi
Error handling in gRPC (Kotlin version)
Imitate Java's functional interface in Kotlin
How to get Excel sheet name list in Java (POI vs SAX)