Busy Developers' Guide to HSSF and XSSF Features(给忙成狗的开发者的HSSF和XSSF功能指南)

Busy Developers' Guide to Features(给忙成狗的开发者的HSSF和XSSF功能指南)

Want to use HSSF and XSSF read and write spreadsheets in a hurry? This guide is for you. If you're after more in-depth coverage of the HSSF and XSSF user-APIs, please consult the HOWTO guide as it contains actual descriptions of how to use this stuff.(想要快速使用 HSSF 和 XSSF 读写电子表格？本指南适合您。如果您想要更深入地了解 HSSF 和 XSSF 用户 API，请参阅HOWTO指南，因为它包含有关如何使用这些内容的描述。)

Index of Features(特征索引)

Features(特征)

New Workbook(新工作簿)

Workbook wb = new HSSFWorkbook();
...
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
Workbook wb = new XSSFWorkbook();
...
try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
wb.write(fileOut);
}

New Sheet(新工作表)

Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();(.)
Sheet sheet1 = wb.createSheet("new sheet");
Sheet sheet2 = wb.createSheet("second sheet");
// Note that sheet name is Excel must not exceed 31 characters
// and must not contain any of the any of the following characters:
// 0x0000
// 0x0003
// colon (:)
// backslash (\)
// asterisk (*)
// question mark (?)
// forward slash (/)
// opening square bracket ([)
// closing square bracket (])
// 请注意，工作表名称 Excel 不得超过 31 个字符
// 并且不得包含以下任何字符：
// 0x0000
// 0x0003
// 冒号 (:)
// 反斜杠 (\)
// 星号 (* )
// 问号 (?)
// 正斜杠 (/)
// 左方括号 ([)
// 右方括号 (])
// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
// 您可以使用 org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
// 为了安全地创建有效名称，此实用程序将无效字符替换为空格 (' ')
String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "(返回工作表名“ O'Brien's sales ”)
Sheet sheet3 = wb.createSheet(safeName);
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}

Creating Cells(创建单元格)

Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
.
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
// 创建一行并将一些单元格放入其中。行的初始索引是0。
Row row = sheet.createRow(0);
// Create a cell and put a value in it.
// 创建一个单元格并在其中放入一个值。
Cell cell = row.createCell(0);
cell.setCellValue(1);
// Or do it on one line.
// 或者直接在行上做这件事。
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(
createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}

Creating Date Cells(创建日期单元格)

Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
// 创建一行并将一些单元格放入其中。行的初始索引是0。
Row row = sheet.createRow(0);
// Create a cell and put a date value in it. The first cell is not styled
// as a date.
// 创建一个单元格并将日期值放入其中。 第一个单元格的样式不是日期。
Cell cell = row.createCell(0);
cell.setCellValue(new Date());
// we style the second cell as a date (and time). It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but other cells.
// 我们将第二个单元格设置为日期（和时间）。
//从工作簿中创建一个新的单元格样式很重要，否则可能会修改内置样式，不仅影响此单元格，还会影响其他单元格。
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
//you can also set date as java.util.Calendar
//您也可以将日期设置为 java.util.Calendar
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}

Working with different types of cells(使用不同类型的单元格)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
Row row = sheet.createRow(2);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(new Date());
row.createCell(2).setCellValue(Calendar.getInstance());
row.createCell(3).setCellValue("a string");
row.createCell(4).setCellValue(true);
row.createCell(5).setCellType(CellType.ERROR);
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}

Files vs InputStreams(文件与输入流)

When opening a workbook, either a .xls HSSFWorkbook, or a .xlsx XSSFWorkbook, the Workbook can be loaded from either a File or an InputStream. Using a File object allows for lower memory consumption, while an InputStream requires more memory as it has to buffer the whole file.(打开工作簿(.xls HSSFWorkbook 或 .xlsx XSSFWorkbook)时，可以从File或InputStream加载工作簿。使用 File 对象可以降低内存消耗，而InputStream需要更多内存，因为它必须缓冲整个文件。)

If using WorkbookFactory, it's very easy to use one or the other:(如果使用 WorkbookFactory，那么使用其中一个非常容易：)

// Use a file
// 使用文件
Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));
// Use an InputStream, needs more memory
// 使用 InputStream，需要更多内存
Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));

If using HSSFWorkbook or XSSFWorkbook directly, you should generally go through POIFSFileSystem or OPCPackage, to have full control of the lifecycle (including closing the file when done):(如果直接使用 HSSFWorkbook 或 XSSFWorkbook，一般应该通过 POIFSFileSystem 或 OPCPackage 来完全控制生命周期（包括完成后关闭文件）：)

// HSSFWorkbook, File
// HSSF工作簿，文件
POIFSFileSystem fs = new POIFSFileSystem(new File("file.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
....
fs.close();
// HSSFWorkbook, InputStream, needs more memory
// HSSFWorkbook，InputStream，需要更多内存
POIFSFileSystem fs = new POIFSFileSystem(myInputStream);
HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
// XSSFWorkbook, File
// XSSF工作簿，文件
OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(pkg);
....
pkg.close();
// XSSFWorkbook, InputStream, needs more memory
// XSSFWorkbook，InputStream，需要更多内存
OPCPackage pkg = OPCPackage.open(myInputStream);
XSSFWorkbook wb = new XSSFWorkbook(pkg);
....
pkg.close();

Demonstrates various alignment options(演示各种对齐选项)

public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();(.)
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(2);
row.setHeightInPoints(30);
createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) {
wb.write(fileOut);
}
wb.close();
}
/**
* Creates a cell and aligns it a certain way.
*
* @param wb the workbook
* @param row the row to create the cell in
* @param column the column number to create the cell in
* @param halign the horizontal alignment for the cell.
* @param valign the vertical alignment for the cell.
*/
/**
* 创建一个单元格并以某种方式对齐它.
*
* @param wb 工作簿对象
* @param row 用于创建单元格的行
* @param column 单元格的列号
* @param halign 单元格的水平对齐方式
* @param valign 单元格的垂直对齐方式
*/
private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
Cell cell = row.createCell(column);
cell.setCellValue("Align It");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);
}

Working with borders(使用边框)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
// 创建一行并将一些单元格放入其中。行的初始索引是0。
Row row = sheet.createRow(1);
// Create a cell and put a value in it.
// 创建一个单元格并在其中放入一个值。
Cell cell = row.createCell(1);
cell.setCellValue(4);
// Style the cell with borders all around.
// 设置带有边框的单元格。
CellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(BorderStyle.MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(style);
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
wb.close();

Iterate over rows and cells(迭代行和单元格)

Sometimes, you'd like to just iterate over all the sheets in a workbook, all the rows in a sheet, or all the cells in a row. This is possible with a simple for loop.(有时，您只想遍历工作簿中的所有工作表、工作表中的所有行或一行中的所有单元格。这可以通过一个简单的 for 循环来实现。)

These iterators are available by calling workbook.sheetIterator(), sheet.rowIterator(), and row.cellIterator(), or implicitly using a for-each loop. Note that a rowIterator and cellIterator iterate over rows or cells that have been created, skipping empty rows and cells.(这些迭代器可通过调用 workbook.sheetIterator()、sheet.rowIterator() 和 row.cellIterator() 或隐式使用 for-each 循环获得。请注意，rowIterator 和 cellIterator 遍历已创建的行或单元格，跳过空行和单元格。)

for (Sheet sheet : wb ) {
for (Row row : sheet) {
for (Cell cell : row) {
// Do something here
// 在这里做点什么
}
}
}

Iterate over cells, with control of missing / blank cells(迭代单元格，控制缺失/空白单元格)

In some cases, when iterating, you need full control over how missing or blank rows and cells are treated, and you need to ensure you visit every cell and not just those defined in the file. (The CellIterator will only return the cells defined in the file, which is largely those with values or stylings, but it depends on Excel).(在某些情况下，在迭代时，您需要完全控制丢失或空白行和单元格的处理方式，并且您需要确保访问每个单元格，而不仅仅是文件中定义的单元格。 （CellIterator 将只返回文件中定义的单元格，主要是那些具有值或样式的单元格，这取决于 Excel）。)

In cases such as these, you should fetch the first and last column information for a row, then call getCell(int, MissingCellPolicy) to fetch the cell. Use a MissingCellPolicy to control how blank or null cells are handled.(在这种情况下，您应该获取一行的第一列和最后一列信息，然后调用 getCell(int, MissingCellPolicy) 来获取单元格。使用 MissingCellPolicy控制如何处理空白或空单元格。)

// Decide which rows to process
// 决定要处理的行
int rowStart = Math.min(15, sheet.getFirstRowNum());
int rowEnd = Math.max(1400, sheet.getLastRowNum());
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row r = sheet.getRow(rowNum);
if (r == null) {
// This whole row is empty
// Handle it as needed
// 此行是空的
// 根据需要处理
continue;
}
int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);
for (int cn = 0; cn < lastColumn; cn++) {
Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
if (c == null) {
// The spreadsheet is empty in this cell
// 此单元格中的电子表格为空
} else {
// Do something useful with the cell's contents
// 对单元格的内容做一些有用的事情
}
}
}

Getting the cell contents(获取单元格内容)

To get the contents of a cell, you first need to know what kind of cell it is (asking a string cell for its numeric contents will get you a NumberFormatException for example). So, you will want to switch on the cell's type, and then call the appropriate getter for that cell.(要获取单元格的内容，您首先需要知道它是什么类型的单元格（例如，向字符串单元格获取一个数字内容会得到一个 NumberFormatException）。因此，您需要获取单元格的类型，然后对该单元格调用适当的 getter方法。)

In the code below, we loop over every cell in one sheet, print out the cell's reference (eg A3), and then the cell's contents.(在下面的代码中，我们循环遍历一张表中的每个单元格，打印出单元格的引用（例如A3、Sheet1!A3），然后打印出单元格的内容。)

// import org.apache.poi.ss.usermodel.*;
// import org.apache.poi.ss.usermodel.*;
DataFormatter formatter = new DataFormatter();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
for (Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
System.out.print(cellRef.formatAsString());
System.out.print(" - ");
// get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
// 通过获取单元格值来获取单元格中的文本，并应用任何数据格式（Date、0.00、1.23e9、$1.23 等）
//DataFormatter.formatCellValue(Cell cell)无论单元格类型如何，都将单元格的格式化值作为字符串返回
String text = formatter.formatCellValue(cell);
System.out.println(text);
// Alternatively, get the value and format it yourself
// 或者，获取单元格类型并自行格式化
switch (cell.getCellType()) {
case CellType.STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case CellType.NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case CellType.BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case CellType.FORMULA:
System.out.println(cell.getCellFormula());
break;
case CellType.BLANK:
System.out.println();
break;
default:
System.out.println();
}
}
}

Text Extraction(文本提取)

For most text extraction requirements, the standard ExcelExtractor class should provide all you need.(对于大多数文本提取要求，标准 ExcelExtractor 类应该满足您的需要。)

try (InputStream inp = new FileInputStream("workbook.xls")) {
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
ExcelExtractor extractor = new ExcelExtractor(wb);
extractor.setFormulasNotResults(true);
extractor.setIncludeSheetNames(false);
String text = extractor.getText();
wb.close();
}

For very fancy text extraction, XLS to CSV etc, take a look at /src/examples/src/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java(对于非常花哨的文本提取、XLS 到 CSV 等，请查看 /src/examples/src/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java)

