Copying Shapes (especially charts) with SpreadsheetGear

Question

My task right now is to go through some workbooks within my C# web application and aggregate certain parts of each workbook into a single new workbook. This requires me to copy cell ranges using SpreadsheetGear. With most things, this works fine, but copying a range does not copy shapes (images or charts) within it.

In order to copy charts, I've elected to simply identify the cell range within which the chart resides, and turn that cell range into an image which I can add as a picture in my other worksheet. I've tested this and have had partial success - some chart formatting seems to be lost when initially opening the workbook with SpreadsheetGear. Incidentally, when saving a worksheet containing such a chart, even more formatting seems to disappear.

The worksheets with charts were made with Excel 2013, and I am using SpreadsheetGear version 7.1.1.120.

Here are images of the charts I am starting with and the image that gets copied from the cell range containing the graph (another strange thing - notice that the shape in A2:A3 doesn't get its range copied, and indeed doesn't show up in the sheet.Shapes collection).

https://i.imgsafe.org/dc2b7118c4.png

https://i.imgsafe.org/dc2bec6e8f.png

Here's some minimal code I used to produce the above example. I go through all the shapes in the sheet and copy the range containing the shape as an image:

string exPath = System.Web.HttpContext.Current.Server.MapPath(@"~/App_Data");
string exFile = exPath + @"\examplechart.xlsx";
IWorkbook exBook = SpreadsheetGear.Factory.GetWorkbook(exFile, System.Globalization.CultureInfo.CurrentCulture);
IWorksheet inSheet = exBook.Worksheets["Sheet1"];
IWorksheet outSheet = exBook.Worksheets["Sheet2"];
SpreadsheetGear.Shapes.IShapes shapes = inSheet.Shapes;
foreach (SpreadsheetGear.Shapes.IShape shape in shapes)
{
    IRange topLeft = shape.TopLeftCell;
    IRange botRight = shape.BottomRightCell;
    IRange imageRange = inSheet.Range[string.Format("{0}:{1}", topLeft.Address, botRight.Address)];

    // Where to place the shape in terms of columns and rows
    int destinationColumn = shape.TopLeftCell.Column;
    int destinationRow = shape.TopLeftCell.Row;

    // Copy column widths
    imageRange.Copy(outSheet.Range[destinationRow, destinationColumn], SpreadsheetGear.PasteType.ColumnWidths, PasteOperation.None, false, false);

    // Add the cell range within which the shape is, as a picture, in the correct place
    double left = outSheet.WindowInfo.ColumnToPoints(destinationColumn);
    double right = outSheet.WindowInfo.ColumnToPoints(destinationColumn + imageRange.ColumnCount);
    double top = outSheet.WindowInfo.RowToPoints(destinationRow);
    double bottom = outSheet.WindowInfo.RowToPoints(destinationRow + imageRange.RowCount);
    SpreadsheetGear.Drawing.Image image = new SpreadsheetGear.Drawing.Image(imageRange);
    Bitmap bitmap = image.GetBitmap();
    ImageConverter converter = new ImageConverter();
    byte[] byteArray = (byte[])converter.ConvertTo(bitmap, typeof(byte[]));

    outSheet.Shapes.AddPicture(byteArray, left, top, right - left, bottom - top);
}
exBook.SaveAs(exPath + @"\examplechart-output.xlsx", FileFormat.OpenXMLWorkbook);
exBook.Close();

I've tried to turn the shapes themselves into images rather than their cell range, with the same results. It doesn't look like copying shapes and charts is a very common thing to do with SpreadsheetGear, so maybe that's why I haven't heard of these types of problems (or maybe I'm just using too old a version of SpreadsheetGear?).

If anyone knows why the chart looks so strange when opened by SpreadsheetGear, that's the main thing I'm trying to find out. But if anyone knows a more straight-forward way to copy shapes (including charts) from one sheet to another, that could also solve my problem. (Bonus question might be: why is the shape at A2:A3 not even there when the sheet is opened by SpreadsheetGear?)

score 0 · Answer 1 · answered Feb 10 '17 at 16:10

I contacted SpreadsheetGear support and they very helpfully clarified that in my case, it was an unfortunate mix of things in the worksheet which SpreadsheetGear did not support, that was causing problems. Namely (in my case) static data cache for the series values in a chart, the 'show series name' option for series data labels, "tailend" arrowhead options on lines, and the quad arrow autoshape. This may be useful in the future for people who need to copy across charts or shapes with SpreadsheetGear and are encountering problems, but I now consider this problem resolved.

Also, I was advised to use the 'SpreadsheetGear 2012 for Windows' application that comes with SpreadsheetGear to inspect worksheets in the future, to easily see what is and isn't supported.

Copying Shapes (especially charts) with SpreadsheetGear

1 Answers1