POI关于HSSF和XSSF功能开发指南
ps: 以下功能针对版本 4.0+
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0</version> </dependency>
poi也有两个不同的jar包，分别是处理excel2003和excel2007+的，对应的是poi和poi-ooxml。
毕竟poi-ooxml是poi的升级版本，处理的单页数据量也是百万级别的，所以我们选择的也是poi-ooxml
1.如何创建一个新的工作簿
1 Workbook wb = new HSSFWorkbook(); 2 ... 3 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { 4 wb.write(fileOut); 5 } 6 Workbook wb = new XSSFWorkbook(); 7 ... 8 try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) { 9 wb.write(fileOut); 10 } 11
2.如何建立工作表
Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); Sheet sheet2 = wb.createSheet("second sheet"); //请注意，工作表名称为Excel不得超过31个字符 //，且不得包含以下任何字符： // 0x0000 // 0x0003 //冒号（:) //反斜杠（\） //星号（*） //问号（？） //正斜杠（/） //打开方括号（[） //右方括号（]） //您可以使用org.apache.poi.ss.util.WorkbookUtil＃createSafeSheetName（String nameProposal）} //为了安全地创建有效名称，此实用程序将无效字符替换为空格（''） String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales " Sheet sheet3 = wb.createSheet(safeName); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); }
3.如何创建细胞
Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); //创建一行并在其中放入一些单元格。行从0开始。 Row row = sheet.createRow(0); //创建一个单元格并在其中放置一个值。 Cell cell = row.createCell(0); cell.setCellValue(1); //或一行完成。 row.createCell(1).setCellValue(1.2); row.createCell(2).setCellValue( createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); //将输出写入文件 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); }
4.如何创建日期单元
Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); //创建一行并在其中放入一些单元格。行从0开始。 Row row = sheet.createRow(0); //创建一个单元格并在其中放置一个日期值。第一个单元格未设置样式 //作为日期。 Cell cell = row.createCell(0); cell.setCellValue(new Date()); //我们将第二个单元格设置为日期（和时间）。重要的是要 //从工作簿中创建新的单元格样式，否则您可以结束 //修改内置样式并不仅影响此单元格，而且影响其他单元格。 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); //您也可以将日期设置为java.util.Calendar cell = row.createCell(2); cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); //将输出写入文件 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); }
5.处理不同类型的细胞
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); //将输出写入文件 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); }
6.遍历行和单元格
有时，您只想遍历工作簿中的所有工作表，工作表中的所有行或行中的所有单元格。这可以通过简单的for循环来实现。
通过调用workbook.sheetIterator（）， sheet.rowIterator（）和row.cellIterator（）或隐式使用for-each循环，可以使用这些迭代器。请注意，rowIterator和cellIterator遍历已创建的行或单元格，跳过空的行和单元格。
for (Sheet sheet : wb ) { for (Row row : sheet) { for (Cell cell : row) { // 填充内容 } } }
遍历单元格，控制丢失/空白的单元格
在某些情况下，进行迭代时，您需要完全控制如何处理丢失或空白的行和单元格，并且需要确保访问每个单元格，而不仅仅是访问文件中定义的那些单元格。（CellIterator将仅返回文件中定义的单元格，这些单元格在很大程度上具有值或样式，但取决于Excel）。
在这种情况下，应该获取一行的第一列和最后一列信息，然后调用getCell（int，MissingCellPolicy） 来获取单元格。使用 MissingCellPolicy 控制空白或空单元格的处理方式。
//确定要处理的行 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) { //这整行是空的 //需要处理 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) { //电子表格在此单元格中为空 } else { //对单元格内容进行一些有用的操作 } } }
7.获取单元格内容
要获取单元格的内容，您首先需要知道它是哪种单元格（例如，将字符串单元格作为其数字内容将获得NumberFormatException）。因此，您将需要打开单元格的类型，然后为该单元格调用适当的getter。
在下面的代码中，我们遍历一张纸中的每个单元格，打印出单元格的引用（例如A3），然后打印出单元格的内容。
// 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(" - "); //通过获取单元格值并应用任何数据格式（日期，0.00、1.23e9，$ 1.23等）来获取显示在单元格中的文本 String text = formatter.formatCellValue(cell); System.out.println(text); //或者，获取值并自行格式化 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(); } } }
8.文字提取
对于大多数文本提取要求，标准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(); }
对于非常精美的文本提取，XLS到CSV等，请查看 /src/examples/src/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java
9.文件与InputStreams
当打开工作簿（.xls HSSFWorkbook或.xlsx XSSFWorkbook）时，可以从File 或InputStream加载工作簿。使用File对象可以减少内存消耗，而InputStream需要更多内存，因为它必须缓冲整个文件。
如果使用WorkbookFactory，则使用其中一个非常容易：
//使用文件
Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));
//使用InputStream，需要更多的内存
Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));
如果直接使用HSSFWorkbook或XSSFWorkbook，通常应遍历POIFSFileSystem或 OPCPackage，以完全控制生命周期（包括完成后关闭文件）：
// HSSFWorkbook，文件
POIFSFileSystem fs = new POIFSFileSystem(new File("file.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
....
fs.close();
// HSSFWorkbook InputStream需要更多内存
POIFSFileSystem fs = new POIFSFileSystem(myInputStream);
HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
// XSSFWorkbook，文件
OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(pkg);
....
pkg.close();
// XSSFWorkbook InputStream需要更多内存
OPCPackage pkg = OPCPackage.open(myInputStream);
XSSFWorkbook wb = new XSSFWorkbook(pkg);
....
pkg.close();
10.对齐细胞
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); //将输出写入文件 try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) { wb.write(fileOut); } wb.close(); } / ** * 创建一个单元格并以某种方式对齐它。 * * @param wb工作簿 * @param row在其中创建单元格的行 * @param列在其中创建单元格的列号 * @param将单元格的水平对齐方式对齐。 * @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); }
11.边界处理
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); //创建一行并在其中放入一些单元格。行从0开始。 Row row = sheet.createRow(1); //创建一个单元格并在其中放置一个值。 Cell cell = row.createCell(1); cell.setCellValue(4); //用周围的边框设置单元格的样式。 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); //将输出写入文件 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close();
12.填充和颜色
Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); //创建一行并在其中放入一些单元格。行从0开始。 Row row = sheet.createRow(1); //水色背景 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); //橙色的“ foreground”，前景是填充前景，而不是字体颜色。 style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); cell = row.createCell(2); cell.setCellValue("X"); cell.setCellStyle(style); //将输出写入文件 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close();
13.合并细胞
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，//第一行（从0开始） 1，//最后一行（从0开始） 1，//第一列（从0开始） 2 //最后一列（从0开始） )); // Write the output to a file try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close();
14.使用字体
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); //创建一行并在其中放入一些单元格。行从0开始。 Row row = sheet.createRow(1); //创建一个新字体并进行更改。 Font font = wb.createFont(); font.setFontHeightInPoints((short)24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); //字体设置为一种样式，因此请创建一种新样式来使用。 CellStyle style = wb.createCellStyle(); style.setFont(font); //创建一个单元格并在其中放置一个值。 Cell cell = row.createCell(1); cell.setCellValue("This is a test of fonts"); cell.setCellStyle(style); //将输出写入文件 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close();
请注意，工作簿中唯一字体的最大数量限制为32767。您应该在应用程序中重新使用字体，而不是为每个单元格创建字体。例子：
错误
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); }
正确
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); }
15.自定义颜色
HHFS:
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); cell.setCellValue("Default Palette"); //应用标准调色板中的某些颜色， //如前面的示例。 //我们将在石灰背景上使用红色文本 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); //使用默认调色板保存 try (OutputStream out = new FileOutputStream("default_palette.xls")) { wb.write(out); } //现在，让我们替换调色板中的RED和LIME //具有更有吸引力的组合 cell.setCellValue("Modified Palette"); //为工作簿创建自定义调色板 HSSFPalette palette = wb.getCustomPalette(); palette.setColorAtIndex(HSSFColor.RED.index, （字节）153，// RGB红色（0-255） （字节）0，// RGB绿色 （字节）0 // RGB蓝色 ); //replacing lime with freebsd.org gold palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102); //保存修改后的调色板 //请注意，无论我们以前使用RED还是LIME的地方， //新颜色神奇地出现 try (out = new FileOutputStream("modified_palette.xls")) { wb.write(out); }
XXSF:
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);
16.读写
try (InputStream inp = new FileInputStream("workbook.xls")) { //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); } }
17.在单元格中使用换行符
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"); //要启用换行符，您需要使用wrap = true设置单元格样式 CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); cell.setCellStyle(cs); //增加行高以容纳两行文本 row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); //调整列宽以适合内容 sheet.autoSizeColumn(2); try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) { wb.write(fileOut); } wb.close();
18.创建用户定义的数据格式
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();
19.将工作表调整为一页
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); //为电子表格创建各种单元格和行。 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close();
20.设置纸张的打印区域
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet1"); //设置第一张纸的打印区域 wb.setPrintArea(0, "$A$1:$C$2"); //Alternatively: wb.setPrintArea( 0，//工作表索引 0，//开始列 1，//结束列 0，//开始行 0 //结束行 ); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close();
22.在工作表的页脚上设置页码
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() ); //为电子表格创建各种单元格和行。 try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } wb.close();
23.排行
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("row sheet"); //为电子表格创建各种单元格和行。 //将电子表格上的6-11行移至顶部（第0-5行） sheet.shiftRows(5, 10, -5);
24.将工作表设置为选中状态
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("row sheet"); sheet.setSelected(true);
25.设置图纸的缩放倍率
缩放表示为分数。例如，要表示75％的缩放比例，请将3用作分子，将4用作分母。
Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); sheet1.setZoom(75); // 放大75%
26.创建拆分和冻结窗格
您可以创建两种类型的窗格：冻结窗格和拆分窗格。
冻结窗格按列和行划分。使用以下机制创建冻结窗格：
sheet1.createFreezePane（3，2，3，2）;
前两个参数是您希望分割的列和行。后两个参数指示在右下象限中可见的单元格。
拆分窗格的显示方式有所不同。分割区域分为四个独立的工作区域。分割发生在像素级别，用户可以通过将其拖动到新位置来调整分割。
通过以下调用创建拆分窗格：
sheet2.createSplitPane（2000，2000，0，0，Sheet.PANE_LOWER_LEFT）;
第一个参数是拆分的x位置。这是一个点的1/20。在这种情况下，一点似乎等于一个像素。第二个参数是分割的y位置。再次在1/20分之内。
最后一个参数指示当前具有焦点的窗格。这将是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"); //只冻结一行 sheet1.createFreezePane( 0, 1, 0, 1 ); //只冻结一列 sheet2.createFreezePane( 1, 0, 1, 0 ); //冻结列和行（忘记右下象限的滚动位置） sheet3.createFreezePane( 2, 2 ); //创建一个拆分，其左下侧为活动象限 sheet4.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT ); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); }
27.重复行和列
通过使用Sheet类中的setRepeatingRows（）和setRepeatingColumns（）方法，可以在打印输出中设置重复的行和列。
这些方法需要一个CellRangeAddress参数，该参数指定要重复的行或列的范围。对于setRepeatingRows（），它应指定要重复的行范围，其中列部分跨越所有列。对于setRepeatingColums（），应指定要重复的列范围，其中行部分跨越所有行。如果参数为null，则将删除重复的行或列。
Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); Sheet sheet1 = wb.createSheet("Sheet1"); Sheet sheet2 = wb.createSheet("Sheet2"); //设置要在第一张纸上的第4到5列重复的行。 sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5")); //将列设置为在第二张纸上从A列重复到C列 sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); }
28.页眉和页脚
示例适用于页眉，但直接应用于页脚。
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); }
29.XSSF对页眉和页脚的增强
示例适用于页眉，但直接应用于页脚。请注意，上面的基本页眉和页脚示例适用于XSSF工作簿以及HSSF工作簿。HSSFHeader内容不适用于XSSF工作簿。
XSSF能够处理首页页眉和页脚以及偶数/奇数页眉和页脚。所有标头/页脚属性标志也可以在XSSF中处理。奇数页眉和页脚是默认的页眉和页脚。它显示在所有不显示首页标题或偶数页标题的页面上。也就是说，如果偶数页眉/页脚不存在，则奇数页眉/页脚将显示在偶数页上。如果第一页的页眉/页脚不存在，则奇数页眉/页脚将显示在第一页上。如果未设置even / odd属性，则与不存在偶数页眉/页脚相同。
Workbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet) wb.createSheet("new sheet"); //创建首页标题 Header header = sheet.getFirstHeader(); header.setCenter("Center First Page Header"); header.setLeft("Left First Page Header"); header.setRight("Right First Page Header"); //创建一个偶数页眉 Header header2 = sheet.getEvenHeader(); der2.setCenter("Center Even Page Header"); header2.setLeft("Left Even Page Header"); header2.setRight("Right Even Page Header"); //创建一个奇数页头 Header header3 = sheet.getOddHeader(); der3.setCenter("Center Odd Page Header"); header3.setLeft("Left Odd Page Header"); header3.setRight("Right Odd Page Header"); //设置/删除标题属性 XSSFHeaderProperties prop = sheet.getHeaderFooterProperties(); prop.setAlignWithMargins(); prop.scaleWithDoc(); prop.removeDifferentFirstPage(); //这不会删除首页的页眉或页脚 prop.removeDifferentEvenOdd(); //这不会删除页眉或页脚 try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) { wb.write(fileOut); }
30.绘图形状
POI支持使用Microsoft Office绘图工具绘制图形。图纸上的形状按组和形状的层次结构进行组织。最高的形状是族长。这一点在工作表上根本看不到。要开始绘制，您需要 在HSSFSheet类上调用createPatriarch。这具有擦除存储在该纸张中的任何其他形状信息的效果。默认情况下，除非您调用此方法，否则POI会将形状记录留在工作表中。
要创建形状，您必须执行以下步骤：
- 创建族长。
- 创建锚点以将形状放置在图纸上。
- 要求族长创造形状。
- 设置形状类型（线，椭圆，矩形等）
- 设置有关形状的其他样式详细信息。（例如：线宽等）
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);
使用不同的调用创建文本框：
HSSFTextbox textbox1 = patriarch.createTextbox( new HSSFClientAnchor(0,0,0,0,(short)1,1,(short)2,2)); textbox1.setString(new HSSFRichTextString("This is a test") );
可以使用不同的字体来设置文本框中文本的样式。这是如何做：
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 );
就像可以使用Excel手动完成一样，可以将形状分组在一起。这是通过调用 createGroup（）然后使用这些组创建形状来完成的。
也可以在组内创建组。
警告
您创建的任何组都应至少包含两个其他形状或子组。
以下是创建形状组的方法：
//创建形状组。 HSSFShapeGroup group = patriarch.createGroup( new HSSFClientAnchor(0,0,900,200,(short)2,2,(short)2,2)); //在组中创建几行。 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);
如果您观察的话，会发现添加到组中的形状使用了一种新型的锚点：HSSFChildAnchor。发生的情况是，所创建的组具有自己的坐标空间，用于放置到其中的形状。POI将此默认设置为（0,0,1023,255），但是您可以根据需要进行更改。这是如何做：
myGroup.setCoordinates（10,10,20,20）; //左上，右下
如果您在一个组内创建一个组，则还将拥有它自己的坐标空间。
31.造型形状
默认情况下，形状看起来有点普通。但是，可以对形状应用不同的样式。目前可以完成的事情有：
- 更改填充颜色。
- 制作没有填充颜色的形状。
- 更改线条的粗细。
- 更改线条的样式。例如：虚线，虚线。
- 更改线条颜色。
这是完成此操作的示例：
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);
32.形状和图形
虽然推荐使用本机POI形状绘制命令来绘制形状，但有时还是希望使用标准API与外部库兼容。考虑到这一点，我们为Graphics和Graphics2d创建了一些包装。
警告
但是重要的是，在继续操作之前， Graphics2d与Microsoft Office绘图命令的功能不匹配。较早的 Graphics类提供了更接近的匹配，但仍然是圆孔中的方钉。
所有图形命令均发布到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 );
我们要做的第一件事是创建组并设置其坐标以匹配我们计划绘制的对象。接下来，我们计算一个合理的fontSizeMultiplier，然后创建EscherGraphics对象。由于我们真正想要的是Graphics2d 对象，因此我们创建了EscherGraphics2d对象并传入我们创建的图形对象。最后，我们调用一个提取EscherGraphics2d对象的例程。
每个像素的垂直点值得更多解释。将Graphics调用转换为escher绘图调用的困难之一是Excel没有绝对像素位置的概念。它以“字符”为单位测量单元格宽度，以点为单位测量单元格高度。不幸的是，它并没有确切定义要测量的字符类型。大概这是由于Excel将在不同平台上甚至在同一平台上使用不同字体的事实。
由于这种限制，我们不得不实现verticalPointsPerPixel的概念。发出诸如drawString（）之类的命令时，字体应缩放的数量。要计算此值，请使用以下公式：
multipler = groupHeightInPoints / heightOfGroup
通过计算形状的边界框的y坐标之间的差，可以非常简单地计算出组的高度。可以使用称为HSSFClientAnchor.getAnchorHeightInPoints（）的便捷性来计算组的高度 。
图形类支持的许多功能都不完整。这是一些已知可以正常工作的功能。
- fillRect（）
- fillOval（）
- drawString（）
- drawOval（）
- drawLine（）
- clearRect（）
不支持的功能将使用POI日志记录基础结构（默认情况下禁用）返回并记录消息。
33.概述
大纲非常适合将信息的各个部分组合在一起，并且可以使用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); }
要折叠（或展开）轮廓，请使用以下调用：
sheet1.setRowGroupCollapsed（7，true）; sheet1.setColumnGroupCollapsed（4，true）
您选择的行/列应包含一个已创建的组。它可以在组中的任何位置。
34.图片
图像是图形支持的一部分。要添加图像，只需在绘图族长上调用createPicture（）。在撰写本文时，支持以下类型：
- PNG
- JPG格式
- DIB
应当注意，一旦将图像添加到图纸上，任何现有的图纸都可能被删除
//创建一个新的工作簿 Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); //将图片数据添加到此工作簿。 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(); //创建工作表 Sheet sheet = wb.createSheet(); //创建绘图族长。这是所有形状的顶层容器。 Drawing drawing = sheet.createDrawingPatriarch(); //添加图片形状 ClientAnchor anchor = helper.createClientAnchor(); //设置图片的左上角， //随后调用Picture＃resize（）将相对于它进行操作 anchor.setCol1(3); anchor.setRow1(2); Picture pict = drawing.createPicture(anchor, pictureIdx); //相对于左上角的图片自动调整大小 pict.resize(); //保存工作簿 String file = "picture.xls"; if(wb instanceof XSSFWorkbook) file += "x"; try (OutputStream fileOut = new FileOutputStream(file)) { wb.write(fileOut); }
警告
Picture.resize（）仅适用于JPEG和PNG。尚不支持其他格式。
从工作簿中读取图像：
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); } }
35.命名范围和命名单元格
命名范围是一种通过名称引用一组单元格的方法。命名单元格是命名范围的简写情况，因为“单元格组”恰好包含一个单元格。您可以在工作簿中按其命名范围创建和引用单元格。使用命名范围时，将使用org.apache.poi.ss.util.CellReference 和org.apache.poi.ss.util.AreaReference类。
注意：使用诸如'A1：B1'之类的相对值会导致在Microsoft Excel中使用该工作簿时该名称指向的单元格的意外移动，通常使用诸如'$ A $ 1：$ B $ 1'之类的绝对引用可以避免这种情况，另请参阅 此讨论。
创建命名范围/命名单元格
//设置代码 String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(sname); sheet.createRow(0).createCell(0).setCellValue(cvalue); // 1.使用areareference为单个单元格创建命名范围 Name namedCell = wb.createName(); namedCell.setNameName(cname + "1"); String reference = sname+"!$A$1:$A$1"; // area reference namedCell.setRefersToFormula(reference); // 2.使用cellreference为单个单元格创建命名范围 Name namedCel2 = wb.createName(); namedCel2.setNameName(cname + "2"); reference = sname+"!$A$1"; // cell reference namedCel2.setRefersToFormula(reference); // 3.使用AreaReference为区域创建命名范围 Name namedCel3 = wb.createName(); namedCel3.setNameName(cname + "3"); reference = sname+"!$A$1:$C$5"; // area reference namedCel3.setRefersToFormula(reference); // 4.创建命名公式 Name namedCel4 = wb.createName(); namedCel4.setNameName("my_sum"); namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");
从命名范围/命名单元格读取
//设置代码 String cname = "TestName"; Workbook wb = getMyWorkbook(); // retrieve workbook //检索命名范围 int namedCellIdx = wb.getNameIndex(cellName); Name aNamedCell = wb.getNameAt(namedCellIdx); //检索指定范围内的单元格并测试其内容 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()); //根据单元格类型等提取单元格内容 }
从不连续的命名范围读取
//设置代码 String cname = "TestName"; Workbook wb = getMyWorkbook(); // retrieve workbook //检索命名范围 //将类似于“ $ C $ 10，$ D $ 12：$ D $ 14”； int namedCellIdx = wb.getNameIndex(cellName); Name aNamedCell = wb.getNameAt(namedCellIdx); //检索指定范围内的单元格并测试其内容 //将获取C10的一个AreaReference，然后 // D12到D14的另一个 AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula()); for (int i=0; i<arefs.length; i++) { //只获取区域的角落 //（使用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()); //对这个角落单元格进行操作 } }
请注意，删除单元格后，Excel不会删除附加的命名范围。因此，工作簿可以包含指向不再存在的单元格的命名范围。在构造AreaReference之前，您应该检查参考的有效性。
if(name.isDeleted()){ //已命名的范围指向已删除的单元格。 } else { AreaReference ref = new AreaReference(name.getRefersToFormula()); }
36.如何设置单元格注释
注释是附于单元格并与单元格关联的富文本注释，与其他单元格内容分开。注释内容与单元格分开存储，并显示在与单元格独立但相关联的图形对象（如文本框）中
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(); //当注释框可见时，将其显示在1x3的空间中 ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex()+1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum()+3); //创建评论并设置文字+作者 Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString("Hello, World!"); comment.setString(str); comment.setAuthor("Apache POI"); //将注释分配给单元格 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();
阅读单元格注释
Cell cell = sheet.get(3).getColumn(1); Comment comment = cell.getCellComment(); if (comment != null) { RichTextString str = comment.getString(); String author = comment.getAuthor(); } //或者，您也可以按（行，列）检索单元格注释 comment = sheet.getCellComment(3, 1);
要获得工作表上的所有评论：
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()); }
37.如何调整列宽以适合内容
Sheet sheet = workbook.getSheetAt(0); sheet.autoSizeColumn(0); //调整第一列的宽度 sheet.autoSizeColumn(1); //调整第二列的宽度
仅对于SXSSFWorkbooks，因为随机访问窗口可能会排除工作表中的大多数行，而这是计算列的最佳匹配宽度所必需的，因此在刷新任何行之前，必须跟踪这些列以自动调整大小。
SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet sheet = workbook.createSheet(); sheet.trackColumnForAutoSizing(0); sheet.trackColumnForAutoSizing(1); //如果您具有列索引的Collection，请参见SXSSFSheet＃trackColumnForAutoSizing（Collection <Integer>） //或滚动自己的for循环。 //或者，如果没有自动调整大小的列，请使用SXSSFSheet＃trackAllColumnsForAutoSizing（） //预先知道，或者您正在升级现有代码，并试图最小化更改。记住 //由于计算出最适合的宽度，因此跟踪所有列将需要更多的内存和CPU周期 //在刷新的每一行的所有跟踪列上。 //创建一些单元格 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()); } } //自动调整列的大小。 sheet.autoSizeColumn(0); sheet.autoSizeColumn(1);
请注意，Sheet＃autoSizeColumn（）不计算公式单元格，公式单元格的宽度是根据缓存的公式结果计算的。如果您的工作簿有很多公式，那么在自动调整大小之前最好对它们进行评估。
警告
要计算列宽，Sheet.autoSizeColumn使用Java2D类，如果图形环境不可用，则该类将引发异常。如果没有图形环境，则必须告诉Java您正在以无头模式运行，并设置以下系统属性：java.awt.headless = true。您还应确保在工作簿中使用的字体可用于Java。
38.超连结
如何阅读超链接
Sheet sheet = workbook.getSheetAt(0); Cell cell = sheet.getRow(0).getCell(0); Hyperlink link = cell.getHyperlink(); if(link != null){ System.out.println(link.getAddress()); }
如何创建超链接
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); //超链接的单元格样式 //默认情况下，超链接为蓝色并带有下划线 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); //链接到当前目录中的文件 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); //电子邮件链接 cell = sheet.createRow(2).createCell(0); cell.setCellValue("Email Link"); link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL); ///注意，如果主题中包含空格，请确保其为url编码 link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //链接到此工作簿中的位置 //创建目标工作表和单元格 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();
39.资料验证
40.嵌入式对象
41.自动过滤器
42.条件格式
43.隐藏和取消隐藏行
44.设置单元格属性
45.绘制边框
46.创建数据透视表
47.具有多种样式的单元格
