JVM languages(JVM 语言)

Intro(介绍)

Apache POI can be used with any JVM language that can import Java jar files such as Jython, Groovy, Scala, Kotlin, and JRuby.(Apache POI 可以与任何可以导入 Java jar 文件的 JVM 语言一起使用,例如 Jython、Groovy、Scala、Kotlin 和 JRuby。)

Tested Environments(测试环境)

  • Jython 2.5+ (older versions probably work, but are untested)(Jython 2.5+(旧版本可能有效,但未经测试))
  • Scala 2.x(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)(Groovy 2.4(从 1.6 开始应该可以工作,但我们只测试了最新的 2.4 版本))
  • Clojure 1.5.1+(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.(如果您在不同的语言(Kotlin、JRuby、...)中使用 POI,并且想分享一个 Hello POI!请分享)

Please let us know if you use POI in an environment not listed here(如果您在此处未列出的环境中使用 POI,请告知项目小组)

Java code(JAVA代码)

POILanguageExample.java(POILanguageExample.java)

// include poi-{version}-{yyyymmdd}.jar, poi-ooxml-{version}-{yyyymmdd}.jar,
// and poi-ooxml-lite-{version}-{yyyymmdd}.jar on Java classpath
// 在Java classpath引入poi-{version}-{yyyymmdd}.jar,poi-ooxml-{version}-{yyyymmdd}.jar和poi-ooxml-lite-{version}-{yyyymmdd}.jar
// Import the POI classes
// 导入 POI 类
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(Jython 示例)

# Add
poi jars
onto the python classpath or add them at run time
import sys
for jar in ('poi', 'poi-ooxml', 'poi-ooxml-lite'):
sys.path.append('/path/to/%s-5.0.0-SNAPSHOT.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.(有几个网站提供了在 Jython 项目中使用 Apache POI 的示例:python.org、jython.org 等等。)

Scala example(Scala示例)

build.sbt(build.sbt)

// Add the POI core and OOXML support dependencies into your build.sbt
// 添加 POI 核心和 OOXML 支持依赖到你的 build.sbt
libraryDependencies ++= Seq(
"org.apache.poi" % "poi" % "5.0.0-SNAPSHOT",
"org.apache.poi" % "poi-ooxml" % "5.0.0-SNAPSHOT",
"org.apache.poi" % "poi-ooxml-lite" "5.0.0-SNAPSHOT"
)

XSSFMain.scala(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
// 自动将 Java 集合转换为 Scala 等价物
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(Groovy 示例)

build.gradle(构建.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
// 将 POI 核心和 OOXML 支持依赖项添加到您的 gradle 构建中,连同所有 Groovy,以便它可以作为独立脚本运行
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(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(Clojure 示例)

SpreadSheetDemo.clj(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(贾文·奥尼尔)

 
中英文 | 中文 | 英文