poi/jxl将图片写入excel，仅在打开excel的时候才加载图片

     创建excel的时候，将图片写入到excel，如果直接将图片写入excel中，会导致excel文件特别大，导出时或者打开时都比较困难，所以采用了excel中的宏语言，在excel打开时才加载图片。

    实现思路：在创建excel时，通过一个包含vb语言脚本的excel创建，脚本语言做的操作是遍历excel,找到表头中批注是"image"的 列，然后根据该列中的单元格内容（图片地址）加载图片，所以创建excel时只需要在图片列的表头中添加批注，单元格中写入图片地址即可；

    功能实现：

1、创建一个空的excel模板，编写vb语言（根据图片列（表头中有批注"image"）中的图片地址加载图片功能），将编写的vb语言放入excel模板中，操作：打开excel-->开发工具-->VB编辑器，编辑代码即可，示例代码如下：

Private Sub Workbook_Open()
    Call loadImage(ActiveSheet)
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Call loadImage(Sh)
End Sub

Private Sub loadImage(ByVal Sh As Object)

    If Sh.UsedRange.Rows.Count <= 0 Then
        Exit Sub
    End If
    
    Dim imgCol() As Integer
    Dim nComment As Integer
    Dim n As Integer
    nComment = 0
    n = 0
    For cn = 1 To Sh.UsedRange.Columns.Count
        If Not Sh.Cells(1, cn).Comment Is Nothing Then
            nComment = nComment + 1
            If Sh.Cells(1, cn).Comment.Text = "image" Then
                ReDim Preserve imgCol(n)
                imgCol(n) = cn
                n = n + 1
                Sh.Columns(cn).ColumnWidth = 30
            End If
        End If
    Next cn

    If n <= 0 Then
        'MsgBox ("no image column")
        Exit Sub
    End If
    
    If Sh.Shapes.Count > nComment Then
        'MsgBox ("已加载图片" & (Sh.Shapes.Count - nComment))
        Exit Sub
    End If
            
    
    Dim picPathStr As String
    Dim picPath() As String
    For rn = 2 To Sh.UsedRange.Rows.Count
        For Each cn In imgCol
            With Sh.Cells(rn, cn)
                n = 0
                picPath() = Split(.Value, ",")
                For Each onePicPath In picPath
                    'MsgBox (onePicPath)
                    Sh.Shapes.AddPicture Path + "/" + onePicPath, msoTrue, msoFalse, .Left + n * 20, .Top, 100, 100
                    n = n + 1
                    .Value = ""
                Next onePicPath
            End With
        Next cn
        
        Sh.Rows(rn).RowHeight = 100
    Next rn
    
End Sub

2、通过该模板创建excel，并且生成想要的目标excel文件，我分别通过poi和jxl格实现了一次

     2.1：通过poi实现该功能，代码如下：

