excel导出图片---HSSFWorkbook--SXSSFWorkbook

1 概述

平时在工作中，excel导出图片经常会用到，但奈何HSSFWorkbook导出数据数量有限制问题，所以企业里大多都用SXSSFWorkbook格式，很少用HSSFWorkbook。所以今天以这两种格式分别记录下，图片的导出过程。

2 HSSFWorkbook

 @Test
    public void excelImageTestHSSFWorkbook1(){
 
        String[] heads = new String[]{"编号","姓名","年龄","地址","状态","照片"};
        List<Employee> employees = new ArrayList<>();
 
        Employee employee1 = new Employee(1,"张三",23,"郑州","合法");
        Employee employee2 = new Employee(2,"李四",25,"合肥","合法");
        Employee employee3 = new Employee(3,"王五",26,"青岛","合法");
        Employee employee4 = new Employee(4,"王二麻子",27,"上海","合法");
        Employee employee5 = new Employee(5,"赵子龙",28,"北京","合法");
        Employee employee6 = new Employee(5,"刘能",28,"东北","合法");
        employees.add(employee1);
        employees.add(employee2);
        employees.add(employee3);
        employees.add(employee4);
        employees.add(employee5);
        employees.add(employee6);
 
        List<List<String>>  lists = new ArrayList<>();
        employees.forEach(employee -> {
            List<String> list = new ArrayList<>();
            list.add(String.valueOf(employee.getId()));
            list.add(employee.getName());
            list.add(String.valueOf(employee.getAge()));
            list.add(employee.getAddress());
            list.add(employee.getStatus());
            lists.add(list);
        });
 
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("test picture");
            //填充表头数据
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < heads.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(heads[i]);
            }
            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            //填充实际数据
            for (int i = 0; i < lists.size(); i++) {
                //因为第一行表头数据已经填充过，从第二行开始填充
                HSSFRow row2 = sheet.createRow(i + 1);
                List<String> stringList = lists.get(i);
                for (int j = 0; j < stringList.size() ; j++) {
                    HSSFCell cell = row2.createCell(j);
                    cell.setCellValue(stringList.get(j));
                }
                //设置图片列宽度
                sheet.setColumnWidth(5,30 * 256);
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                bufferImg = ImageIO.read(new File("C:\\Users\\LiuBuJun\\Desktop\\timg .jpg"));
                ImageIO.write(bufferImg, "jpg", byteArrayOut);
                //原始宽度
                int width = bufferImg.getWidth();
                //原始高度
                int height = bufferImg.getHeight();
                //计算该列对应高度
                height = (int) Math.round((height * (30 * 13) * 1.0 / width));
                row2.setHeight((short) (height / 5 * 20));
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 5, (i+1),(short) 6, (i+2));
                //插入图片
                patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            }
            fileOut = new FileOutputStream("D:/测试Excel.xls");
            // 写入excel文件
            wb.write(fileOut);
            System.out.println("----Excle文件已生成------");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(fileOut != null){
                try {
                    fileOut.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

导出结果：

2.1 导出要点

要点1：

//设置图片列宽度
sheet.setColumnWidth(5,30 * 256);
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
bufferImg = ImageIO.read(new File("C:\\Users\\LiuBuJun\\Desktop\\timg .jpg"));
ImageIO.write(bufferImg, "jpg", byteArrayOut);
//原始宽度
int width = bufferImg.getWidth();
//原始高度
int height = bufferImg.getHeight();
//计算该列对应高度
height = (int) Math.round((height * (30 * 13) * 1.0 / width));
row2.setHeight((short) (height / 5 * 20));

首先定义列的宽度，然后创建ByteArrayOutputStream，读取照片，并且根据照片的原本高度和宽度，计算导出到excel中的高度和宽度。

要点2：

/**
* 该构造函数有8个参数
* 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
* 后四个参数，前两个个表示图片左上角所在的cellNum1和 rowNum1，后两个参数对应的表示图片右下角所在的cellNum2和 rowNum2，其实cellNum2-cellNum1就是图片的宽度所占单元格，rowNum2-rowNum1就是图片高度所占单元格
* excel中的cellNum和rowNum的index都是从0开始的
*/
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 5, (i+1),(short) 6, (i+2));