Search Apache POI

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.

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-schemas-{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-schemas'):
        sys.path.append('/path/to/%s-3.14-20160307.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" % "3.15-beta2",
      "org.apache.poi" % "poi-ooxml" % "3.15-beta2",
      "org.apache.poi" % "poi-ooxml-schemas" "3.15-beta2"
    )
      

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 java.io.File

if (args.length == 0) {
   println "Use:"
   println "   SpreadSheetDemo [excel-file]"
   return 1
}

File f = new File(args[0]);
WorkbookFactory.create(f,null,true).withCloseable { workbook ->
   println "Has ${workbook.getNumberOfSheets()} sheets"
   0.step workbook.getNumberOfSheets(), 1, { sheetNum ->
     println "Sheet ${sheetNum} is called ${workbook.getSheetName(sheetNum)}"
   }
}
      

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