创建并将Excel图表嵌入到Microsoft PowerPoint幻灯片中的OLE对象

图表是数据的直观表示，广泛应用于演示幻灯片中。本文将向您展示如何通过使用VSTO和Aspose.Slides for PHP via Java以编程方式创建并将Excel图表作为OLE对象嵌入到PowerPoint幻灯片中。

创建并嵌入Excel图表

下面的两个代码示例较长且详细，因为它们描述的任务比较复杂。您需要创建一个Microsoft Excel工作簿，创建一个图表，然后创建一个将嵌入图表的Microsoft PowerPoint演示文稿。OLE对象包含指向原始文档的链接，因此用户双击嵌入的文件会启动该文件及其应用程序。

VSTO示例

使用VSTO，执行以下步骤：

1. 创建Microsoft Excel ApplicationClass对象的实例。
2. 创建一个包含一个工作表的新工作簿。
3. 向工作表添加图表。
4. 保存工作簿。
5. 打开包含图表数据的工作表的Excel工作簿。
6. 获取工作表的ChartObjects集合。
7. 获取要复制的图表。
8. 创建Microsoft PowerPoint演示文稿。
9. 向演示文稿添加一个空白幻灯片。
10. 从Excel工作表复制图表到剪贴板。
11. 将图表粘贴到PowerPoint演示文稿中。
12. 在幻灯片上定位图表。
13. 保存演示文稿。

CreateNewChartInExcel();
UseCopyPaste();

static void SetCellValue(xlNS.Worksheet targetSheet, string Cell, object Value)
{
    targetSheet.get_Range(Cell, Cell).set_Value(xlNS.XlRangeValueDataType.xlRangeValueDefault, Value);
}

static void CreateNewChartInExcel()
{
    // Declare a variable for the Excel ApplicationClass instance.
    Microsoft.Office.Interop.Excel.ApplicationClass excelApplication = null;

    // Declare variables for the Workbooks.Open method parameters.
    string paramWorkbookPath = Application.StartupPath + @"\ChartData.xlsx";
    object paramMissing = Type.Missing;

    // Declare variables for the Chart.ChartWizard method.
    object paramChartFormat = 1;
    object paramCategoryLabels = 0;
    object paramSeriesLabels = 0;
    bool paramHasLegend = true;
    object paramTitle = "Sales by Quarter";
    object paramCategoryTitle = "Fiscal Quarter";
    object paramValueTitle = "Billions";

    try
    {
        // Create an instance of the Excel ApplicationClass object.
        excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();

        // Create a new workbook with 1 sheet in it.
        xlNS.Workbook newWorkbook = excelApplication.Workbooks.Add(xlNS.XlWBATemplate.xlWBATWorksheet);

        // Change the name of the sheet.
        xlNS.Worksheet targetSheet = (xlNS.Worksheet)(newWorkbook.Worksheets[1]);
        targetSheet.Name = "Quarterly Sales";

        // Insert some data for the chart into the sheet.
        //              A       B       C       D       E
        //     1                Q1      Q2      Q3      Q4
        //     2    N. America  1.5     2       1.5     2.5
        //     3    S. America  2       1.75    2       2
        //     4    Europe      2.25    2       2.5     2
        //     5    Asia        2.5     2.5     2       2.75

        SetCellValue(targetSheet, "A2", "N. America");
        SetCellValue(targetSheet, "A3", "S. America");
        SetCellValue(targetSheet, "A4", "Europe");
        SetCellValue(targetSheet, "A5", "Asia");

        SetCellValue(targetSheet, "B1", "Q1");
        SetCellValue(targetSheet, "B2", 1.5);
        SetCellValue(targetSheet, "B3", 2);
        SetCellValue(targetSheet, "B4", 2.25);
        SetCellValue(targetSheet, "B5", 2.5);

        SetCellValue(targetSheet, "C1", "Q2");
        SetCellValue(targetSheet, "C2", 2);
        SetCellValue(targetSheet, "C3", 1.75);
        SetCellValue(targetSheet, "C4", 2);
        SetCellValue(targetSheet, "C5", 2.5);

        SetCellValue(targetSheet, "D1", "Q3");
        SetCellValue(targetSheet, "D2", 1.5);
        SetCellValue(targetSheet, "D3", 2);
        SetCellValue(targetSheet, "D4", 2.5);
        SetCellValue(targetSheet, "D5", 2);

        SetCellValue(targetSheet, "E1", "Q4");
        SetCellValue(targetSheet, "E2", 2.5);
        SetCellValue(targetSheet, "E3", 2);
        SetCellValue(targetSheet, "E4", 2);
        SetCellValue(targetSheet, "E5", 2.75);

        // Get the range holding the chart data.
        xlNS.Range dataRange = targetSheet.get_Range("A1", "E5");

        // Get the ChartObjects collection for the sheet.
        xlNS.ChartObjects chartObjects = (xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing));

        // Add a Chart to the collection.
        xlNS.ChartObject newChartObject = chartObjects.Add(0, 100, 600, 300);
        newChartObject.Name = "Sales Chart";

        // Create a new chart of the data.
        newChartObject.Chart.ChartWizard(dataRange, xlNS.XlChartType.xl3DColumn, paramChartFormat, xlNS.XlRowCol.xlRows,
            paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramTitle, paramCategoryTitle, paramValueTitle, paramMissing);

        // Save the workbook.
        newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing,
            paramMissing, xlNS.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        if (excelApplication != null)
        {
            // Close Excel.
            excelApplication.Quit();
        }
    }
}

