Compartilhar via

Comparing Excel Automation to Excel Services

  • Artigo

In a previous post, I compared and contrasted Word Automation to Word Automation Services.  There is a similar contrast between Excel Automation and Excel Services, although there is less overlap in functionality when compared to the two approaches for Word.

This blog is inactive.
New blog: EricWhite.com/blog

Blog TOCThis post is one in a series on Microsoft Office 2010 application development. These posts will be published in the future as part of an MSDN article. As usual, after the MSDN article is published, I’ll place pointers in the blog posts to the article on MSDN.

  1. Office/SharePoint Building Blocks and Developer Stories
  2. Overview of Office 2010 Application Development
  3. Office Application Scenarios
  4. Understanding the Three Approaches to Office Development using VSTO
  5. What is the Difference between ‘Word Automation’ and ‘Word Automation Services’?
  6. Understanding the Architecture of Office 2010 Managed Add-Ins and Customizations
  7. Understanding the Difference between Custom Task Panes and Action Panes
  8. Microsoft Word 2010 Developer Building Blocks
  9. Comparing Excel Automation to Excel Services

Excel Automation

Excel Automation is a technology where you can write a .NET application that uses the Excel client executable to accomplish some task such as creating a spreadsheet, querying a spreadsheet for content, or modifying the content of a spreadsheet.  Its roots are COM – in addition to using .NET to link to the Primary Interop Assemblies, you can use C++ or VB6 (or any other technology that can consume COM) to automate the Excel client application.  In other words, you write a program that runs Excel.  There is a rich object model that you can use to manipulate workbook, worksheets, ranges, cells, comments, etc.  This technology has been around for years.  If you are an experienced Excel developer, then you have probably used Excel Automation at one time or another.

The user operates a .NET managed application that in turn runs the Excel 2007 or Excel 2010 client.

 

To make this completely clear, the following small console application starts Excel, creates a workbook and worksheet, sets the value of a cell, saves it, and then causes the Excel application to quit.  You need to add a reference to the Excel 2010 PIA.  You find this on the COM tab.

 

Note that the following sample uses the feature C# 4.0 that allows you to omit arguments that have default values.

using Excel = Microsoft.Office.Interop.Excel;

class Program
{
static void Main(string[] args)
{
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook workbook = app.Workbooks.Add();
Excel.Worksheet sheet = workbook.ActiveSheet;
sheet.Cells[1, 1] = "Hello World!";
workbook.SaveAs(@"E:\Temp\Test.xlsx");
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}

This scenario, where you are programmatically generating spreadsheets, is where you should be using the Open XML SDK instead of Excel automation.

However, there are scenarios where we recommend that instead of using the Open XML SDK, you should automate Excel in some fashion.  If you are altering or modifying the workbook (other than simple setting of cell values), then you should automate Excel.  Altering spreadsheets using the Open XML SDK is involved.  Inserting a row or column using the Open XML SDK would involve parsing formulas and modifying the parsed formulas, a fairly involved operation.

In addition, just as with Word, many customers find it is advantageous to integrate Excel into their application, or to extend Excel to augment their applications functionality.

Excel Services

Excel Services is a service application that enables you to load, calculate, and display Microsoft Excel workbooks on Microsoft SharePoint Server 2010. Excel Services was first introduced in Microsoft Office SharePoint Server 2007.

By using Excel Services, you can reuse and share Excel workbooks on SharePoint Server 2010 portals and dashboards. For example, financial analysts, business planners, or engineers can create content in Excel and share it with others by using a SharePoint Server 2010 portal and dashboard—without writing custom code. You can control what data is displayed, and you can maintain a single version of your Excel workbook.

You cannot use Excel Services to create new workbooks or to edit existing workbooks. To author a workbook for use with Excel Services, you can use the Open XML SDK, Microsoft Office Excel 2007, or Microsoft Excel 2010.

There are four primary interfaces for Excel Services:

  • An Excel Web Access Web Part, which enables you to view and interact with a live workbook by using a browser
  • Excel Web Services for programmatic access
  • An ECMAScript (JavaScript, JScript) object model for automating and customizing, and to drive the Excel Web Access control and help build more compelling, integrated solutions
  • A Representational State Transfer (REST) API for accessing workbook parts directly through a URL

See the Excel Services Overview for more information.

To Summarize

You can use Excel Automation or the Open XML SDK to create workbooks.

You can use Excel Automation, the Open XML SDK, or Excel Services to retrieve contents of workbooks.

You can use Excel Automation to alter workbooks, with the exception that in some cases you can use the Open XML SDK to set values of cells.