如何将Excel工作表复制到新工作簿中并带来所有图表，图像等？

Our application has distribution functionality. It takes several Excel 2007 spreadsheets, copies them into a single sheet, then emails them to the users. The problem is that images and charts are not copying over. I have tried everything I can find on here and various other sources, but sadly nothing seems to work. Our application is written using VSTO, and I have also tried OpenXML, but nothing seems to work. In fact trying to copy over in OpenXML corrupted the files so badly that Excel could not read or recover them. Here's the code we use (note: we have ExcelWrapper simply calls the same functions on the Excel App but hides all the optional items).

我们的应用具有分发功能。它需要几个Excel 2007电子表格，将它们复制到一个工作表中，然后通过电子邮件发送给用户。问题是图像和图表没有复制。我已经尝试了我能在这里找到的所有东西和其他各种来源，但遗憾的是似乎没有任何工作。我们的应用程序是使用VSTO编写的，我也尝试过OpenXML，但似乎没有任何工作。实际上，尝试在OpenXML中进行复制会严重损坏文件，导致Excel无法读取或恢复它们。这是我们使用的代码（注意：我们让ExcelWrapper在Excel App上调用相同的函数，但隐藏了所有可选项）。

 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 have tried every promising option and searched both the VSTO and OpenXML object models and I am stumped. Please * community, you're my only hope.

我已经尝试了所有有前途的选项，并搜索了VSTO和OpenXML对象模型，我很难过。请*社区，你是我唯一的希望。

UPDATE: Here's the answer folks:

更新：这是答案的人：

 //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 do the save after each copy to get the Paste to finalize. Thanks for your input.

您需要在每次复制后执行保存以获取最终确定的粘贴。感谢您的输入。

4 个解决方案

#1

You'll need to check the WORKSHEET object of the worksheet you're looking to copy, then run through all the "*Objects" properties, and, for each of those collections, write code to manually copy all the elements in that collection to the new sheet.

您需要检查要复制的工作表的WORKSHEET对象，然后运行所有“* Objects”属性，并为每个集合编写代码以手动复制该集合中的所有元素新表。

For example, you've got:

例如，你有：

ChartObjects ListObjects OleObjects Shapes (Which might get copied along with the sheet, I'm not sure).

ChartObjects ListObjects OleObjects形状（可能会与工作表一起复制，我不确定）。

#2

Perhaps you can get there by a copy/paste? Here's an example that copies cell data, charts and images: MSDN

也许你可以通过复制/粘贴到达那里？这是一个复制单元格数据，图表和图像的示例：MSDN

Good luck!

祝你好运！

#3

I don't have an answer but I can give you some pointers. First, this is not an openxml question, it's a vsto question. Second, images and charts are attached to a spreadsheet, they are not content in it (a subtle distinction). There is probably a property of all charts and one of all images attached to the sheet. Copy that across.

我没有答案，但我可以给你一些指示。首先，这不是一个openxml问题，这是一个vsto问题。其次，图像和图表附在电子表格中，它们并不满足于它（一种微妙的区别）。可能存在所有图表的属性以及附在表格上的所有图像之一。复制它。

(I use the IExtensibility interface instead of VSTO so I know what's in the underlying API, but not the property names - sorry.)

（我使用IExtensibility接口而不是VSTO，所以我知道底层API中有什么，但不知道属性名称 - 抱歉。）

#4

This is how I do it -- moves the entire worksheet from source workbook to a brand new workbook:

我是这样做的 - 将整个工作表从源工作簿移动到一个全新的工作簿：

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 {}
}
}

#1