データを更新する
最終更新日: 2009年12月3日
適用対象: SharePoint Server 2010
この例では、Refresh メソッドを使用して、開いているブックの外部のデータ ソースから更新されたデータを取得する方法を示しています。Refresh メソッドの Excel Web Services 署名は次のとおりです。
public Status[] Refresh (string sessionId, string connectionName)
Public Function Refresh(ByVal sessionId As String, ByVal connectionName As String) As Status()
End Function
Microsoft.Office.Excel.Server.WebServices.dll に直接リンクする場合、Refresh メソッドの署名は次のとおりです。
public void Refresh (string sessionId, string connectionName,
out Status[] status)
Public Sub Refresh(ByVal sessionId As String, ByVal connectionName As String, <System.Runtime.InteropServices.Out()> ByRef status() As Status)
End Sub
connectionName 引数は、Microsoft Office Excel 2007 ブック内の接続名を参照します。
Refresh メソッドを使用して、ブック内の単一のデータ接続を最新の情報に更新したり、すべての接続を最新の情報に更新したりすることができます。これは、開く際に最新の情報に更新する機能を使わずに接続が作成される場合に特に役に立ちます。
接続を最新の情報に更新すると、その接続を使用するデータとすべてのオブジェクトが最新の情報に更新されます。ブック内の使用可能な接続をすべて最新の情報に更新するには、空の接続文字列または null を接続名引数に渡します。
更新操作は、使用されている認証の種類に関係なく、追加の確認またはプロンプトなしで試行されます。
Refresh メソッドの詳細については、Excel Web Services のリファレンス ドキュメントを参照してください。
例
以下のコード例は、Excel Web Services を使用して Refresh メソッドを呼び出す方法を示しています。この例での接続名は "MyInventoryConnection" です。
// Instantiate the Web service.
ExcelService xlservice = new ExcelService();
Status[] outStatus;
RangeCoordinates rangeCoordinates = new RangeCoordinates();
string sheetName = "Sheet3";
// Set the path to the workbook to open.
// TODO: Change the path to the workbook
// to point to a workbook you have access to.
// The workbook must be in a trusted location.
string targetWorkbookPath =
http://myserver02/example/Shared%20Documents/Book1.xlsx";
// Set credentials for requests.
xlservice.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Call open workbook, and point to the trusted
// location of the workbook to open.
string sessionId = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", out outStatus);
// Prepare object to define range coordinates.
rangeCoordinates.Column = 0;
rangeCoordinates.Row = 0;
rangeCoordinates.Height = 8;
rangeCoordinates.Width = 10;
// Set the cell located in the first row and
// ninth column to 300.
xlservice.SetCell(sessionId, sheetName, 0, 8, 300);
xlservice.Refresh(sessionId, "MyInventoryConnection");
' Instantiate the Web service.
Dim xlservice As New ExcelService()
Dim outStatus() As Status
Dim rangeCoordinates As New RangeCoordinates()
Dim sheetName As String = "Sheet3"
' Set the path to the workbook to open.
' TODO: Change the path to the workbook
' to point to a workbook you have access to.
' The workbook must be in a trusted location.
' Set credentials for requests.
Dim targetWorkbookPath As String = http: xlservice.Credentials = System.Net.CredentialCache.DefaultCredentials 'myserver02/example/Shared%20Documents/Book1.xlsx";
' Call open workbook, and point to the trusted
' location of the workbook to open.
Dim sessionId As String = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", outStatus)
' Prepare object to define range coordinates.
rangeCoordinates.Column = 0
rangeCoordinates.Row = 0
rangeCoordinates.Height = 8
rangeCoordinates.Width = 10
' Set the cell located in the first row and
' ninth column to 300.
xlservice.SetCell(sessionId, sheetName, 0, 8, 300)
xlservice.Refresh(sessionId, "MyInventoryConnection")
Microsoft.Office.Excel.Server.WebServices.dll に直接リンクする場合、コードは次のようになります。
// Instantiate the ExcelService class.
ExcelService xlservice = new ExcelService();
Status[] outStatus;
RangeCoordinates rangeCoordinates = new RangeCoordinates();
string sheetName = "Sheet3";
// Set the path to the workbook to open.
// TODO: Change the path to the workbook
// to point to a workbook you have access to.
// The workbook must be in a trusted location.
string targetWorkbookPath =
http://myserver02/example/Shared%20Documents/Book1.xlsx";
// Call the open workbook, and point to the trusted
// location of the workbook to open.
string sessionId = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", out outStatus);
// Set the cell located in the first row and
// ninth column to 300.
xlservice.SetCell(sessionId, sheetName, 0, 8, 300, out outStatus);
xlservice.Refresh(sessionId, "MyInventoryConnection", out outStatus);
byte[] workbook = xlService.GetWorkbook(sessionId, WorkbookType.FullWorkbook, out status);
// Write the resulting Excel file to stdout
// as a binary stream.
BinaryWriter binaryWriter =
new BinaryWriter(Console.OpenStandardOutput());
binaryWriter.Write(workbook);
binaryWriter.Close();
...
...
' Instantiate the ExcelService class.
Dim xlservice As New ExcelService()
Dim outStatus() As Status
Dim rangeCoordinates As New RangeCoordinates()
Dim sheetName As String = "Sheet3"
' Set the path to the workbook to open.
' TODO: Change the path to the workbook
' to point to a workbook you have access to.
' The workbook must be in a trusted location.
' Call the open workbook, and point to the trusted
' location of the workbook to open.
Dim targetWorkbookPath As String = http: String sessionId = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", outStatus) 'myserver02/example/Shared%20Documents/Book1.xlsx";
' Set the cell located in the first row and
' ninth column to 300.
xlservice.SetCell(sessionId, sheetName, 0, 8, 300, outStatus)
xlservice.Refresh(sessionId, "MyInventoryConnection", outStatus)
Dim workbook() As Byte = xlService.GetWorkbook(sessionId, WorkbookType.FullWorkbook, status)
' Write the resulting Excel file to stdout
' as a binary stream.
Dim binaryWriter As New BinaryWriter(Console.OpenStandardOutput())
binaryWriter.Write(workbook)
binaryWriter.Close()
...
...
関連項目
タスク
[ウォークスルー] Excel Web Services を使用してカスタム アプリケーションを開発する