How to: Import Data to a Worksheet

• Apr 24, 2019
• 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 is defined in the DevExpress.Docs.v19.1.dll assembly. Add this assembly to your project to use the worksheet extensions. You require a license to the DevExpress Office File API or DevExpress Universal Subscription to use this assembly in production code. 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.