ExcelCommand.CommandText Property

Gets or sets the statement to execute at the data source

[Visual Basic]
Public Overrides Property CommandText As String

[C#]
public override string CommandText {get; set;}

Property Value

The statement to execute. The default is an empty string.

Remarks

Current version of the .NET xlReader for Microsoft® Excel supports only semicolon-delimited list of the spreadsheets and CommandType.TableDirect type of the command. For example, if you need to specify a command text to query data from two spreadsheets (Sheet1 and Sheet2), then CommandText property will look like "Sheet1;Sheet1" and ExcelCommand will treat a query string as a two separate queries to the Sheet1 and Sheet2 spreadsheets.

Example

[C#, Visual Basic] The following example creates an ExcelCommand and sets some of its properties.

[C#] 

using VM.xPort.ExcelClient;

private void ReadCell() 
{ 
    
    //Sample code retrieves data from individual cells inside of the spreadsheet 
    ExcelConnection connection; 
    ExcelDataPicker picker; 
    ExcelCommand command; 
    string pathToFile = "AuthorsInfo.xlsx"; 
    
    //Open connection to the Excel file and specify that source file is in 
    //Excel 2007 XLSX file format (Format=xlsx) 
    connection = new ExcelConnection("Data Source=" + pathToFile + ";Format=xlsx"); 
    connection.Open(); 
    
    //Create an ExcelCommand to specify from which spreadsheets inside of the 
    //workbook to query data. ExcelDataPicker works only with one spreadsheet at a time 
    command = new ExcelCommand(); 
    command.CommandText = "Authors"; 
    command.Connection = connection; 
    
    //Create and open ExcelDataPicker 
    picker = command.ExecutePicker(); 
    
    if (!picker.IsDBNull(5, 3)) { 
        //Read the data from the cell with the row 5 and column 3 (indexes are 1-based) 
        Debug.Write("First Name: " + picker.GetValue(5, 3)); 
        
        //Read the data from the cell using name D5 
        Debug.WriteLine(" Phone: " + picker.GetValue("D5")); 
    } 
    
    //Release all the opened resources 
    picker.Close(); 
    picker.Dispose(); 
    picker = null; 
    
    connection.Close(); 
    connection.Dispose(); 
    connection = null; 
    
    command.Dispose(); 
    command = null; 
    
}

[Visual Basic] 

Imports VM.xPort.ExcelClient
			
Private Sub ReadCell()

    'Sample code retrieves data from individual cells inside of the spreadsheet
    Dim connection As ExcelConnection
    Dim picker As ExcelDataPicker
    Dim command As ExcelCommand
    Dim pathToFile As String = "AuthorsInfo.xlsx"

    'Open connection to the Excel file and specify that source file is in 
    'Excel 2007 XLSX file format (Format=xlsx)
    connection = New ExcelConnection("Data Source=" & pathToFile & ";Format=xlsx")
    connection.Open()

    'Create an ExcelCommand to specify from which spreadsheets inside of the 
    'workbook to query data. ExcelDataPicker works only with one spreadsheet at a time
    command = New ExcelCommand
    command.CommandText = "Authors"
    command.Connection = connection

    'Create and open ExcelDataPicker
    picker = command.ExecutePicker()

    If Not picker.IsDBNull(5, 3) Then
        'Read the data from the cell with the row 5 and column 3 (indexes are 1-based)
        Debug.Write("First Name: " & picker.GetValue(5, 3))

        'Read the data from the cell using name D5
        Debug.WriteLine(" Phone: " & picker.GetValue("D5"))
    End If

    'Release all the opened resources 
    picker.Close()
    picker.Dispose()
    picker = Nothing

    connection.Close()
    connection.Dispose()
    connection = Nothing

    command.Dispose()
    command = Nothing

End Sub

[C++, JScript] No example is available for C++ or JScript.

.NET Framework

Supported in: 4.0, 3.5, 3.0, 2.0

Assembly: