Populates DataSet with the rows from the Microsoft® Excel data source

[Visual Basic]
Overloads Public Function Fill( _
   ByVal dataSet As DataSet _
) As Integer
[C#]
public int Fill(
   DataSet dataSet
);

Parameters

dataSet

A DataSet to fill with records.

Return Value

The number of rows successfully added to DataSet.

Exceptions

Remarks

The Fill method retrieves the data from the data source using a statement as a list of the semicolon-delimited names of the spreadsheets inside of the Microsoft® Excel workbook. The ExcelConnection object associated with the select command must be valid, but it does not need to be open. If the ExcelConnection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

If an error is encountered while populating the data set, rows added prior to the occurrence of the error remain in the data set. The remainder of the operation is aborted.

If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.

If the ExcelDataAdapter object encounters duplicate columns while populating a DataTable, it will generate names for the subsequent columns, using the pattern "columnname1", "columnname2", "columnname3", and so on. If the incoming data contains unnamed columns, they are placed in the DataSet according to the pattern "Column1", "Column2", and so on.

When the query specified returns multiple results, the result set for each row returning query is placed in a separate table. Additional result sets are named by appending integral values to the specified table name (for example, "Table", "Table1", "Table2", and so on).

When the statement used to populate the DataSet returns multiple results if one of the results contains an error, all subsequent results are skipped and not added to the DataSet.

Example

[C#, Visual Basic] The following example creates an ExcelDataAdapter and populates DataSet with the data from Microsoft® Excel file.

[C#] 

using VM.xPort.ExcelClient;

private void GetSpreadsheetsDataSet()
{
    //Sample code retrieves data from two spreadsheets inside of Excel file. 

    ExcelConnection connection;
    ExcelDataAdapter adapter;
    ExcelCommand command;
    DataSet result;

    //Open connection to the Excel file and specify that first row in 
    //spreadsheets is a header row 
    connection = GetConnection("AuthorsInfo.xlsx", "xlsx", true);

    //Create an ExcelCommand to specify from which spreadsheets inside of 
    //workbook to query data 
    command = new ExcelCommand("Authors;Titles", connection);
    //It is always TableDirect 
    command.CommandType = CommandType.TableDirect;

    //Create an ExceldataAdapter to retrieve data into the DataSet 
    adapter = new ExcelDataAdapter(command);

    result = new DataSet();
    adapter.Fill(result);

    //Do something with the DataSet here 
    dgvXLS1.DataSource = result.Tables[0];
    dgvXLS2.DataSource = result.Tables[1];

    //Release all the opened resources 
    result.Dispose();
    result = null;

    adapter.Dispose();
    adapter = null;

    connection.Close();
    connection.Dispose();
    connection = null;

    command.Dispose();
    command = null;
} 
        

[Visual Basic] 
			
Imports VM.xPort.ExcelClient

Private Sub GetSpreadsheetsDataSet()

    'Sample code retrieves data from two spreadsheets inside of Excel file. 

    Dim connection As ExcelConnection
    Dim adapter As ExcelDataAdapter
    Dim command As ExcelCommand
    Dim result As DataSet

    'Open connection to the Excel file and specify that first row in 
    'spreadsheets is a header row 
    connection = GetConnection("AuthorsInfo.xlsx", "xlsx", True)

    'Create an ExcelCommand to specify from which spreadsheets inside of  
    'workbook to query data
    command = New ExcelCommand("Authors;Titles", connection)
    command.CommandType = CommandType.TableDirect 'It is always TableDirect

    'Create an ExceldataAdapter to retrieve data into the DataSet
    adapter = New ExcelDataAdapter(command)

    result = New DataSet
    adapter.Fill(result)

    'Do something with the DataSet here
    dgvXLS1.DataSource = result.Tables(0)
    dgvXLS2.DataSource = result.Tables(1)

    'Release all the opened resources 
    result.Dispose() 
    result = Nothing 
    
    adapter.Dispose()
    adapter = 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: 

VM.xPort.ExcelClient (in VM.xPort.ExcelClient.dll)

See Also

ExcelDataAdapter Class | ExcelDataAdapter Members | VM.xPort.ExcelClient Namespace