NPOI 在指定单元格导入导出图片

Intro#

我维护vb.net教程了一个 NPOI 的扩展(WeihanLi.Npoi)，主要用来c#教程导入导python基础教程出 Excel 数据，最近java基础教程有网友提出了导sql教程入 Excel 的时候解析图片的需求，于是就有了本文的探索

导入Excel 时解析图片#

xls 和 xlsx 的 API 稍有不同，详细可以直接参考以下代码，实现代码如下：

public static Dictionary<CellPosition, IPictureData> GetPicturesAndPosition(this ISheet sheet)
{
    var dictionary = new Dictionary<CellPosition, IPictureData>();
    if (sheet.Workbook is HSSFWorkbook)
    {
        foreach (var shape in ((HSSFPatriarch)sheet.DrawingPatriarch).Children)
        {
            if (shape is HSSFPicture picture)
            {
                var position = new CellPosition(picture.ClientAnchor.Row1, picture.ClientAnchor.Col1);
                dictionary[position] = picture.PictureData;
            }
        }
    }
    else if (sheet.Workbook is XSSFWorkbook)
    {
        foreach (var shape in ((XSSFDrawing)sheet.DrawingPatriarch).GetShapes())
        {
            if (shape is XSSFPicture picture)
            {
                var position = new CellPosition(picture.ClientAnchor.Row1, picture.ClientAnchor.Col1);
                dictionary[position] = picture.PictureData;
            }
        }
    }
    return dictionary;
}

CellPosition 是一个自定义的结构体，表示当前单元格的位置，源码如下：

public readonly struct CellPosition : IEquatable<CellPosition>
{
    public CellPosition(int row, int col)
    {
        Row = row;
        Column = col;
    }

    public int Row { get; }
    public int Column { get; }

    public bool Equals(CellPosition other)
    {
        return Row == other.Row && Column == other.Column;
    }

    public override bool Equals(object? obj) => obj is CellPosition other && Equals(other);

    public override int GetHashCode() => $"{Row}_{Column}".GetHashCode();
}

根据上面的代码，我们就可以获取到获取到所有的图片以及图片的所在位置，这样根据单元格位置去找图片信息的时候就会很方便了

导出 Excel 时设置图片#

实现代码如下：

public static bool TryAddPicture(this ISheet sheet, int row, int col, byte[] pictureBytes, PictureType pictureType = PictureType.PNG)
{
    if (sheet is null)
    {
        throw new ArgumentNullException(nameof(sheet));
    }

    try
    {
        var pictureIndex = sheet.Workbook.AddPicture(pictureBytes, pictureType);

        var clientAnchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor();
        clientAnchor.Row1 = row;
        clientAnchor.Col1 = col;

        var picture = (sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch())
            .CreatePicture(clientAnchor, pictureIndex);
        picture.Resize();
        return true;
    }
    catch (Exception e)
    {
        Debug.WriteLine(e);
    }

    return false;
}

通过上面的代码我们就可以在指定的单元格设置图片，目前没有支持单元格合并操作，有需要自己进行修改

WeihanLi.Npoi#

WeihanLi.Npoi 在 1.15.0 版本中增加了图片导入导出的支持，使用示例可以参考下面的单元测试：

[Theory]
[ExcelFormatData]
public async Task ImageImportExportTest(ExcelFormat excelFormat)
{
    using var httpClient = new HttpClient();
    var imageBytes = await httpClient.GetByteArrayAsync("https://weihanli.xyz/assets/avator.jpg");
    var list = Enumerable.Range(1, 5)
        .Select(x => new ImageTest() { Id = x, Image = imageBytes })
        .ToList();
    var excelBytes = list.ToExcelBytes(excelFormat);
    var importResult = ExcelHelper.ToEntityList<ImageTest>(excelBytes, excelFormat);
    Assert.NotNull(importResult);
    Assert.Equal(list.Count, importResult.Count);
    for (var i = 0; i < list.Count; i++)
    {
        Assert.NotNull(importResult[i]);
        var result = importResult[i]!;
        Assert.Equal(list[i].Id, result.Id);
        Assert.NotNull(result.Image);
        Assert.True(list[i].Image.SequenceEqual(result.Image));
    }
}

private class ImageTest
{
    public int Id { get; set; }

    public byte[] Image { get; set; } = null!;
}

导入时会自动将 byte[] 类型的属性尝试获取对应的单元格位置的图片，如果在对应的位置找到了图片就能够读取到图片的字节数组信息映射到 model 里的字节数组属性

除此之外，还支持直接导入的时候将图片信息 Map 到 IPictureData 属性上，但是觉得还是字节数组更通用一些，如果对此感兴趣可以参考项目源码以及单元测试

More#

感谢 @ZeguangZhang94 童鞋提出的需求和帮忙测试~

如果你也有类似的读取指定单元格的图片或者在指定单元格插入图片的需求，可以试一下上面的方法，希望对你有帮助，可以直接引用我的类库或者直接拷贝源码到自己的项目里使用

References#

• https://github.com/WeihanLi/WeihanLi.Npoi
• https://github.com/WeihanLi/WeihanLi.Npoi/blob/dev/src/WeihanLi.Npoi/NpoiExtensions.cs#L1143
• https://stackoverflow.com/questions/24084129/read-image-from-excel-file-using-npoi
• https://stackoverflow.com/questions/41138848/add-image-to-excel-xlsx-using-npoi-c-sharp
• https://github.com/WeihanLi/WeihanLi.Npoi/issues/99

作者：WeihanLi