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

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

• PNG
• JPG 格式
• DIB公司

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

//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 it

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);
}

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

从工作簿中读取图像：

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' 这样的绝对引用可以避免这种情况，另请参阅此讨论。

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

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

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

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

从非连续的命名范围读取

// 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 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
    }
}

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

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

34、Cell 注释

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

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();

读取单元格注释

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

获取工作表上的所有注释

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、调整列宽以适应内容

Sheet sheet = workbook.getSheetAt(0);

sheet.autoSizeColumn(0); //adjust width of the first column
sheet.autoSizeColumn(1); //adjust width of the second column

仅适用于 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.
// 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);

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

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

36、如何阅读超链接

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、如何创建超链接

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();