Apache Poi 简明教程

Apache POI - Core Classes

本章将讲解 Apache POI API 下的几个类和方法，它们对于使用 Java 程序处理 Excel 文件至关重要。

This chapter explains a few classes and methods under the Apache POI API that are critical to work on Excel files using Java programs.

Workbook

这是所有创建或维护 Excel 工作簿的类的父接口。它属于 org.apache.poi.ss.usermodel 包。实现此接口的两个类如下所示 −

This is the super-interface of all classes that create or maintain Excel workbooks. It belongs to the org.apache.poi.ss.usermodel package. The two classes that implement this interface are as follows −

  1. HSSFWorkbook − This class has methods to read and write Microsoft Excel files in .xls format. It is compatible with MS-Office versions 97-2003.

  2. XSSFWorkbook − This class has methods to read and write Microsoft Excel and OpenOffice xml files in .xls or .xlsx format. It is compatible with MS-Office versions 2007 or later.

HSSFWorkbook

它是 org.apache.poi.hssf.usermodel 包下的一种高级类。它实现了 Workbook 接口，用于处理 .xls 格式的 Excel 文件。列出此类下的一些方法和构造函数。

It is a high-level class under the org.apache.poi.hssf.usermodel package. It implements the Workbook interface and is used for Excel files in .xls format. Listed below are some of the methods and constructors under this class.

Class Constructors

Sr.No.

Constructor & Description

1

HSSFWorkbook() Creates a new HSSFWorkbook object from scratch.

2

HSSFWorkbook(DirectoryNode directory, boolean preserveNodes) Creates a new HSSFWworkbook objectinside a specific directory.

3

HSSFWorkbook(DirectoryNode directory, POIFSFileSystem fs, boolean preserveNodes) Given a POIFSFileSystem object and a specific directory within it, it creates an SSFWorkbook object to read a specified workbook.

4

HSSFWorkbook(java.io.InputStream s) Creates a new HSSFWorkbook object using an input stream.

5

HSSFWorkbook(java.io.InputStream s, boolean preserveNodes) Constructs a POI file system around your input stream.

6

HSSFWorkbook(POIFSFileSystem fs) Constructs a new HSSFWorkbook object using a POIFSFileSystem object.

7

HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes) Given a POIFSFileSystem object, it creates a new HSSFWorkbook object to read a specified workbook.

这些构造函数中经常使用的参数有 −

The frequently used parameters inside these constructors are −

  1. directory − It is the POI filesystem directory to process from.

  2. fs − It is the POI filesystem that contains the workbook stream.

  3. preservenodes − This is an optional parameter that decides whether to preserve other nodes like macros. It consumes a lot of memory as it stores all the POIFileSystem in memory (if set).

Note − HSSFWorkbook 类包含许多方法；但是，它们仅与 xls 格式兼容。在本教程中，重点在于最新版本的 Excel 文件格式。因此，此处未列出 HSSFWorkbook 的类方法。如果您需要这些类方法，那么可以在 https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html. 处参考 POI-HSSFWorkbook 类 API。

Note − The HSSFWorkbook class contains a number of methods; however they are compatible with xls format only. In this tutorial, the focus is on the latest version of Excel file formats. Hence, the class methods of HSSFWorkbook are not listed here. If you require these class methods, then refer POI-HSSFWorkbook class API at https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html.

XSSFWorkbook

它是一个用于表示高低级别 Excel 文件格式的类。它属于 org.apache.xssf.usemodel 包并实现了 Workbook 接口。以下是此类下的方法和构造函数。

It is a class that is used to represent both high and low level Excel file formats. It belongs to the org.apache.xssf.usemodel package and implements the Workbook interface. Listed below are the methods and constructors under this class.

Class Constructors

Sr.No.

Constructor & Description

1

XSSFWorkbook() Creates a new XSSFworkbook object from scratch.

2

XSSFWorkbook(java.io.File file) Constructs an XSSFWorkbook object from a given file.

3

XSSFWorkbook(java.io.InputStream is) Constructs an XSSFWorkbook object, by buffering the whole input stream into memory and then opening an OPCPackage object for it.

4

XSSFWorkbook(java.lang.String path) Constructs an XSSFWorkbook object given the full path of a file.

Class Methods

Sr.No.

Method & Description

