在Java中使用Apache POI导入导出Excel（四）

本文将继续介绍POI的使用，上接在Java中使用Apache POI导入导出Excel（三）

使用Apache POI组件操作Excel（四）

31、外边框

外边框非常适合对信息部分进行分组，并且可以轻松添加到列和行中使用 POI API。方法如下：

java">Workbook wb = new XSSFWorkbook();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);
}

要折叠（或展开）外边框，请使用以下调用：

java">sheet1.setRowGroupCollapsed( 7, true );
sheet1.setColumnGroupCollapsed( 4, true );

您选择的行/列应包含一个 already created 组。它可以位于组中的任何位置。

32、图像

图像是绘图支持的一部分。要仅添加图像在绘图 patriarch 上调用 createPicture() 。在撰写本文时，支持以下类型：

PNG
JPG 格式
DIB公司

应该注意的是，任何现有的图纸都可能被擦除将图像添加到工作表后。

java">
//create a new workbook
Workbook wb = new XSSFWorkbook();//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 itanchor.setCol1(3);
anchor.setRow1(2);Picture pict = drawing.createPicture(anchor, pictureIdx);//auto-size picture relative to its top-left cornerpict.resize();//save workbook
String file = "picture.xls";if(wb instanceof XSSFWorkbook) file += "x";try (OutputStream fileOut = new FileOutputStream(file)) {wb.write(fileOut);
}

警告：Picture.resize（）仅适用于 JPEG 和 PNG。尚不支持其他格式。

从工作簿中读取图像：

java">List lst = workbook.getAllPictures();for (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);}}

33、命名区域和命名单元格

命名范围是一种通过名称引用一组单元格的方法。命名 Cell 是一个命名范围的退化情况，因为“单元格组”只包含一个细胞。您可以按命名范围创建以及引用工作簿中的单元格。使用命名范围时，将使用类 org.apache.poi.ss.util.CellReference 和 org.apache.poi.ss.util.AreaReference。

注意：使用相对值（如 'A1：B1' ）可能会导致在 Microsoft Excel 中处理工作簿时名称指向的单元格，通常使用像 '$A$1：$B$1' 这样的绝对引用可以避免这种情况，另请参阅此讨论。

创建命名区域 / 命名单元格

java">// setup code
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
Workbook wb = new XSSFWorkbook();Sheet sheet = wb.createSheet(sname);
sheet.createRow(0).createCell(0).setCellValue(cvalue);// 1. create named range for a single cell using areareference
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
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
Name namedCel3 = wb.createName();
namedCel3.setNameName(cname + "3");
reference = sname+"!$A$1:$C$5"; // area reference
namedCel3.setRefersToFormula(reference);// 4. create named formula
Name namedCel4 = wb.createName();
namedCel4.setNameName("my_sum");
namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");

从命名区域/命名单元格中读取

java">// 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.
}

从非连续的命名范围读取

java">// Setup code
String cname = "TestName";
Workbook wb = getMyWorkbook(); // retrieve workbook// Retrieve the named range
// Will be something like "$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
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)CellReference[] crefs = arefs[i].getCells();for (int j=0; j<crefs.length; j++) {// Check it turns into real stuffSheet 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}
}

请注意，删除单元格时，Excel 不会删除 attached 命名范围。因此，工作簿可以包含指向不再存在的单元格的命名区域。您应该在之前检查引用的有效性构造 AreaReference

java">if(name.isDeleted()){//named range points to a deleted cell.
} else {AreaReference ref = new AreaReference(name.getRefersToFormula());
}

34、Cell 注释

注释是附加到 & 的富文本注释，与单元格关联，与其他单元格内容分开。注释内容与单元格分开存储，并显示在绘图对象（如文本框）中独立于单元格但与单元格相关联

java">Workbook wb = new XSSFWorkbook(); 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
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();

读取单元格注释

java">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);

获取工作表上的所有注释

java">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());
}

35、调整列宽以适应内容

java">Sheet sheet = workbook.getSheetAt(0);sheet.autoSizeColumn(0); //adjust width of the first column
sheet.autoSizeColumn(1); //adjust width of the second column

仅适用于 SXSSFWorkbooks，因为随机访问窗口可能会排除大多数行在计算列的最佳拟合宽度所需的工作表中，列必须在刷新任何行之前跟踪自动调整大小。

java">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.
// create some cellsfor (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);

请注意，Sheet#autoSizeColumn()不计算公式单元格，公式单元格的宽度是根据缓存的公式结果计算的。如果您的工作簿有许多公式，那么最好在自动调整大小之前评估它们。

警告：为了计算列宽，Sheet.autoSizeColumn 使用 Java2D 类如果图形环境不可用，则引发异常。如果图形环境不可用，则必须告诉 Java 您正在无头模式下运行，并且设置以下系统属性： java.awt.headless=true 。您还应确保在工作簿中使用的字体是可用于 Java。

36、如何阅读超链接

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

37、如何创建超链接

java">Workbook wb = new XSSFWorkbook(); 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(HyperlinkType.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(HyperlinkType.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(HyperlinkType.EMAIL);//note, if subject contains white spaces, make sure they are url-encoded
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(HyperlinkType.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();