使用OpenXML SDK在VB.net/C#中能否向Excel单元格内插入图片而非浮于其上？

可以通过OpenXML SDK在VB.NET或C#中实现将图片插入Excel单元格内部，使其随单元格筛选联动隐藏，核心是将图片设置为与单元格绑定关联，而非普通的浮动形状。

实现原理 #

Excel客户端中“嵌入单元格”的图片，本质是通过设置形状的锚点属性，让图片与单元格绑定：

• 使用TwoCellAnchor定义图片的位置范围，并设置EditAs="OneCell"，让图片被视为单元格的一部分
• 将图片的锚点关联到目标单元格的行和列，确保移动、筛选单元格时图片同步变化

C# 示例代码 #

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using DocumentFormat.OpenXml.Drawing;
using System.IO;

public static void InsertImageIntoCell(string excelPath, string sheetName, string cellAddress, string imagePath)
{
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(excelPath, true))
    {
        WorkbookPart workbookPart = doc.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault(w => w.Worksheet.Name?.Value == sheetName);
        if (worksheetPart == null)
            throw new System.Exception("指定工作表不存在");

        // 创建图片资源部分
        ImagePart imagePart = worksheetPart.AddImagePart(ImagePartType.Png);
        using (FileStream stream = new FileStream(imagePath, FileMode.Open))
        {
            imagePart.FeedData(stream);
        }

        // 解析目标单元格的行、列索引
        CellReference cellRef = new CellReference(cellAddress);
        uint rowIndex = cellRef.Row.Value;
        uint columnIndex = (uint)CellReference.ConvertColumnLetterToNumber(cellRef.ColumnName);

        // 创建绑定到单元格的锚点
        TwoCellAnchor anchor = new TwoCellAnchor();
        anchor.EditAs = EditValues.OneCell;

        // 锚点起始位置（目标单元格左上角）
        anchor.From = new From(
            new ColumnId(columnIndex.ToString()),
            new ColumnOffset("0"),
            new RowId(rowIndex.ToString()),
            new RowOffset("0")
        );

        // 锚点结束位置（与起始位置一致，确保图片仅占单个单元格）
        anchor.To = new To(
            new ColumnId(columnIndex.ToString()),
            new ColumnOffset("0"),
            new RowId(rowIndex.ToString()),
            new RowOffset("0")
        );

        // 构建图片形状
        Picture pic = new Picture();
        NonVisualPictureProperties nvPicProps = new NonVisualPictureProperties(
            new NonVisualDrawingProperties() { Id = 1U, Name = "嵌入式图片" },
            new NonVisualPictureDrawingProperties(new PictureLocks() { NoChangeAspect = true })
        );
        BlipFill blipFill = new BlipFill(
            new Blip(new EmbeddedPicture() { Id = worksheetPart.GetIdOfPart(imagePart) })
            { Embed = worksheetPart.GetIdOfPart(imagePart), CompressionState = BlipCompressionValues.Print },
            new Stretch(new FillRectangle())
        );
        ShapeProperties shapeProps = new ShapeProperties(
            new Transform2D(
                new Offset() { X = 0, Y = 0 },
                new Extents() { Cx = 952500, Cy = 952500 } // 图片尺寸，可按需调整
            ),
            new PresetGeometry(new AdjustValueList()) { Preset = ShapeTypeValues.Rectangle }
        );

        pic.Append(nvPicProps, blipFill, shapeProps);
        anchor.Append(pic, new ClientData());

        // 将图片添加到工作表的绘图部分
        DrawingsPart drawingsPart;
        if (worksheetPart.DrawingsPart == null)
        {
            drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
            worksheetPart.Worksheet.Append(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) });
            drawingsPart.WorksheetDrawing = new WorksheetDrawing();
        }
        else
        {
            drawingsPart = worksheetPart.DrawingsPart;
        }

        drawingsPart.WorksheetDrawing.Append(anchor);
        drawingsPart.WorksheetDrawing.Save();
        worksheetPart.Worksheet.Save();
    }
}

