ExcelチャートをOLEオブジェクトとしてMicrosoft PowerPointスライドに作成および埋め込む
Contents
[
Hide
]
チャートはデータの視覚的表現であり、プレゼンテーションスライドに広く使用されています。この文書では、VSTOとAspose.Slides for Javaを使用して、ExcelチャートをOLEオブジェクトとしてPowerPointスライドにプログラムで作成し埋め込むためのコードを示します。
Excelチャートの作成と埋め込み
以下の2つのコード例は、説明しているタスクが複雑なため、長く詳細になっています。Microsoft Excelワークブックを作成し、チャートを作成し、その後、チャートを埋め込むMicrosoft PowerPointプレゼンテーションを作成します。OLEオブジェクトには元のドキュメントへのリンクが含まれているため、埋め込まれたファイルをダブルクリックすると、そのファイルとそのアプリケーションが起動します。
VSTOの例
VSTOを使用して、以下のステップが実行されます:
- Microsoft Excel ApplicationClassオブジェクトのインスタンスを作成します。
- 1シートの新しいワークブックを作成します。
- シートにチャートを追加します。
- ワークブックを保存します。
- チャートデータを含むワークシートを持つ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 Javaの例
Aspose.Slides for .NETを使用して、以下のステップが実行されます:
- Aspose.Cells for Javaを使用してワークブックを作成します。
- Microsoft Excelチャートを作成します。
- ExcelチャートのOLEサイズを設定します。
- チャートの画像を取得します。
- Aspose.Slides for Javaを使用してPPTXプレゼンテーション内にExcelチャートをOLEオブジェクトとして埋め込みます。
- オブジェクトが変更された問題に対応するために、ステップ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; | |
} |