ContextQMD
Back to Devexpress

How to: Import Data to a Worksheet in SpreadsheetControl for WinForms

windowsforms-15442-controls-and-libraries-spreadsheet-examples-data-import-and-export-how-to-import-data-to-a-worksheet.md

latest10.3 KB
Original Source

How to: Import Data to a Worksheet in SpreadsheetControl for WinForms

  • Apr 15, 2025
  • 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.v25.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:

View Example: How to Import Data to a Worksheet from Different Data Sources

Import Data from an Array

The following code snippet imports data from an array to a worksheet:

cs
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);
vb
Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
' Create an array containing string values.
Dim array() As 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.
Dim names(,) As String = {
    {"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:

cs
byte[] imageBytes1 = File.ReadAllBytes("images//img.png");
byte[] imageBytes2 = File.ReadAllBytes("images//x-docserver.png");
// ...
    Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
    // Create a List object containing string values.
    List<string> cities = new List<string>
    {
        "New York",
        "Rome",
        "Beijing",
        "Delhi"
    };

    // Import the list into the worksheet and insert it vertically, starting with the B1 cell.
    worksheet.Import(cities, 0, 1, true);

    List<byte[]> imageList = new List<byte[]>();
    {
        imageList.Add(imageBytes1);
        imageList.Add(imageBytes2);
    };

    // Import the image list into the worksheet and insert it vertically
    worksheet.Import(imageList, 0, 2, true, new DataImportOptions());
vb
Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
' Create a List object containing string values.
Dim cities As New List(Of String) From {"New York", "Rome", "Beijing", "Delhi"}

' Import the list into the worksheet and insert it vertically, starting with the B1 cell.
worksheet.Import(cities, 0, 1, True)

Dim imageList As New List(Of Byte())()
If True Then
    imageList.Add(imageBytes1)
    imageList.Add(imageBytes2)
End If

' Import the image list into the worksheet and insert it vertically
worksheet.Import(imageList, 0, 2, True, New DataImportOptions())

Import Data from a List of Custom Objects

The following code imports data from a list of custom TestObject class objects.

cs
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);
vb
Private imageBytes1() As Byte = File.ReadAllBytes("images//img.png")
        Private imageBytes2() As Byte = File.ReadAllBytes("images//x-docserver.png")
' ...
            Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
            Dim listDataSource As New System.Collections.ArrayList() From {
                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

View Example: How to Import Data from a DataTable Instance

cs
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);
}
vb
Private imageBytes1() As Byte = File.ReadAllBytes("images//img.png")
        Private imageBytes2() As Byte = File.ReadAllBytes("images//x-docserver.png")
' ...
            Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)

            ' Create a "Products" DataTable object with four columns.
            Dim sourceTable As New DataTable("Products")
            sourceTable.Columns.Add("Product", GetType(String))
            sourceTable.Columns.Add("Price", GetType(Single))
            sourceTable.Columns.Add("Quantity", GetType(Int32))
            sourceTable.Columns.Add("Discount", GetType(Single))
            sourceTable.Columns.Add("Image", GetType(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.v25.2.dll , you can use the following method to import data into a worksheet:

  1. 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.

  2. Call the WorksheetDataBindingCollection.Remove or WorksheetDataBindingCollection.Clear method to remove the binding.