创建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,若不启用宏,则单元格中仅是展示图片路径),入下图