你如何复制Excel工作表到新工作簿，并把所有的图表，图像等？ [英] How do you copy an Excel worksheet into a new workbook AND bring all the charts, images, etc.?

问题描述

我们的应用程序发布功能。这需要几个Excel 2007中的电子表格，把它们拷贝到一张纸上，然后将其电子邮件给用户。问题是，图像和图表没有抄袭过。我已经试过一切，我在这里可以找到和其他各种来源，但遗憾的是似乎没有任何工作。我们的应用程序使用VSTO写的，我也尝试过的OpenXML，但似乎没有任何工作。事实上试图OpenXML中损坏的文件如此糟糕，Excel中无法读取或恢复他们拷贝过来。下面是我们使用的代码（注意：我们有ExcelWrapper只是调用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).

 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();
    }

我已经想尽有前途的选项，并搜查了VSTO和OpenXML的对象模型都和我难倒。请StackOverflow的社区，你是我唯一的希望。

I have tried every promising option and searched both the VSTO and OpenXML object models and I am stumped. Please stackoverflow community, you're my only hope.

更新：这里的答案乡亲：

 //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.

推荐答案

您将需要检查你正在寻找复制工作表的工作表对象，然后通过所有的*对象的属性运行，并为每个集合，编写的代码，收集到新的工作表中的所有元素手动复制。

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.

有关例如，你有：

ChartObjects
ListObjects
OleObjects
形状（可能获得与表一起复制，我不敢肯定）。

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

这篇关于你如何复制Excel工作表到新工作簿，并把所有的图表，图像等？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！