POI导出Excel(xls、xlsx均可以，也支持图片)——（三）

Jar包

有可能也需要:    commons-codec-1.11.jar   这个包。

• HSSFXXXXX导出低版本Excel(以xls结尾的excel，如果导出xlsx结尾的excel，打开会报错，必须手动将后缀改为xls才可以正确打开)

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class POIExt03VersionExcel {
    public static void main(String[] args) {
        String[] title = { "id", "name", "sex" };
        // 创建一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作表sheet
        HSSFSheet sheet = workbook.createSheet();
        // 创建第一行
        HSSFRow row = sheet.createRow(0);
        // 创建一个单元格
        HSSFCell cell = null;
        // 创建表头
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        // 从第二行开始追加数据
        for (int i = 1; i <= 10; i++) {
            // 创建第i行
            HSSFRow nextRow = sheet.createRow(i);
            // 参数代表第几列
            HSSFCell cell2 = nextRow.createCell(0);
            cell2.setCellValue("a" + i);
            cell2 = nextRow.createCell(1);
            cell2.setCellValue("user" + i);
            cell2 = nextRow.createCell(2);
            cell2.setCellValue("男");
        }
        // 创建一个文件
        File file = new File("G:/POI_TEST11.xlsx");
        try {
            file.createNewFile();
            // 打开文件流
            FileOutputStream outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

 

导出图片:

导出单个图片

package cn.qlq.test;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 导出单个图片
 * 
 * @author liqiang
 *
 */
public class ExtExcelOnePic {

    public static void main(String[] args) {
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;
        try {
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            // 加载图片
            bufferImg = ImageIO.read(new File("e:/1.jpg"));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("sheet1");
            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
            /**
             * dx1 - the x coordinate within the first
             * cell.//定义了图片在第一个cell内的偏移x坐标，既左上角所在cell的偏移x坐标，一般可设0 dy1 - the y
             * coordinate within the first
             * cell.//定义了图片在第一个cell的偏移y坐标，既左上角所在cell的偏移y坐标，一般可设0 dx2 - the x
             * coordinate within the second
             * cell.//定义了图片在第二个cell的偏移x坐标，既右下角所在cell的偏移x坐标，一般可设0 dy2 - the y
             * coordinate within the second
             * cell.//定义了图片在第二个cell的偏移y坐标，既右下角所在cell的偏移y坐标，一般可设0 col1 - the
             * column (0 based) of the first cell.//第一个cell所在列，既图片左上角所在列 row1 -
             * the row (0 based) of the first cell.//图片左上角所在行 col2 - the column
             * (0 based) of the second cell.//图片右下角所在列 row2 - the row (0 based)
             * of the second cell.//图片右下角所在行
             */
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 2, 2, (short) 5, 8);
            // 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            fileOut = new FileOutputStream("e:/excel.xls");
            // 输出文件
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

// 关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下：
// dx1：起始单元格的x偏移量，
// dy1：起始单元格的y偏移量，
// dx2：终止单元格的x偏移量，
// dy2：终止单元格的y偏移量，
// col1：起始单元格列序号，从0开始计算；
// row1：起始单元格行序号，从0开始计算，
// col2：终止单元格列序号，从0开始计算；
// row2：终止单元格行序号，从0开始计算，
//添加多个图片时:多个pic应该share同一个DrawingPatriarch在同一个sheet里面。

 

导出多个图片，多个图片一样

package cn.qlq.test;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 导出多个图片(多个图片一样)
 * 
 * @author liqiang
 *
 */
public class ExtExcelMultiPic {

    public static void main(String[] args) {
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;
        try {
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            // 加载图片
            bufferImg = ImageIO.read(new File("e:/2.jpg"));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("sheet1");
            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 2, 2, (short) 5, 8);
            // 插入图片 1
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

            // 图片2
            anchor = new HSSFClientAnchor(200, 0, 0, 0, (short) 2, 9, (short) 5, 15);
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            fileOut = new FileOutputStream("e:/excel.xls");
            // 输出文件
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

导出多个图片，图片不一样

package cn.qlq.test;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 导出多个图片(多个图片不一样)
 * 
 * @author liqiang
 *
 */
public class ExtExcelMultiPic2 {

    public static void main(String[] args) {
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;
        BufferedImage bufferImg1 = null;
        try {
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
            // 加载图片
            bufferImg = ImageIO.read(new File("e:/1.jpg"));
            bufferImg1 = ImageIO.read(new File("e:/2.jpg"));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
            ImageIO.write(bufferImg1, "jpg", byteArrayOut1);
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("sheet1");
            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 2, 2, (short) 5, 8);
            // 插入图片 1
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

            // 图片2
            anchor = new HSSFClientAnchor(200, 0, 0, 0, (short) 2, 9, (short) 5, 15);
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut1.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            fileOut = new FileOutputStream("e:/excel.xls");
            // 输出文件
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

• XSSFXXXXX导出高版本Excel(以xlsx结尾的excel，如果导出xls结尾的excel，打开会报错，必须手动将后缀改为xlsx才可以正确打开)

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POIExt07VersionExcel {
    public static void main(String[] args) {
        String[] title = { "id", "name", "sex" };
        // 创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建一个工作表sheet
        Sheet sheet = workbook.createSheet();
        // 创建第一行
        Row row = sheet.createRow(0);
        // 创建一个单元格
        Cell cell = null;
        // 创建表头
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        // 从第二行开始追加数据
        for (int i = 1; i <= 10; i++) {
            // 创建第i行
            Row nextRow = sheet.createRow(i);
            // 参数代表第几列
            Cell cell2 = nextRow.createCell(0);
            cell2.setCellValue("a" + i);
            cell2 = nextRow.createCell(1);
            cell2.setCellValue("user" + i);
            cell2 = nextRow.createCell(2);
            cell2.setCellValue("男");
        }
        // 创建一个文件
        File file = new File("G:/POI_TEST11.xls");
        try {
            file.createNewFile();
            // 打开文件流
            FileOutputStream outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

最后发现相同数据的excel，以xlsx结尾的确实大小比xls的小，07年office改版的目的就是为了节约内存，所以xlsx的占用内存小:

 

导出图片:

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;

import javax.imageio.ImageIO;

import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 导出单个图片
 * 
 * @author liqiang
 *
 */
public class ExtExcelOnePic {

    public static void main(String[] args) {
        FileOutputStream fileOut = null;
        BufferedImage bufferImg = null;
        try {
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            // 加载图片
            bufferImg = ImageIO.read(new File("e:/1.jpg"));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet1 = wb.createSheet("sheet1");
            XSSFDrawing patriarch = sheet1.createDrawingPatriarch();
            /**
             * dx1 - the x coordinate within the first
             * cell.//定义了图片在第一个cell内的偏移x坐标，既左上角所在cell的偏移x坐标，一般可设0 dy1 - the y
             * coordinate within the first
             * cell.//定义了图片在第一个cell的偏移y坐标，既左上角所在cell的偏移y坐标，一般可设0 dx2 - the x
             * coordinate within the second
             * cell.//定义了图片在第二个cell的偏移x坐标，既右下角所在cell的偏移x坐标，一般可设0 dy2 - the y
             * coordinate within the second
             * cell.//定义了图片在第二个cell的偏移y坐标，既右下角所在cell的偏移y坐标，一般可设0 col1 - the
             * column (0 based) of the first cell.//第一个cell所在列，既图片左上角所在列 row1 -
             * the row (0 based) of the first cell.//图片左上角所在行 col2 - the column
             * (0 based) of the second cell.//图片右下角所在列 row2 - the row (0 based)
             * of the second cell.//图片右下角所在行
             */
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 2, 2, (short) 5, 8);
            // 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
            fileOut = new FileOutputStream("e:/excel.xlsx");
            // 输出文件
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

　　导出多图片类似于上面，这里不再贴代码了。

　　导出xls用HSSFXXXXX，导出xlsx用XSSFXXXX。。。。。。

 

补充:

　　另外补充一种根据Work的类型生成对应图片到版本的Excel的文件的方法:

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WritePicToExcel07 {
    public static void main(String[] args) throws Exception {
        /* 写EXCEL，将目标excel中图片写入到新的excel中 */
        String basePath = "e:\\";

        Workbook wb = new HSSFWorkbook();// 根据这个判断生成对应版本的图片
        // Workbook wb = new XSSFWorkbook();//根据这个判断生成对应版本的图片
        FileInputStream fis = new FileInputStream(basePath + "1.jpg");
        byte[] bytes = IOUtils.toByteArray(fis);
        int pictureIdx = wb.addPicture(bytes, wb.PICTURE_TYPE_JPEG);
        fis.close();

        Sheet sheet = wb.createSheet("sheet1");
        // 创建一个顶级容器
        Drawing drawing = sheet.createDrawingPatriarch();
        CreationHelper helper = wb.getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(3);
        anchor.setRow1(2);
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        // auto-size picture relative to its top-left corner
        pict.resize();// 该方法只支持JPEG 和 PNG后缀文件
        String file = "生成的EXCEL.xls";
        if (wb instanceof XSSFWorkbook)
            file += "x";
        FileOutputStream fos = new FileOutputStream(basePath + file);

        // Row row = sheet.createRow(0);//生成第一行
        // row.createCell(0).setCellValue("A");
        // row.createCell(1).setCellValue("B");
        wb.write(fos);
        fos.close();
    }
}