使用Kotlin读写Excel

1. Overview

In this tutorial, we’ll show how to use the Apache POI library. We’ll learn how to read an Excel file and how to write content into it.

2. Maven Dependencies

Let’s first have a look at how to handle an Excel file. First of all, Kotlin does not provide out-of-the-box handling of the Excel format. For that purpose, we’ll use the Apache POI libraries. The Apache POI library is a Java API for Microsoft documents.
Firstly, we’ll need to add two dependencies to our project:

 <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${apache.poi.version}</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${apache.poi.version}</version>
</dependency>

The latest versions of poi and poi-ooxml can be downloaded from Maven Central. The Apache POI libraries support both .xls and .xlsx files. The poi dependency supports the old  Excel format. The poi-ooxml supports Office Open XML format. Moreover, both libraries are necessary to handle the xlsx format.

We’ll use classes XSSFWorkbook, XSSFSheet, XSSFRow, and XSSFCell. For the old format, we use the HSSFWorkbook, HSSFSheet, HSSFRow, and HSSFCell classes.

3. Read Excel File With Kotlin

Let’s now create an example of how to read an Excel file:

@Test
fun `when file is read then content is correct`() {
    val inputStream = this::class.java.getResourceAsStream("test_input.xlsx")
    val workbook = WorkbookFactory.create(inputStream)

    val workSheet = workbook.getSheetAt(0)
    assertThat(workSheet.getRow(0).getCell(0).stringCellValue).isEqualTo("TEST VALUE")
}

Firstly, we created an input file in the resources of our application. We read the file from resources, but we can read it from an external location as well. After that, we initialized a workbook, which represents the Excell worksheet. From the workbook, we read the first sheet. Finally, we check the first cell value.

In short, reading an Excel file is straightforward with the Apache POI library.

4. Write to Excel File With Kotlin

Let’s now have a look at how to create an Excel file and save it. For that purpose, we’ll use the Apache POI library as well.

We’ll create a worksheet with a single sheet. In the first sheet, we’ll put a value in the first cell:

@Test
fun `when file is created then content is correct`() {
    val workbook = XSSFWorkbook()
    val workSheet = workbook.createSheet()
    val cellStyle = workbook.createCellStyle()
    cellStyle.fillForegroundColor = IndexedColors.RED.getIndex()
    cellStyle.fillPattern = FillPatternType.SOLID_FOREGROUND
    val firstCell = workSheet
        .createRow(0)
        .createCell(0)
    firstCell.setCellValue("SAVED VALUE")
    firstCell.cellStyle = cellStyle

    val tempFile = createTempFile("test_output_", ".xlsx")
    workbook.write(tempFile.outputStream())
    workbook.close()

    val inputWorkbook = WorkbookFactory.create(tempFile.toFile().inputStream())
    val firstSheet = inputWorkbook.getSheetAt(0)
    assertThat(firstSheet.getRow(0).getCell(0).stringCellValue).isEqualTo("SAVED VALUE")
}

We used the cellStyle object to set the formatting. Moreover, we may change the format on the column level with the setDefaultColumnStyle() method available in the XSSFSheet class. Similarly, the XSSFRow class exposes the rowStyle field to set the format for the entire row.

When working with a workbook and writing to it, we must call the close() after the content is written to the file. Finally, in the example, we verified the file content. We opened the file and checked if the value is correct. Above all, creating and saving an Excel worksheet is as simple as reading it. Moreover, the implementation looks very similar to Java.

5. Conclusion

In this short article, we showed how to read and write an Excel file with the Apache POI library.

As always, the source code of the examples is available over on GitHub.