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