How to copy an Excel sheet to a new workbook and bring all the diagrams, images, etc.?

Question

Our application has distribution functions. It takes several Excel 2007 tables, copies them into one sheet, and then sends them to users. The problem is that images and charts are not copied. I tried everything I could find here and various other sources, but, unfortunately, nothing works. Our application is written using VSTO, and I also tried OpenXML, but nothing works. In fact, an attempt to copy to OpenXML damaged files so much that Excel could not read or restore them. Here is the code that we use (note: with us, ExcelWrapper simply calls the same functions in Excel, but hides all optional elements).

private void CreateWorkbook(string filePath, string batchReportsPath) { //place the xlsx file into a workbook. //call getfilesnames Excel.Workbook bookToCopy; Excel.Workbook newWorkbook; Excel.Worksheet tempSheet = new Excel.Worksheet(); newWorkbook = ExcelWrapper.WorkbooksAdd(ExcelApp.Workbooks); if (File.Exists(filePath)) File.Delete(filePath); ExcelWrapper.WorkbookSaveAs(newWorkbook, filePath); List<string> filePaths = new List<string>(Directory.GetFiles(batchReportsPath)); filePaths.ForEach(delegate(string reportPath) { string reportPathAndName = reportPath; bookToCopy = ExcelWrapper.WorkbooksOpen(ExcelApp.Workbooks, reportPathAndName); int nextSheetNumber = newWorkbook.Sheets.Count; ((Excel.Worksheet)sheetToSend.Sheets[1]).Copy(Type.Missing, newWorkbook.Sheets[nextSheetNumber]); ExcelWrapper.WorkbookClose(bookToCopy); }); newWorkbook.Save(); ExcelWrapper.WorkbookClose(newWorkbook); bookToCopy= null; tempSheet = null; newWorkbook = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } 

I tried every promising option and searched for both VSTO and OpenXML object models, and I'm at a dead end. The stackoverflow community, you are my only hope.

UPDATE:. Respondents' answers:

  //Copy all the images, Charts, shapes foreach (Excel.Shape o in copySheet.Shapes) { if (o.Type == Microsoft.Office.Core.MsoShapeType.msoPicture) o.CopyPicture(); else o.Copy(); newWorkbook.Save(); } 

You need to make a save after each copy to complete the paste. Thanks for your input.

Answer 1

You will need to check the WORKSHEET object on the sheet that you want to copy, and then run all the "* Objects" properties, and for each of these collections write code to manually copy all the elements in this collection to a new sheet.

For example, you have:

ChartObjects ListObjects OleObjects Forms (which can be copied along with the sheet, I'm not sure).

Answer 2

Perhaps you can get there with copy / paste? Here is an example that copies cell, graph, and image data: MSDN

Good luck

Answer 3

I have no answer, but I can give you some pointers. Firstly, this is not an openxml question, this is a question. Secondly, images and diagrams are tied to a spreadsheet, they are not content with it (subtle difference). Probably there is a property of all diagrams and one of all the images attached to the sheet. Copy this through.

(I use the IExtensibility interface instead of VSTO, so I know that in the base API, but not in the property names - sorry.)

Answer 4

Here's how I do it - moves the entire worksheet from the source book to a new book:

 public static void MoveWorksheet() { public static Excel.Application oXL; public static Excel._Worksheet ws; if (File.Exists(Global.Variables.GlobalVariables.recap)) { //workbookToOpen is location and name oXL.Workbooks.Open(workbookToOpen, Missing.Value, true); object ws = null; try { ws = wb.Sheets["Sheet 1"]; if (ws != null) { ((Worksheet)ws).UsedRange.Interior.ColorIndex = Constants.xlNone; ((Worksheet)ws).Copy(); oXL.ActiveWorkbook.SaveAs("SaveAsFileName" + ".xlsx"); oXL.ActiveWorkbook.Close(true); } } catch {} } }