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?

Answer 1

EDIT (2022-10-28):

In the current latest version of GemBox.Spreadsheet the CellRange.CopyTo() method is copying pictures, shapes, and charts.

Also, there is another set of CellRange.CopyTo() overload methods that accept the CopyOptions parameter with which you can specify what you want to copy.

For example:

range1.CopyTo(sheet2, row2, column2,
    new CopyOptions() { CopyTypes = CopyTypes.Values | CopyTypes.Styles | CopyTypes.Drawings });

Also, see the second example on this page (it shows various options for copying and deleting cell ranges):
https://www.gemboxsoftware.com/spreadsheet/examples/excel-sheet-copy-delete/111

ORIGINAL:

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");