Search code examples
c#excelepplusepplus-4

How can I add an image to my sheet without being prompted to replace contents of the (empty) destination cells (EPPlus)?

When I open the Excel file I generate using EPPlus (C#), I get "Do you want to replace the contents of the destination cells in [... .xlsx]PivotTable?"

As you can see in the screen shot below, there is nothing in the cell where I want the image to display. Even when I push the image beyond the outer edge of the PivotTable, I get this message (I had been placing beneath column C/3). When I select yes to the dialog quoted above, the image is finally placed on the sheet (if I select "no" it is not added), and the sheet looks like this:

enter image description here

This is the code I'm using to add the image:

int imageCol = _grandTotalsColumnPivotTable + 1; // -1; <= I really want it to be this
AddImage(pivotTableWorksheet, 1, imageCol);

. . .

private void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex)
{
    var excelImage = oSheet.Drawings.AddPicture("PRO*ACT Logo", _logo);
    excelImage.From.Column = colIndex - 1;
    excelImage.From.Row = rowIndex - 1;
    excelImage.SetSize(130, 100);
    excelImage.From.ColumnOff = Pixel2MTU(2);
    excelImage.From.RowOff = Pixel2MTU(2);
}

public int Pixel2MTU(int pixels)
{
    int mtus = pixels * 9525;
    return mtus;
}

How can I either get the message to not display (programmatically, this can't be something the user needs to do) and "just do it" or let it know that there is nothing in that location, anyway?

UPDATE

This is apparently not the problem; I tried to delete this post, but can't, since it has an answer. Even when I comment out the writing of the image, I get it. And trying to prevent messages like so:

_xlApp.DisplayAlerts = false;

...does not work, either - I still get it.

Apparently the problem is elsewhere, but I don't know where.

UPDATE 2

It was a case of hidden data underneath the PivotTable; once I got rid of that, the problem went away. IOW, the problem was with my code, not with EPPlus.

Solution

  • I use this code to add an image to Excel. It does not asks me to replace content. But I do not use PivotTables. Maybe that would make a difference.

    using (System.Drawing.Image image = System.Drawing.Image.FromFile(HttpContext.Current.Server.MapPath("test.png")))
{
     var excelImage = ws.Drawings.AddPicture("My Logo", image);
     excelImage.SetPosition(1, 0, 5, 0);
}