Adding Drawing part image prevents the cell update

[ZubairImran]

Hi Team,
I am adding a image using drawing layer(drawing part) in a Excel file. I could able to add the image but the image prevents the cells available in the background. Is there anyway to send the image to background using openxml sdk. Any pointer would be great help.

[mikeebowen]

Hi @ZubairImran,

This will add an image to the background of a sheet:

using DocumentFormat.OpenXml.Packaging;

string xlsxPath = @"path/to/your.xlsx";
string imagePath = @"path/to/your/image.jpg";

using (var doc = SpreadsheetDocument.Open(xlsxPath, true))
{
    if (doc?.WorkbookPart is not { } workbookPart ||
        workbookPart.WorksheetParts.FirstOrDefault() is not { } worksheetPart)
    {
        throw new ArgumentException("Can't find worksheet part");
    }

    ImagePart imagePart = worksheetPart.AddImagePart("image/jpeg");
    var id = workbookPart.GetIdOfPart(imagePart);

    using (var stream = File.OpenRead(imagePath))
    {
        imagePart.FeedData(stream);
    }

    worksheetPart.Worksheet.AddChild(new DocumentFormat.OpenXml.Spreadsheet.Picture() { Id = id});
}

[ZubairImran]

@mikeebowen , Thanks for the above code and time. But when i tried it is not working and corrupting the excel. Am i missing anything.

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filePath, true))
{
    WorkbookPart workbookPart = spreadsheet.WorkbookPart;
    WorksheetPart worksheetPart = GetWorksheetPartByName(workbookPart, sheetName);

    if (worksheetPart == null)
    {
        throw new Exception($"Worksheet '{sheetName}' not found.");
    }

    ImagePart imagePart = worksheetPart.AddImagePart("image/png");
    var id = worksheetPart.GetIdOfPart(imagePart);
    string imagePath = "c:\\users\\public\\t.png";
    using (var stream = File.OpenRead(imagePath))
    {
        imagePart.FeedData(stream);
    }

    worksheetPart.Worksheet.AddChild(new DocumentFormat.OpenXml.Spreadsheet.Picture() { Id = id });
    worksheetPart.Worksheet.Save();

}
 private static WorksheetPart GetWorksheetPartByName(WorkbookPart workbookPart, string sheetName)
{
    foreach (Sheet sheet in workbookPart.Workbook.Sheets)
    {
        if (sheet.Name == sheetName)
        {
            return (WorksheetPart)workbookPart.GetPartById(sheet.Id);
        }
    }
    return null;
}

[ZubairImran]

@mikeebowen , i have tried again with simple xlsx with single sheet, the above code is working fine. But its adding the image multiple time in same sheet. Is it the actual behaviour?

[mikeebowen]

Hi @ZubairImran, repeating the image in the background is the expected behavior, because a sheet has an unlimited number of rows, i.e. infinite height, there is no way for Excel to know how to scale the image.

[ZubairImran]

Thanks @mikeebowen for the prompt reply.