Fills and colors(填充和颜色)

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
// 创建一行并将一些单元格放入其中。行的初始索引是0。
Row row = sheet.createRow(1);
// Aqua background
// 水绿色背景
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(FillPatternType.BIG_SPOTS);
Cell cell = row.createCell(1);
cell.setCellValue("X");
cell.setCellStyle(style);
// Orange "foreground", foreground being the fill foreground not the font color.
// 橙色“前景”，前景是填充前景而不是字体颜色。
style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell = row.createCell(2);
cell.setCellValue("X");
cell.setCellStyle(style);
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
wb.close();

Merging cells(合并单元格)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
Row row = sheet.createRow(1);
Cell cell = row.createCell(1);
cell.setCellValue("This is a test of merging");
sheet.addMergedRegion(new CellRangeAddress(
1, //first row (0-based)( 起始行（从 0 开始）)
1, //last row (0-based)( 最后一行（从 0 开始）)
1, //first column (0-based)( 起始列（从 0 开始）)
2 //last column (0-based)( 最后一列（从 0 开始）)
));
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
wb.close();

Working with fonts(使用字体)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
// 创建一行并将一些单元格放入其中。行的初始索引是0。
Row row = sheet.createRow(1);
// Create a new font and alter it.
// 创建一个新字体并改变它。
Font font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
// Fonts are set into a style so create a new one to use.
// 字体被设置成一种样式，所以创建一种新的样式来使用
CellStyle style = wb.createCellStyle();
style.setFont(font);
// Create a cell and put a value in it.
// 创建一个单元格并在其中放入一个值。
Cell cell = row.createCell(1);
cell.setCellValue("This is a test of fonts");
cell.setCellStyle(style);
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
wb.close();

Note, the maximum number of unique fonts in a workbook is limited to 32767. You should re-use fonts in your applications instead of creating a font for each cell. Examples:(请注意，工作簿中唯一字体的最大数量限制为 32767。您应该在应用程序中重复使用字体，而不是为每个单元格创建字体。例子：)

Wrong:(错误的：)

for (int i = 0; i < 10000; i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
cell.setCellStyle(style);
}

Correct:(正确的：)

CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
for (int i = 0; i < 10000; i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellStyle(style);
}

Custom colors(自定义颜色)

HSSF:(HSSF：)

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("Default Palette");
//apply some colors from the standard palette,
// as in the previous examples.
//we'll use red text on a lime background
// 应用标准调色板中的一些颜色，如前面的示例所示。我们将在石灰背景上使用红色文本
HSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.RED.index);
style.setFont(font);
cell.setCellStyle(style);
//save with the default palette
//使用默认调色板保存
try (OutputStream out = new FileOutputStream("default_palette.xls")) {
wb.write(out);
}
//now, let's replace RED and LIME in the palette
// with a more attractive combination
// (lovingly borrowed from freebsd.org)
//现在，让我们用更有吸引力的组合替换调色板中的 RED 和 LIME（亲切地从 freebsd.org 借用）
cell.setCellValue("Modified Palette");
//creating a custom palette for the workbook
//为工作簿创建自定义调色板
HSSFPalette palette = wb.getCustomPalette();
//replacing the standard red with freebsd.org red
//用freebsd.org red替换标准的red
palette.setColorAtIndex(HSSFColor.RED.index,
(byte) 153, //RGB red (0-255)(红色)
(byte) 0, //RGB green( 绿色)
(byte) 0 //RGB blue( 蓝色)
);
//replacing lime with freebsd.org gold
//用 freebsd.org gold 替换石灰色
palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);
//save with the modified palette
// note that wherever we have previously used RED or LIME, the
// new colors magically appear
//使用修改后的调色板保存。注意，无论我们以前在哪里使用红色或石灰，新的颜色都会神奇地出现
try (out = new FileOutputStream("modified_palette.xls")) {
wb.write(out);
}

XSSF:(XSSF：)

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell( 0);
cell.setCellValue("custom XSSF colors");
XSSFCellStyle style1 = wb.createCellStyle();
style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128), new DefaultIndexedColorMap()));
style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);

Reading and Rewriting Workbooks(读取和重写工作簿)

try (InputStream inp = new FileInputStream("workbook.xls")) {
//InputStream inp = new FileInputStream("workbook.xlsx");
//InputStream inp = new FileInputStream("workbook.xlsx");
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(2);
Cell cell = row.getCell(3);
if (cell == null)
cell = row.createCell(3);
cell.setCellType(CellType.STRING);
cell.setCellValue("a test");
// Write the output to a file
// 将输出写入文件
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
}

Using newlines in cells(在单元格中使用换行符)

Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();(.)
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(2);
Cell cell = row.createCell(2);
cell.setCellValue("Use \n with word wrap on to create a new line");
//to enable newlines you need set a cell styles with wrap=true
//要启用换行符，您需要设置单元格样式wrap=true
CellStyle cs = wb.createCellStyle();
cs.setWrapText(true);
cell.setCellStyle(cs);
//increase row height to accommodate two lines of text
//增加行高以容纳两行文本
row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
//adjust column width to fit the content
//调整列宽以适应内容
sheet.autoSizeColumn(2);
try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) {
wb.write(fileOut);
}
wb.close();

Data Formats(数据格式)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");
CellStyle style;
DataFormat format = wb.createDataFormat();
Row row;
Cell cell;
int rowNum = 0;
int colNum = 0;
row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(11111.25);
style = wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0"));
cell.setCellStyle(style);
row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(11111.25);
style = wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.0000"));
cell.setCellStyle(style);
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
wb.close();

Fit Sheet to One Page(将工作表设置为一页(打印设置))

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");
PrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)1);
ps.setFitWidth((short)1);
// Create various cells and rows for spreadsheet.
// 为电子表格创建各种单元格和行。
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
wb.close();

Set Print Area(设置打印区域)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
//sets the print area for the first sheet
//设置第一张工作表的打印区域
wb.setPrintArea(0, "$A$1:$C$2");
//Alternatively:
//或者：
wb.setPrintArea(
0, //sheet index(0, //工作表索引)
0, //start column(0, //开始列)
1, //end column(1, //结束列)
0, //start row(0, //开始行)
0 //end row(0 //结束行)
);
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
wb.close();

Set Page Numbers on Footer(在页脚设置页码)

Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();(.)
Sheet sheet = wb.createSheet("format sheet");
Footer footer = sheet.getFooter();
footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() );
// Create various cells and rows for spreadsheet.
// 为电子表格创建各种单元格和行。
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
wb.close();

Using the Convenience Functions(使用便捷函式)

The convenience functions provide utility features such as setting borders around merged regions and changing style attributes without explicitly creating new styles.(便捷函式提供实用功能，例如在合并区域周围设置边框和更改样式属性而无需显式创建新样式。)

Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook()(.)
Sheet sheet1 = wb.createSheet( "new sheet" );
// Create a merged region
// 创建一个合并区域
Row row = sheet1.createRow( 1 );
Row row2 = sheet1.createRow( 2 );
Cell cell = row.createCell( 1 );
cell.setCellValue( "This is a test of merging" );
CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");
sheet1.addMergedRegion( region );
// Set the border and border colors.
// 设置边框和边框颜色。
RegionUtil.setBorderBottom( BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderTop( BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderLeft( BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBorderRight( BorderStyle.MEDIUM_DASHED, region, sheet1, wb );
RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setTopBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setLeftBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setRightBorderColor( IndexedColors.AQUA.getIndex(), region, sheet1, wb);
// Shows some usages of HSSFCellUtil
// 展示 HSSFCellUtil 的一些用法
CellStyle style = wb.createCellStyle();
style.setIndention((short)4);
CellUtil.createCell(row, 8, "This is the value of the cell", style);
Cell cell2 = CellUtil.createCell( row2, 8, "This is the value of the cell");
CellUtil.setAlignment(cell2, HorizontalAlignment.CENTER);
// Write out the workbook
//写出工作簿
try (OutputStream fileOut = new FileOutputStream( "workbook.xls" )) {
wb.write( fileOut );
}
wb.close();

Shift rows up or down on a sheet(在工作表上向上或向下移动行)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("row sheet");
// Create various cells and rows for spreadsheet.
// 为电子表格创建各种单元格和行。
// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
// 将电子表格上的第 6 - 11 行移到顶部（第 0 - 5 行）
sheet.shiftRows(5, 10, -5);

Set a sheet as selected(将工作表设置为选中)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("row sheet");
sheet.setSelected(true);

Set the zoom magnification(设置缩放倍率)

The zoom is expressed as a fraction. For example to express a zoom of 75% use 3 for the numerator and 4 for the denominator.(缩放以分数表示。例如，要表示 75% 的缩放，分子使用 3，分母使用 4。)

Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
sheet1.setZoom(75); // 75 percent magnification( 75% 放大率)

Splits and freeze panes(拆分和冻结窗格)

There are two types of panes you can create; freeze panes and split panes.(您可以创建两种类型的窗格；冻结窗格和拆分窗格。)

A freeze pane is split by columns and rows. You create a freeze pane using the following mechanism:(冻结窗格按列和行进行冻结，您可以使用以下机制创建冻结窗格：)

sheet1.createFreezePane( 3, 2, 3, 2 );(sheet1.createFreezePane(3,2,3,2);)

The first two parameters are the columns and rows you wish to split by. The second two parameters indicate the cells that are visible in the bottom right quadrant.(前两个参数是您希望冻结的列和行。后两个参数表示右下象限中可见的单元格。)

Split panes appear differently. The split area is divided into four separate work area's. The split occurs at the pixel level and the user is able to adjust the split by dragging it to a new position.(拆分窗格的显示方式不同。分割区域分为四个独立的工作区域。分割发生在像素级别，用户可以通过将分割拖动到新位置来调整。)

Split panes are created with the following call:(拆分窗格是通过以下调用创建的：)

sheet2.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );(sheet2.createSplitPane(2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT);)

The first parameter is the x position of the split. This is in 1/20th of a point. A point in this case seems to equate to a pixel. The second parameter is the y position of the split. Again in 1/20th of a point.(第一个参数是分割的x位置。它是一个点的 1/20。在这种情况下，一个点大概等同于一个像素。第二个参数是分割的y位置，也是一个点的1/20。)

The last parameter indicates which pane currently has the focus. This will be one of Sheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT.(最后一个参数指示当前哪个窗格具有焦点。这将是 Sheet.PANE_LOWER_LEFT、PANE_LOWER_RIGHT、PANE_UPPER_RIGHT 或 PANE_UPPER_LEFT 之一。)

Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
Sheet sheet2 = wb.createSheet("second sheet");
Sheet sheet3 = wb.createSheet("third sheet");
Sheet sheet4 = wb.createSheet("fourth sheet");
// Freeze just one row
// 只冻结一行
sheet1.createFreezePane( 0, 1, 0, 1 );
// Freeze just one column
// 只冻结一列
sheet2.createFreezePane( 1, 0, 1, 0 );
// Freeze the columns and rows (forget about scrolling position of the lower right quadrant).
// 冻结列和行（忽略右下象限的滚动位置）。
sheet3.createFreezePane( 2, 2 );
// Create a split with the lower left side being the active quadrant
// 创建一个以左下角为活动象限的分割
sheet4.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}

Repeating rows and columns(重复行和列)

It's possible to set up repeating rows and columns in your printouts by using the setRepeatingRows() and setRepeatingColumns() methods in the Sheet class.(可以使用 Sheet 类中的 setRepeatingRows() 和 setRepeatingColumns() 方法在打印输出中设置重复的行和列。)

These methods expect a CellRangeAddress parameter which specifies the range for the rows or columns to repeat. For setRepeatingRows(), it should specify a range of rows to repeat, with the column part spanning all columns. For setRepeatingColums(), it should specify a range of columns to repeat, with the row part spanning all rows. If the parameter is null, the repeating rows or columns will be removed.(这些方法需要一个 CellRangeAddress 参数，该参数指定要重复的行或列的范围。对于 setRepeatingRows()，它应该指定要重复的行范围，列部分跨越所有列。对于 setRepeatingColums()，它应该指定要重复的列范围，其中行部分跨越所有行。如果该参数为空，则将删除重复的行或列。)

Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();(.)
Sheet sheet1 = wb.createSheet("Sheet1");
Sheet sheet2 = wb.createSheet("Sheet2");
// Set the rows to repeat from row 4 to 5 on the first sheet.
// 设置第一个工作表的第 4 行到第 5 行重复。
sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5"));
// Set the columns to repeat from column A to C on the second sheet
// 设置第二个工作表从 A 列到 C 列重复
sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C"));
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}

