JVM languages

Intro

Apache POI can be used with any JVM language that can import Java jar files such as Jython, Groovy, Scala, Kotlin, and JRuby.

Jython
Scala
Groovy
Clojure

Tested Environments

Jython 2.5+ (older versions probably work, but are untested)
Scala 2.x
Groovy 2.4 (anything from 1.6 onwards ought to work, but only the latest 2.4 releases have been tested by us)
Clojure 1.5.1+

If you use POI in a different language (Kotlin, JRuby, ...) and would like to share a Hello POI! example, please share it.

Please let us know if you use POI in an environment not listed here

Java code

POILanguageExample.java

 
    // include poi-{version}-{yyyymmdd}.jar, poi-ooxml-{version}-{yyyymmdd}.jar,

    // and poi-ooxml-lite-{version}-{yyyymmdd}.jar on Java classpath

    // Import the POI classes

    import java.io.File;

    import java.io.FileOutputStream;

    import java.io.OutputStream;

    import org.apache.poi.ss.usermodel.Cell;

    import org.apache.poi.ss.usermodel.Row;

    import org.apache.poi.ss.usermodel.Sheet;

    import org.apache.poi.ss.usermodel.Workbook;

    import org.apache.poi.ss.usermodel.WorkbookFactory;

    import org.apache.poi.ss.usermodel.DataFormatter;

    // Read the contents of the workbook

    File f = new File("SampleSS.xlsx");

    Workbook wb = WorkbookFactory.create(f);

    DataFormatter formatter = new DataFormatter();

    int i = 1;

    int numberOfSheets = wb.getNumberOfSheets();

    for ( Sheet sheet : wb ) {

        System.out.println("Sheet " + i + " of " + numberOfSheets + ": " + sheet.getSheetName());

        for ( Row row : sheet ) {

            System.out.println("\tRow " + row.getRowNum());

            for ( Cell cell : row ) {

                System.out.println("\t\t"+ cell.getAddress().formatAsString() + ": " + formatter.formatCellValue(cell));

            }

        }

    }

    // Modify the workbook

    Sheet sh = wb.createSheet("new sheet");

    Row row = sh.createRow(7);

    Cell cell = row.createCell(42);

    cell.setActiveCell(true);

    cell.setCellValue("The answer to life, the universe, and everything");

    // Save and close the workbook

    OutputStream fos = new FileOutputStream("SampleSS-updated.xlsx");

    wb.write(fos);

    fos.close();

Jython example

 
    # Add 

poi jars

 onto the python classpath or add them at run time

    import sys

    for jar in ('poi', 'poi-ooxml', 'poi-ooxml-lite'):

        sys.path.append('/path/to/%s-5.0.0-SNAPSHOT.jar')

    from java.io import File, FileOutputStream

    from contextlib import closing

    # Import the POI classes

    from org.apache.poi.ss.usermodel import 

WorkbookFactory

, 