static void UseCopyPaste()
{
    // Declare variables to hold references to PowerPoint objects.
    pptNS.ApplicationClass powerpointApplication = null;
    pptNS.Presentation pptPresentation = null;
    pptNS.Slide pptSlide = null;
    pptNS.ShapeRange shapeRange = null;

    // Declare variables to hold references to Excel objects.
    xlNS.ApplicationClass excelApplication = null;
    xlNS.Workbook excelWorkBook = null;
    xlNS.Worksheet targetSheet = null;
    xlNS.ChartObjects chartObjects = null;
    xlNS.ChartObject existingChartObject = null;

    string paramPresentationPath = Application.StartupPath + @"\ChartTest.pptx";
    string paramWorkbookPath = Application.StartupPath + @"\ChartData.xlsx";
    object paramMissing = Type.Missing;

    try
    {
        // Create an instance of PowerPoint.
        powerpointApplication = new pptNS.ApplicationClass();

        // Create an instance Excel.
        excelApplication = new xlNS.ApplicationClass();

        // Open the Excel workbook containing the worksheet with the chart data.
        excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath,
            paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
            paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
            paramMissing, paramMissing, paramMissing, paramMissing);

        // Get the worksheet that contains the chart.
        targetSheet =
            (xlNS.Worksheet)(excelWorkBook.Worksheets["Quarterly Sales"]);

        // Get the ChartObjects collection for the sheet.
        chartObjects =
            (xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing));

        // Get the chart to copy.
        existingChartObject =
            (xlNS.ChartObject)(chartObjects.Item("Sales Chart"));

        // Create a PowerPoint presentation.
        pptPresentation =
            powerpointApplication.Presentations.Add(
            Microsoft.Office.Core.MsoTriState.msoTrue);

        // Add a blank slide to the presentation.
        pptSlide =
            pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank);

        // Copy the chart from the Excel worksheet to the clipboard.
        existingChartObject.Copy();

        // Paste the chart into the PowerPoint presentation.
        shapeRange = pptSlide.Shapes.Paste();

        // Position the chart on the slide.
        shapeRange.Left = 60;
        shapeRange.Top = 100;

        // Save the presentation.
        pptPresentation.SaveAs(paramPresentationPath, pptNS.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Microsoft.Office.Core.MsoTriState.msoTrue);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        // Release the PowerPoint slide object.
        shapeRange = null;
        pptSlide = null;

        // Close and release the Presentation object.
        if (pptPresentation != null)
        {
            pptPresentation.Close();
            pptPresentation = null;
        }

        // Quit PowerPoint and release the ApplicationClass object.
        if (powerpointApplication != null)
        {
            powerpointApplication.Quit();
            powerpointApplication = null;
        }

        // Release the Excel objects.
        targetSheet = null;
        chartObjects = null;
        existingChartObject = null;

        // Close and release the Excel Workbook object.
        if (excelWorkBook != null)
        {
            excelWorkBook.Close(false, paramMissing, paramMissing);
            excelWorkBook = null;
        }

        // Quit Excel and release the ApplicationClass object.
        if (excelApplication != null)
        {
            excelApplication.Quit();
            excelApplication = null;
        }

        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

通过Java使用Aspose.Slides for PHP示例

使用Aspose.Slides for .NET，执行以下步骤：