Headers and Footers(页眉和页脚)

Example is for headers but applies directly to footers.(示例适用于页眉，也适用于页脚。)

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}

XSSF Enhancement for Headers and Footers(页眉和页脚的 XSSF 增强)

Example is for headers but applies directly to footers. Note, the above example for basic headers and footers applies to XSSF Workbooks as well as HSSF Workbooks. The HSSFHeader stuff does not work for XSSF Workbooks.(示例适用于页眉，也适用于页脚。请注意，上述基本页眉和页脚示例适用于 XSSF 工作簿以及 HSSF 工作簿。 HSSFHeader 内容不适用于 XSSF 工作簿。)

XSSF has the ability to handle First page headers and footers, as well as Even/Odd headers and footers. All Header/Footer Property flags can be handled in XSSF as well. The odd header and footer is the default header and footer. It is displayed on all pages that do not display either a first page header or an even page header. That is, if the Even header/footer does not exist, then the odd header/footer is displayed on even pages. If the first page header/footer does not exist, then the odd header/footer is displayed on the first page. If the even/odd property is not set, that is the same as the even header/footer not existing. If the first page property does not exist, that is the same as the first page header/footer not existing.(XSSF 能够处理首页页眉和页脚，以及偶数/奇数页眉和页脚。所有页眉/页脚属性标志也可以在 XSSF 中处理。奇数页眉和页脚是默认页眉和页脚。它显示在除了首页标题或偶数页标题的所有页面上。也就是说，如果偶数页眉/页脚不存在，则奇数页眉/页脚将显示在偶数页上。如果第一页页眉/页脚不存在，则奇数页眉/页脚显示在第一页上。如果未设置偶数/奇数属性，则与相当于不存在偶数页眉/页脚。如果首页页属性不存在，则相当于首页页眉/页脚不存在。)

Workbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet) wb.createSheet("new sheet");
// Create a first page header
// 创建第一页标题
Header header = sheet.getFirstHeader();
header.setCenter("Center First Page Header");
header.setLeft("Left First Page Header");
header.setRight("Right First Page Header");
// Create an even page header
// 创建偶数页眉
Header header2 = sheet.getEvenHeader();
der2.setCenter("Center Even Page Header");
header2.setLeft("Left Even Page Header");
header2.setRight("Right Even Page Header");
// Create an odd page header
// 创建奇数页眉
Header header3 = sheet.getOddHeader();
der3.setCenter("Center Odd Page Header");
header3.setLeft("Left Odd Page Header");
header3.setRight("Right Odd Page Header");
// Set/Remove Header properties
// 设置/删除标题属性
XSSFHeaderProperties prop = sheet.getHeaderFooterProperties();
prop.setAlignWithMargins();
prop.scaleWithDoc();
prop.removeDifferentFirstPage(); // This does not remove first page headers or footers( 这不会删除首页页眉或页脚)
prop.removeDifferentEvenOdd(); // This does not remove even headers or footers( 这不会删除偶数页眉或页脚)
try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
wb.write(fileOut);
}

Drawing Shapes(绘制形状)

POI supports drawing shapes using the Microsoft Office drawing tools. Shapes on a sheet are organized in a hierarchy of groups and and shapes. The top-most shape is the patriarch. This is not visible on the sheet at all. To start drawing you need to call createPatriarch on the HSSFSheet class. This has the effect erasing any other shape information stored in that sheet. By default POI will leave shape records alone in the sheet unless you make a call to this method.(POI 支持使用 Microsoft Office 绘图工具绘制图形。工作表上的图形根据组和图形的层次结构进行组织，顶层图形叫做“族长”，它们在工作表上并不可见。要开始绘图，您需要在 HSSFSheet 类上调用 createPatriarch。这会删除该工作表中存储的任何其他图形信息。默认情况下，除非您调用此方法，否则 POI 将在工作表中保留图形记录。)

To create a shape you have to go through the following steps:(要创建图形，您必须执行以下步骤：)

  1. Create the patriarch.(创建“族长”。)
  2. Create an anchor to position the shape on the sheet.(创建一个锚点以将形状定位在图纸上。)
  3. Ask the patriarch to create the shape.(使用“族长”创建图形。)
  4. Set the shape type (line, oval, rectangle etc...)(设置图形类型（线、椭圆、矩形等...）)
  5. Set any other style details concerning the shape. (eg: line thickness, etc...)(设置图形的其他样式细节。 （例如：线条粗细等...）)
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 );
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);

Text boxes are created using a different call:(文本框是调用另一个方法创建的：)

HSSFTextbox textbox1 = patriarch.createTextbox(
new HSSFClientAnchor(0,0,0,0,(short)1,1,(short)2,2));
textbox1.setString(new HSSFRichTextString("This is a test") );

It's possible to use different fonts to style parts of the text in the textbox. Here's how:(可以使用不同的字体来设置文本框中部分文本的样式。比如这样：)

HSSFFont font = wb.createFont();
font.setItalic(true);
font.setUnderline(HSSFFont.U_DOUBLE);
HSSFRichTextString string = new HSSFRichTextString("Woo!!!");
string.applyFont(2,5,font);
textbox.setString(string );

Just as can be done manually using Excel, it is possible to group shapes together. This is done by calling createGroup() and then creating the shapes using those groups.(图形可以进行分组，就像使用Excel手动操作一样。可以通过调用 createGroup() 然后使用这些组创建图形。)

It's also possible to create groups within groups.(也可以在组内创建组。)

Warning (警告)
Any group you create should contain at least two other shapes or subgroups. (您创建的任何组都应包含至少两个其他图形或子组。)

Here's how to create a shape group:(以下是创建图形组的方法：)

// Create a shape group.
// 创建一个形状组。
HSSFShapeGroup group = patriarch.createGroup(
new HSSFClientAnchor(0,0,900,200,(short)2,2,(short)2,2));
// Create a couple of lines in the group.
// 在组中创建几条线
HSSFSimpleShape shape1 = group.createShape(new HSSFChildAnchor(3,3,500,500));
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
( (HSSFChildAnchor) shape1.getAnchor() ).setAnchor(3,3,500,500);
HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor(1,200,400,600));
shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);

If you're being observant you'll noticed that the shapes that are added to the group use a new type of anchor: the HSSFChildAnchor. What happens is that the created group has it's own coordinate space for shapes that are placed into it. POI defaults this to (0,0,1023,255) but you are able to change it as desired. Here's how:(如果您仔细观察，您会注意到添加到组中的形状使用了一种新类型的锚：HSSFChildAnchor。因为放置在组中的图形拥有相对于组的坐标空间。POI 默认为 (0,0,1023,255)，但您可以根据需要更改它。比如这样：)

myGroup.setCoordinates(10,10,20,20); // top-left, bottom-right( 左上角，右下角)

If you create a group within a group it's also going to have it's own coordinate space.(如果您在组内创建一个组，它也将拥有自己的坐标空间。)

Styling Shapes(图形样式)

By default shapes can look a little plain. It's possible to apply different styles to the shapes however. The sorts of things that can currently be done are:(默认情况下，图形看上去很简单。但是，可以对图形应用不同的样式来让它变得花哨。目前可以这样做：)

  • Change the fill color.(更改填充颜色。)
  • Make a shape with no fill color.(创建一个没有填充颜色的图形。)
  • Change the thickness of the lines.(更改线条的粗细。)
  • Change the style of the lines. Eg: dashed, dotted.(更改线条的样式。如：虚线、点线。)
  • Change the line color.(更改线条颜色。)

Here's an examples of how this is done:(下述代码演示如何完成：)

HSSFSimpleShape s = patriarch.createSimpleShape(a);
s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);
s.setLineStyleColor(10,10,10);
s.setFillColor(90,10,200);
s.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT * 3);
s.setLineStyle(HSSFShape.LINESTYLE_DOTSYS);

Shapes and Graphics2d(图形和Graphics2d)

While the native POI shape drawing commands are the recommended way to draw shapes in a shape it's sometimes desirable to use a standard API for compatibility with external libraries. With this in mind we created some wrappers for Graphics and Graphics2d.(虽然本机POI图形绘制命令是在图形中绘制图形的推荐方法，但有时需要使用标准 API 以与外部库兼容。考虑到这一点，我们为 Graphics 和 Graphics2d 创建了一些包装器。)

Warning (警告)
It's important to not however before continuing that Graphics2d is a poor match to the capabilities of the Microsoft Office drawing commands. The older Graphics class offers a closer match but is still a square peg in a round hole. (重要的是，在继续之前，Graphics2d 与 Microsoft Office 绘图命令的功能不匹配。较旧的 Graphics 类提供了更紧密的匹配，但仍然显得格格不入。)

All Graphics commands are issued into an HSSFShapeGroup. Here's how it's done:(所有图形命令都会发送到一个 HSSFShapeGroup。下述代码演示如何完成：)

a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 );
group = patriarch.createGroup( a );
group.setCoordinates( 0, 0, 80 * 4 , 12 * 23 );
float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / (float)Math.abs(group.getY2() - group.getY1());
g = new EscherGraphics( group, wb, Color.black, verticalPointsPerPixel );
g2d = new EscherGraphics2d( g );
drawChemicalStructure( g2d );

The first thing we do is create the group and set it's coordinates to match what we plan to draw. Next we calculate a reasonable fontSizeMultiplier then create the EscherGraphics object. Since what we really want is a Graphics2d object we create an EscherGraphics2d object and pass in the graphics object we created. Finally we call a routine that draws into the EscherGraphics2d object.(我们要做的第一件事是创建组并设置它的坐标以匹配我们计划绘制的内容。接下来我们计算一个合理的 fontSizeMultiplier 然后创建 EscherGraphics 对象。因为我们真正想要的是一个 Graphics2d 对象，所以我们创建一个 EscherGraphics2d 对象并传入我们创建的图形对象。最后，我们调用一个绘制到 EscherGraphics2d 对象中的例程。)

The vertical points per pixel deserves some more explanation. One of the difficulties in converting Graphics calls into escher drawing calls is that Excel does not have the concept of absolute pixel positions. It measures it's cell widths in 'characters' and the cell heights in points. Unfortunately it's not defined exactly what type of character it's measuring. Presumably this is due to the fact that the Excel will be using different fonts on different platforms or even within the same platform.(每个像素的垂直点需要更多解释。将 Graphics转换为Escher绘图的困难之一是 Excel 没有绝对像素位置的概念。它以“字符”为单位测量其单元格宽度和以点为单位的单元格高度。不幸的是，它没有准确定义它正在测量的字符类型。这大概是由于Excel在不同平台上甚至在同一平台内使用不同的字体。)

