Apache Software Foundation > 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 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