How to: Import Data to a Worksheet

  • 4 min 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.2.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

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

Import Data from a List

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

Import Data from a DataTable

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;
}

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

Spreadsheet_ImportData

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.