Page History

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

Table of Contents

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

...

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

Code Block

	c#
	c#

ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
DataImportProperties importProps = wb.CreateDataImportProperties();

Set one or more data import propeties:

Code Block

	c#
	c#

//--- Truncate imported data rows and columns that do
//--- not fit within the target rows and columns in the spreadsheet.
importProps.Truncate = true;

//--- By default, when data is imported from a two-dimensional
//--- array, the data will be entered as [row][column]. If
//--- Transpose is set to true, the data will be entered as
//--- [column][row].
importProps.Transpose = true;

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

Code Block

	c#
	c#

string[,] arrayData = {{"Nancy", "Davolio", "Sales Manager"},
     {"Michael", "Suyama", "HR Representative"},
     {"Adrian", "King", "IS Support"}};

Pass the data and the DataImportProperties object to ImportData:

Code Block

	c#
	c#

ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
Worksheet ws = wb.Worksheets[0];
DataImportProperties importProps = wb.CreateDataImportProperties();

//--- UseColumnNames will import column names in addition to data
importProps.UseColumnNames = true;

//--- Import to a worksheet and pass ImportData
//--- the cell at which to start entering the data.
Cell CellB5 = ws.Cells[4, 1];
String[] colNames = {"Name", "LastName", "Title"};
Area importedValues = ws.ImportData(arrayData, colNames,
     CellB5, importProps);

//--- Or: Import to a defined area.
Area targetArea = ws.CreateArea(4, 4, 15, 6);
String[] colNames = {"Name", "LastName", "Title"};
Area importedValues = targetArea.ImportData(arrayData, colNames,
     importProps);

Scrollbar

Space Shortcuts

Page tree

Versions Compared

Old Version 1

New Version 2

Key