Because of this constraint we've had to implement the concept of a verticalPointsPerPixel. This the amount the font should be scaled by when you issue commands such as drawString(). To calculate this value use the follow formula:(由于这个限制，我们不得不实现verticalPointsPerPixel 的概念，它是当您发出诸如 drawString() 之类的命令时字体应该被缩放的值。要计算此值，请使用以下公式：)

multipler = groupHeightInPoints / heightOfGroup

The height of the group is calculated fairly simply by calculating the difference between the y coordinates of the bounding box of the shape. The height of the group can be calculated by using a convenience called HSSFClientAnchor.getAnchorHeightInPoints().(通过计算图形边界框的y坐标之间的距离，可以相当简单地计算组的高度。组的高度可以通过使用HSSFClientAnchor.getAnchorHeightInPoints() 来便捷计算。)

Many of the functions supported by the graphics classes are not complete. Here's some of the functions that are known to work.(图形类支持的很多功能并不完整。以下是一些已知的有用的功能。)

  • fillRect()(fillRect() 填充矩形)
  • fillOval()(fillOval() 填充椭圆)
  • drawString()(drawString() 绘制字符串)
  • drawOval()(drawOval() 绘制椭圆)
  • drawLine()(drawLine() 画线)
  • clearRect()(clearRect() 清除矩形)

Functions that are not supported will return and log a message using the POI logging infrastructure (disabled by default).(不受支持的函数将使用POI日志基础结构返回并记录消息（默认情况下禁用）。)

Outlining(概述)

Outlines are great for grouping sections of information together and can be added easily to columns and rows using the POI API. Here's how:(大纲非常适合将信息部分组合在一起，并且可以使用 POI API 轻松添加到列和行中。如下所示：)

Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
sheet1.groupRow( 5, 14 );
sheet1.groupRow( 7, 14 );
sheet1.groupRow( 16, 19 );
sheet1.groupColumn( 4, 7 );
sheet1.groupColumn( 9, 12 );
sheet1.groupColumn( 10, 11 );
try (OutputStream fileOut = new FileOutputStream(filename)) {
wb.write(fileOut);
}

To collapse (or expand) an outline use the following calls:(要折叠（或展开）大纲，请使用以下调用：)

sheet1.setRowGroupCollapsed( 7, true );
sheet1.setColumnGroupCollapsed( 4, true );

The row/column you choose should contain an already created group. It can be anywhere within the group.(您选择的行/列应包含已创建的组。它可以是组内的任何位置。)

Images(图片)

Images are part of the drawing support. To add an image just call createPicture() on the drawing patriarch. At the time of writing the following types are supported:(图像是绘图支持的一部分。要添加图像，只需在绘图族长上调用 createPicture()。在撰写本文时，它支持以下图片类型：)

  • PNG(PNG)
  • JPG(JPG)
  • DIB(DIB)

It should be noted that any existing drawings may be erased once you add a image to a sheet.(应该注意的是，一旦您将图像添加到工作表，任何现有的图纸都可能会被删除。)

//create a new workbook
//创建一个新的工作簿
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();(.)
//add picture data to this workbook.
//将图片数据添加到此工作簿。
InputStream is = new FileInputStream("image1.jpeg");
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
is.close();
CreationHelper helper = wb.getCreationHelper();
//create sheet
//创建工作表
Sheet sheet = wb.createSheet();
// Create the drawing patriarch. This is the top level container for all shapes.
// 创建绘图族长。这是所有形状的顶级容器。
Drawing drawing = sheet.createDrawingPatriarch();
//add a picture shape
//添加图片形状
ClientAnchor anchor = helper.createClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
//设置图片的左上角，后续调用Picture#resize()会相对于它进行操作
anchor.setCol1(3);
anchor.setRow1(2);
Picture pict = drawing.createPicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
//相对于左上角自动调整图片大小
pict.resize();
//save workbook
//保存工作簿
String file = "picture.xls";
if(wb instanceof XSSFWorkbook) file += "x";
try (OutputStream fileOut = new FileOutputStream(file)) {
wb.write(fileOut);
}
Warning (警告)
Picture.resize() works only for JPEG and PNG. Other formats are not yet supported. (Picture.resize() 仅适用于 JPEG 和 PNG。尚不支持其他格式。)

Reading images from a workbook:(从工作簿中读取图像：)

ist lst = workbook.getAllPictures();
or (Iterator it = lst.iterator(); it.hasNext(); ) {
PictureData pict = (PictureData)it.next();
String ext = pict.suggestFileExtension();
byte[] data = pict.getData();
if (ext.equals("jpeg")){
try (OutputStream out = new FileOutputStream("pict.jpg")) {
out.write(data);
}
}

Named Ranges and Named Cells(命名范围和命名单元格)

Named Range is a way to refer to a group of cells by a name. Named Cell is a degenerate case of Named Range in that the 'group of cells' contains exactly one cell. You can create as well as refer to cells in a workbook by their named range. When working with Named Ranges, the classes org.apache.poi.ss.util.CellReference and org.apache.poi.ss.util.AreaReference are used.(命名范围是一种通过名称引用一组单元格的方法。命名单元格是命名范围的退化情况，因为“单元格组”仅包含一个单元格。您可以通过命名范围创建和引用工作簿中的单元格。使用命名范围时，使用类 org.apache.poi.ss.util.CellReference 和 org.apache.poi.ss.util.AreaReference。)

Note: Using relative values like 'A1:B1' can lead to unexpected moving of the cell that the name points to when working with the workbook in Microsoft Excel, usually using absolute references like '$A$1:$B$1' avoids this, see also this discussion.(注意：在 Microsoft Excel 中使用工作簿时，使用像“A1:B1”这样的相对值可能会导致名称指向的单元格发生意外移动，通常使用像“$A$1:$B$1”这样的绝对引用可以避免这种情况，另见此讨论。)

Creating Named Range / Named Cell(创建命名范围/命名单元格)

// setup code
// 设置代码
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet(sname);
sheet.createRow(0).createCell(0).setCellValue(cvalue);
// 1. create named range for a single cell using areareference
// 1. 使用区域引用为单个单元格创建命名范围
Name namedCell = wb.createName();
namedCell.setNameName(cname + "1");
String reference = sname+"!$A$1:$A$1"; // area reference( 区域参考)
namedCell.setRefersToFormula(reference);
// 2. create named range for a single cell using cellreference
// 2. 使用单元格引用为单个单元格创建命名范围
Name namedCel2 = wb.createName();
namedCel2.setNameName(cname + "2");
reference = sname+"!$A$1"; // cell reference( 单元格引用)
namedCel2.setRefersToFormula(reference);
// 3. create named range for an area using AreaReference
// 3. 使用区域引用为区域创建命名范围
Name namedCel3 = wb.createName();
namedCel3.setNameName(cname + "3");
reference = sname+"!$A$1:$C$5"; // area reference( 区域参考)
namedCel3.setRefersToFormula(reference);
// 4. create named formula
// 4. 创建命名公式
Name namedCel4 = wb.createName();
namedCel4.setNameName("my_sum");
namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");

Reading from Named Range / Named Cell(从命名范围/命名单元格中读取)

// setup code
// 设置代码
String cname = "TestName";
Workbook wb = getMyWorkbook(); // retrieve workbook( 检索工作簿)
// retrieve the named range
// 检索命名范围
int namedCellIdx = wb.getNameIndex(cellName);
Name aNamedCell = wb.getNameAt(namedCellIdx);
// retrieve the cell at the named range and test its contents
// 检索指定命名范围内的单元格并测试其内容
AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
CellReference[] crefs = aref.getAllReferencedCells();
for (int i=0; i<crefs.length; i++) {
Sheet s = wb.getSheet(crefs[i].getSheetName());
Row r = sheet.getRow(crefs[i].getRow());
Cell c = r.getCell(crefs[i].getCol());
// extract the cell contents based on cell type etc.
// 根据单元格类型等提取单元格内容
}

Reading from non-contiguous Named Ranges(从不连续的命名范围中读取)

// Setup code
// 设置代码
String cname = "TestName";
Workbook wb = getMyWorkbook(); // retrieve workbook( 检索工作簿)
// Retrieve the named range
// Will be something like "$C$10,$D$12:$D$14";
// 检索命名范围
// 类似于 "$C$10,$D$12:$D$14";
int namedCellIdx = wb.getNameIndex(cellName);
Name aNamedCell = wb.getNameAt(namedCellIdx);
// Retrieve the cell at the named range and test its contents
// Will get back one AreaReference for C10, and
// another for D12 to D14
// 检索指定范围内的单元格并测试其内容
// 将返回C10的一个区域引用，以及D12到D14的另一个区域引用
AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula());
for (int i=0; i<arefs.length; i++) {
// Only get the corners of the Area
// (use arefs[i].getAllReferencedCells() to get all cells)
// 只获取区域的角落（使用 arefs[i].getAllReferencedCells() 获取所有单元格）
CellReference[] crefs = arefs[i].getCells();
for (int j=0; j<crefs.length; j++) {
// Check it turns into real stuff
// 检查它的内容并获得相关对象
Sheet s = wb.getSheet(crefs[j].getSheetName());
Row r = s.getRow(crefs[j].getRow());
Cell c = r.getCell(crefs[j].getCol());
// Do something with this corner cell
// 用这个角单元做一些事情
}
}

Note, when a cell is deleted, Excel does not delete the attached named range. As result, workbook can contain named ranges that point to cells that no longer exist. You should check the validity of a reference before constructing AreaReference(请注意，删除单元格时，Excel 不会删除附加的命名范围。因此，工作簿可以包含指向不再存在的单元格的命名范围。您应该在构建 AreaReference 之前检查引用的有效性)

if(name.isDeleted()){
//named range points to a deleted cell.
//命名范围指向已删除的单元格。
} else {
AreaReference ref = new AreaReference(name.getRefersToFormula());
}

Cell Comments - HSSF and XSSF(单元格注释 - HSSF 和 XSSF)

A comment is a rich text note that is attached to & associated with a cell, separate from other cell content. Comment content is stored separate from the cell, and is displayed in a drawing object (like a text box) that is separate from, but associated with, a cell(注释是附加到单元格并与单元格相关联的富文本注释，与其他单元格内容不相干。注释内容与单元格分开存储，并显示在与单元格分开但与单元格相关联的绘图对象（如文本框）中)

Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();(.)
CreationHelper factory = wb.getCreationHelper();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(3);
Cell cell = row.createCell(5);
cell.setCellValue("F4");
Drawing drawing = sheet.createDrawingPatriarch();
// When the comment box is visible, have it show in a 1x3 space
// 当注释框可见时，让它显示在 1x3 的空间中
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex()+1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum()+3);
// Create the comment and set the text+author
// 创建注释并设置注释内容为文本+作者
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString("Hello, World!");
comment.setString(str);
comment.setAuthor("Apache POI");
// Assign the comment to the cell
// 将注释分配给单元格
cell.setCellComment(comment);
String fname = "comment-xssf.xls";
if(wb instanceof XSSFWorkbook) fname += "x";
try (OutputStream out = new FileOutputStream(fname)) {
wb.write(out);
}
wb.close();

