Page History

Excerpt
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

...

To import values from a database to your worksheet:

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

Code Block

	c#
	c#


private DataTable GetEmployeeDataTable()
{
     string EmployeeSQL = "SELECT TOP 10 FirstName + ' ' + LastName As Name, " +
          "Title FROM Employee";

     DataTable dt = new DataTable();
    
     //--- "connString" is a SQL connection string
     using(SqlConnection conn = new SqlConnection(connString))
          new SqlDataAdapter(EmployeeSQL, conn).Fill(dt);

     return dt;
}

Pass the ADO.NET DataTable to ImportData:

Code Block

	c#
	c#


//--- Import to a worksheet and pass ImportData
//--- the cell at which to start entering the data.
Cell CellB5 = Sheet1.Cells[4, 1];
Area importedValues = Sheet1.ImportData(dt, CellB5);

//--- Or: Import to a defined area.
Area targetArea = Sheet1.CreateArea(4, 4, 15, 6);
Area importedValues = targetArea.ImportData(dt);

...

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

Create a rectangular array, for example:

Code Block

	c#
	c#


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

Pass the array to ImportData:

Code Block

	c#
	c#


//--- Import to a worksheet and pass ImportData
//--- the cell at which to start entering the data.
Cell CellB5 = Sheet1.Cells[4, 1];
Area importedValues = Sheet1.ImportData(arrayData, CellB5);

//--- Or: Import to a defined area.
Area targetArea = Sheet1.CreateArea(4, 4, 15, 6);
Area importedValues = targetArea.ImportData(arrayData);

...

To customize a data import using a DataImportProperties object:

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);

Space Shortcuts

Page tree

Versions Compared

Old Version 4

New Version Current

Key