.Net Core NPOI Excel插入图片_Excel图片操作

一、NPOI Excel插入图片案例

//创建工作簿
HSSFWorkbook wk = new HSSFWorkbook();
ISheet sheet = wk.CreateSheet("sheet1");
//sheet.SetColumnWidth(0, 1);
//sheet.AutoSizeColumn(0);

//添加内容
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
HSSFRichTextString rich = new HSSFRichTextString("签名：");
cell.SetCellValue(rich);

//添加图片
byte[] bytes = System.IO.File.ReadAllBytes(@"D:\1.jpg");
int pictureIdx = wk.AddPicture(bytes, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

//HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
HSSFClientAnchor anchor = new HSSFClientAnchor(223, 0, 0, 0, 0, 0, 1, 1);
//设置图片变换类型
anchor.AnchorType = AnchorType.MoveDontResize;
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize(100,50);
//保存到文件
using (FileStream fs = File.OpenWrite("testimg.xls"))
{
    wk.Write(fs);
    Console.WriteLine("导出数据成功！");
}

二、HSSFClientAnchor 位置说明

//设置图片坐标与大小
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第六列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小

//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效

三、绘制图形

//绘制图形
HSSFSimpleShape triangle = (HSSFSimpleShape)patriarch.CreateSimpleShape(anchor);
triangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_PICTURE;
triangle.SetFillColor(255,0,0);

四、读取Excel模版，插入图片

using (FileStream fs = File.OpenRead("2.xls"))
{
    //读取Excel 
    HSSFWorkbook wk = new HSSFWorkbook(fs);
    ISheet sheet = wk.GetSheetAt(0);
    ICell cell = wk.GetSheetAt(0).GetRow(0).GetCell(0);

    //插入图片
    byte[] bytes = System.IO.File.ReadAllBytes(@"D:\1.jpg");
    int pictureIdx = wk.AddPicture(bytes, PictureType.JPEG);
    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
    HSSFClientAnchor anchor = new HSSFClientAnchor(223, 0, 0, 0, 0, 0, 1, 1);
    anchor.AnchorType = AnchorType.MoveDontResize;
    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
    //保存
    using (FileStream fsNew = File.OpenWrite("testimg2.xls"))
    {
        wk.Write(fsNew);
        Console.WriteLine("导出数据成功！");
    }
}

 

 

更多：

NPOI 获取行数、获取列数

NPOI Excel导出报错 Cannot access a closed stream

NPOI Excel导出超出65536行报错 Invalid row number (65536) outside allowable range (0..65535)解决方法