Créer et intégrer un graphique Excel en tant qu'objet OLE dans une diapositive Microsoft PowerPoint

Les graphiques sont des représentations visuelles de vos données et sont largement utilisés dans les diapositives de présentation. Cet article vous montrera le code pour créer et intégrer un graphique Excel en tant qu’objet OLE dans la diapositive PowerPoint de manière programmatique en utilisant VSTO et Aspose.Slides pour Java.

Création et intégration d’un graphique Excel

Les deux exemples de code ci-dessous sont longs et détaillés car la tâche qu’ils décrivent est complexe. Vous créez un classeur Microsoft Excel, créez un graphique, puis créez la présentation Microsoft PowerPoint dans laquelle vous intégrerez le graphique. Les objets OLE contiennent des liens vers le document d’origine, donc un utilisateur qui double-clique sur le fichier intégré lancera le fichier et son application.

Exemple VSTO

En utilisant VSTO, les étapes suivantes sont effectuées :

1. Créer une instance de l’objet Microsoft Excel ApplicationClass.
2. Créer un nouveau classeur avec une feuille.
3. Ajouter un graphique à la feuille.
4. Enregistrer le classeur.
5. Ouvrir le classeur Excel contenant la feuille de calcul avec les données du graphique.
6. Obtenir la collection ChartObjects pour la feuille.
7. Obtenir le graphique à copier.
8. Créer une présentation Microsoft PowerPoint.
9. Ajouter une diapositive vierge à la présentation.
10. Copier le graphique de la feuille de calcul Excel dans le presse-papiers.
11. Coller le graphique dans la présentation PowerPoint.
12. Positionner le graphique sur la diapositive.
13. Enregistrer la présentation.

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

Exemple Aspose.Slides pour Java

En utilisant Aspose.Slides pour .NET, les étapes suivantes sont effectuées :

1. Créer un classeur en utilisant Aspose.Cells pour Java.
2. Créer un graphique Microsoft Excel.
3. Définir la taille OLE du graphique Excel.
4. Obtenir une image du graphique.
5. Intégrer le graphique Excel en tant qu’objet OLE dans la présentation PPTX en utilisant Aspose.Slides pour Java.
6. Remplacer l’image de l’objet modifié par l’image obtenue à l’étape 3 pour résoudre le problème d’objet modifié.
7. Écrire la présentation de sortie sur le disque au format 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;
 
}