Apache POI整合使用并实现包含图片的Excel导入导出

Apache POI

Apache POI官网：poi.apache.org

Apache POI Maven仓库：Home » org.apache » poi

Apache POI components API：Apache POI - Component Overview

Apache POI 官方示例：asf - Revision 1881339: /poi/trunk/src/examples/src/org/apache/poi/examples

Apache POI 简介

Apache POI 是 Apache 软件基金会的开源项目，是一个 Java 实现的操作 Microsoft Office格式文档的工具库，通常用于处理 Excel 文件，同时它也支持读写 MS Word 、 MS PowerPoint 文件等其他Office格式文档。

POI 是 Poor Obfuscation Implementation 首字母缩写，意为“简洁版的模糊实现”。

Apache POI 组件概述

Apache POI 项目组件对应的格式文档

• POIFS for OLE 2 Documents
• HSSF and XSSF for Excel Documents
• HWPF and XWPF for Word Documents
• HSLF and XSLF for PowerPoint Documents
• HPSF for OLE 2 Document Properties
• HDGF and XDGF for Visio Documents
• HPBF for Publisher Documents
• HMEF for TNEF (winmail.dat) Outlook Attachments
• HSMF for Outlook Messages

Component Map (组件对应文档及Maven仓库项目)：

整合使用

项目中引入Maven依赖：

<!--Apache POI-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

Excel文档处理示例

导出带图片的Excel示例

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import java.util.Arrays;
import java.util.List;

/**
 * POI导出带图片的Excel示例
 */
public class PoiExcelExport {
   

    public static void main(String[] args) throws IOException {
   
        exportXls();
        exportXlsx();
    }

    public static void exportXls() throws IOException {
   
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 650);

        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //声明列对象
        HSSFCell cell;

        //创建标题
        List<String> titleList = Arrays.asList("姓名", "人物图片");
        for (int i = 0; i < titleList.size(); i++) {
   
            sheet.setColumnWidth(i, 6000);
            cell = row.createCell(i);
            cell.setCellValue(titleList.get(i));
            HSSFFont font = workbook.createFont();
            //设置excel数据字体颜色
            font.setColor(Font.COLOR_NORMAL);
            //设置excel数据字体大小
            font.setFontHeightInPoints((short) 15);
            headStyle.setFont(font);
            cell.setCellStyle(headStyle);
        }

        HSSFCellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setWrapText(true);//自动换行

        //填充数据
        row = sheet.createRow(1);
        row.setHeight((short) 2000);
        cell = row.createCell(0);
        cell.setCellValue("蒙奇·D·路飞");
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        BufferedImage image = ImageIO.read(new URL("https://ss0.bdstatic.com/70cFvHSh_Q1YnxGkpoWK1HF6hhy/it/u=1400063385,2378282079&fm=26&gp=0.jpg"));
        ImageIO.write(image, "jpg", byteArrayOut);
        HSSFClientAnchor anchor = new HSSFClientAnchor(
                0, 0, 0, 0,
                //第二行、第二列
                (short) 1, 1, (short) 2, 2
        );
        //插入图片
        patriarch.createPicture(
                anchor,
                workbook.addPicture(
                        byteArrayOut.toByteArray(),
                        HSSFWorkbook.PICTURE_TYPE_JPEG
                )
        );
        byteArrayOut.close();

        //输出至文件
        workbook.write(new FileOutputStream(new File("D:\\测试.xls")));

        workbook.close();
    }

    public static void exportXlsx() throws IOException {
   
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet1");
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        XSSFRow row = sheet.createRow(0);
        row.setHeight((short) 650);

        XSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //声明列对象
        XSSFCell cell;

        //创建标题
        List<String> titleList = Arrays.asList("姓名", "人物图片");
        for (int i = 0; i < titleList.size(); i++) {
   
            sheet.setColumnWidth(i, 6000);
            cell = row.createCell(i);
            cell.setCellValue(titleList.get(i));
            XSSFFont font = workbook.createFont();
            //设置excel数据字体颜色
            font.setColor(Font.COLOR_NORMAL);
            //设置excel数据字体大小
            font.setFontHeightInPoints((short) 15);
            headStyle.setFont(font);
            cell.setCellStyle(headStyle);
        }

        XSSFCellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setWrapText(true); //自动换行

        //填充数据
        row = sheet.createRow(1);
        row.setHeight((short) 2000);
        cell = row.createCell(0);
        cell.setCellValue("蒙奇·D·路飞");
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        BufferedImage image = ImageIO.read(new URL("https://ss0.bdstatic.com/70cFvHSh_Q1YnxGkpoWK1HF6hhy/it/u=1400063385,2378282079&fm=26&gp=0.jpg"));
        ImageIO.write(image, "jpg", byteArrayOut);
        XSSFClientAnchor anchor = new XSSFClientAnchor(
                0, 0, 0, 0,
                //第二行、第二列
                (short) 1, 1, (short) 2, 2
        );
        // 插入图片
        patriarch.createPicture(
                anchor,
                workbook.addPicture(
                        byteArrayOut.toByteArray(),
                        XSSFWorkbook.PICTURE_TYPE_JPEG
                )
        );
        byteArrayOut.close();

        //输出至文件
        workbook.write(new FileOutputStream(new File("D:\\测试.xlsx")));

        workbook.close();
    }

}

导入带图片的Excel示例

import org.apache<