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

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)