DataFormatter

    # Read the contents of the workbook

    wb = WorkbookFactory.create(File('

SampleSS.xlsx

'))

    formatter = DataFormatter()

    for i, sheet in enumerate(wb, start=1):

        print('Sheet %d of %d: %s'.format(i, wb.numberOfSheets, sheet.sheetName))

        for row in sheet:

            print('\tRow %i' % row.rowNum)

            for cell in row:

                print('\t\t%s: %s' % (cell.address, formatter.formatCellValue(cell)))

    # Modify the workbook

    sh = wb.createSheet('new sheet')

    row = sh.createRow(7)

    cell = sh.createCell(42)

    cell.activeCell = True

    cell.cellValue = 'The answer to life, the universe, and everything'

    # Save and close the workbook

    with closing(FileOutputStream('SampleSS-updated.xlsx')) as fos:

        wb.write(fos)

    wb.close()

There are several websites that have examples of using Apache POI in Jython projects: python.org, jython.org, and many others.

Scala example

build.sbt

 
    // Add the POI core and OOXML support dependencies into your build.sbt

    libraryDependencies ++= Seq(

      "org.apache.poi" % "poi" % "5.0.0-SNAPSHOT",

      "org.apache.poi" % "poi-ooxml" % "5.0.0-SNAPSHOT",

      "org.apache.poi" % "poi-ooxml-lite" "5.0.0-SNAPSHOT"

    )

XSSFMain.scala

 
    // Import the required classes

    import org.apache.poi.ss.usermodel.{

WorkbookFactory

, 

DataFormatter

}

    import java.io.{File, FileOutputStream}

    object XSSFMain extends App {

        // Automatically convert Java collections to Scala equivalents

        import scala.collection.JavaConversions._

        // Read the contents of the workbook

        val workbook = WorkbookFactory.create(new File("

SampleSS.xlsx

"))

        val formatter = new DataFormatter()

        for {

            // Iterate and print the sheets

            (sheet, i) <- workbook.zipWithIndex

            _ = println(s"Sheet $i of ${workbook.getNumberOfSheets}: ${sheet.getSheetName}")

            // Iterate and print the rows

            row <- sheet

            _ = println(s"\tRow ${row.getRowNum}")

            // Iterate and print the cells

            cell <- row

        } {

            println(s"\t\t${cell.getCellAddress}: ${formatter.formatCellValue(cell)}")

        }

        // Add a sheet to the workbook

        val sheet = workbook.createSheet("new sheet")

        val row = sheet.createRow(7)

        val cell = row.createCell(42)

        cell.setAsActiveCell()

        cell.setCellValue("The answer to life, the universe, and everything")

        // Save the updated workbook as a new file

        val fos = new FileOutputStream("SampleSS-updated.xlsx")

        workbook.write(fos)

        workbook.close()

    }

Groovy example

build.gradle

 
// Add the POI core and OOXML support dependencies into your gradle build,

//  along with all of Groovy so it can run as a standalone script

repositories {

    mavenCentral()

}

dependencies {

    runtime 'org.codehaus.groovy:groovy-all:2.4.7'

    runtime 'org.apache.poi:poi:3.14'

    runtime 'org.apache.poi:poi-ooxml:3.14'

}

SpreadSheetDemo.groovy

 
import org.apache.poi.ss.usermodel.*

import org.apache.poi.ss.util.*

import java.io.File

if (args.length == 0) {

   println "Use:"

   println "   SpreadSheetDemo <excel-file> [output-file]"

   return 1

}

File f = new File(args[0])

DataFormatter formatter = new DataFormatter()

WorkbookFactory.create(f,null,true).withCloseable { workbook ->

   println "Has ${workbook.getNumberOfSheets()} sheets"

   // Dump the contents of the spreadsheet

   (0..<workbook.getNumberOfSheets()).each { sheetNum ->

      println "Sheet ${sheetNum} is called ${workbook.getSheetName(sheetNum)}"

      def sheet = workbook.getSheetAt(sheetNum)

      sheet.each { row ->

         def nonEmptyCells = row.grep { c -> c.getCellType() != Cell.CELL_TYPE_BLANK }

         println " Row ${row.getRowNum()} has ${nonEmptyCells.size()} non-empty cells:"

         nonEmptyCells.each { c ->

            def cRef = [c] as CellReference

            println "  * ${cRef.formatAsString()} = ${formatter.formatCellValue(c)}"

         }

      }

   }

   // Add two new sheets and populate

   CellStyle headerStyle = makeHeaderStyle(workbook)

   Sheet ns1 = workbook.createSheet("Generated 1")

   exportHeader(ns1, headerStyle, null, ["ID","Title","Num"] as String[])

   ns1.createRow(1).createCell(0).setCellValue("TODO - Populate with data")

   Sheet ns2 = workbook.createSheet("Generated 2")

   exportHeader(ns2, headerStyle, "This is a demo sheet",

                ["ID","Title","Date","Author","Num"] as String[])

   ns2.createRow(2).createCell(0).setCellValue(1)

   ns2.createRow(3).createCell(0).setCellValue(4)

   ns2.createRow(4).createCell(0).setCellValue(1)

   // Save

   File output = File.createTempFile("output-", (f.getName() =~ /(\.\w+$)/)[0][0])

   output.withOutputStream { os -> workbook.write(os) }

   println "Saved as ${output}"

}

CellStyle makeHeaderStyle(Workbook wb) {

   int HEADER_HEIGHT = 18

   CellStyle style = wb.createCellStyle()

   style.setFillForegroundColor(IndexedColors.AQUA.getIndex())

   style.setFillPattern(FillPatternType.SOLID_FOREGROUND)

   Font font = wb.createFont()

   font.setFontHeightInPoints((short)HEADER_HEIGHT)

   font.setBold(true)

   style.setFont(font)

   return style

}

void exportHeader(Sheet s, CellStyle headerStyle, String info, String[] headers) {

   Row r

   int rn = 0

   int HEADER_HEIGHT = 18

   // Do they want an info row at the top?

   if (info != null && !info.isEmpty()) {

      r = s.createRow(rn)

      r.setHeightInPoints(HEADER_HEIGHT+1)

      rn++

      Cell c = r.createCell(0)

      c.setCellValue(info)

      c.setCellStyle(headerStyle)

      s.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1))

   }

   // Create the header row, of the right size

   r = s.createRow(rn)

   r.setHeightInPoints(HEADER_HEIGHT+1)

   // Add the column headings

   headers.eachWithIndex { col, idx ->

      Cell c = r.createCell(idx)

      c.setCellValue(col)

      c.setCellStyle(headerStyle)

      s.autoSizeColumn(idx)

   }

   // Make all the columns filterable

   s.setAutoFilter(new CellRangeAddress(rn, rn, 0, headers.length-1))

}

Clojure example

SpreadSheetDemo.clj

 
(ns poi.core

    (:gen-class)

    (:use [clojure.java.io :only [input-stream]])

    (:import [org.apache.poi.ss.usermodel WorkbookFactory DataFormatter]))

(defn sheets [wb] (map #(.getSheetAt wb %1) (range 0 (.getNumberOfSheets wb))))

(defn print-all [wb]

  (let [df (DataFormatter.)]

    (doseq [sheet (sheets wb)]

      (doseq [row (seq sheet)]

        (doseq [cell (seq row)]

          (println (.formatAsString (.getAddress cell)) ": " (.formatCellValue df cell)))))))

(defn -main [& args]

  (when-let [name (first args)]

    (let [wb (WorkbookFactory/create (input-stream name))]

      (print-all wb))))

by Javen O'Neal