package poi;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class TestVba
{
	private static final String TEMPLET_FILE = "E:\\111\\test_vba\\vba_image_templet.xls";
	private static final String IMAGE_COMMENT = "image";

	public static void main(String[] args)
	{
		try
		{
			FileInputStream fis = new FileInputStream(TEMPLET_FILE);
			// 启用宏模板 将图片写入excel，打开excel时再加载图片
			HSSFWorkbook wb = new HSSFWorkbook(fis);
			wb.createSheet();
			HSSFSheet sheet = wb.getSheetAt(0);
			Row rowTitle = sheet.createRow((short) 0);
			HSSFPatriarch drawing = sheet.createDrawingPatriarch();
			HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 4, (short) 7, 7);
			//
			{
				Cell cell = rowTitle.createCell(0);
				cell.setCellValue("员工");
			}
			{
				Cell cell = rowTitle.createCell(1);
				cell.setCellValue("图片列1");
				HSSFComment comment = drawing.createCellComment(anchor);
				HSSFRichTextString commentStr = new HSSFRichTextString(IMAGE_COMMENT);
				comment.setString(commentStr);
				cell.setCellComment(comment);
			}
			{
				Cell cell = rowTitle.createCell(2);
				cell.setCellValue("又一列图片");
				HSSFComment comment = drawing.createCellComment(anchor);
				HSSFRichTextString commentStr = new HSSFRichTextString(IMAGE_COMMENT);
				comment.setString(commentStr);
				cell.setCellComment(comment);
			}
			//
			short nRow = 1;
			List<List<String>> rowList = createDemoData();
			for (List<String> row : rowList)
			{
				Row rowObj = sheet.createRow(nRow++);
				for (int nCol = 0; nCol < row.size(); nCol++)
				{
					Cell cell = rowObj.createCell(nCol);
					cell.setCellValue(row.get(nCol));
				}
			}
			FileOutputStream stream = new FileOutputStream("E:\\111\\test_vba\\my10.xls");
			wb.write(stream);
			System.out.println("OK");
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}

	}

	private static List<List<String>> createDemoData()
	{
		List<List<String>> rowList = new ArrayList<List<String>>();
		{
			List<String> row1 = new ArrayList<String>();
			row1.add("张三");
			row1.add("images/image1/101.jpg,images/image1/102.jpg");
			row1.add("");
			rowList.add(row1);
		}
		{
			List<String> row1 = new ArrayList<String>();
			row1.add("李四");
			row1.add("");
			row1.add("images/image3/301.jpg,images/image3/302.jpg,images/image3/303.jpg");
			rowList.add(row1);
		}
		{
			List<String> row1 = new ArrayList<String>();
			row1.add("王五");
			row1.add("images/image2/201.jpg");
			row1.add("");
			rowList.add(row1);
		}
		return rowList;
	}

	private static HSSFWorkbook readFile(String filename) throws IOException
	{
		try (FileInputStream fis = new FileInputStream(filename))
		{
			return new HSSFWorkbook(fis);
		}
	}
}

2.2：通过jxl实现该功能：

package jxl;

import java.io.File;

import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class TestVba
{

	/**
	 * @param args
	 */
	private static final String TEMPLET_FILE = "F:\\jxlTestPic\\vba_image_templet.xls";
	private static final String IMAGE_COMMENT = "image";

	private static final String FILEPATH = "F:\\jxlTestPic\\";
	private static final String FILENAME = "jxltwo.xls";

	public static void main(String[] args)
	{
		try
		{
			String targetfile = FILEPATH + FILENAME;// 生成文件位置
			File file = new File(targetfile);
			Workbook wk = Workbook.getWorkbook(new File(TEMPLET_FILE));

			WritableWorkbook wwb = Workbook.createWorkbook(file, wk);
			// 创建Excel电子薄;
			WritableSheet sheet = wwb.createSheet("插入图片演示", 0);

			WritableCellFormat cellFormat = new WritableCellFormat();
			// 设置背景颜色;
			cellFormat.setBackground(Colour.WHITE);
			// 设置边框;
			cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			// 设置自动换行;
			cellFormat.setWrap(true);
			// 设置文字居中对齐方式;
			cellFormat.setAlignment(Alignment.CENTRE);
			// 设置垂直居中;
			cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

			Label imageLabel = new Label(0, (int) 2, "images\\图片51\\20171227150115433_279a2e86_CAMERA.jpeg", cellFormat);
			sheet.addCell(imageLabel);
			WritableCellFeatures features = new WritableCellFeatures();
			features.setComment(IMAGE_COMMENT);
			WritableCellFormat cellFormat1 = new WritableCellFormat();
			Label label = new Label(0, 1, "图片列", cellFormat1);
			label.setCellFeatures(features);
			sheet.addCell(label);

			// Label imageLabel2 = new Label(0, (int) 3, "haha", cellFormat);
			// sheet.addCell(imageLabel2);
			WritableCellFormat cellFormat2 = new WritableCellFormat();
			Label label2 = new Label(0, 0, "", cellFormat2);
			sheet.addCell(label2);

			// 写入Excel表格中;
			wwb.write();
			// 关闭流;
			wwb.close();
			System.out.println("恭喜，图片插入成功！");

		}
		catch (Exception e)
		{
			e.printStackTrace();
		}

	}

}

打开目标文件是，会提示是否加载宏，点击确定（因为只有同意了启用宏才会执行vb脚本，继而把图片加载到excel，若不启用宏，则单元格中仅是展示图片路径），入下图