| 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. 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-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)}"
       }
    }
### 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