Reading cell comments(读取单元格注释)

Cell cell = sheet.get(3).getColumn(1);
Comment comment = cell.getCellComment();
if (comment != null) {
RichTextString str = comment.getString();
String author = comment.getAuthor();
}
// alternatively you can retrieve cell comments by (row, column)
// 或者，您可以通过（行，列）检索单元格注释
comment = sheet.getCellComment(3, 1);

To get all the comments on a sheet:(获取工作表上的所有注释：)

Map<CellAddress, Comment> comments = sheet.getCellComments();
Comment commentA1 = comments.get(new CellAddress(0, 0));
Comment commentB1 = comments.get(new CellAddress(0, 1));
for (Entry<CellAddress, ? extends Comment> e : comments.entrySet()) {
CellAddress loc = e.getKey();
Comment comment = e.getValue();
System.out.println("Comment at " + loc + ": " +
"[" + comment.getAuthor() + "] " + comment.getString().getString());
}

Adjust column width to fit the contents(调整列宽以适应内容)

Sheet sheet = workbook.getSheetAt(0);
sheet.autoSizeColumn(0); //adjust width of the first column(调整第一列的宽度)
sheet.autoSizeColumn(1); //adjust width of the second column(调整第二列的宽度)

For SXSSFWorkbooks only, because the random access window is likely to exclude most of the rows in the worksheet, which are needed for computing the best-fit width of a column, the columns must be tracked for auto-sizing prior to flushing any rows.(仅对于 SXSSFWorkbooks，由于随机访问窗口可能会排除工作表中的大多数行，而这些行是计算列的最佳适应宽度所需的，因此必须在刷新任何行之前跟踪列以自动调整大小。)

SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet sheet = workbook.createSheet();
sheet.trackColumnForAutoSizing(0);
sheet.trackColumnForAutoSizing(1);
// If you have a Collection of column indices, see SXSSFSheet#trackColumnForAutoSizing(Collection<Integer>)
// or roll your own for-loop.
// Alternatively, use SXSSFSheet#trackAllColumnsForAutoSizing() if the columns that will be auto-sized aren't
// known in advance or you are upgrading existing code and are trying to minimize changes. Keep in mind
// that tracking all columns will require more memory and CPU cycles, as the best-fit width is calculated
// on all tracked columns on every row that is flushed.
// 如果您有列索引的集合，请参阅 SXSSFSheet#trackColumnForAutoSizing(Collection) 或遍历您自己的 for 循环
// 或者，如果事先不知道将自动调整大小的列，或者您正在升级现有代码并试图最小化更改，则使用 SXSSFSheet#trackAllColumnsForAutoSizing()。请记住，跟踪所有列将需要更多的内存和CPU周期，因为在刷新的每一行的所有跟踪列上都会计算最佳拟合宽度。
// create some cells
// 创建一些单元格
for (int r=0; r < 10; r++) {
Row row = sheet.createRow(r);
for (int c; c < 10; c++) {
Cell cell = row.createCell(c);
cell.setCellValue("Cell " + c.getAddress().formatAsString());
}
}
// Auto-size the columns.
// 自动调整列大小。
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);

Note, that Sheet#autoSizeColumn() does not evaluate formula cells, the width of formula cells is calculated based on the cached formula result. If your workbook has many formulas then it is a good idea to evaluate them before auto-sizing.(请注意，Sheet#autoSizeColumn() 不会计算公式单元格，公式单元格的宽度是根据缓存的公式结果计算的。如果您的工作簿有许多公式，那么在自动调整大小之前评估它们是个好主意。)

Warning (警告)
To calculate column width Sheet.autoSizeColumn uses Java2D classes that throw exception if graphical environment is not available. In case if graphical environment is not available, you must tell Java that you are running in headless mode and set the following system property: java.awt.headless=true . You should also ensure that the fonts you use in your workbook are available to Java. (计算列宽 Sheet.autoSizeColumn 使用 Java2D 类，如果图形环境不可用，则会抛出异常。如果图形环境不可用，您必须告诉 Java 您正在以无头模式运行并设置以下系统属性： java.awt.headless=true 。您还应该确保您在工作簿中使用的字体可用于 Java。)

How to read hyperlinks(如何读取超链接)

Sheet sheet = workbook.getSheetAt(0);
Cell cell = sheet.getRow(0).getCell(0);
Hyperlink link = cell.getHyperlink();
if(link != null){
System.out.println(link.getAddress());
}