1

createSheet() Creates an XSSFSheet for this workbook, adds it to the sheets, and returns the high level representation.

2

createSheet(java.lang.String sheetname) Creates a new sheet for this Workbook and returns the high level representation.

3

createFont() Creates a new font and adds it to the workbook’s font table.

4

createCellStyle() Creates a new XSSFCellStyle and adds it to the workbook’s style table.

5

createFont() Creates a new font and adds it to the workbook’s font table.

6

setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow,int endRow) Sets the print area of a given sheet as per the specified parameters.

对于此类的剩余方法，请参考完整 API 文档获取完整方法列表 − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html. 。

For the remaining methods of this class, refer the complete API document at − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html. for the complete list of methods.

Sheet

Sheet 是 org.apache.poi.ss.usermodel 包下的一个接口，它是创建具有特定名称的高级或低级电子表格的所有类的超接口。最常见的电子表格类型是工作表，表示为单元格网格。

Sheet is an interface under the org.apache.poi.ss.usermodel package and it is a super-interface of all classes that create high or low level spreadsheets with specific names. The most common type of spreadsheet is worksheet, which is represented as a grid of cells.

HSSFSheet

这是 org.apache.poi.hssf.usermodel 包下的一个类。它可以创建 Excel 电子表格，还允许设置工作表样式和工作表数据。

This is a class under the org.apache.poi.hssf.usermodel package. It can create excel spreadsheets and it allows to format the sheet style and sheet data.

Class Constructors

Sr.No.

Constructor & Description

1

HSSFSheet(HSSFWorkbook workbook) Creates new HSSFSheet called by HSSFWorkbook to create a sheet from scratch.

2

HSSFSheet(HSSFWorkbook workbook, InternalSheet sheet) Creates an HSSFSheet representing the given sheet object.

XSSFSheet

这是一个类，它表示电子表格的高级别表示。它位于 org.apache.poi.hssf.usermodel 包中。

This is a class which represents high level representation of excel spreadsheet. It is under org.apache.poi.hssf.usermodel package.

Class Constructors

Sr.No.

Constructor & Description

1

XSSFSheet() Creates new XSSFSheet − called by XSSFWorkbook to create a sheet from scratch.

2

XSSFSheet(PackagePart part, PackageRelationship rel) Creates an XSSFSheet representing the given package part and relationship.

Class Methods

Sr.No.

Method & Description

1

addMergedRegion(CellRangeAddress region) Adds a merged region of cells (hence those cells form one).

2

autoSizeColumn(int column) Adjusts the column width to fit the contents.

3

iterator() This method is an alias for rowIterator() to allow foreach loops

4

addHyperlink(XSSFHyperlink hyperlink) Registers a hyperlink in the collection of hyperlinks on this sheet

有关此类的其余方法，请参阅以下位置的完整 API − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html.

For the remaining methods of this class, refer the complete API at − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html.

Row

这是一个 org.apache.poi.ss.usermodel 包下的接口。它用于对电子表格中一行的高级表示。它是 POI 库中表示行的所有类的超级接口。

This is an interface under the org.apache.poi.ss.usermodel package. It is used for high-level representation of a row of a spreadsheet. It is a super-interface of all classes that represent rows in POI library.

XSSFRow

这是一个 org.apache.poi.xssf.usermodel 包下的类。它实现了 Row 接口，因此它可以在电子表格中创建行。以下是该类下的方法和构造函数。

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Row interface, therefore it can create rows in a spreadsheet. Listed below are the methods and constructors under this class.

Class Methods

Sr.No.

Method & Description

1

createCell(int columnIndex) Creates new cells within the row and returns it.

2

setHeight(short height) Sets the height in short units.

有关此类的其余方法，请遵循给定的链接 https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRow.html

For the remaining methods of this class, follow the given link https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRow.html

Cell

这是一个 org.apache.poi.ss.usermodel 包下的接口。它是电子表格行中的单元格表示的所有类的超级接口。

This is an interface under the org.apache.poi.ss.usermodel package. It is a super-interface of all classes that represent cells in the rows of a spreadsheet.

单元格可以获取各种属性，例如空白、数字、日期、错误等。在将单元格添加到行之前，它们应该有自己的编号（从 0 开始）。

