SoftArtisans ExcelWriter Guide

ExcelApplication Object Reference

GetDataFromDataSet Method

GetDataFromDataSet is available in .NET only. To import database values with ADO in ASP, using ExcelApplication, use CopyFromRecordSet or GetDataFromRecordset.

GetDataFromDataSet(Worksheet, DataSet, ShowFieldNames, FirstRow, FirstCol, MaxRows, MaxColumns, FieldList, FieldListMode)

Imports an ADO.NET DataSet into a specified range of cells, and returns a Range object. This method allows you to specify the position of the imported data in a worksheet and the maximum number of rows and columns to import, include or exclude field headers, and include or exclude specific DataSet fields. If the DataSet contains more than one DataTable, this method will get the first DataTable in the DataSet; to get a DataTable other than the first, use the second GetDataFromDataSet signature.

GetDataFromDataSet(Worksheet, DataSet, DataTableIndex, ShowFieldNames, FirstRow, FirstCol, MaxRows, MaxColumns, FieldList, FieldListMode)

Imports an ADO.NET DataSet into a specified range of cells, and returns a Range object. This method allows you to import a single DataTable in a DataSet by specifying the DataTable index, specify the position of the imported data in a worksheet and the maximum number of rows and columns to import, include or exclude field headers, and include or exclude specific DataSet fields.

GetDataFromDataSet(Worksheet, DataSet, ShowFieldNames, FirstRow, FirstCol,
MaxRows, MaxColumns, FieldList, FieldListMode)

Syntax:

[C#]
public SARange ExcelApplicationDotNet.GetDataFromDataSet(SAWorksheet Worksheet, DataSet DataSet,_
		bool aShowFieldNames, int FirstRow, int FirstCol,_
		int MaxRows, int MaxColumns, string FieldList,_
		SAXLFieldListMode FieldListMode)

Description:

GetDataFromDataSet imports an ADO.NET DataSet into a specified range of cells, and returns a Range object. If the DataSet contains more than one DataTable, this method will get the first DataTable in the DataSet; to get a DataTable other than the first, use the second GetDataFromDataSet signature.

This method allows you:

To specify the position of the imported data in a worksheet and the maximum number of rows and columns to import,
To include or exclude field headers, and
To include or exclude specific DataSet fields.

Parameters:

Worksheet
The DataSet will be imported to this worksheet.
DataSet
The ADO.NET DataSet to import to the worksheet.
ShowFieldNames
If set to True, DataSet column headers will be imported to the first row.
FirstRow
First worksheet row for the imported DataSet.
FirstCol
First worksheet column for the imported DataSet.
MaxRows
Specifies the maximum number of rows to import from the data source. To import the maximum rows available, use the constant ExcelApplication.ALL_ROWS. Note: Excel 95 will delete rows after 16,384.
MaxColumns
Maximum number of worksheet columns for the imported DataSet.
FieldList
DataSet fields to include or exclude in the spreadsheet, depending on the value of FieldListMode.
FieldListMode
Includes or excludes fields listed in FieldList parameter. To include only fields specified by the parameter FieldList, set FieldListMode to saxlsInclude.

The parameter FieldList is a comma delimited string specifying DataSet fields to include or exclude. They may be referenced by field name or position, or both. The order in which fields are specified in the FieldList parameter is the order in which they will be returned.

Returns:

A Range object.

Example:

[C#]
...
{
  DataSet Set = null;
  using(SAExcelApplicationDotNet XlwApp = new SAExcelApplicationDotNet())
  {
    try
    {
      Set = GetOrdersDataSet();
      XlwApp.Open(Page.MapPath("./DataImportBook.xls"), false);
      SAWorksheet WrkSht2 = XlwApp.Worksheets[2];
      WrkSht2.Name = "DataSet";
      SARange Range2 = XlwApp.GetDataFromDataSet(WrkSht2, Set, true, 7, 1, 500, 500, 
                  "", SAXLFieldListMode.saxlsExclude);
      XlwApp.Save("DatabaseImport.xls", 
                  SASaveMethod.saOpenInExcel, 
                  SAFileFormat.saFileFormatDefault);
      ...
    }
    catch(System.Exception ex)
    ...
    finally
    ...
  }
}

DataSet GetOrdersDataSet()
{
  OleDbConnection Conn = new OleDbConnection();
  DataSet OrdersDs = null;
  try
  {
    Conn.ConnectionString = Application["connstring"].ToString();
    string OrdersSQL = "SELECT Orders.OrderID, Customers.CompanyName As Customer, " +
            "Orders.OrderDate, " +
            "([Order Details].UnitPrice * [Order Details].Quantity) As [OrderTotal] " +
            "FROM Orders, [Order Details], Customers " +
            "WHERE Orders.OrderID=[Order Details].OrderID AND "	+
            "Orders.CustomerID=Customers.CustomerID AND Orders.EmployeeID=1";
    OleDbCommand CmdOrders = new OleDbCommand(OrdersSQL, Conn);
    OleDbDataAdapter AdptSales = new OleDbDataAdapter(CmdOrders);
    OrdersDs = new DataSet();
    AdptSales.Fill(OrdersDs, "Orders");				
  }
  finally
  ...
}
...

Top

GetDataFromDataSet(Worksheet, DataSet, DataTableIndex, ShowFieldNames, FirstRow, FirstCol,
MaxRows, MaxColumns, FieldList, FieldListMode)

Syntax:

[C#]
public SARange ExcelApplicationDotNet.GetDataFromDataSet(SAWorksheet Worksheet, DataSet DataSet,_
		int aDataTableIndex, bool aShowFieldNames, int FirstRow,_
		int FirstCol, int MaxRows, int MaxColumns, string FieldList,_
		SAXLFieldListMode FieldListMode)

Description:

GetDataFromDataSet imports an ADO.NET DataSet into a specified range of cells, and returns a Range object. This method allows you:

To import a single DataTable in a DataSet by specifying the DataTable index,
To specify the position of the imported data in a worksheet and the maximum number of rows and columns to import,
To include or exclude field headers, and
To include or exclude specific DataSet fields.

Parameters:

Worksheet
The DataSet will be imported to this worksheet.
DataSet
The ADO.NET DataSet to import to the worksheet.
DataTableIndex
A DataSet may contain more than one DataTable. This parameter specifies the index of the specific DataTable to import.
ShowFieldNames
If set to True, DataSet column headers will be imported to the first row.
FirstRow
First worksheet row for the imported DataSet.
FirstCol
First worksheet column for the imported DataSet.
MaxRows
Specifies the maximum number of rows to import from the data source. To import the maximum rows available, use the constant ExcelApplication.ALL_ROWS. Note: Excel 95 will delete rows after 16,384.
MaxColumns
Maximum number of worksheet columns for the imported DataSet.
FieldList
DataSet fields to include or exclude in the spreadsheet, depending on the value of FieldListMode.
FieldListMode
Includes or excludes fields listed in FieldList parameter. To include only fields specified by the parameter FieldList, set FieldListMode to saxlsInclude.

Returns:

A Range object.

Example:

ASP.NET Example: Importing from a Database with ADO.NET

[Run Sample] | [View Source: Form] [View Source: Code-behind]

Top