Apache | POI |
Home |
|
JVM languagesIntroApache POI can be used with any JVM language that can import Java jar files such as Jython, Groovy and Scala. Tested Environments
Please let us know if you use POI in an environment not listed here Java codePOILanguageExample.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 examplebuild.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 examplebuild.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.groovyimport 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)}" } } |