Cells can take various attributes such as blank, numeric, date, error, etc. Cells should have their own numbers (0 based) before being added to a row.

XSSFCell

这是一个 org.apache.poi.xssf.usermodel 包下的类。它实现了 Cell 接口。它是电子表格行中单元格的高级表示。

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Cell interface. It is a high-level representation of cells in the rows of a spreadsheet.

Class Methods

Sr.No.

Method & Description

1

setCellStyle(CellStyle style) Sets the style for the cell.

2

setCellType(int cellType) Sets the type of cells (numeric, formula, or string).

3

setCellValue(boolean value) Sets a boolean value for the cell.

4

setCellValue(java.util.Calendar value) Sets a date value for the cell.

5

setCellValue(double value) Sets a numeric value for the cell.

6

setCellValue(java.lang.String str) Sets a string value for the cell.

7

setHyperlink(Hyperlink hyperlink) Assigns a hyperlink to this cell.

有关此类的剩余方法和字段，请访问以下链接 − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCell.html

For the remaining methods and fields of this class, visit the following link − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCell.html

XSSFCellStyle

此类属于 org.apache.poi.xssf.usermodel 包。它将提供有关电子表格单元格中内容格式的可能信息。它还提供了修改该格式的选项。它实现了 CellStyle 接口。

This is a class under the org.apache.poi.xssf.usermodel package. It will provide possible information regarding the format of the content in a cell of a spreadsheet. It also provides options for modifying that format. It implements the CellStyle interface.

Class Constructors

Sr.No.

Constructor & Description

1

XSSFCellStyle(int cellXfId, int cellStyleXfId, StylesTable stylesSource, ThemesTable theme) Creates a cell style from the supplied parts

2

XSSFCellStyle(StylesTable stylesSource) Creates an empty cell Style

Class Methods

Sr.No

Method & Description

1

setAlignment(short align) Sets the type of horizontal alignment for the cell

2

setBorderBottom(short border) Sets the type of border for the bottom border of the cell

3

setBorderColor(XSSFCellBorder.BorderSide side, XSSFColor color) Sets the color for the selected border

4

setBorderLeft(Short border) Sets the type of border for the left border of the cell

5

setBorderRight(short border) Sets the type of border for the right border of the cell

6

setBorderTop(short border) Sets the type of border for the top border of the cell

7

setFillBackgroundColor(XSSFColor color) Sets the background fill color represented as an XSSFColor value.

8

setFillForegroundColor(XSSFColor color) Sets the foreground fill color represented as an XSSFColor value.

9

setFillPattern(short fp) Specifies the cell fill information for pattern and solid color cell fills.

10

setFont(Font font) Sets the font for this style.

11

setRotation(short rotation) Sets the degree of rotation for the text in the cell.

12

setVerticalAlignment(short align) Sets the type of vertical alignment for the cell.

有关此类中的剩余方法和字段，请通过以下链接查找 − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCellStyle.html

For the remaining methods and fields in this class, go through the following link − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCellStyle.html

HSSFColor

这是一个属于 org.apache.poi.hssf.util 包的类。它以嵌套类的形式提供不同的颜色。通常，用它们自己的索引来表示这些嵌套类。它实现了颜色接口。

This is a class under the org.apache.poi.hssf.util package. It provides different colors as nested classes. Usually these nested classes are represented by using their own indexes. It implements the Color interface.

Nested classes

此类中的所有嵌套类均为静态类，且每个类都有自己的索引。这些嵌套颜色类用于单元格格式设置，例如单元格内容、边框、前景色和背景色。如下列出的几个嵌套类。

All nested classes of this class are static and each class has its index. These nested color classes are used for cell formatting such as cell content, border, foreground, and background. Listed below are some of the nested classes.

Sr.No.

Class names (colors)

1

HSSFColor.AQUA

2

HSSFColor.AUTOMATIC

3

HSSFColor.BLACK

4

HSSFColor.BLUE

5

HSSFColor.BRIGHT_GREEN

6

HSSFColor.BRIGHT_GRAY

7

HSSFColor.CORAL

8

HSSFColor.DARK_BLUE

9

HSSFColor.DARK_GREEN

10

HSSFColor.SKY_BLUE

11

HSSFColor.WHITE

12

HSSFColor.YELLOW

Class Methods