How to create hyperlinks(如何创建超链接)

Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();(.)
CreationHelper createHelper = wb.getCreationHelper();
//cell style for hyperlinks
//by default hyperlinks are blue and underlined
//超链接的单元格样式
//默认情况下超链接是蓝色的并带有下划线
CellStyle hlink_style = wb.createCellStyle();
Font hlink_font = wb.createFont();
hlink_font.setUnderline(Font.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
Cell cell;
Sheet sheet = wb.createSheet("Hyperlinks");
//URL
//网址
cell = sheet.createRow(0).createCell(0);
cell.setCellValue("URL Link");
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("https://poi.apache.org/");(// 设置链接地址)
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//link to a file in the current directory
//链接到当前目录中的文件
cell = sheet.createRow(1).createCell(0);
cell.setCellValue("File Link");
link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
link.setAddress("link1.xls");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//e-mail link
//电子邮件链接
cell = sheet.createRow(2).createCell(0);
cell.setCellValue("Email Link");
link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
//注意，如果主题包含空格，请确保它们是url编码
link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
//link to a place in this workbook
//链接到此工作簿中的某个位置
//create a target sheet and cell
//创建目标工作表和单元格
Sheet sheet2 = wb.createSheet("Target Sheet");
sheet2.createRow(0).createCell(0).setCellValue("Target Cell");
cell = sheet.createRow(3).createCell(0);
cell.setCellValue("Worksheet Link");
Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
link2.setAddress("'Target Sheet'!A1");
cell.setHyperlink(link2);
cell.setCellStyle(hlink_style);
try (OutputStream out = new FileOutputStream("hyperinks.xlsx")) {
wb.write(out);
}
wb.close();

Data Validations(数据验证)

As of version 3.8, POI has slightly different syntax to work with data validations with .xls and .xlsx formats.(从 3.8 版开始，POI 使用 .xls 和 .xlsx 格式的数据验证的语法略有不同。)

hssf.usermodel (binary .xls format)(hssf.usermodel (binary .xls format))

Check the value a user enters into a cell against one or more predefined value(s).(对照一个或多个预定义值检查用户在单元格中输入的值。)

The following code will limit the value the user can enter into cell A1 to one of three integer values, 10, 20 or 30.(以下代码将用户可以在单元格 A1 中输入的值限制为三个整数值之一，即 10、20 或 30。)

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(
0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
new String[]{"10", "20", "30"});
DataValidation dataValidation = new HSSFDataValidation
(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);

Drop Down Lists:(下拉列表：)

This code will do the same but offer the user a drop down list to select a value from.(此代码将执行相同的操作，但会为用户提供一个下拉列表以从中选择一个值。)

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(
0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
new String[]{"10", "20", "30"});
DataValidation dataValidation = new HSSFDataValidation
(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);

Messages On Error:(错误消息：)

To create a message box that will be shown to the user if the value they enter is invalid.(创建一个消息框，如果用户输入的值无效，该消息框将显示。)

dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("Box Title", "Message Text");

Replace 'Box Title' with the text you wish to display in the message box's title bar and 'Message Text' with the text of your error message.(将“框标题”替换为您希望在消息框标题栏中显示的文本，将“消息文本”替换为错误消息的文本。)

Prompts:(提示：)

To create a prompt that the user will see when the cell containing the data validation receives focus(创建一个提示，当包含数据验证的单元格获得焦点时，用户将看到该提示)

dataValidation.createPromptBox("Title", "Message Text");
dataValidation.setShowPromptBox(true);

The text encapsulated in the first parameter passed to the createPromptBox() method will appear emboldened and as a title to the prompt whilst the second will be displayed as the text of the message. The createExplicitListConstraint() method can be passed and array of String(s) containing interger, floating point, dates or text values.(传递给 createPromptBox() 方法的第一个参数中封装的文本将显示为粗体并作为提示的标题，而第二个参数将显示为消息的文本。createExplicitListConstraint()方法可以使用包含整数、浮点、日期或文本值的字符串数组。)

Further Data Validations:(进一步的数据验证：)

To obtain a validation that would check the value entered was, for example, an integer between 10 and 100, use the DVConstraint.createNumericConstraint(int, int, String, String) factory method.(要获得并检查输入的值是否为 10 到 100 之间的整数，请使用 DVConstraint.createNumericConstraint(int, int, String, String) 工厂方法。)

dvConstraint = DVConstraint.createNumericConstraint(
DVConstraint.ValidationType.INTEGER,
DVConstraint.OperatorType.BETWEEN, "10", "100");

Look at the javadoc for the other validation and operator types; also note that not all validation types are supported for this method. The values passed to the two String parameters can be formulas; the '=' symbol is used to denote a formula(查看 javadoc 以了解其他验证和运算符类型；另请注意，此方法并非支持所有验证类型。传递给两个 String 参数的值可以是公式； '=' 符号用于表示公式)

dvConstraint = DVConstraint.createNumericConstraint(
DVConstraint.ValidationType.INTEGER,
DVConstraint.OperatorType.BETWEEN, "=SUM(A1:A3)", "100");

It is not possible to create a drop down list if the createNumericConstraint() method is called, the setSuppressDropDownArrow(false) method call will simply be ignored.(如果调用 createNumericConstraint() 方法，则无法创建下拉列表，setSuppressDropDownArrow(false) 方法调用将被忽略。)

Date and time constraints can be created by calling the createDateConstraint(int, String, String, String) or the createTimeConstraint(int, String, String). Both are very similar to the above and are explained in the javadoc.(可以通过调用 createDateConstraint(int, String, String, String) 或 createTimeConstraint(int, String, String) 来创建日期和时间约束。两者与上述所说非常相似，并在 javadoc 中有说明。)

Creating Data Validations From Spreadsheet Cells.(从电子表格单元格创建数据验证。)

The contents of specific cells can be used to provide the values for the data validation and the DVConstraint.createFormulaListConstraint(String) method supports this. To specify that the values come from a contiguous range of cells do either of the following:(特定单元格的内容可用于为数据验证提供值，DVConstraint.createFormulaListConstraint(String) 方法支持这一点。要指定验证的值来自连续的单元格范围，请执行以下任一操作：)

dvConstraint = DVConstraint.createFormulaListConstraint("$A$1:$A$3");

or(或者)

Name namedRange = workbook.createName();
namedRange.setNameName("list1");
namedRange.setRefersToFormula("$A$1:$A$3");
dvConstraint = DVConstraint.createFormulaListConstraint("list1");

and in both cases the user will be able to select from a drop down list containing the values from cells A1, A2 and A3.(在这两种情况下，用户都可以从包含单元格 A1、A2 和 A3 的值的下拉列表中进行选择。)

The data does not have to be as the data validation. To select the data from a different sheet however, the sheet must be given a name when created and that name should be used in the formula. So assuming the existence of a sheet named 'Data Sheet' this will work:(数据不一定要做数据验证。但是，要从不同的工作表中选择数据，必须在创建工作表时为工作表指定名称，并且要在公式中使用该名称。因此，假设存在名为“Data Sheet”的工作表，应该这样写：)

Name namedRange = workbook.createName();
namedRange.setNameName("list1");
namedRange.setRefersToFormula("'Data Sheet'!$A$1:$A$3");
dvConstraint = DVConstraint.createFormulaListConstraint("list1");

as will this:(就像这样：)

dvConstraint = DVConstraint.createFormulaListConstraint("'Data Sheet'!$A$1:$A$3");

whilst this will not:(显然这不会起作用：)

Name namedRange = workbook.createName();
namedRange.setNameName("list1");
namedRange.setRefersToFormula("'Sheet1'!$A$1:$A$3");
dvConstraint = DVConstraint.createFormulaListConstraint("list1");

and nor will this:(这也不会：)

dvConstraint = DVConstraint.createFormulaListConstraint("'Sheet1'!$A$1:$A$3");

xssf.usermodel (.xlsx format)(xssf.usermodel (.xlsx format))

Data validations work similarly when you are creating an xml based, SpreadsheetML, workbook file; but there are differences. Explicit casts are required, for example, in a few places as much of the support for data validations in the xssf stream was built into the unifying ss stream, of which more later. Other differences are noted with comments in the code.(当您创建基于 xml 的 SpreadsheetML 工作簿文件时，数据验证的工作方式类似；但也有区别。例如，在一些地方需要显式强制转换，因为xssf流中对数据验证的大部分支持都内置在统一的ss流中，后续会有更多支持。代码中的注释说明了其他差异)

Check the value the user enters into a cell against one or more predefined value(s).(对照一个或多个预定义值检查用户在单元格中输入的值。)

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Data Validation");
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
XSSFDataValidation validation =(XSSFDataValidation)dvHelper.createValidation(
dvConstraint, addressList);
// Here the boolean value false is passed to the setSuppressDropDownArrow()
// method. In the hssf.usermodel examples above, the value passed to this
// method is true.
// 这里布尔值false被传递给setSuppressDropDownArrow()方法。 在上面的 hssf.usermodel 示例中，传递给此方法的值为 true。
validation.setSuppressDropDownArrow(false);
// Note this extra method call. If this method call is omitted, or if the
// boolean value false is passed, then Excel will not validate the value the
// user enters into the cell.
// 请注意这个额外的方法调用。 如果省略此方法调用，或者传递布尔值 false，则 Excel 将不会验证用户在单元格中输入的值。
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

Drop Down Lists:(下拉列表：)

This code will do the same but offer the user a drop down list to select a value from.(此代码将执行相同的操作，但会为用户提供一个下拉列表以从中选择一个值。)

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Data Validation");
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(
dvConstraint, addressList);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

Note that the call to the setSuppressDropDowmArrow() method can either be simply excluded or replaced with:(请注意，对 setSuppressDropDowmArrow() 方法的调用可以不写或写成如下形式：)

validation.setSuppressDropDownArrow(true);

Prompts and Error Messages:(提示和错误信息：)

These both exactly mirror the hssf.usermodel so please refer to the 'Messages On Error:' and 'Prompts:' sections above.(这些和hssf.usermodel中的相同，因此请参阅上面的“Messages On Error:”和“Prompts:”部分。)

Further Data Validations:(进一步的数据验证：)

To obtain a validation that would check the value entered was, for example, an integer between 10 and 100, use the XSSFDataValidationHelper(s) createNumericConstraint(int, int, String, String) factory method.(例如，要获得并检查输入值是否为 10 到 100 之间的整数，请使用 XSSFDataValidationHelper(s) createNumericConstraint(int, int, String, String) 工厂方法。)

SSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createNumericConstraint(
XSSFDataValidationConstraint.ValidationType.INTEGER,
XSSFDataValidationConstraint.OperatorType.BETWEEN,
"10", "100");

The values passed to the final two String parameters can be formulas; the '=' symbol is used to denote a formula. Thus, the following would create a validation the allows values only if they fall between the results of summing two cell ranges(传递给最后两个 String 参数的值可以是公式； '=' 符号用于表示公式。因此，以下内容将创建一个验证，仅当值介于两个单元格范围求和的结果之间时，才允许进行验证)

XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createNumericConstraint(
XSSFDataValidationConstraint.ValidationType.INTEGER,
XSSFDataValidationConstraint.OperatorType.BETWEEN,
"=SUM(A1:A10)", "=SUM(B24:B27)");

It is not possible to create a drop down list if the createNumericConstraint() method is called, the setSuppressDropDownArrow(true) method call will simply be ignored.(如果调用 createNumericConstraint() 方法，则无法创建下拉列表，setSuppressDropDownArrow(true) 方法调用将被忽略。)

Please check the javadoc for other constraint types as examples for those will not be included here. There are, for example, methods defined on the XSSFDataValidationHelper class allowing you to create the following types of constraint; date, time, decimal, integer, numeric, formula, text length and custom constraints.(请检查 javadoc 以了解其他约束类型，因为这里没有这些约束类型的示例。例如，在 XSSFDataValidationHelper 类中定义的方法允许您创建以下类型的约束；日期、时间、小数、整数、数字、公式、文本长度和自定义约束。)

Creating Data Validations From Spread Sheet Cells:(从电子表格单元格创建数据验证：)

One other type of constraint not mentioned above is the formula list constraint. It allows you to create a validation that takes it value(s) from a range of cells. This code(上面未提及的另一种类型的约束是公式列表约束。它允许您创建一个从一系列单元格中获取值的验证。如下代码)

XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createFormulaListConstraint("$A$1:$F$1");

would create a validation that took it's values from cells in the range A1 to F1.(将创建一个从 A1 到 F1 范围内的单元格值的验证。)

The usefulness of this technique can be extended if you use named ranges like this;(如果您使用这样的命名范围，则可以扩展此技术的实用性；)

XSSFName name = workbook.createName();
name.setNameName("data");
name.setRefersToFormula("$B$1:$F$1");
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createFormulaListConstraint("data");
CellRangeAddressList addressList = new CellRangeAddressList(
0, 0, 0, 0);
XSSFDataValidation validation = (XSSFDataValidation)
dvHelper.createValidation(dvConstraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

OpenOffice Calc has slightly different rules with regard to the scope of names. Excel supports both Workbook and Sheet scope for a name but Calc does not, it seems only to support Sheet scope for a name. Thus it is often best to fully qualify the name for the region or area something like this;(OpenOffice Calc 在名称范围方面的规则略有不同。Excel支持工作簿和工作表范围的名称，但Calc不支持，它似乎只支持工作表范围的名称。因此，通常最好完全限定该地区或区域的名称，例如：)

XSSFName name = workbook.createName();
name.setNameName("data");
name.setRefersToFormula("'Data Validation'!$B$1:$F$1");
....

This does open a further, interesting opportunity however and that is to place all of the data for the validation(s) into named ranges of cells on a hidden sheet within the workbook. These ranges can then be explicitly identified in the setRefersToFormula() method argument.(然而，这带来了另一个有趣的机会，那就是将所有用于验证的数据放入工作簿中一个隐藏工作表上的命名单元格区域。然后可以在setRefersToFormula（）方法参数中显式标识这些范围)

ss.usermodel(ss.usermodel)

The classes within the ss.usermodel package allow developers to create code that can be used to generate both binary (.xls) and SpreadsheetML (.xlsx) workbooks.(ss.usermodel 包中的类允许开发人员创建可用于生成二进制 (.xls) 和 SpreadsheetML (.xlsx) 工作簿的代码。)

The techniques used to create data validations share much in common with the xssf.usermodel examples above. As a result just one or two examples will be presented here.(用于创建数据验证的技术与上面的 xssf.usermodel 示例有很多共同之处。因此，这里只介绍一两个示例。)

Check the value the user enters into a cell against one or more predefined value(s).(对照一个或多个预定义值检查用户在单元格中输入的值。)

Workbook workbook = new XSSFWorkbook(); // or new HSSFWorkbook(.)
Sheet sheet = workbook.createSheet("Data Validation");
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(
new String[]{"13", "23", "33"});
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = dvHelper.createValidation(
dvConstraint, addressList);
// Note the check on the actual type of the DataValidation object.
// If it is an instance of the XSSFDataValidation class then the
// boolean value 'false' must be passed to the setSuppressDropDownArrow()
// method and an explicit call made to the setShowErrorBox() method.
// 注意检查 DataValidation 对象的实际类型。
// 如果它是XSSFDataValidation类的实例，则必须将布尔值“false”传递给setSuppressDropDownArrow（）方法，并显式调用setShowErrorBox（）方法。
if(validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(false);
validation.setShowErrorBox(true);
}
else {
// If the Datavalidation contains an instance of the HSSFDataValidation
// class then 'true' should be passed to the setSuppressDropDownArrow()
// method and the call to setShowErrorBox() is not necessary.
// 如果 Datavalidation 包含 HSSFDataValidation 类的实例，则应该将“true”传递给 setSuppressDropDownArrow() 方法，并且不需要调用 setShowErrorBox()
validation.setSuppressDropDownArrow(true);
}
sheet.addValidationData(validation);

Drop Down Lists:(下拉列表：)

This code will do the same but offer the user a drop down list to select a value from.(此代码将执行相同的操作，但会为用户提供一个下拉列表以从中选择一个值。)

Workbook workbook = new XSSFWorkbook(); // or new HSSFWorkbook(.)
Sheet sheet = workbook.createSheet("Data Validation");
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(
new String[]{"13", "23", "33"});
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = dvHelper.createValidation(
dvConstraint, addressList);
// Note the check on the actual type of the DataValidation object.
// If it is an instance of the XSSFDataValidation class then the
// boolean value 'false' must be passed to the setSuppressDropDownArrow()
// method and an explicit call made to the setShowErrorBox() method.
// 注意检查 DataValidation 对象的实际类型。
// 如果它是XSSFDataValidation类的实例，则必须将布尔值“false”传递给setSuppressDropDownArrow（）方法，并显式调用setShowErrorBox（）方法。
if(validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
}
else {
// If the Datavalidation contains an instance of the HSSFDataValidation
// class then 'true' should be passed to the setSuppressDropDownArrow()
// method and the call to setShowErrorBox() is not necessary.
// 如果 Datavalidation 包含 HSSFDataValidation 类的实例，则应该将“true”传递给 setSuppressDropDownArrow() 方法，并且不需要调用 setShowErrorBox()
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);

Prompts and Error Messages:(提示和错误信息：)

These both exactly mirror the hssf.usermodel so please refer to the 'Messages On Error:' and 'Prompts:' sections above.(这些都和 hssf.usermodel一样，因此请参阅上面的'Messages On Error:'和'Prompts:'部分。)

As the differences between the ss.usermodel and xssf.usermodel examples are small - restricted largely to the way the DataValidationHelper is obtained, the lack of any need to explicitly cast data types and the small difference in behaviour between the hssf and xssf interpretation of the setSuppressDropDowmArrow() method, no further examples will be included in this section.(由于 ss.usermodel 和 xssf.usermodel 示例之间的差异很小 - 主要限于获取 DataValidationHelper 的方式，不需要显式转换数据类型，以及SetSuppressDropDowmorrow（）方法的hssf和xssf解释之间的行为差异很小，本节不再举例。)

Advanced Data Validations.(高级数据验证。)

Dependent Drop Down Lists.(从属下拉列表。)

In some cases, it may be necessary to present to the user a sheet which contains more than one drop down list. Further, the choice the user makes in one drop down list may affect the options that are presented to them in the second or subsequent drop down lists. One technique that may be used to implement this behaviour will now be explained.(在某些情况下，可能需要向用户呈现包含多个下拉列表的工作表。此外，用户在一个下拉列表中所做的选择可能会影响在第二个或后续下拉列表中呈现给他们的选项。现在将讲述可用于实现该行为的一种技术。)

There are two keys to the technique; one is to use named areas or regions of cells to hold the data for the drop down lists, the second is to use the INDIRECT() function to convert between the name and the actual addresses of the cells. In the example section there is a complete working example- called LinkedDropDownLists.java - that demonstrates how to create linked or dependent drop down lists. Only the more relevant points are explained here.(该技术有两个关键；一种是使用命名区域或单元格区域来保存下拉列表的数据，第二种是使用 INDIRECT() 函数在单元格的名称和实际地址之间进行转换。在示例部分有一个完整的工作示例——名为 LinkedDropDownLists.java——它演示了如何创建链接或依赖下拉列表。这里只解释更相关的点。)

To create two drop down lists where the options shown in the second depend upon the selection made in the first, begin by creating a named region of cells to hold all of the data for populating the first drop down list. Next, create a data validation that will look to this named area for its data, something like this;(要创建两个下拉列表，其中第二个中显示的选项取决于第一个中所做的选择，首先创建一个命名的单元格区域来保存用于填充第一个下拉列表的所有数据。接下来，创建一个数据验证，它将在这个命名区域中查找其数据，如下所示；)

CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(
"CHOICES");
DataValidation validation = dvHelper.createValidation(
dvConstraint, addressList);
sheet.addValidationData(validation);

Note that the name of the area - in the example above it is 'CHOICES' - is simply passed to the createFormulaListConstraint() method. This is sufficient to cause Excel to populate the drop down list with data from that named region.(请注意，区域的名称（在上面的示例中为“CHOICES”）只是传递给 createFormulaListConstraint() 方法。这足以使 Excel 使用来自该命名区域的数据填充下拉列表。)

Next, for each of the options the user could select in the first drop down list, create a matching named region of cells. The name of that region should match the text the user could select in the first drop down list. Note, in the example, all upper case letters are used in the names of the regions of cells.(接下来，对于用户可以在第一个下拉列表中选择的每个选项，创建一个匹配的命名单元格区域。该区域的名称应与用户可以在第一个下拉列表中选择的文本相匹配。请注意，在示例中，所有大写字母都用于单元格区域的名称中。)

Now, very similar code can be used to create a second, linked, drop down list;(现在，可以使用非常相似的代码来创建第二个链接的下拉列表；)

CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1);
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(
"INDIRECT(UPPER($A$1))");
DataValidation validation = dvHelper.createValidation(
dvConstraint, addressList);
sheet.addValidationData(validation);

The key here is in the following Excel function - INDIRECT(UPPER($A$1)) - which is used to populate the second, linked, drop down list. Working from the inner-most pair of brackets, it instructs Excel to look at the contents of cell A1, to convert what it reads there into upper case – as upper case letters are used in the names of each region - and then convert this name into the addresses of those cells that contain the data to populate another drop down list.(这里的关键在于以下 Excel 函数 - INDIRECT(UPPER($A$1)) - 用于填充第二个链接的下拉列表。从最里面的一对括号开始，它指示 Excel 查看单元格 A1 的内容，将其中读取的内容转换为大写字母 - 因为每个区域的名称中使用大写字母 - 然后转换此名称到包含数据的单元格的地址以填充另一个下拉列表。)

Embedded Objects(嵌入对象)

It is possible to perform more detailed processing of an embedded Excel, Word or PowerPoint document, or to work with any other type of embedded object.(可以对嵌入的 Excel、Word 或 PowerPoint 文档执行更详细的处理，或者使用任何其他类型的嵌入对象。)

HSSF:(HSSF：)

POIFSFileSystem fs = new POIFSFileSystem(new File("excel_with_embeded.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fs);
for (HSSFObjectData obj : workbook.getAllEmbeddedObjects()) {
//the OLE2 Class Name of the object
//对象的OLE2类名
String oleName = obj.getOLE2ClassName();
if (oleName.equals("Worksheet")) {
DirectoryNode dn = (DirectoryNode) obj.getDirectory();
HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(dn, false);
//System.out.println(entry.getName() + ": " + embeddedWorkbook.getNumberOfSheets());
//System.out.println(entry.getName() + ":" + embeddedWorkbook.getNumberOfSheets());
} else if (oleName.equals("Document")) {
DirectoryNode dn = (DirectoryNode) obj.getDirectory();
HWPFDocument embeddedWordDocument = new HWPFDocument(dn);
//System.out.println(entry.getName() + ": " + embeddedWordDocument.getRange().text());
//System.out.println(entry.getName() + ":" + embeddedWordDocument.getRange().text());
} else if (oleName.equals("Presentation")) {
DirectoryNode dn = (DirectoryNode) obj.getDirectory();
SlideShow<?,?> embeddedPowerPointDocument = new HSLFSlideShow(dn);
//System.out.println(entry.getName() + ": " + embeddedPowerPointDocument.getSlides().length);
//System.out.println(entry.getName() + ":" + embeddedPowerPointDocument.getSlides().length);
} else {
if(obj.hasDirectoryEntry()){
// The DirectoryEntry is a DocumentNode. Examine its entries to find out what it is
// DirectoryEntry 是一个 DocumentNode。检查它的条目以找出它是什么
DirectoryNode dn = (DirectoryNode) obj.getDirectory();
for (Entry entry : dn) {
//System.out.println(oleName + "." + entry.getName());
//System.out.println(oleName + "." + entry.getName());
}
} else {
// There is no DirectoryEntry
// Recover the object's data from the HSSFObjectData instance.
// 没有 DirectoryEntry
// 从 HSSFObjectData 实例中恢复对象的数据。
byte[] objectData = obj.getObjectData();
}
}
}

XSSF:(XSSF：)

XSSFWorkbook workbook = new XSSFWorkbook("excel_with_embeded.xlsx");
for (PackagePart pPart : workbook.getAllEmbeddedParts()) {
String contentType = pPart.getContentType();
// Excel Workbook - either binary or OpenXML
// Excel 工作簿 - 二进制或 OpenXML
if (contentType.equals("application/vnd.ms-excel")) {
HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream());
}
// Excel Workbook - OpenXML file format
// Excel 工作簿 - OpenXML 文件格式
else if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
OPCPackage docPackage = OPCPackage.open(pPart.getInputStream());
XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(docPackage);
}
// Word Document - binary (OLE2CDF) file format
// Word 文档 - 二进制 (OLE2CDF) 文件格式
else if (contentType.equals("application/msword")) {
HWPFDocument document = new HWPFDocument(pPart.getInputStream());
}
// Word Document - OpenXML file format
// Word 文档 - OpenXML 文件格式
else if (contentType.equals("application/vnd.openxmlformats-officedocument.wordprocessingml.document")) {
OPCPackage docPackage = OPCPackage.open(pPart.getInputStream());
XWPFDocument document = new XWPFDocument(docPackage);
}
// PowerPoint Document - binary file format
// PowerPoint 文档 - 二进制文件格式
else if (contentType.equals("application/vnd.ms-powerpoint")) {
HSLFSlideShow slideShow = new HSLFSlideShow(pPart.getInputStream());
}
// PowerPoint Document - OpenXML file format
// PowerPoint 文档 - OpenXML 文件格式
else if (contentType.equals("application/vnd.openxmlformats-officedocument.presentationml.presentation")) {
OPCPackage docPackage = OPCPackage.open(pPart.getInputStream());
XSLFSlideShow slideShow = new XSLFSlideShow(docPackage);
}
// Any other type of embedded object.
// 任何其他类型的嵌入对象。
else {
System.out.println("Unknown Embedded Document: " + contentType);
InputStream inputStream = pPart.getInputStream();
}
}

(Since POI-3.7)(（自 POI-3.7 起）)

Autofilters(自动过滤器)

Workbook wb = new HSSFWorkbook(); //or new XSSFWorkbook();(.)
Sheet sheet = wb.createSheet();
sheet.setAutoFilter(CellRangeAddress.valueOf("C5:F200"));

Conditional Formatting(条件格式)

Workbook workbook = new HSSFWorkbook(); // or new XSSFWorkbook();(.)
Sheet sheet = workbook.createSheet();
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0");
FontFormatting fontFmt = rule1.createFontFormatting();
fontFmt.setFontStyle(true, false);
fontFmt.setFontColorIndex(IndexedColors.DARK_RED.index);
BorderFormatting bordFmt = rule1.createBorderFormatting();
bordFmt.setBorderBottom(BorderStyle.THIN);
bordFmt.setBorderTop(BorderStyle.THICK);
bordFmt.setBorderLeft(BorderStyle.DASHED);
bordFmt.setBorderRight(BorderStyle.DOTTED);
PatternFormatting patternFmt = rule1.createPatternFormatting();
patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);
ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "-10", "10");
ConditionalFormattingRule [] cfRules =
{
rule1, rule2
};
CellRangeAddress[] regions = {
CellRangeAddress.valueOf("A3:A5")
};
sheetCF.addConditionalFormatting(regions, cfRules);

