Copying cell range with images in Excel files

Question

I'm copying cells from one Excel sheet into another with GemBox.Spreadsheet. The cells are coming from a specific named range and I'm using CellRange.CopyTo method like this:

ExcelFile book = ExcelFile.Load("sv-data.xlsx");
ExcelWorksheet sheet1 = book.Worksheets[0];
CellRange range1 = sheet1.NamedRanges["SV"].Range;
ExcelWorksheet sheet2 = book.Worksheets.Add("Sheet2");
range1.CopyTo(sheet2, 14, 3);

This works great for all the cells' value and formatting, but it doesn't copy over the images.

Is this the intended behavior? How can I copy both data and images?

A worksheet has a Shapes collection. That is how you handle images. It works similarly to ranges, but it's obviously more intricate. This should put you on track : ocs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa174305(v=office.11) — MarcG, Nov 04 '20 at 12:11

score 2 · Accepted Answer · answered Nov 05 '20 at 04:45

Yes, it seems to be intended because images are not stored inside the cells, but rather inside a sheet. They are part of a separate collection, the ExcelWorksheet.Pictures.

So, perhaps you could iterate through that collection and copy the required elements.

For example, something like the following:

ExcelFile book = ExcelFile.Load("sv-data.xlsx");
ExcelWorksheet sheet1 = book.Worksheets[0];
CellRange range1 = sheet1.NamedRanges["SV"].Range;

ExcelWorksheet sheet2 = book.Worksheets.Add("Sheet2");

int row2 = 14;
int column2 = 3;
range1.CopyTo(sheet2, row2, column2);

int rowOffset = row2 - range1.FirstRowIndex;
int columnOffset = column2 - range1.FirstColumnIndex;

foreach (ExcelPicture picture1 in sheet1.Pictures)
{
    ExcelDrawingPosition position1 = picture1.Position;
    CellRange pictureRange1 = sheet1.Cells.GetSubrangeAbsolute(position1.From.Row.Index, position1.From.Column.Index, position1.To.Row.Index, position1.To.Column.Index);

    if (range1.Overlaps(pictureRange1))
    {
        ExcelPicture picture2 = sheet2.Pictures.AddCopy(picture1);
        ExcelDrawingPosition position2 = picture2.Position;

        position2.From.Row = sheet2.Rows[position2.From.Row.Index + rowOffset];
        position2.To.Row = sheet2.Rows[position2.To.Row.Index + rowOffset];
        position2.From.Column = sheet2.Columns[position2.From.Column.Index + columnOffset];
        position2.To.Column = sheet2.Columns[position2.To.Column.Index + columnOffset];
    }
}

book.Save("output.xlsx");

Copying cell range with images in Excel files

1 Answers1

Linked