先看一下插入后的效果
转载请保留 http://www.luofenming.com/show.aspx?id=ART2020051800001
以下是核心源码
在这里 我在NuGet上下载的是 NPOI 2.5.1版本
引用命名空间
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
/// <summary> /// 插入图片到Excel2003版本 /// </summary> public void AddPic() { //读取Excel模版 FileStream file = new FileStream(@"d:\PurchaseOrder.xls", FileMode.Open, FileAccess.Read); IWorkbook workbook = new HSSFWorkbook(file); ISheet sheet1 = workbook.GetSheet("sheet1"); //插入图片 string picPath = @"C:\Users\lqwvje\Desktop\1.png"; FileStream picFs = File.OpenRead(picPath); //OpenRead int filelength = 0; filelength = (int)picFs.Length; //获得文件长度 Byte[] image = new Byte[filelength]; //建立一个字节数组 picFs.Read(image, 0, filelength); //按字节流读 int pictureIdx = workbook.AddPicture(image, PictureType.PNG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch(); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) //参数表示图片的位置.文章尾部有说明 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 10, 10, 13, 16); //把图片插到相应的位置 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); using (Stream stream = File.OpenWrite(@"d:\aaa.xls")) { workbook.Write(stream); } } /// <summary> /// 插入图片到Excel2007以上版本 /// </summary> public void AddPic2007() { //读取Excel模版 FileStream file = new FileStream(@"d:\test.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(file); ISheet sheet1 = workbook.GetSheet("sheet1"); //插入图片 string picPath = @"C:\Users\lqwvje\Desktop\2.png"; FileStream picFs = File.OpenRead(picPath); //OpenRead int filelength = 0; filelength = (int)picFs.Length; //获得文件长度 Byte[] image = new Byte[filelength]; //建立一个字节数组 picFs.Read(image, 0, filelength); //按字节流读 int pictureIdx = workbook.AddPicture(image, PictureType.PNG); IDrawing patriarch = sheet1.CreateDrawingPatriarch(); // 插图片的位置 XSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) //参数表示图片的位置.文章尾部有说明 IClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 2, 3, 6, 9); //把图片插到相应的位置 patriarch.CreatePicture(anchor, pictureIdx); using (Stream stream = File.OpenWrite(@"d:\aaa2.xlsx")) { workbook.Write(stream); } }
HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2);
XSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2);参数说明
dx1 dy1 起始单元格中的x,y坐标.
dx2 dy2 结束单元格中的x,y坐标
col1,row1 指定起始的单元格,下标从0开始
col2,row2 指定结束的单元格,下标从0开始