Page tree
Skip to end of metadata
Go to start of metadata
ExcelApplication's ImportData method allows you to import blocks of data to a worksheet from a database or a rectangular array. ImportData is a method of both Worksheet and Area. The method returns a Area object representing a the set of cells that contain the imported data.

Table of Contents

Icon

ImportData does not insert values into the worksheet and push existing values down. The imported data will overwrite existing values in the target cells.

Importing from a Database

You can import values from a database to a worksheet by passing the ImportData method a DataTable, DataView, SqlDataReader, OleDbDataReader, or AdomdDataReader.

The DataTable and DataView classes are in the System.Data namespace. Use an Import directive to import the namespace to the aspx page:

To import System.Data to a C# code-behind page (.aspx.cs), use:

To import database values using OleDb, import the System.Data.OleDb namespace to your page. To import database values using SqlClient, import the System.Data.SqlClient namespace to your page.

To import values from a database to your worksheet:

  1. Connect to the database and execute a query to return a DataTable, DataView, SqlDataReader, OleDbDataReader, or AdomdDataReader for example:

  2. Pass the ADO.NET DataTable to ImportData:

Importing from an Array

To import values from a two-dimensional array to your worksheet:

  1. Create a rectangular array, for example:

  2. Pass the array to ImportData:

Customizing Your Data Import

The DataImportProperties class contains a set of properties that are used when importing data to cells in a worksheet. The settings of a DataImportProperties object will be applied to a data import if the object is passed to ImportData (with the set of values to import). You can create several DataImportProperties objects and assign a different one to each data import, or re-use one object in multipleImportData calls.

To customize a data import using a DataImportProperties object:

  1. Create a DataImportProperties object with the Workbook.CreateDataImportProperties() method:

  2. Set one or more data import propeties:

  3. Define a DataTable, DataView, SqlDataReader, OleDbDataReader, AdomdDataReader or rectangular array, for example:

  4. Pass the data and the DataImportProperties object to ImportData:

  • No labels