1. 使用Aspose.Cells for Java创建工作簿。
2. 创建Microsoft Excel图表。
3. 设置Excel图表的OLE大小。
4. 获取图表的图像。
5. 使用Aspose.Slides for PHP via Java将Excel图表作为OLE对象嵌入到PPTX演示文稿中。
6. 将对象更改后的图像替换为步骤3获得的图像，以处理对象更改问题。
7. 将输出演示文稿以PPTX格式写入磁盘。

        
try {
    
    //Create a workbook
Workbook wb = new Workbook();
//Add an excel chart
int chartRows = 55;
int chartCols = 25;
int chartSheetIndex = AddExcelChartInWorkbook(wb, chartRows, chartCols);
//Set chart ole size
wb.getWorksheets().setOleSize(0, chartRows, 0, chartCols);
//Get the chart image and save to stream
com.aspose.cells.ImageOrPrintOptions opts= new com.aspose.cells.ImageOrPrintOptions();
opts.setImageFormat(com.aspose.cells.ImageFormat.getPng());
ByteArrayOutputStream imageStream=new ByteArrayOutputStream();
wb.getWorksheets().get(chartSheetIndex).getCharts().get(0).toImage(imageStream, opts);
//Save the workbook to stream
ByteArrayOutputStream bout=new ByteArrayOutputStream();
 
wb.save(bout,com.aspose.cells.SaveFormat.EXCEL_97_TO_2003);
//Create a presentation
Presentation pres = new Presentation();
ISlide sld = pres.getSlides().get_Item(0);
//Add the workbook on slide
AddExcelChartInPresentation(pres, sld, bout.toByteArray(), imageStream.toByteArray());
//Write the presentation to disk
pres.save("outputJ.pptx", SaveFormat.Pptx);
    
} catch (Exception e) {
}

static void AddExcelChartInPresentation(Presentation pres, ISlide sld, byte[] wbArray,    byte[] imgChart) throws Exception
{
    double oleHeight = pres.getSlideSize().getSize().getHeight();
    double oleWidth = pres.getSlideSize().getSize().getWidth();
    Workbook wb=new Workbook();
 
    //Createing and EXCEL_97_TO_2003 LoadOptions object
    com.aspose.cells.LoadOptions loadOptions = new com.aspose.cells.LoadOptions(com.aspose.cells.FileFormatType.EXCEL_97_TO_2003);         
    wb=new Workbook(new ByteArrayInputStream(wbArray),loadOptions);
 
    IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(0f, 0f, (float)oleWidth, (float)oleHeight, "Excel.Sheet.8", wbArray);
    oof.getSubstitutePictureFormat().getPicture().setImage(pres.getImages().addImage(new ByteArrayInputStream(imgChart)));
 
}

static int AddExcelChartInWorkbook(Workbook wb, int chartRows, int chartCols)
{
//Array of cell names
String[] cellsName = new String[]
        {
                "A1", "A2", "A3", "A4",
                "B1", "B2", "B3", "B4",
                "C1", "C2", "C3", "C4",
                "D1", "D2", "D3", "D4",
                "E1", "E2", "E3", "E4"
        };
//Array of cell data
int[] cellsValue = new int[]
        {
                67,86,68,91,
                44,64,89,48,
                46,97,78,60,
                43,29,69,26,
                24,40,38,25
        };
//Add a new worksheet to populate cells with data
int dataSheetIndex =wb.getWorksheets().add();
Worksheet dataSheet =wb.getWorksheets().get(dataSheetIndex);
String sheetName = "DataSheet";
dataSheet.setName(sheetName);
//Populate DataSheet with data
int size= Array.getLength(cellsName);
for (int i = 0; i < size; i++)
{
    String cellName = cellsName[i];
    int cellValue = cellsValue[i];
    dataSheet.getCells().get(cellName).setValue(cellValue);
}
//Add a chart sheet
int WorksheetIndex = wb.getWorksheets().add(SheetType.CHART);
Worksheet chartSheet = wb.getWorksheets().get(WorksheetIndex);
chartSheet.setName("ChartSheet");
int chartSheetIdx = chartSheet.getIndex();
//Add a chart in ChartSheet with data series from DataSheet
int chIndex = chartSheet.getCharts().add(ChartType.COLUMN, 0, chartRows, 0, chartCols);
Chart chart=chartSheet.getCharts().get(chIndex);
 
chart.getNSeries().add(sheetName + "!A1:E1", false);
chart.getNSeries().add(sheetName + "!A2:E2", false);
chart.getNSeries().add(sheetName + "!A3:E3", false);
chart.getNSeries().add(sheetName + "!A4:E4", false);
//Set ChartSheet as active sheet
wb.getWorksheets().setActiveSheetIndex(chartSheetIdx);
return chartSheetIdx;
 
}