How to: Import Data to a Worksheet in SpreadsheetControl for WinForms
- 5 minutes to read
You can import data to worksheet cells from different data sources (for example, arrays, lists, and data tables). To import data into a worksheet, call the WorksheetExtensions.Import method and pass the following parameters:
- An object that specifies a data source;
- Row and column indexes of the start cell in the destination cell range;
- A Boolean value that specifies whether to insert data vertically or horizontally. Use this parameter when you import data from a one-dimensional array or list.
- A DataSourceImportOptions instance that contains import options (optional).
Important
A WorksheetExtensions class is defined in DevExpress.Docs.v24.2.dll. Add this assembly to your project to use worksheet extensions. You need an active license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this assembly in production code.
SpreadsheetControl allows you to import image data from a data source. The following image data types are available:
Byte[]System.IO.StreamSystem.Drawing.Image- DXImage
- OfficeImage
Import Data from an Array
The following code snippet imports data from an array to a worksheet:
Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
// Create an array containing string values.
string[] array = new string[] { "AAA", "BBB", "CCC", "DDD" };
// Import the array into the worksheet and insert it horizontally, starting with the B1 cell.
worksheet.Import(array, 0, 1, false);
// Create a two-dimensional array containing string values.
String[,] names = new String[2, 4]{
{"Ann", "Edward", "Angela", "Alex"},
{"Rachel", "Bruce", "Barbara", "George"}
};
// Import the two-dimensional array into the worksheet and insert it, starting with the B3 cell.
worksheet.Import(names, 2, 1);
Import Data from a List
The following code snippet imports data from a list of string objects:
byte[] imageBytes1 = File.ReadAllBytes("images//img.png");
byte[] imageBytes2 = File.ReadAllBytes("images//x-docserver.png");
// ...
Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
System.Collections.ArrayList listDataSource = new System.Collections.ArrayList()
{
new TestObject(1, "Jane", true, imageBytes1),
new TestObject(2, "Joe", false, imageBytes2),
new TestObject(3, "Bill", true, imageBytes1),
new TestObject(4, "Michael", false, imageBytes2),
};
worksheet.Import(listDataSource, 0, 0);
Import Data from a List of Custom Objects
The following code imports data from a list of custom TestClass class objects.
byte[] imageBytes1 = File.ReadAllBytes("images//img.png");
byte[] imageBytes2 = File.ReadAllBytes("images//x-docserver.png");
// ...
Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
System.Collections.ArrayList listDataSource = new System.Collections.ArrayList()
{
new TestObject(1, "Jane", true, imageBytes1),
new TestObject(2, "Joe", false, imageBytes2),
new TestObject(3, "Bill", true, imageBytes1),
new TestObject(4, "Michael", false, imageBytes2),
};
worksheet.Import(listDataSource, 0, 0);
Import Data from a Data Table
The following code imports data from a DataTable object to a worksheet.
Note that the cell data types are set automatically according to the data types of the source column. Cell formats are set automatically to the default value for the cell data type. Refer to the following topic for information on how to change this behavior: How to: Specify Number or Date Format for Cell Content
byte[] imageBytes1 = File.ReadAllBytes("images//img.png");
byte[] imageBytes2 = File.ReadAllBytes("images//x-docserver.png");
// ...
void ImportDataTable()
{
Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
// Create a "Products" DataTable object with four columns.
DataTable sourceTable = new DataTable("Products");
sourceTable.Columns.Add("Product", typeof(string));
sourceTable.Columns.Add("Price", typeof(float));
sourceTable.Columns.Add("Quantity", typeof(Int32));
sourceTable.Columns.Add("Discount", typeof(float));
sourceTable.Columns.Add("Image", typeof(byte[]));
sourceTable.Rows.Add("Chocolade", 5, 15, 0.03, imageBytes1);
sourceTable.Rows.Add("Konbu", 9, 55, 0.1, imageBytes1);
sourceTable.Rows.Add("Geitost", 15, 70, 0.07, imageBytes2);
// Import data from the data table into the worksheet and insert it, starting with the B2 cell.
worksheet.Import(sourceTable, true, 1, 1);
}
Tip
If you do not own the Office File API Subscription and cannot reference DevExpress.Docs.v24.2.dll, you can use the following method to import data into a worksheet:
Bind the worksheet to a read-only data source as described in this help topic: How to Bind a Spreadsheet to a List of Objects.
Call the WorksheetDataBindingCollection.Remove or WorksheetDataBindingCollection.Clear method to remove the binding.