See more examples on Excel conditional formatting in ConditionalFormats.java(在 ConditionalFormats.java中查看有关 Excel 条件格式的更多示例)

Hiding and Un-Hiding Rows(隐藏和显示行)

Using Excel, it is possible to hide a row on a worksheet by selecting that row (or rows), right clicking once on the right hand mouse button and selecting 'Hide' from the pop-up menu that appears.(使用 Excel，可以通过选择该行（或多行）、单击鼠标右键并从出现的弹出菜单中选择“隐藏”来隐藏工作表上的一行。)

To emulate this using POI, simply call the setZeroHeight() method on an instance of either XSSFRow or HSSFRow (the method is defined on the ss.usermodel.Row interface that both classes implement), like this:(要使用 POI 进行模拟，只需在 XSSFRow 或 HSSFRow 的实例上调用 setZeroHeight() 方法（该方法在两个类都实现的 ss.usermodel.Row 接口上定义），如下所示：)

Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook()(.)
Sheet sheet = workbook.createSheet(0);
Row row = workbook.createRow(0);
row.setZeroHeight();

If the file were saved away to disc now, then the first row on the first sheet would not be visible.(如果现在将文件保存到光盘，则第一个工作表上的第一行将不可见。)

Using Excel, it is possible to unhide previously hidden rows by selecting the row above and the row below the one that is hidden and then pressing and holding down the Ctrl key, the Shift and the pressing the number 9 before releasing them all.(使用 Excel，可以显示隐藏的行，方法是选择隐藏行的上方和下方的行，然后按住 Ctrl 键、Shift 和按数字 9，然后再释放它们。)

To emulate this behaviour using POI do something like this:(要使用 POI 模拟此行为，请执行以下操作：)

Workbook workbook = WorkbookFactory.create(new File(.......));
Sheet = workbook.getSheetAt(0);
Iterator<Row> row Iter = sheet.iterator();
while(rowIter.hasNext()) {
Row row = rowIter.next();
if(row.getZeroHeight()) {
row.setZeroHeight(false);
}
}

If the file were saved away to disc now, any previously hidden rows on the first sheet of the workbook would now be visible.(如果现在将文件保存到光盘上，那么现在可以看到工作簿第一张表上以前隐藏的行。)

