Module Module1
' Declare variables to hold the Web service URL,
' target workbook, worksheet, and named range.
Dim serviceURL As String = String.Empty
Dim targetWorkbookPath As String = String.Empty
Dim sheetName As String = String.Empty
Dim rangeName As String = String.Empty
Sub Main()
' Check command-line arguments.
If Not CheckArgs() Then
Return
End If
' Create an instance of the Web service proxy class
' and set the URL to use.
Dim service As ExcelService = New ExcelService()
service.Url = serviceURL
' Declare a status array.
Dim outStatus() As Status
' Declare a variable to hold the session ID.
Dim sessionId As String = String.Empty
' Set the credentials for the request by specifying
' the network credentials of the current security context.
service.Credentials = _
System.Net.CredentialCache.DefaultCredentials
' An alternative approach is to create a NetworkCredential
' object to connect as a specific user.
'
' service.Credentials = _
' New System.Net.NetworkCredential("User", _
' "Password", "Domain")
Try
' Open the workbook, specifying string.Empty for the
' uiCultureName and dataCultureName parameters. Doing this
' tells the Web service to use the server's default
' settings for UI- and Data-Culture for the site and user.
sessionId = service.OpenWorkbook(targetWorkbookPath, _
String.Empty, String.Empty, outStatus)
' Request the range by name, specifying true for the
' formatted parameter to tell the Web service to return
' empty cells as the Empty string. Specfying false for this
' parameter would tell the Web service to return empty
' cells as Null.
Dim rangeResult() As Object = _
service.GetRangeA1(sessionId, sheetName, rangeName, _
True, outStatus)
' Display the values in the range.
Console.WriteLine("The values in the " + rangeName + _
" range are:" + vbCrLf)
For Each row As Object In rangeResult
For Each cell As Object In CType(row, Object())
Console.Write(cell + " ")
Next cell
Console.WriteLine()
Next row
Catch se As SoapException
Console.WriteLine("SOAP Exception Message: {0}", _
se.Message)
Catch ex As Exception
Console.WriteLine("Exception Message: {0}", ex.Message)
Finally
' Close the workbook. This also closes the session,
' proactively releasing resources on the server.
If Not sessionId = String.Empty Then
service.CloseWorkbook(sessionId)
End If
End Try
Console.Write(vbCrLf + "Press any key to continue...")
Console.ReadLine()
End Sub
#Region "Commandline Argument Processing"
Private Function CheckArgs() As Boolean
Dim bArgsOK As Boolean = True
' Check the number of command-line arguments.
If (My.Application.CommandLineArgs.Count < 4) Then
bArgsOK = False
Else
' Parse the command-line arguments.
For Each arg As String In My.Application.CommandLineArgs
If (arg.ToLower().StartsWith("/service=")) Then
serviceURL = arg.Remove(0, 9)
End If
If (arg.ToLower().StartsWith("/book=")) Then
targetWorkbookPath = arg.Remove(0, 6)
End If
If (arg.ToLower().StartsWith("/sheet=")) Then
sheetName = arg.Remove(0, 7)
End If
If (arg.ToLower().StartsWith("/range=")) Then
rangeName = arg.Remove(0, 7)
End If
Next arg
' Ensure that the URL for the Web service was specified.
If (serviceURL = String.Empty) Then
Console.Error.WriteLine("Please specify the URL " + _
"to the Web service with the /service= switch." + _
vbCrLf)
bArgsOK = False
End If
' Ensure that a workbook was specified.
If (targetWorkbookPath = String.Empty) Then
Console.Error.WriteLine("Please specify a " + _
"workbook with the /book= switch." + vbCrLf)
bArgsOK = False
End If
' Ensure that a worksheet was specified.
If (sheetName = String.Empty) Then
Console.Error.WriteLine("Please specify a " + _
"worksheet with the /sheet= switch." + vbCrLf)
bArgsOK = False
End If
' Ensure that a named range was specified.
If (rangeName = String.Empty) Then
Console.Error.WriteLine("Please specify a named " + _
"range with the /range= switch." + vbCrLf)
bArgsOK = False
End If
End If
If Not bArgsOK Then
ShowUsage()
End If
Return bArgsOK
End Function
Sub ShowUsage()
Console.Error.WriteLine("Usage:" + vbCrLf)
Console.Error.WriteLine("ExcelSvcsApp /service=ServiceURL " + _
"/book=WorkbookPath /sheet=WorksheetName " + _
"/range=RangeName" + vbCrLf)
Console.Error.WriteLine("/service= " + _
"The URL of the Web service,")
Console.Error.WriteLine(" " + _
"e.g. /service=" + _
"http://SiteURL/Subsite/_vti_bin/excelservice.asmx" + _
vbCrLf)
Console.Error.WriteLine("/book= " + _
"The path or URL to the target workbook,")
Console.Error.WriteLine(" " + _
"e.g. /book=http://Server/Documents/Workbook.xlsx" + vbCrLf)
Console.Error.WriteLine("/sheet= " + _
"The name of the worksheet to use in the target " + _
"workbook,")
Console.Error.WriteLine(" " + _
"e.g. /sheet=Sheet1" + vbCrLf)
Console.Error.WriteLine("/range= " + _
"The name of the range in the worksheet to request " + _
"data for,")
Console.Error.WriteLine(" " + _
"e.g. /range=MyRange" + vbCrLf)
Console.Error.Write("Press any key to continue...")
Console.ReadLine()
End Sub
#End Region
End Module |