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


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

 'ApachePOI'
  compile("org.apache.poi:poi:4.1.2")

  'When creating an xlsx file, the following poi-ooxml required'
  compile("org.apache.poi:poi-ooxml:4.1.2")

}

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

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

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

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


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)

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)