The example illustrates two features. Firstly, that it is possible to unhide a row simply by calling the setZeroHeight() method and passing the boolean value 'false'. Secondly, it illustrates how to test whether a row is hidden or not. Simply call the getZeroHeight() method and it will return 'true' if the row is hidden, 'false' otherwise.(该示例说明了两个功能。首先，可以简单地通过调用 setZeroHeight() 方法并传递布尔值“false”来取消隐藏一行。其次，它说明了如何测试一行是否隐藏。只需调用 getZeroHeight() 方法，如果该行被隐藏，它将返回“true”，否则返回“false”。)

Setting Cell Properties(设置单元格属性)

Sometimes it is easier or more efficient to create a spreadsheet with basic styles and then apply special styles to certain cells such as drawing borders around a range of cells or setting fills for a region. CellUtil.setCellProperties lets you do that without creating a bunch of unnecessary intermediate styles in your spreadsheet.(有时，创建具有基本样式的电子表格，然后将特殊样式应用于某些单元格，例如在一系列单元格周围绘制边框或为某个区域设置填充，会更容易或更有效。 CellUtil.setCellProperties 让您可以做到这一点，而无需在电子表格中创建一堆不必要的中间样式。)

Properties are created as a Map and applied to a cell in the following manner.(属性被创建为 Map 并以下列方式应用于单元格。)

Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook()(.)
Sheet sheet = workbook.createSheet("Sheet1");
Map<String, Object> properties = new HashMap<String, Object>();
// border around a cell
// 单元格周围的边框
properties.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM);
properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.MEDIUM);
properties.put(CellUtil.BORDER_LEFT, BorderStyle.MEDIUM);
properties.put(CellUtil.BORDER_RIGHT, BorderStyle.MEDIUM);
// Give it a color (RED)
// 给它一个颜色（红色）
properties.put(CellUtil.TOP_BORDER_COLOR, IndexedColors.RED.getIndex());
properties.put(CellUtil.BOTTOM_BORDER_COLOR, IndexedColors.RED.getIndex());
properties.put(CellUtil.LEFT_BORDER_COLOR, IndexedColors.RED.getIndex());
properties.put(CellUtil.RIGHT_BORDER_COLOR, IndexedColors.RED.getIndex());
// Apply the borders to the cell at B2
// 将边框应用到 B2 处的单元格
Row row = sheet.createRow(1);
Cell cell = row.createCell(1);
CellUtil.setCellStyleProperties(cell, properties);
// Apply the borders to a 3x3 region starting at D4
// 将边框应用到从 D4 开始的 3x3 区域
for (int ix=3; ix <= 5; ix++) {
row = sheet.createRow(ix);
for (int iy = 3; iy <= 5; iy++) {
cell = row.createCell(iy);
CellUtil.setCellStyleProperties(cell, properties);
}
}

NOTE: This does not replace the properties of the cell, it merges the properties you have put into the Map with the cell's existing style properties. If a property already exists, it is replaced with the new property. If a property does not exist, it is added. This method will not remove CellStyle properties.(注意：这不会替换单元格的属性，它会将您放入地图的属性与单元格的现有样式属性合并。如果属性已存在，则将其替换为新属性。如果属性不存在，则添加它。此方法不会删除 CellStyle 属性。)

Drawing Borders(绘制边框)

In Excel, you can apply a set of borders on an entire workbook region at the press of a button. The PropertyTemplate object simulates this with methods and constants defined to allow drawing top, bottom, left, right, horizontal, vertical, inside, outside, or all borders around a range of cells. Additional methods allow for applying colors to the borders.(在 Excel 中，您可以通过按下按钮在整个工作簿区域上应用一组边框。 PropertyTemplate 对象使用定义的方法和常量来模拟这一点，以允许在一系列单元格周围绘制顶部、底部、左侧、右侧、水平、垂直、内部、外部或所有边框。其他方法允许将颜色应用于边框。)

It works like this: you create a PropertyTemplate object which is a container for the borders you wish to apply to a sheet. Then you add borders and colors to the PropertyTemplate, and finally apply it to whichever sheets you need that set of borders on. You can create multiple PropertyTemplate objects and apply them to a single sheet, or you can apply the same PropertyTemplate object to multiple sheets. It is just like a preprinted form.(它的工作原理如下：您创建一个 PropertyTemplate 对象，该对象是您希望应用于工作表的边框的容器。然后为 PropertyTemplate 添加边框和颜色，最后将其应用到您需要该组边框的任何工作表上。您可以创建多个 PropertyTemplate 对象并将它们应用到单个工作表，也可以将相同的 PropertyTemplate 对象应用到多个工作表。它就像一个预印的表格。)

Enums:(枚举：)

BorderStyle (边框样式)
Defines the look of the border, is it thick or thin, solid or dashed, single or double. This enum replaces the CellStyle.BORDER_XXXXX constants which have been deprecated. The PropertyTemplate will not support the older style BORDER_XXXXX constants. A special value of BorderStyle.NONE will remove the border from a Cell once it is applied. (定义边框的外观，是粗还是细，实线还是虚线，单还是双。 此枚举替换了已弃用的 CellStyle.BORDER_XXXXX 常量。 PropertyTemplate 将不支持旧样式 BORDER_XXXXX 常量。 BorderStyle.NONE这个特殊值将在应用后从单元格中删除边框)
BorderExtent (边框范围)
Describes the portion of the region that the BorderStyle will apply to. For example, TOP, BOTTOM, INSIDE, or OUTSIDE. A special value of BorderExtent.NONE will remove the border from the PropertyTemplate. When the template is applied, no change will be made to a cell border where no border properties exist in the PropertyTemplate. (描述 BorderStyle 将应用于的区域部分。 例如，顶部、底部、内部或外部。 BorderExtent.NONE这个特殊值将从 PropertyTemplate 中删除边框。 应用模板时，不会对 PropertyTemplate 中不存在边框属性的单元格边框进行任何更改。)
// draw borders (three 3x3 grids)
// 绘制边框（三个 3x3 网格）
PropertyTemplate pt = new PropertyTemplate();
// #1) these borders will all be medium in default color
// #1) 这些边框在默认颜色中都是中等的
pt.drawBorders(new CellRangeAddress(1, 3, 1, 3),
BorderStyle.MEDIUM, BorderExtent.ALL);
// #2) these cells will have medium outside borders and thin inside borders
// #2) 这些单元格的外边界中等，内边界细
pt.drawBorders(new CellRangeAddress(5, 7, 1, 3),
BorderStyle.MEDIUM, BorderExtent.OUTSIDE);
pt.drawBorders(new CellRangeAddress(5, 7, 1, 3), BorderStyle.THIN,
BorderExtent.INSIDE);
// #3) these cells will all be medium weight with different colors for the
// outside, inside horizontal, and inside vertical borders. The center
// cell will have no borders.
// #3) 这些单元格都是中等重量，外部、内部水平和内部垂直边框的颜色都不同。中心单元将没有边框。
pt.drawBorders(new CellRangeAddress(9, 11, 1, 3),
BorderStyle.MEDIUM, IndexedColors.RED.getIndex(),
BorderExtent.OUTSIDE);
pt.drawBorders(new CellRangeAddress(9, 11, 1, 3),
BorderStyle.MEDIUM, IndexedColors.BLUE.getIndex(),
BorderExtent.INSIDE_VERTICAL);
pt.drawBorders(new CellRangeAddress(9, 11, 1, 3),
BorderStyle.MEDIUM, IndexedColors.GREEN.getIndex(),
BorderExtent.INSIDE_HORIZONTAL);
pt.drawBorders(new CellRangeAddress(10, 10, 2, 2),
BorderStyle.NONE,
BorderExtent.ALL);
// apply borders to sheet
// 将边框应用到工作表
Workbook wb = new XSSFWorkbook();
Sheet sh = wb.createSheet("Sheet1");
pt.applyBorders(sh);

NOTE: The last pt.drawBorders() call removes the borders from the range by using BorderStyle.NONE. Like setCellStyleProperties, the applyBorders method merges the properties of a cell style, so existing borders are changed only if they are replaced by something else, or removed only if they are replaced by BorderStyle.NONE. To remove a color from a border, use IndexedColor.AUTOMATIC.getIndex().(注意：最后一个 pt.drawBorders() 调用时使用 BorderStyle.NONE 从范围中删除边框。与 setCellStyleProperties 一样，applyBorders 方法合并单元格样式的属性，因此现有边框只有在被其他东西替换时才会更改，或者只有在被 BorderStyle.NONE 替换时才会删除。要从边框中删除颜色，请使用 IndexedColor.AUTOMATIC.getIndex()。)

Additionally, to remove a border or color from the PropertyTemplate object, use BorderExtent.NONE.(此外，要从 PropertyTemplate 对象中删除边框或颜色，请使用 BorderExtent.NONE。)

This does not work with diagonal borders yet.(这不适用于对角线边框。)

Creating a Pivot Table(创建数据透视表)

Pivot Tables are a powerful feature of spreadsheet files. You can create a pivot table with the following piece of code.(数据透视表是电子表格文件的一项强大功能。您可以使用以下代码创建数据透视表。)

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
//Create some data to build the pivot table on
//创建一些数据来构建数据透视表
setCellData(sheet);
XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5"));
//Configure the pivot table
//Use first column as row label
//配置数据透视表
//使用第一列作为行标签
pivotTable.addRowLabel(0);
//Sum up the second column
//总结第二列
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
//Set the third column as filter
//设置第三列为过滤器
pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
//Add filter on forth column
//在第四列添加过滤器
pivotTable.addReportFilter(3);

Cells with multiple styles (Rich Text Strings)(具有多种样式的单元格（富文本字符串）)

To apply a single set of text formatting (colour, style, font etc) to a cell, you should create a CellStyle for the workbook, then apply to the cells.(要将一组文本格式（颜色、样式、字体等）应用于单元格，您应该为工作簿创建一个 CellStyle，然后应用于单元格。)

// HSSF Example
// HSSF 示例
HSSFCell hssfCell = row.createCell(idx);
//rich text consists of two runs
//富文本由两次运行组成
HSSFRichTextString richString = new HSSFRichTextString( "Hello, World!" );
richString.applyFont( 0, 6, font1 );
richString.applyFont( 6, 13, font2 );
hssfCell.setCellValue( richString );
// XSSF Example
// XSSF 示例
XSSFCell cell = row.createCell(1);
XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");
XSSFFont font1 = wb.createFont();
font1.setBold(true);
font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
rt.applyFont(0, 10, font1);
XSSFFont font2 = wb.createFont();
font2.setItalic(true);
font2.setUnderline(XSSFFont.U_DOUBLE);
font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
rt.applyFont(10, 19, font2);
XSSFFont font3 = wb.createFont();
font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
rt.append(" Jumped over the lazy dog", font3);
cell.setCellValue(rt);

To apply different formatting to different parts of a cell, you need to use RichTextString, which permits styling of parts of the text within the cell.(要将不同的格式应用于单元格的不同部分，您需要使用 RichTextString，它允许对单元格内的部分文本进行样式设置。)

There are some slight differences between HSSF and XSSF, especially around font colours (the two formats store colours quite differently internally), refer to the HSSF Rich Text String and XSSF Rich Text String javadocs for more details.(HSSF 和 XSSF 之间存在一些细微差别，尤其是在字体颜色方面（这两种格式在内部存储颜色完全不同），有关详细信息，请参阅 HSSF 富文本字符串和 XSSF 富文本字符串 的javadocs。)

 
中英文 | 中文 | 英文
▲