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