创建并将 Excel 图表作为 OLE 对象嵌入到 Microsoft PowerPoint 幻灯片中
Contents
[
Hide
]
图表是您数据的可视化表示,广泛用于演示文稿幻灯片。本文将向您展示如何通过使用 VSTO 和 Aspose.Slides for Android via Java 以编程方式创建并将 Excel 图表作为 OLE 对象嵌入到 PowerPoint 幻灯片中的代码。
创建并嵌入 Excel 图表
下面的两个代码示例比较长且详细,因为它们描述的任务相对复杂。您将创建一个 Microsoft Excel 工作簿,创建一个图表,然后创建您将图表嵌入其中的 Microsoft PowerPoint 演示文稿。OLE 对象包含指向原始文档的链接,因此双击嵌入文件的用户会打开该文件及其应用程序。
VSTO 示例
使用 VSTO,执行以下步骤:
- 创建 Microsoft Excel ApplicationClass 对象的实例。
- 创建一个包含一个工作表的新工作簿。
- 向工作表添加图表。
- 保存工作簿。
- 打开包含图表数据的工作表的 Excel 工作簿。
- 获取工作表的 ChartObjects 集合。
- 获取要复制的图表。
- 创建一个 Microsoft PowerPoint 演示文稿。
- 向演示文稿添加一个空白幻灯片。
- 将 Excel 工作表中的图表复制到剪贴板。
- 将图表粘贴到 PowerPoint 演示文稿中。
- 将图表放置在幻灯片上。
- 保存演示文稿。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CreateNewChartInExcel(); | |
UseCopyPaste(); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
static void SetCellValue(xlNS.Worksheet targetSheet, string Cell, object Value) | |
{ | |
targetSheet.get_Range(Cell, Cell).set_Value(xlNS.XlRangeValueDataType.xlRangeValueDefault, Value); | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} | |
} | |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} |
Aspose.Slides for Android via Java 示例
使用 Aspose.Slides for .NET,执行以下步骤:
- 使用 Aspose.Cells for Java 创建一个工作簿。
- 创建一个 Microsoft Excel 图表。
- 设置 Excel 图表的 OLE 大小。
- 获取图表的图像。
- 使用 Aspose.Slides for Android via Java 将 Excel 图表作为 OLE 对象嵌入到 PPTX 演示文稿中。
- 将步骤 3 中获取的图像替换更改对象的图像,以解决对象更改问题。
- 将输出演示文稿以 PPTX 格式写入磁盘。
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) { | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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))); | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
} |