此类中只有一个方法非常重要，用于获取索引值。

Only one method of this class is important and that is used to get the index value.

Sr.No.

Method & Description

1

getIndex() This method is used to get the index value of a nested class.

有关剩余方法和嵌套类，请参考以下链接 − https://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html

For the remaining methods and nested classes, refer the following link − https://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html

XSSFColor

这是一个属于 org.apache.poi.xssf.usermodel 包的类。它用于在电子表格中表示颜色。它实现了颜色接口。如下列出它的几个方法和构造函数。

This is a class under the org.apache.poi.xssf.usermodel package. It is used to represent color in a spreadsheet. It implements the Color interface. Listed below are some of its methods and constructors.

Class Constructors

Sr.No.

Constructor & Description

1

XSSFColor() Creates a new instance of XSSFColor.

2

XSSFColor(byte[] rgb) Creates a new instance of XSSFColor using RGB.

3

XSSFColor(java.awt.Color clr) Creates a new instance of XSSFColor using the Color class from the awt package.

Class Methods

Sr.No.

Method & Description

1

setAuto(boolean auto) Sets a boolean value to indicate that the ctColor is automatic and the system ctColor is dependent.

2

setIndexed(int indexed) Sets indexed ctColor value as system ctColor.

有关剩余方法，请访问以下链接 − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFColor.html

For the remaining methods, visit the following link − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFColor.html

XSSFFont

这是一个属于 org.apache.poi.xssf.usermodel 包的类。它实现了字体接口，因此它可以在工作簿中处理不同的字体。

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Font interface and therefore it can handle different fonts in a workbook.

Class Constructor

Sr.No.

Constructor & Description

1

XSSFFont() Creates a new XSSFont instance.

Class Methods

Sr.No.

Method & Description

1

setBold(boolean bold) Sets a Boolean value for the 'bold' attribute.

2

setColor(short color) Sets the indexed color for the font.

3

setColor(XSSFColor color) Sets the color for the font in Standard Alpha RGB color value.

4

setFontHeight(short height) Sets the font height in points.

5

setFontName(java.lang.String name) Sets the name for the font.

6

setItalic(boolean italic) Sets a Boolean value for the 'italic' property.

对于其余方法，请通过以下链接进行 − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFFont.html

For the remaining methods, go through the following link − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFFont.html

XSSFHyperlink

这是一个 org.apache.poi.xssf.usermodel 包下的类。它实现了 Hyperlink 接口。它用于设置指向电子表格中单元格内容的超链接。

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Hyperlink interface. It is used to set a hyperlink to the cell contents of a spreadsheet.

Class Methods

Sr.No.

Method & Description

1

setAddress(java.lang.String address) Hyperlink address.

对于其余方法，请访问以下链接 − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFHyperlink.html

For the remaining methods, visit the following link − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFHyperlink.html

XSSFCreationHelper

这是一个 org.apache.poi.xssf.usermodel 包下的类。它实现了 CreationHelper 接口。它用作公式评估和设置超链接的支持类。

This is a class under the org.apache.poi.xssf.usermodel package. It implements the CreationHelper interface. It is used as a support class for formula evaluation and setting up hyperlinks.

Class Methods

Sr.No.

Method & Description

1

createFormulaEvaluator() Creates an XSSFFormulaEvaluator instance, the object that evaluates formula cells.

2

createHyperlink(int type) Creates a new XSSFHyperlink.

对于其余方法，请参阅以下链接 − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCreationHelper.html

For the remaining methods, refer the following link − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCreationHelper.html

XSSFPrintSetup

这是一个 org.apache.poi.xsssf.usermodel 包下的类。它实现了 PrintSetup 接口。它用于设置打印页面大小、区域、选项和设置。

This is a class under the org.apache.poi.xsssf.usermodel package. It implements the PrintSetup interface. It is used to set print page size, area, options, and settings.

Class Methods

Sr.No.

Method & Description

1

setLandscape(boolean ls) Sets a boolean value to allow or block landscape printing.

2

setLeftToRight(boolean ltor) Sets whether to go left to right or top down in ordering while printing.

3

setPaperSize(short size) Sets the paper size.

对于其余方法，请访问以下链接 − https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFPrintSetup.html

For the remaining methods, visit the following link − https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFPrintSetup.html