Excel file with Kotlin | Apache POI

In every Development environment, data is mandatory part of their development, we put all your data in in your program files. But if any change in data results editing of the program file, which makes us to 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 compilation fails ?

Development should happen in such a that data do not have any effect on the program files. Placing data outside 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 open source tool developed by apache.

Apache POI will be helpful to modify large content of data. Below are 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
apache-poi-download-kotlin

3. Click on poi-bin-#####.zip link under binary distribution section
apache-poi-zip-download-kotlin

4. Click on the mirror link, apache would be suggesting nearest location for your mirror link
apache-poi-download-mirror-site-kotlin

5. Your poi files will be started to download
apache-poi-downloading-kotlin

6. Extract the downloaded zip file
extract-apache-poi-kotlin

7. Extracted folder should contain below files. inside-apache-poi-folder-kotlin

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 inteliJ

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

fun main(args: Array<String>) {
    val filepath = "./test_file.xlsx"
    //Instantiate Excel workbook:
    val xlWb = XSSFWorkbook()
    //Instantiate Excel worksheet:
    val xlWs = xlWb.createSheet()
    //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
    //Write text value to cell located at ROW_NUMBER / COLUMN_NUMBER:
    val xlRow = xlWs.createRow(rowNumber)
    val xlCol = xlRow.createCell(columnNumber)
    xlCol.setCellValue("Chercher Tech")
    //Write file:
    val outputStream = FileOutputStream(filepath)
    xlWb.write(outputStream)
    xlWb.close()
}

Write Properties File

Read Excel in Kotlin

  • To read any file we have to bring the file from Local system to JVM, use FileInputStream() object to bring the excel file into JVM
  • Read the file as excel file using the WorkbookFactory.create(), 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 getSheetAt("index/name") function, we can get the sheet either using the name or by index, index starts from 0
  • Get the row using getRow(rowNumber) from the sheet object and pass the row number you want to read s parameter
  • Get the cell number from the row using getCell(columnNumber) function and print it

fun main(args: Array<String>) {
    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)
    println(xlWs.getRow(rowNumber).getCell(columnNumber))
}		

KProperty in kotlin

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 a 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<String>) {
    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("")
    }
}	

Append content in kotlin

Read all values from a specific Column

We ca 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.

For going through every row in excel sheet we have to create rowIterator() for the excel sheet

We have all rows now, lets get the data based on the cell/Column number. In 'E' column I have store months, lets retrieve it. E column would have 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 last row number would give us the count of 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


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.lastRowNum
    var i=0;
    while (i<= xlRows){
        println(xlWs.getRow(i).getCell(4))
        i++
    }
}		

List Collections in Kotlin

aaaaaaaaaaaaa
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions

Recent Addition

new tutorial Selenium Online Training : Our next online training course for Selenium with Java starts from 17th December 2018.

You can attend first 3 classes for free, the total course fee is INR 10,000

The course time would be 8.00 PM(IST) for the first three classes

If you are interested to learn, then you can join the course by sending email to chercher.tech@gmail.com

or Register below


 
Join My Facebook Group
Join Group