25. 使用 Excel 数据实现自动化
在 Excel 中按照Excel 数据链接所述提供数据后，您可以使用 UpdateChart 和 PresentationFromTemplate 函数通过编程对该数据的使用进行控制。
通过 UpdateChart，您可以对特定图表的数据表和您的数据进行交换。通过 PresentationFromTemplate，您可以根据 PowerPoint 模板，使用数据创建新的演示文稿（将 think-cell 图表链接到 Excel 中的数据范围）。
这两个函数的接口都已集成到 Office Automation 模型中，因此可以通过能对 Office 编程的任何语言（例如 Visual Basic for Applications 或 C#）进行访问。
think-cell 的入口点是 think-cell 加载项对象。可以通过 Application.COMAddIns 集合对其进行访问。调用 think-cell 始终采用后期绑定。如需说明，请参阅 Microsoft 的知识库：
http://support.microsoft.com/kb/245115
因此，think-cell 加载项对象的类型只是 Object，没有要添加的类型库或引用。只需获取对象，即可进行调用：
Dim tcaddin As Object
Set tcaddin = Application.COMAddIns("thinkcell.addin").Object
- 25.1
- UpdateChart
- 25.2
- PresentationFromTemplate
25.1 UpdateChart
25.1.1 签名
tcaddin.UpdateChart( _
pres As PowerPoint.Presentation, _
strName As String, _
rgData As Excel.Range, _
bTransposed As Boolean _
)
25.1.2 描述
从 Excel 调用此函数，此函数将使用 rgData 中包含的数字更新 pres 中的图表 strName。范围 rgData 必须符合 从 Excel 创建图表 中所述的布局。
请注意，演示文稿对象 pres 也可用于参考演示文稿中的幻灯片范围。有关更多信息，请参阅：
http://msdn.microsoft.com/en-us/vba/powerpoint-vba/articles/slide-object-powerpoint
图表名称 strName 的匹配不区分大小写。先前必须已经按照自动化简介中所述在 PowerPoint 中使用 UpdateChart 名称属性控件分配该名称。图表名称在 pres 定义的演示文稿或幻灯片范围内必须唯一。
若已将图表链接到 Excel 数据范围，则调用此函数时，链接将中断。此后，不会将图表链接到任何 Excel 范围。
25.1.3 示例
若要使用此示例，请在 Excel 的 Visual Basic for Applications 窗口中，依次前往工具和引用，然后添加 Microsoft PowerPoint 对象库。
' When Option Explicit appears in a file, you must
' explicitly declare all variables using the Dim
' or ReDim statements. If you attempt to use an
' undeclared variable name, an error occurs at
' compile time.
' Use Option Explicit to avoid incorrectly typing
' the name of an existing variable or to avoid
' confusion in code where the scope of the
' variable is not clear. If you do not use the
' Option Explicit statement, all undeclared
' variables are of Object type.
' http://msdn.microsoft.com/en-us/
' library/y9341s4f%28v=vs.80%29.aspx
Option Explicit
Sub UpdateChart_Sample()
' Get the range containing the new data
Dim rng As Excel.Range
Set rng = ActiveWorkbook.Sheets("Sheet1").Range("A1:D5")
' Get the think-cell add-in object
Dim tcaddin As Object
Set tcaddin = Application.COMAddIns("thinkcell.addin").Object
' Get a PowerPoint instance. Hold on to this
' object as long as you want to access the
' generated presentations. There can only be a
' single PowerPoint instance. If there is no
' PowerPoint running, one will be started.
' Otherwise the existing one is used.
Dim ppapp As Object
Set ppapp = New PowerPoint.Application
Dim pres As PowerPoint.Presentation
' PowerPoint window visible
' Set pres = ppapp.Presentations.Open( _
' Filename:="c:\\template.pptx", _
' Untitled:=msoTrue)
' PowerPoint window invisible
Set pres = ppapp.Presentations.Open( _
Filename:="c:\\template.pptx", _
Untitled:=msoTrue, _
WithWindow:=msoFalse)
' The name "Chart1" must have been
' previously assigned to the chart using
' the control in the floating toolbar.
' The final argument indicates whether
' the data range is transposed or not.
Call tcaddin.UpdateChart(pres, "Chart1", rng, False)
' Save the updated presentation
pres.SaveAs ("c:\\template_updated.pptx")
pres.Close
ppapp.Quit
End Sub
下一个示例会显示如何通过 C# 使用 UpdateChart。
using Excel = Microsoft.Office.Interop.Excel;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;
// Open the Solution Explorer > right-click the project file > "Add Reference..." button and add he following references:
// .NET tab > Microsoft.Office.Interop.Excel 12.0.0.0
// .NET tab > Microsoft.Office.Interop.PowerPoint 12.0.0.0
// COM tab > Microsoft Office 14.0 Object Library
namespace ConsoleApplication_UpdateChart
{
class Program
{
static void Main()
{
Excel.Application xlapp = new Excel.Application();
xlapp.Visible = true;
Excel.Workbook workbook = xlapp.Workbooks.Add(1);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
worksheet.Cells[3, 1] = "Series 1";
worksheet.Cells[3, 2] = 1;
worksheet.Cells[3, 3] = 2;
worksheet.Cells[3, 4] = 3;
PowerPoint.Application ppapp = new PowerPoint.Application();
PowerPoint.Presentation presentation =
ppapp.Presentations.Open("C://template.pptx", Office.MsoTriState.msoFalse, Office.MsoTriState.msoTrue);
object[] aobjArg = new object[] { (object)presentation, "Chart1", worksheet.get_Range("A1", "D3"), false };
Office.COMAddIn comaddin = xlapp.COMAddIns.Item("thinkcell.addin");
object objAddIn = comaddin.Object;
objAddIn.GetType().InvokeMember("UpdateChart", System.Reflection.BindingFlags.InvokeMethod, null, objAddIn, aobjArg);
presentation.SaveAs("C://template_updated.pptx");
presentation.Close();
ppapp.Quit();
workbook.Close(false);
xlapp.Quit();
}
}
}
25.2 PresentationFromTemplate
25.2.1 签名
tcaddin.PresentationFromTemplate( _
wb As Excel.Workbook, _
strTemplate As String, _
ppapp As PowerPoint.Application _
) As PowerPoint.Presentation
25.2.2 描述
从 Excel 中调用此函数，此函数可将 wb 中的任何数据链接应用到文件名为 strTemplate 的模板。结果是，在 PowerPoint 实例 ppapp 中产生新的演示文稿。
strTemplate 可以是完整路径，也可以是相对路径（随后会将其作为相对于 Excel 工作簿文件 wb 所在位置的相对路径）。
将更新 strTemplate 中已链接到 Excel 工作簿 wb 的所有图表（不论是否已将其设为自动更新，都是如此）。随后会中断其数据链接，以防止对这些图表进行进一步更改。
strTemplate 中已链接到除 wb 外其他 Excel 工作簿的图表将保持不变，并且仍保持链接状态，因此可以将此函数的结果另存为新模板，然后使用下一个工作簿再次调用此函数，以便更新多个 Excel 工作簿中的链接。
若您希望使用 Excel 链接控制图表段的颜色，可以将配色方案设为在顶部使用 Excel 填充（请参阅配色方案）。同样，若要使用 Excel 链接控制数字格式，请将其设为使用 Excel 格式（请参阅数字格式）。请确保先设置 Excel 中各单元格的背景色和数字格式，然后再调用 PresentationFromTemplate。
25.2.3 示例
若要使用此示例，请在 Excel 的 Visual Basic for Applications 窗口中，依次前往工具和引用，然后添加 Microsoft PowerPoint 对象库。
' When Option Explicit appears in a file, you must
' explicitly declare all variables using the Dim
' or ReDim statements. If you attempt to use an
' undeclared variable name, an error occurs at
' compile time.
' Use Option Explicit to avoid incorrectly typing
' the name of an existing variable or to avoid
' confusion in code where the scope of the
' variable is not clear. If you do not use the
' Option Explicit statement, all undeclared
' variables are of Object type.
' http://msdn.microsoft.com/en-us/
' library/y9341s4f%28v=vs.80%29.aspx
Option Explicit
Sub PresentationFromTemplate_Sample()
' Get the range to modify. It is more efficient
' to do this once rather than within the loop.
Dim rng As Excel.Range
Set rng = ActiveWorkbook.Sheets("Sheet1").Cells(3, 2)
' Get the think-cell add-in object
Dim tcaddin As Object
Set tcaddin = Application.COMAddIns("thinkcell.addin").Object
' Get a PowerPoint instance. Hold on to this
' object as long as you want to access the
' generated presentations. There can only be a
' single PowerPoint instance. If there is no
' PowerPoint running, one will be started.
' Otherwise the existing one is used.
Dim ppapp As Object
Set ppapp = New PowerPoint.Application
Dim i As Integer
For i = 1 To 10
' Modify the range value.
' Note: Avoid selecting the cell prior to
' changing it. It is very slow and has
' undesirable side-effects.
' BAD:
' rng.Select
' ActiveWindow.Selection.Value = 0
rng.Value = i
' Generate a new presentation based on the
' linked template.
Dim pres As PowerPoint.Presentation
Set pres = tcaddin.PresentationFromTemplate( _
Excel.ActiveWorkbook, "template.pptx", ppapp)
' If you want to modify the new presentation
' before saving it this is the place to do it.
' Save the new presentation
pres.SaveAs "c:\\output" & i & ".pptx"
' Explicitly close the presentation when we
' are done with it to free its memory.
' Letting the object go out of scope is not
' sufficient.
pres.Close
Next
End Sub