How to: Import Data to a Worksheet

• Nov 14, 2018
• 4 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 source for importing data.
• Row and column indexes of the start cell in which you want to insert imported data into the worksheet.
• A Boolean value specifying whether to insert imported data vertically or horizontally. Use this parameter when importing data from a one-dimensional array or list.

Important

The WorksheetExtensions class in defined in the DevExpress.Docs.v17.2.dll assembly. Make sure you added a reference for this library to your project before using the worksheet extensions. Note that use of this library in production code requires a license to the DevExpress Document Server or DevExpress Universal Subscription. Refer to the DevExpress Subscription page for pricing information.

Import Data from Arrays

Note

A complete sample project is available at https://github.com/DevExpress-Examples/spreadsheet-document-server-api-e4339

ImportActions.cs

Worksheet worksheet = workbook.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 the two-dimensional array containing string values.
String[,] names = new String[2, 4]{
{"Ann", "Edward", "Angela", "Alex"},
{"Rachel", "Bruce", "Barbara", "George"}
};

// Import a two-dimensional array into the worksheet and insert it, starting with the B3 cell.
worksheet.Import(names, 2, 1);

ImportActions.vb

Dim worksheet As Worksheet = workbook.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 the two-dimensional array containing string values.
Dim names(,) As String = {
    {"Ann", "Edward", "Angela", "Alex"},
    {"Rachel", "Bruce", "Barbara", "George"}
}

' Import a two-dimensional array into the worksheet and insert it, starting with the B3 cell.
worksheet.Import(names, 2, 1)

Import Data from a List

Note

A complete sample project is available at https://github.com/DevExpress-Examples/spreadsheet-document-server-api-e4339

ImportActions.cs

Worksheet worksheet = workbook.Worksheets[0];
// Create the List object containing string values.
List<string> cities = new List<string>();
cities.Add("New York");
cities.Add("Rome");
cities.Add("Beijing");
cities.Add("Delhi");

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

ImportActions.vb

Dim worksheet As Worksheet = workbook.Worksheets(0)
' Create the List object containing string values.
Dim cities As New List(Of String)()
cities.Add("New York")
cities.Add("Rome")
cities.Add("Beijing")
cities.Add("Delhi")

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

Import Data from a DataTable

Note

A complete sample project is available at https://github.com/DevExpress-Examples/spreadsheet-document-server-api-e4339

ImportActions.cs

Worksheet worksheet = workbook.Worksheets[0];
// Create the "Employees" DataTable object with four columns.
DataTable table = new DataTable("Employees");
table.Columns.Add("FirstN", typeof(string));
table.Columns.Add("LastN", typeof(string));
table.Columns.Add("JobTitle", typeof(string));
table.Columns.Add("Age", typeof(Int32));

table.Rows.Add("Nancy", "Davolio", "recruiter", 32);
table.Rows.Add("Andrew", "Fuller", "engineer", 28);

// Import data from the data table into the worksheet and insert it, starting with the B11 cell.
worksheet.Import(table, true, 0, 0);

// Color the table header.
for (int i = 1; i < 5; i++) {
    worksheet.Cells[10, i].FillColor = Color.LightGray;
}

ImportActions.vb

Dim worksheet As Worksheet = workbook.Worksheets(0)
' Create the "Employees" DataTable object with four columns.
Dim table As New DataTable("Employees")
table.Columns.Add("FirstN", GetType(String))
table.Columns.Add("LastN", GetType(String))
table.Columns.Add("JobTitle", GetType(String))
table.Columns.Add("Age", GetType(Int32))

table.Rows.Add("Nancy", "Davolio", "recruiter", 32)
table.Rows.Add("Andrew", "Fuller", "engineer", 28)

' Import data from the data table into the worksheet and insert it, starting with the B11 cell.
worksheet.Import(table, True, 0, 0)

' Color the table header.
For i As Integer = 1 To 4
    worksheet.Cells(10, i).FillColor = Color.LightGray
Next i

The image below shows the result of executing the code above (the workbook is opened in Microsoft® Excel®).

Tip

To import data into a worksheet, you can bind a read-only data source as described in the How to Bind a Spreadsheet to a List of Objects document and subsequently remove the binding using the WorksheetDataBindingCollection.Remove or WorksheetDataBindingCollection.Clear method.