VB.NET 示例代码 #

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports DocumentFormat.OpenXml.Drawing.Spreadsheet
Imports DocumentFormat.OpenXml.Drawing
Imports System.IO

Public Shared Sub InsertImageIntoCell(excelPath As String, sheetName As String, cellAddress As String, imagePath As String)
    Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(excelPath, True)
        Dim workbookPart As WorkbookPart = doc.WorkbookPart
        Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.FirstOrDefault(Function(w) w.Worksheet.Name?.Value = sheetName)
        If worksheetPart Is Nothing Then
            Throw New Exception("指定工作表不存在")
        End If

        ' 创建图片资源部分
        Dim imagePart As ImagePart = worksheetPart.AddImagePart(ImagePartType.Png)
        Using stream As New FileStream(imagePath, FileMode.Open)
            imagePart.FeedData(stream)
        End Using

        ' 解析目标单元格的行、列索引
        Dim cellRef As New CellReference(cellAddress)
        Dim rowIndex As UInteger = cellRef.Row.Value
        Dim columnIndex As UInteger = CUInt(CellReference.ConvertColumnLetterToNumber(cellRef.ColumnName))

        ' 创建绑定到单元格的锚点
        Dim anchor As New TwoCellAnchor()
        anchor.EditAs = EditValues.OneCell

        ' 锚点起始位置（目标单元格左上角）
        anchor.From = New From(
            New ColumnId(columnIndex.ToString()),
            New ColumnOffset("0"),
            New RowId(rowIndex.ToString()),
            New RowOffset("0")
        )

        ' 锚点结束位置（与起始位置一致）
        anchor.To = New To(
            New ColumnId(columnIndex.ToString()),
            New ColumnOffset("0"),
            New RowId(rowIndex.ToString()),
            New RowOffset("0")
        )

        ' 构建图片形状
        Dim pic As New Picture()
        Dim nvPicProps As New NonVisualPictureProperties(
            New NonVisualDrawingProperties() With {.Id = 1UI, .Name = "嵌入式图片"},
            New NonVisualPictureDrawingProperties(New PictureLocks() With {.NoChangeAspect = True})
        )
        Dim blipFill As New BlipFill(
            New Blip(New EmbeddedPicture() With {.Id = worksheetPart.GetIdOfPart(imagePart)}) With {
                .Embed = worksheetPart.GetIdOfPart(imagePart),
                .CompressionState = BlipCompressionValues.Print
            },
            New Stretch(New FillRectangle())
        )
        Dim shapeProps As New ShapeProperties(
            New Transform2D(
                New Offset() With {.X = 0, .Y = 0},
                New Extents() With {.Cx = 952500, .Cy = 952500} ' 图片尺寸，可按需调整
            ),
            New PresetGeometry(New AdjustValueList()) With {.Preset = ShapeTypeValues.Rectangle}
        )

        pic.Append(nvPicProps, blipFill, shapeProps)
        anchor.Append(pic, New ClientData())

        ' 将图片添加到工作表的绘图部分
        Dim drawingsPart As DrawingsPart
        If worksheetPart.DrawingsPart Is Nothing Then
            drawingsPart = worksheetPart.AddNewPart(Of DrawingsPart)()
            worksheetPart.Worksheet.Append(New Drawing() With {.Id = worksheetPart.GetIdOfPart(drawingsPart)})
            drawingsPart.WorksheetDrawing = New WorksheetDrawing()
        Else
            drawingsPart = worksheetPart.DrawingsPart
        End If

        drawingsPart.WorksheetDrawing.Append(anchor)
        drawingsPart.WorksheetDrawing.Save()
        worksheetPart.Worksheet.Save()
    End Using
End Sub

关键注意事项 #

• 必须设置EditAs="OneCell"：这是让图片成为单元格一部分的核心属性，缺失此设置图片仍会保持浮动状态
• 锚点的From和To需指向同一个单元格：确保图片完全嵌入目标单元格内，不会跨单元格
• 若目标单元格属于Excel表格（Table），需确保图片锚点在表格范围内，这样筛选表格时图片才会随单元格同步隐藏/显示

内容的提问来源于stack exchange，提问作者Simyan