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 and Scala.

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)

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

    # 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
    fos = FileOutputStream('SampleSS-updated.xlsx')
    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)}"
   }
}
      
by Javen O'Neal